Merge two dataset into one with nulls in some columns

 ublic DataTable myJoinMethod(DataTable LeftTable, DataTable RightTable, 
			String LeftPrimaryColumn, String RightPrimaryColumn) 
{ 
    //first create the datatable columns 
    DataSet mydataSet = new DataSet(); 
    mydataSet.Tables.Add("  "); 
    DataTable myDataTable = mydataSet.Tables[0]; 

    //add left table columns 
    DataColumn[] dcLeftTableColumns = new DataColumn[LeftTable.Columns.Count]; 
    LeftTable.Columns.CopyTo(dcLeftTableColumns, 0); 

    foreach (DataColumn LeftTableColumn in dcLeftTableColumns) 
    { 
        if (!myDataTable.Columns.Contains(LeftTableColumn.ToString())) 
            myDataTable.Columns.Add(LeftTableColumn.ToString()); 
    } 

    //now add right table columns 
    DataColumn[] dcRightTableColumns = new DataColumn[RightTable.Columns.Count]; 
    RightTable.Columns.CopyTo(dcRightTableColumns, 0); 
 
    foreach (DataColumn RightTableColumn in dcRightTableColumns) 
    { 
        if (!myDataTable.Columns.Contains(RightTableColumn.ToString())) 
        { 
            if (RightTableColumn.ToString() != RightPrimaryColumn) 
                myDataTable.Columns.Add(RightTableColumn.ToString()); 
        } 
    } 
 
    //add left-table data to mytable 
    foreach (DataRow LeftTableDataRows in LeftTable.Rows) 
    { 
        myDataTable.ImportRow(LeftTableDataRows); 
    } 

    ArrayList var = new ArrayList(); //this variable holds the id's which have joined 

    ArrayList LeftTableIDs = new ArrayList(); 
    LeftTableIDs = this.DataSetToArrayList(0, LeftTable); 

    //import righttable which having not equal Id's with lefttable 
    foreach (DataRow rightTableDataRows in RightTable.Rows) 
    { 
        if (LeftTableIDs.Contains(rightTableDataRows[0])) 
        { 
            string wherecondition = "[" + myDataTable.Columns[0].ColumnName + "]='" 
					+ rightTableDataRows[0].ToString() + "'"; 
            DataRow[] dr = myDataTable.Select(wherecondition); 
            int iIndex = myDataTable.Rows.IndexOf(dr[0]); 

            foreach (DataColumn dc in RightTable.Columns) 
            { 
                if (dc.Ordinal != 0) 
                    myDataTable.Rows[iIndex][dc.ColumnName.ToString().Trim()] = 
		    rightTableDataRows[dc.ColumnName.ToString().Trim()].ToString(); 
            } 
        } 
        else 
        { 
            int count = myDataTable.Rows.Count; 
            DataRow row = myDataTable.NewRow(); 
            row[0] = rightTableDataRows[0].ToString(); 
            myDataTable.Rows.Add(row); 
            foreach (DataColumn dc in RightTable.Columns) 
            { 
                if (dc.Ordinal != 0) 
                    myDataTable.Rows[count][dc.ColumnName.ToString().Trim()] = 
		    rightTableDataRows[dc.ColumnName.ToString().Trim()].ToString(); 
            } 
        } 
    } 

    return myDataTable; 
} 

 private void DemonstrateMergeMissingSchema(){
   // Create a DataSet with one table, two columns, and three rows.
   DataSet ds = new DataSet("myDataSet");
   DataTable t = new DataTable("Items");
   DataColumn c1 = new DataColumn("id", Type.GetType("System.Int32"));
   c1.AutoIncrement=true;
   DataColumn c2 = new DataColumn("Item", Type.GetType("System.Int32"));
   // DataColumn array to set primary key.
   DataColumn[] keyCol= new DataColumn[1];
   DataRow r;
   // Create variable for temporary DataSet. 
   DataSet xSet;
   // Add RowChanged event handler for the table.
   t.RowChanged+= new DataRowChangeEventHandler(Row_Changed);
   ds.Tables.Add(t);
   t.Columns.Add(c1);
   t.Columns.Add(c2);
   // Set primary key column.
   keyCol[0]= c1;
   t.PrimaryKey=keyCol;
   // Add ten rows.
   for(int i = 0; i <10;i++){
      r=t.NewRow();
      r["Item"]= i;
      t.Rows.Add(r);
   }
   // Accept changes.
   ds.AcceptChanges();
   PrintValues(ds, "Original values");
   // Change row values.
   t.Rows[0]["Item"]= 50;
   t.Rows[1]["Item"]= 111;
   // Add one row.
   r=t.NewRow();
   r["Item"]=74;
   t.Rows.Add(r);
   // Insert code for error checking. Here we set one row in error.
   t.Rows[1].RowError= "over 100";
   PrintValues(ds, "Modified and New Values");
   // If the table has changes or errors, create a subset DataSet.
   if(ds.HasChanges(DataRowState.Modified | DataRowState.Added)& ds.HasErrors){
      // Use GetChanges to extract subset.
      xSet = ds.GetChanges(DataRowState.Modified|DataRowState.Added);
      PrintValues(xSet, "Subset values");
      // Insert code to reconcile errors. In this case, we'll reject changes.
      foreach(DataTable xTable in xSet.Tables){
         if (xTable.HasErrors){
            foreach(DataRow xRow in xTable.Rows){
               //Console.WriteLine(xRow["Item"]);
                  if((int)xRow["Item",DataRowVersion.Current ]> 100){
                  xRow.RejectChanges();
                  xRow.ClearErrors();
               }
            }
         }
      }
      // Add a column to the xSet. This changes the schema.
      xSet.Tables["Items"].Columns.Add(new DataColumn("newColumn"));
      PrintValues(xSet, "Reconciled subset values");
      // Add values to the rows for each column.
      foreach(DataRow myRow in xSet.Tables["Items"].Rows){
         myRow["newColumn"] = "my new schema value";
      }
      // Merge changes back to first DataSet.
      ds.Merge(xSet,false,System.Data.MissingSchemaAction.Add);
   }
   PrintValues(ds, "Merged Values");
}

private void Row_Changed(object sender, DataRowChangeEventArgs e){
   Console.WriteLine("Row Changed " + e.Action.ToString() + "\t" + e.Row.ItemArray[0]);
}

private void PrintValues(DataSet ds, string label){
   Console.WriteLine("\n" + label);
   foreach(DataTable t in ds.Tables){
      Console.WriteLine("TableName: " + t.TableName);
      foreach(DataRow r in t.Rows){
         foreach(DataColumn c in t.Columns){
            Console.Write("\t " + r[c] );
         }
         Console.WriteLine();
      }
   }
}