Compare two datasets in C#

Question

i have two datasets and i need to compare these two datasets such that if ID does not exist in one table then i need to write insert Query else update query.

For Ex:

Id in One dataset        ID in second Dataset       
1                          1
2                          2
3                          4

I need to insert ID 3 to second dataset.

Here is my code for your reference:

if (ds.Tables[0].Rows.Count > 0 || clientDS.Tables[0].Rows.Count > 0)
        {
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                for (int j = 0; j < clientDS.Tables[0].Rows.Count; j++)
                {
                    if (ds.Tables[0].Rows[i]["Id"].ToString() == clientDS.Tables[0].Rows[j]["Id"].ToString())
                    {
                        client.GetSingleValue("update customers set Name='" + ds.Tables[0].Rows[i]["Name"].ToString() + "',ContactPerson= '" + ds.Tables[0].Rows[i]["ContactPerson"].ToString() + "',Address='" + ds.Tables[0].Rows[i]["Address"].ToString() + "',TinNo='" + ds.Tables[0].Rows[i]["TinNo"].ToString() + "',ContactNo='" + ds.Tables[0].Rows[i]["Contactno"].ToString() + "',Report=  '" + ds.Tables[0].Rows[i]["Report"].ToString() + "',Sync=0,Ids='" + ds.Tables[0].Rows[i]["Id"].ToString() + "' where id='" + ds.Tables[0].Rows[i]["Id"].ToString() + "' ");
                    }
                    else
                    {
                        client.GetSingleValue("insert into customers(id,Name,ContactPerson,Address,TinNo,ContactNo,Report,Sync,Ids) values('" + ds.Tables[0].Rows[i]["Id"].ToString() + "',  '" + ds.Tables[0].Rows[i]["Name"].ToString() + "','" + ds.Tables[0].Rows[i]["ContactPerson"].ToString() + "',  '" + ds.Tables[0].Rows[i]["Address"].ToString() + "',  '" + ds.Tables[0].Rows[i]["TinNo"].ToString() + "',  '" + ds.Tables[0].Rows[i]["Contactno"].ToString() + "',  '" + ds.Tables[0].Rows[i]["Report"].ToString() + "',0,'" + ds.Tables[0].Rows[i]["Id"].ToString() + "')");
                    }
                }
            }
        }

Above code does not work. Pls rectify my issue.

Thanks

Can you outline how your approach is intended to work, basically write down the steps you want the code to take? I can see what is wrong here, but the only way to fix it is to write the actual code for you, it would be better if you could write the steps, and then we can either point out bugs in those steps, or bugs in the implementation. — Lasse V. Karlsen, Aug 11 '09 at 11:43
i think you can do this in all SQL, no need to use the app. layer. — San Jacinto, Aug 11 '09 at 11:45
step 1 : select query from server database step 2 : select query from client database. step 3: compare these two datasets. step 4: If any of the rows missing in Client Database,then insert server rows to client Database. This is all i have to do. — GIRISH GMAIL, Aug 11 '09 at 11:46
go to this link u will get the solution http://www.dotnetspider.com/forum/287063-How-compare-two-dataset-dt1-dt2-store-unmatched-rows-dt3.aspx — , Oct 30 '12 at 06:47
A similar question [Compare dataset or a better idea + c# 2.0](http://stackoverflow.com/questions/1014906/compare-dataset-or-a-better-idea-c-2-0) — rahul, Aug 11 '09 at 11:49

score 7 · Answer 1 · answered Aug 11 '09 at 12:03

7

Use the Merge method:

Dataset2.Merge(Dataset1);

This will insert into Dataset2 any records that are in Dataset1 but not already in Dataset2. Note: your original question suggests that you need to insert records or update matching records from Dataset1, but your comments appear to suggest that you don't actually need to do an update. The Merge method will only insert new records from Dataset1.

answered Aug 11 '09 at 12:03

MusiGenesis

74,184
40
190
334

That's cool. I've never see or used Merge() before. Great technique. – Dave Markle Aug 11 '09 at 12:13
1

Datasets include methods to do pretty much everything you need to do with table-based data, but programmers would generally rather write their own elaborate hacks to do the same thing (this question provides some excellent examples of this phenomenon). – MusiGenesis Aug 11 '09 at 12:19
1

Should both Datasets have the same schema ? – GIRISH GMAIL Aug 11 '09 at 13:12

score 3 · Answer 2 · edited May 22 '12 at 16:19

3

DataSet data1
DataSet data2

data1.Merge(data2,true)

will merge data2 into data1 not overwrite rows with same primary key and add rows with non existing primary key in data1.

data1.Merge(data2,false)

will merge data2 into data1 overwriting all rows and add new rows

edited May 22 '12 at 16:19

Bob Kaufman

12,864
16
78
107

answered Mar 24 '12 at 11:04

Wyxlwiis

31
1

score 2 · Accepted Answer · answered Aug 11 '09 at 12:14

2

I think your error is compaing the Id string using ==. Try using Equals. I'd just use a foreach and select instead:

foreach (DataRow row in ds.Tables[0].Rows)
{
    string filter = string.Format("Id = '{0}'", row["Id"]);
    DataRow[] rows = clientDS.Tables[0].Select(filter);
    if (rows.length == 0)
    {
        // insert here
    }
    else
    {
        // update here
    }
}

answered Aug 11 '09 at 12:14

Noam Gal

3,315
3
36
53

4

This is C#, not Java - `==` works correctly for strings in C#. – MusiGenesis Jun 23 '11 at 12:45

score 1 · Answer 4 · answered Aug 11 '09 at 12:02

Add both these tables (DataTable instances) into a DataSet and add relation.

DataSet ds = new DataSet(); ds.EnforceConstraints = false;

DataTable dt1 = new DataTable("A");
DataTable dt2 = new DataTable("B");

dt1.Columns.Add("ID", typeof(int));
dt1.PrimaryKey = new DataColumn[] {dt1.Columns[0]};
dt1.Rows.Add(1);
dt1.Rows.Add(2);
dt1.Rows.Add(3);

dt2.Columns.Add("ID", typeof(int));
dt2.Rows.Add(1);
dt2.Rows.Add(2);
dt2.Rows.Add(4);

ds.Tables.Add(dt1);
ds.Tables.Add(dt2);
ds.Relations.Add("ID_REL", dt1.Columns[0], dt2.Columns[0]);

foreach (DataRow r in ds.Tables["A"].Rows)
{
    DataRow []child=r.GetChildRows("ID_REL");
    Console.Write(r[0] + " " );
    if (child.Length != 0)
        Console.WriteLine(child[0][0]);

    Console.WriteLine();
}

Compare two datasets in C#

4 Answers4