locked
Compare two DataTables and return 3rd with Difference RRS feed

  • 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