DataSet's GetChanges() w/ a webapp working in disconnected mode

[matt]

hello,

i have a web app that allows users to query our oracle db and produce a
dataset of report data. they then have the option to serialize this
data and store it in the database. later, then can re-run the report
and get fresh data. now, they would like to be able to compare the
fresh data to the stored data, getting a break-down of
added/deleted/changed rows.

on the surface, this sounded plausible -- by deserializing the stored
dataset, i have access to both the original dataset & the fresh
dataset. it's my understanding then that these two can be merged w/ a
third dataset to produce the differences, 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 isnt quite working for me. forgetting about the
serializing/deserializing, and working w/ dummy data, i simply cannot
get the GetChanges() methods to work as expected.

here is my code:

public static DataSet GetHistoricData()
{
DataTable dt = new DataTable();
dc = dt.Columns.Add("PersonID",
System.Type.GetType("System.Int32"));
dt.PrimaryKey = new DataColumn[1] { dc };
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();
dc = dt.Columns.Add("PersonID",
System.Type.GetType("System.Int32"));
dt.PrimaryKey = new DataColumn[1] { dc };
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;
}


but the GetChage() results im getting:


- dataset "added" 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)...


any ideas? is what im trying to do possible, or is this not how
GetChanges() was designed to work?


thanks!
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