DataSet help -- .Merge() & .GetChanges()

[matt]

hello,

i have two datasets (only one DataTable each) representing the same set
of data, but from two different historic points of time. so they are of
the same structure, but there may have been rows added, removed, or
changed. each row has a unique ID column. i need a way to show my users
the row differences between the two. (much like using Visual Source
Safe's "Show Differences" on a local vs server versions of a file)

i read with glee that there is built-in support for getting the
differences between datasets. great! i read its done like so:


//get the two datasets data
DataSet historic = GetHistoricData();
DataSet current = GetCurrentData();

//make merge container
DataSet merged = new DataSet();

//seed w/ orig data
merged.Merge(historic);
merged.AcceptChanges();

//merge w/ new data
merged.Merge(current);

//and get the differences
DataSet added = merged.GetChanges(DataRowState.Added);
DataSet deleted = merged.GetChanges(DataRowState.Deleted);
DataSet modified = merged.GetChanges(DataRowState.Modified);


....however, that doesnt *quite* seem to work for me. in the above, only
dataset "added" has data, and the data in it is the exactly whats in
dataset "current".

here are the functions that make some test data:


public static DataSet GetHistoricData()
{
DataTable dt = new DataTable();
dt.Columns.Add("PersonID", System.Type.GetType("System.Int32"));
dt.Columns.Add("FirstName", System.Type.GetType("System.String"));
dt.Columns.Add("LastName", System.Type.GetType("System.String"));
dt.Columns.Add("Comments", System.Type.GetType("System.String"));

dt.Rows.Add( new Object[4] { 66, "matt", "smith", "shipped to
office" } );
dt.Rows.Add( new Object[4] { 67, "jonnie", "tyler", "shipped to
home" } );
dt.Rows.Add( new Object[4] { 68, "cory", "default", "shipped to
home" } );

DataSet ds = new DataSet();
ds.Tables.Add(dt);
return ds;
}

//''''''''''''''''''''''''''''''''''''''''''''''''''''

public static DataSet GetCurrentData()
{
DataTable dt = new DataTable();
dt.Columns.Add("PersonID", System.Type.GetType("System.Int32"));
dt.Columns.Add("FirstName", System.Type.GetType("System.String"));
dt.Columns.Add("LastName", System.Type.GetType("System.String"));
dt.Columns.Add("Comments", System.Type.GetType("System.String"));

//row stayed the same:
dt.Rows.Add( new Object[4] { 66, "matt", "smith", "shipped to
office" } );

//(row for PersonID 67 was deleted)

//row changed:
dt.Rows.Add( new Object[4] { 68, "cory", "default", "shipped to
OFFICE" } );

//new row added:
dt.Rows.Add( new Object[4] { 69, "rich", "demel", "lives in hawaii"
} );

DataSet ds = new DataSet();
ds.Tables.Add(dt);
return ds;
}


what am i doing wrong? would love to figure this technique out.


thanks!
matt

 

[matt]

what am i doing wrong?

ok, i noticed one thing -- i needed to designate a primary key column
on the two datasets' tables, in the GetXXXData() methods. so i did
this, using the "PersonID" column.

that improved the results, but only slightly:

- dataset "added" now correctly reports the addition of the new row
(person 69).

- dataset "modified" does correctly list person 68 (comments changed),
but it also lists person 66, which had no change.

- dataset "deleted" doesnt have any rows. i was hoping it would list
rows present in "historic" but lacking in "current" (indicating they
had been removed)...


getting closer, but not there yet. any info appreciated.


thanks,
matt

 

[the principal]

I tried this same thing and hated it, and therfore trashed it from
memory
especially after I read of .net 2.0 DataTable.Merge() Method.


I had good success using a class (SQLOps) pieced together from the
methods in this blog

http://weblogs.sqlteam.com/davidm/archive/2004/01/19/739.aspx

I hope it is still all there with a little digging.

notice the Difference operator!

sorry not to be a direct help, but at least it might lead to a work
around.

 

[matt]

I tried this same thing and hated it, and therfore trashed it from
memory
especially after I read of .net 2.0 DataTable.Merge() Method.

im not familar w/ 2.0's DataTable.Merge() method -- what makes it
special or different than 1.1 method of the same name?

I had good success using a class (SQLOps) pieced together from the
methods in this blog

ill check it out, thanks.


matt

 

[matt]

im not familar w/ 2.0's DataTable.Merge() method -- what makes it
special or different than 1.1 method of the same name?

ah my bad -- 1.1 has a .GetChanges() on the datatable, but not a merge.


matt

 

[John Sitka]

This is an article I read back then,
http://www.knowdotnet.com/articles/datasetmerge.html
from one of the good people here.

 

[matt]

I had good success using a class (SQLOps) pieced together from the
methods in this blog

http://weblogs.sqlteam.com/davidm/archive/2004/01/19/739.aspx

THANK YOU!! that came in very handy. using it, i was able to write the
rest of the code i needed to give me a simple method that takes in
Current and Historic tables (same-schema), and then returns a dataset
of 3 tables: added rows, deleted rows, and modified rows.


matt

 

[matt]

ok, i have a solution for this. never did get the dataset's built-in
methods to do the job.

i found another post that lead to this blog:

http://weblogs.sqlteam.com/davidm/archive/2004/01/19/739.aspx

....which got me half-way there. that nifty little function gives you
the differences between two same-schema tables. that is, rows from one
that are not in the second.

by running that on the "current" and "historic" datasets, i can get
tables of added & deleted rows. then, using another helper i wrote, i
can determine which delta rows were actual modifications. using these
together gets me a clean black box method that, when given two
datatables, produces a three-tabled dataset: one each for added,
deleted, and modified rows. sweet.


matt

 

[the principal]

happy that it worked out. The concept of your comparison and Merge
posts rang familiar.
I was seriously concerned that it would be a waste of your time because
it's kind of a redirect and I couldn't remember all that was involved.
Like reliance on the other methods, and the fact that
Difference(DT1,DT2) is not the same
as Difference(D2,D1) but looks like you found the trick.

What's interesting is the total lack of this technique out there, kind
of shows the 'gap'
between relational concepts and Object/Collection based programming,
much thanks to the Blog and Blogger.

Maybe LINQ will bridge some of that thinking gap.

a fun read here. Kind of puts it in perspective

http://weblogs.sqlteam.com/davidm/archive/2005/10/26/8106.aspx