How to compare two data tables and create a new data table based on the compared results (c#) : The Official Microsoft ASP.NET Forums

Reply

will~

Member

23 Points

48 Posts

How to compare two data tables and create a new data table based on the compared results (c#)

Aug 17, 2009 02:55 PM|LINK

VS2008 c#, asp.net 3.5, ms sql 2008

On the results.aspx page I have two data tables, Results1 and Results2. Results1 are used to hold records that matches with the user selection criteria. Results2 holds records that matches with the user selection criteria with one search criteria omitted.

Results1 are used to populate a gridview on the page. However, I would like to display another gridview on the page, to show records in results2 that are not present in results1.

In the DataTable columns are ("ID"-[Primary key], "Name", "Description"). I am thinking of creating 3 arrays, then go through Data Tables Results1 and Results2 to collect their "ID". Then, comparing the two arrays, and IDs that are not found in array1 to be added to array3.

Then, contents in Array3 needs to query the database in-order to select projects that appears in Results2 but not Results1.

I could list the "ID" in the DataTables but how could I add them in an array or compare them?

DataTable dp = dataset.Tables["Results1"];
 foreach (DataRow rows in dp.Rows)
 {
 Response.Write(" Contents in Results1 ---> " + rows[0].ToString());
 }

DataTable dp2 = dataset.Tables["Results2"];
 foreach (DataRow rows in dp2.Rows)
 {
 Response.Write(" Contents in Results2 ---> " + rows[0].ToString());
 }

Is there a more efficient way to achieve this that you would recommend? Many thanks in advance,

kind Regards,

Will

Reply

PeteNet

All-Star

81342 Points

11398 Posts

Re: How to compare two data tables and create a new data table based on the compared results (c#)

Aug 17, 2009 03:47 PM|LINK

you could probably use the GetChanges method, see an example here: http://www.dotnetspark.com/kb/705-compare-two-datatables-and-get-result.aspx

Regards,
Peter

Reply

will~

Member

23 Points

48 Posts

Re: How to compare two data tables and create a new data table based on the compared results (c#)

Aug 18, 2009 10:42 AM|LINK

Peter,

Many thanks for your quick reply. I tried to use the solution shown, but for some reason the datatable dp3 comes back null. Could you advice if I have got the coding wrong? Many thanks,

In the page load section I declare DataTables dp1, dp2 and dp3. dp1 and dp2 uses the Data tables from SQL query results. I have tested that dp1 and dp2 contains the correct records in gridview and also from the the foreach loop to list the IDs column.

The two datatables dp1 and dp2 are passed to the CompareTwoDataTables method shown in the example you provided. From debug mode, I see dp3 is null when leaving the CompareTwoDataTables method. However, there are definitely differences in dp1 and dp2 (shown below).

            DataTable dp1 = dataset.Tables["ResultsUserSelection"];
            DataTable dp2 = dataset.Tables["ResultsOmitDateSelection"];
            DataTable dp3 = new DataTable();

foreach (DataRow rows in dp1.Rows)
 {
 Response.Write(" Contents in DataTable1 ---> " + rows[0].ToString());
 }

Response.Write(" ");

foreach (DataRow rows in dp2.Rows)
 {
 Response.Write(" Contents in DataTable2 ---> " + rows[0].ToString());
 }

 Response.Write(" Contents in DataTable3~~~~~~~~~~~~~~~~~~");

 CompareTwoDataTable(dp1, dp2);


 foreach (DataRow rows in dp3.Rows)
 {
 Response.Write(" Contents in Results3 ---> " + rows[0].ToString());
 }

~~~~~~

public static DataTable CompareTwoDataTable(DataTable dt1, DataTable dt2)
    {
        dt1.Merge(dt2);
        DataTable d3 = dt2.GetChanges();
        return d3;
    }

~~~~~~

Screen output

Contents in dp1 ---> 107

Contents in dp2 ---> 70
Contents in dp2 ---> 73
Contents in dp2 ---> 74
Contents in dp2 ---> 79
Contents in dp2 ---> 88
Contents in dp2 ---> 90
Contents in dp2 ---> 91
Contents in dp2 ---> 102
Contents in dp2 ---> 107
Contents in dp3 -->

According to the contents dp1 is the results with user selection, dp2 is with the date selection omitted, so dp3 should contains all records in dp2 apart from the record (107). Please could you assist?

Reply

NihirPorecha

Contributor

3400 Points

616 Posts

Re: How to compare two data tables and create a new data table based on the compared results (c#)

Aug 18, 2009 10:55 AM|LINK

I think dt1.GetChanges() will do the trick.

public static DataTable CompareTwoDataTable(DataTable dt1, DataTable dt2)
    {
        dt1.Merge(dt2);
        DataTable d3 = dt1.GetChanges();
        return d3;
    }

Please Mark as answer if it helps you.

My blog

Reply

will~

Member

23 Points

48 Posts

Re: How to compare two data tables and create a new data table based on the compared results (c#)

Aug 18, 2009 11:15 AM|LINK

NihirePorecha,

Thank you for your suggestion. But the DataTable d3 still returns null. It appears that the GetChanges() method has not detected the differences in records.

Reply

NihirPorecha

Contributor

3400 Points

616 Posts

Re: How to compare two data tables and create a new data table based on the compared results (c#)

Aug 18, 2009 11:26 AM|LINK

will~
CompareTwoDataTable(dp1, dp2);

Hope, your actual code is

dp3 = CompareTwoDataTable(dp1, dp2);

Just confirming :-)

Please Mark as answer if it helps you.

My blog

Reply

will~

Member

23 Points

48 Posts

Re: How to compare two data tables and create a new data table based on the compared results (c#)

Aug 18, 2009 11:46 AM|LINK

I have renamed the data tables so I think it would be easier if I include more codes and explain more clearly what I am trying to do.

At the start, two session values with SQL commands are used to query the same database. Two data tables are produced in the process [ResultsUserSelection] and [ResultsOmitDateSelection] and are stored in the same dataset.

Both queried the same table, so the columns gathered are the same, but the second DataTable has some extra records because it has omitted the date in the selection criteria.

These two tables are stored in DataTable dp1 and dp2 before being passed to the CompareTwoDataTable function.

DataTable dp1 = dataset.Tables["ResultsUserSelection"];
DataTable dp2 = dataset.Tables["ResultsOmitDateSelection"];

I would like to compare the DataTable dp1 and dp2, to find out what records has dp2 got that dp1 hasn't.

I have attempted to use the following code to pass the results that have added rows but this does not work (dp3 = null)

DataTable dp3 = dt1.GetChanges(DataRowState.Added);

When I tried the DataRowState.Unchanged, however, dp3 retrieved the values of dt1 merged with dt2. Below shows quick test results of contents of the three data tables if the following are used: DataTable dp3 = dt1.GetChanges(DataRowState.Unchanged);

Contents in dp1 ---> 107

Contents in dp2 ---> 70
Contents in dp2 ---> 73
Contents in dp2 ---> 74
Contents in dp2 ---> 79
Contents in dp2 ---> 88
Contents in dp2 ---> 90
Contents in dp2 ---> 91
Contents in dp2 ---> 102
Contents in dp2 ---> 107
Contents in dp3 -->
Data found in dp3!

Contents in Results3 ---> 107
Contents in Results3 ---> 70
Contents in Results3 ---> 73
Contents in Results3 ---> 74
Contents in Results3 ---> 79
Contents in Results3 ---> 88
Contents in Results3 ---> 90
Contents in Results3 ---> 91
Contents in Results3 ---> 102
Contents in Results3 ---> 107

Notes: 'DataTable 1' has 1 row of record, 'DataTable 2' has 9 rows of records. DataTable '3' has the merged '10' rows of records...how could I create DataTable 3 with only the extra records that appears in DataTable 2 but not in DataTable 1? When I try the following dp3 comes back null.

DataTable dp3 = dt1.GetChanges();
DataTable dp3 = dt1.GetChanges(DataRowState.Added);
DataTable dp3 = dt1.GetChanges(DataRowState.Modified);

I have also tried to pass the Datatable 1 and 2 in different position, and then check for deleted records...but dp3 also returns null.

DataTable dp3 = dt1.GetChanges(DataRowState.Deleted);

What am I missing here? Please could you assist me? Many thanks in advance,

#region This bit populates the DataGrid for gvShortProjectsInfo
//Use the Session stored SQL to query database.

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RES_database"].ConnectionString);
        SqlCommand command = new SqlCommand((Session["SQL_ProjectsDatabase_ProjectSearch"].ToString()), conn);
        SqlCommand command2 = new SqlCommand((Session["SQL_ProjectsDatabase_ProjectSearch2"].ToString()), conn);

        command.CommandType = CommandType.Text;
        command2.CommandType = CommandType.Text;

Response.Write(" Command with all user input criteria ==> " + command.CommandText);
Response.Write(" Command with user input criteria (date omitted) " + command2.CommandText);

        try
        {
            conn.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataSet dataset = new DataSet();
            DataTable myTable = new DataTable("ResultsUserSelection");
            adapter.Fill(myTable);
            dataset.Tables.Add(myTable);

            SqlDataAdapter adapter2 = new SqlDataAdapter(command2);
            DataTable myTable2 = new DataTable("ResultsOmitDateSelection");
            adapter2.Fill(myTable2);
            dataset.Tables.Add(myTable2);

gvShortProjectInfo.DataSource = dataset.Tables["ResultsUserSelection"];
gvShortProjectInfo.DataBind();

DataTable dp1 = dataset.Tables["ResultsUserSelection"];
 DataTable dp2 = dataset.Tables["ResultsOmitDateSelection"];

 foreach (DataRow rows in dp1.Rows)
 {
 Response.Write(" Contents in dp1 ---> " + rows[0].ToString());
 }

Response.Write(" ");

foreach (DataRow rows in dp2.Rows)
 {
 Response.Write(" Contents in dp2 ---> " + rows[0].ToString());
 }

 Response.Write(" Contents in dp3 -->");

 DataTable dp3 = CompareTwoDataTable(dp1, dp2);

if (dp3.Rows.Count > 0)
 {
 Response.Write(" Data found in dp3! ");
 foreach (DataRow rows in dp3.Rows)
 {
 Response.Write(" Contents in Results3 ---> " + rows[0].ToString());
 }
 }
 else
 {
 Response.Write(" There are no data dp3! ");
 }
 }
 catch (Exception ex)
 {
 //this executes if an error occurs
 Response.Write(" ERROR: " + ex.Message + "Exception Thrown [ProjectsFound.aspx]");
 }
 finally
 {
 conn.Close();
 }

 }// Page Load
 #endregion

 public static DataTable CompareTwoDataTable(DataTable dt1, DataTable dt2)
 {
 dt1.Merge(dt2,false);
 DataTable dp3 = dt1.GetChanges(DataRowState.Added);
 return dp3;
 }

Edited by will~ on Aug 18, 2009 03:56 PM
Edited by will~ on Aug 18, 2009 03:57 PM

Reply

PeteNet

All-Star

81342 Points

11398 Posts

Re: How to compare two data tables and create a new data table based on the compared results (c#)

Aug 18, 2009 06:49 PM|LINK

Will,

I'm sorry I completely missed this thread until I noticed a short while ago and did a quick test (unsuccessfully!! - I'll take a look at that particular one laters)

however, if you use DataTable.DefaultView.ToTable(), you would be able to get the distinct rows easily. as you can see in the documentation, one of the the ToTable() method overloads accepts two parameters: distinct (boolean) and an string array of column names...so that's it ...here's a sample I wrote so that you could test away:

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataTable dt1 = GetStringDataTable("Field1,Field2", 4);
                DataTable dt2 = GetStringDataTable("Field1,Field2", 7);


                DataTable dt3 = CompareTwoDataTables(dt1, dt2);
                if (dt3 != null && dt3.Rows.Count > 0)
                {
                    //may want to use a control, like the GridView code below, to visually see the difference
                    //GridView1.DataSource = dt3;
                    //GridView1.DataBind();
                }
            }
        }


        public static DataTable CompareTwoDataTable(DataTable dt1, DataTable dt2)
        {
            dt1.Merge(dt2);
            //DataTable d3 = dt1.GetChanges();
            DataTable d3 = dt1.DefaultView.ToTable(true, "Field1");
            return d3;
        }


        //this function simply builds up dummy data
        public static DataTable GetStringDataTable(string CSVFieldName, int length)
        {
            DataTable dt = new DataTable();


            string[] csvs = CSVFieldName.Split(',');


            if (csvs.Length <= 0)
            {
                return dt;
            }


            for (int i = 0; i < csvs.Length; i++)
            {
                DataColumn dc = new DataColumn();
                dc.DataType = Type.GetType("System.String");
                dc.ColumnName = csvs[i].ToString();
                dt.Columns.Add(dc);
            }


            for (int j = 0; j <= length; j++)
            {
                DataRow row = dt.NewRow();
                foreach (DataColumn dc in dt.Columns)
                {
                    row[dc.Ordinal] = dc.ColumnName.ToString() + j.ToString();
                }
                dt.Rows.Add(row);
            }


            return dt;
        }

note: I don't think distinct is going to help you either! (yeah, I did a test)...so finally I wrote a function that you could use:

        /// <remarks>
        /// *Both Dt1 and Dt2 need to have the same structure
        /// </remarks>
        private void CompareTables(DataTable Dt1, DataTable Dt2, ref DataTable DtInBoth, ref DataTable DtNotInBoth)
        {
            DtInBoth = new DataTable();
            DtInBoth = Dt1.Clone();
            DtNotInBoth = new DataTable();
            DtNotInBoth = Dt1.Clone();
            Object[] vals = new Object[1];
            DataTable dtTableCompared = new DataTable();
            DataTable dtTableSearched = new DataTable();
            if (Dt1.Rows.Count >= Dt2.Rows.Count)
            {
                dtTableCompared = Dt1.Copy();
                dtTableSearched = Dt2.Copy();
            }
            else if (Dt1.Rows.Count < Dt2.Rows.Count)
            {
                dtTableCompared = Dt2.Copy();
                dtTableSearched = Dt1.Copy();
            }

            foreach (DataRow row in dtTableCompared.Rows)
            {
                vals[0] = row["Field1"];
                if (dtTableSearched != null && dtTableSearched.Rows.Count >= 0)
                {
                    dtTableSearched.DefaultView.Sort = "Field1";
                    int intRowFound = dtTableSearched.DefaultView.Find(vals[0]);
                    if (intRowFound <= -1)
                    {
                        DataRow newRow = DtNotInBoth.NewRow();
                        newRow.ItemArray = row.ItemArray;
                        DtNotInBoth.Rows.Add(newRow);
                    }
                    else
                    {
                        DataRow newRow = DtInBoth.NewRow();
                        newRow.ItemArray = row.ItemArray;
                        DtInBoth.Rows.Add(newRow);
                    }
                }
            }
            foreach (DataRow row in dtTableSearched.Rows)
            {
                vals[0] = row["Field1"];
                if (dtTableCompared != null && dtTableCompared.Rows.Count >= 0)
                {
                    dtTableCompared.DefaultView.Sort = "Field1";
                    int intRowFound = dtTableCompared.DefaultView.Find(vals[0]);
                    if (intRowFound <= -1)
                    {
                        DataRow newRow = DtNotInBoth.NewRow();
                        newRow.ItemArray = row.ItemArray;
                        DtNotInBoth.Rows.Add(newRow);
                    }
                    else
                    {
                        DataRow newRow = DtInBoth.NewRow();
                        newRow.ItemArray = row.ItemArray;
                        DtInBoth.Rows.Add(newRow);
                    }
                }
            }

        }

and you could call it like this:

DataTable dt3 = new DataTable();
DataTable dt4 = new DataTable();

CompareTables(dt1, dt2, ref dt3, ref dt4);

where dt1 and dt2 are your DataTables to compare, and dt3 is a DataTable that would give you the common rows in both whereas dt4 would give you the difference - which is what you want, and works perfectly. note that these two DataTables are passed in by reference.also, I've hard-coded the field to search by "Field1" (still using the dummy data that I posted earlier) - you need to change that in the function OR pass it in to the function as an additional parameter.

here's another link that I found that uses data relations but looked longer code:

http://kseesharp.blogspot.com/2007/12/compare-2-datatables-and-return-3rd.html

Regards,
Peter

Edited by PeteNet on Aug 19, 2009 02:33 AM
Marked as answer by will~ on Aug 20, 2009 01:41 PM

Reply

will~

Member

23 Points

48 Posts

Re: How to compare two data tables and create a new data table based on the compared results (c#)

Aug 20, 2009 01:39 PM|LINK

Peter,

Many thanks for your solution. It compares the two data tables (dt1 and dt2) and gives the difference in dt4. This works perfectly. The common rows are also found correctly and displayed in dt3, but both rows from dt1 and dt2 are displayed. Is there a way only to make it not to display duplicated rows? This is not essential as the content in d4 is what I was trying to achieve. I really appreciate the time you have spent in helping me. Many thanks,

kind Regards,

Will

Reply

vivek_cs

Member

65 Points

172 Posts

Re: How to compare two data tables and create a new data table based on the compared results (c#)

May 19, 2010 02:34 PM|LINK

I tried the same as suggested by peter in this thread and that work for Will.

The only difference I can see is that in my case, Column DataType is string and One column name is different in both table. but thats not the column I would be comparing.

Table Structure - t1

GroupId integer

Description varchar

Table - t2

Id integer

Description varchar

I am looking for records which are in t1 but not in t2

So all records which exist in t2 should not be there.

Thanks,

Vivek

How to compare two data tables and create a new data table based on the compared results (c#) RSS

9 replies

will~

How to compare two data tables and create a new data table based on the compared results (c#)

PeteNet

Re: How to compare two data tables and create a new data table based on the compared results (c#)

will~

Re: How to compare two data tables and create a new data table based on the compared results (c#)

NihirPorecha

Re: How to compare two data tables and create a new data table based on the compared results (c#)

will~

Re: How to compare two data tables and create a new data table based on the compared results (c#)

NihirPorecha

Re: How to compare two data tables and create a new data table based on the compared results (c#)

will~

will~

Re: How to compare two data tables and create a new data table based on the compared results (c#)

PeteNet

Re: How to compare two data tables and create a new data table based on the compared results (c#)

will~

Re: How to compare two data tables and create a new data table based on the compared results (c#)

vivek_cs

Re: How to compare two data tables and create a new data table based on the compared results (c#)

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft