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;

    }

 

 

 

Answer 1

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;

        }

Answer 2

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.