Answered by:
Compare two DataTables and return 3rd with Difference

Question
-
User-1825561198 posted
Hello :
I have two DataTables, with Different Columns. Some of the Columns are same between them.
i am looking to do this.
It is also refered to as MINUS and is simply all the rows that are in the First table but not the Second.These three colums are same between the two tables.
string[] strCols = { "CustomerID", "CustomerName", "CustomerCity" };I tried this but it seems to return all the Rows from the Table1.
Kindly help with syntax
Thanks,
protected void Page_Load(object sender, EventArgs e) { DataTable dataTable3 = Difference(Table1.DefaultView.ToTable("FirstTable", true, strColsToExport), Table2.DefaultView.ToTable("SecondTable", true, strColsToExport)).Copy(); } public static DataTable Difference(DataTable First, DataTable Second) { //Create Empty Table DataTable table = new DataTable("Difference"); //Must use a Dataset to make use of a DataRelation object using (DataSet ds = new DataSet()) { //Add tables ds.Tables.AddRange(new DataTable[] { First.Copy(), Second.Copy() }); //Get Columns for DataRelation DataColumn[] firstcolumns = new DataColumn[ds.Tables[0].Columns.Count]; for (int i = 0; i < firstcolumns.Length; i++) { firstcolumns[i] = ds.Tables[0].Columns[i]; } DataColumn[] secondcolumns = new DataColumn[ds.Tables[1].Columns.Count]; for (int i = 0; i < secondcolumns.Length; i++) { secondcolumns[i] = ds.Tables[1].Columns[i]; } //Create DataRelation DataRelation r = new DataRelation(string.Empty, firstcolumns, secondcolumns, false); ds.Relations.Add(r); //Create columns for return table for (int i = 0; i < First.Columns.Count; i++) { table.Columns.Add(First.Columns[i].ColumnName, First.Columns[i].DataType); } //If First Row not in Second, Add to return table. table.BeginLoadData(); foreach (DataRow parentrow in ds.Tables[0].Rows) { DataRow[] childrows = parentrow.GetChildRows(r); if (childrows == null || childrows.Length == 0) table.LoadDataRow(parentrow.ItemArray, true); } table.EndLoadData(); } return table; }
Monday, March 29, 2010 4:26 PM
Answers
-
User2126603731 posted
Hi, I think it's because you are only getting the difference result from the first table. Pls try below which is modified a bit.
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { DataTable Table1, Table2; Table1 = new DataTable("Table1"); Table1.Columns.Add("Col1"); Table1.Columns.Add("Col2"); Table1.Columns.Add("Col3"); Table2 = Table1.Clone(); Table2.TableName = "Table2"; for (int i = 1; i <= 4; i++) { DataRow dr = Table1.NewRow(); dr["Col1"] = "Table1 Row " + i.ToString() + " Col 1"; dr["Col2"] = "Table1 Row " + i.ToString() + " Col 2"; dr["Col3"] = "Table1 Row " + i.ToString() + " Col 3"; Table1.Rows.Add(dr); } for (int i = 1; i <= 4; i++) { DataRow dr = Table2.NewRow(); dr["Col1"] = "Table2 Row " + i.ToString() + " Col 1"; dr["Col2"] = "Table2 Row " + i.ToString() + " Col 2"; dr["Col3"] = "Table2 Row " + i.ToString() + " Col 3"; Table2.Rows.Add(dr); } //Add duplicate row DataRow drNew = Table2.NewRow(); drNew["Col1"] = "Table1 Row 1" + " Col 1"; drNew["Col2"] = "Table1 Row 1" + " Col 2"; drNew["Col3"] = "Table1 Row 1" + " Col 3"; Table2.Rows.Add(drNew); DataTable dataTable3 = Difference(Table1.DefaultView.ToTable("FirstTable"), Table2.DefaultView.ToTable("SecondTable")).Copy(); } } public static DataTable Difference(DataTable First, DataTable Second) { //Create Empty Table DataTable table = new DataTable("Difference"); //Must use a Dataset to make use of a DataRelation object using (DataSet ds = new DataSet()) { //Add tables ds.Tables.AddRange(new DataTable[] { First.Copy(), Second.Copy() }); //Get Columns for DataRelation DataColumn[] firstcolumns = new DataColumn[ds.Tables[0].Columns.Count]; for (int i = 0; i < firstcolumns.Length; i++) { firstcolumns[i] = ds.Tables[0].Columns[i]; } DataColumn[] secondcolumns = new DataColumn[ds.Tables[1].Columns.Count]; for (int i = 0; i < secondcolumns.Length; i++) { secondcolumns[i] = ds.Tables[1].Columns[i]; } //Create DataRelation DataRelation r1 = new DataRelation(string.Empty, firstcolumns, secondcolumns, false); ds.Relations.Add(r1); DataRelation r2 = new DataRelation(string.Empty, secondcolumns, firstcolumns, false); ds.Relations.Add(r2); //Create columns for return table table = First.Clone(); //If First Row not in Second, Add to return table. table.BeginLoadData(); foreach (DataRow parentrow in ds.Tables[0].Rows) { DataRow[] childrows = parentrow.GetChildRows(r1); if (childrows == null || childrows.Length == 0) table.LoadDataRow(parentrow.ItemArray, true); } foreach (DataRow parentrow in ds.Tables[1].Rows) { DataRow[] childrows = parentrow.GetChildRows(r2); if (childrows == null || childrows.Length == 0) table.LoadDataRow(parentrow.ItemArray, true); } table.EndLoadData(); } return table; }
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, March 30, 2010 12:55 AM -
User-1825561198 posted
Hello Thanks for you reply. My code was just perfect. Except that I had to do ToString().Trim() on the fields before comparing. It actually had white space around it.
Thanks for your help.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, April 8, 2010 2:34 PM
All replies
-
User2126603731 posted
Hi, I think it's because you are only getting the difference result from the first table. Pls try below which is modified a bit.
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { DataTable Table1, Table2; Table1 = new DataTable("Table1"); Table1.Columns.Add("Col1"); Table1.Columns.Add("Col2"); Table1.Columns.Add("Col3"); Table2 = Table1.Clone(); Table2.TableName = "Table2"; for (int i = 1; i <= 4; i++) { DataRow dr = Table1.NewRow(); dr["Col1"] = "Table1 Row " + i.ToString() + " Col 1"; dr["Col2"] = "Table1 Row " + i.ToString() + " Col 2"; dr["Col3"] = "Table1 Row " + i.ToString() + " Col 3"; Table1.Rows.Add(dr); } for (int i = 1; i <= 4; i++) { DataRow dr = Table2.NewRow(); dr["Col1"] = "Table2 Row " + i.ToString() + " Col 1"; dr["Col2"] = "Table2 Row " + i.ToString() + " Col 2"; dr["Col3"] = "Table2 Row " + i.ToString() + " Col 3"; Table2.Rows.Add(dr); } //Add duplicate row DataRow drNew = Table2.NewRow(); drNew["Col1"] = "Table1 Row 1" + " Col 1"; drNew["Col2"] = "Table1 Row 1" + " Col 2"; drNew["Col3"] = "Table1 Row 1" + " Col 3"; Table2.Rows.Add(drNew); DataTable dataTable3 = Difference(Table1.DefaultView.ToTable("FirstTable"), Table2.DefaultView.ToTable("SecondTable")).Copy(); } } public static DataTable Difference(DataTable First, DataTable Second) { //Create Empty Table DataTable table = new DataTable("Difference"); //Must use a Dataset to make use of a DataRelation object using (DataSet ds = new DataSet()) { //Add tables ds.Tables.AddRange(new DataTable[] { First.Copy(), Second.Copy() }); //Get Columns for DataRelation DataColumn[] firstcolumns = new DataColumn[ds.Tables[0].Columns.Count]; for (int i = 0; i < firstcolumns.Length; i++) { firstcolumns[i] = ds.Tables[0].Columns[i]; } DataColumn[] secondcolumns = new DataColumn[ds.Tables[1].Columns.Count]; for (int i = 0; i < secondcolumns.Length; i++) { secondcolumns[i] = ds.Tables[1].Columns[i]; } //Create DataRelation DataRelation r1 = new DataRelation(string.Empty, firstcolumns, secondcolumns, false); ds.Relations.Add(r1); DataRelation r2 = new DataRelation(string.Empty, secondcolumns, firstcolumns, false); ds.Relations.Add(r2); //Create columns for return table table = First.Clone(); //If First Row not in Second, Add to return table. table.BeginLoadData(); foreach (DataRow parentrow in ds.Tables[0].Rows) { DataRow[] childrows = parentrow.GetChildRows(r1); if (childrows == null || childrows.Length == 0) table.LoadDataRow(parentrow.ItemArray, true); } foreach (DataRow parentrow in ds.Tables[1].Rows) { DataRow[] childrows = parentrow.GetChildRows(r2); if (childrows == null || childrows.Length == 0) table.LoadDataRow(parentrow.ItemArray, true); } table.EndLoadData(); } return table; }
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, March 30, 2010 12:55 AM -
User-1825561198 posted
Hello Thanks for you reply. My code was just perfect. Except that I had to do ToString().Trim() on the fields before comparing. It actually had white space around it.
Thanks for your help.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, April 8, 2010 2:34 PM