Comparing DataSets

[Alex]

We're trying to minimize the number of postbacks to the server in our web
application by allowing users to change rows in a grid on the client side.
When they click the submit button, we need to update the changes to the
database.

I have the original DataSet and I have the updated one which is built off
the current grid. I now need to compare and find out what was updated,
deleted and inserted before proceeding with the database updates.

Both have the same schema and a uniqie index. Other than looping for each
row and comparing the two, is there an easier and simpler way to get the
changes? I tried merge but it didn't pick up the changes at all.

Here is the code I'm using. All the records are unchanged so dsDifference is
always empty.

// Get from session.
DataSet dsOriginal = (DataSet)Session["ds"];

// Create a DS using grid data.
DataSet dsChanged = base.GridToDataSet(UltraWebGrid1);

// Add primary keys.
dsOriginal.Tables[0].PrimaryKey = new DataColumn[] {
dsOriginal.Tables[0].Columns["ObjId"] };
dsChanged.Tables[0].PrimaryKey = new DataColumn[] {
dsChanged.Tables[0].Columns["ObjId"] };

// Merge.
dsOriginal.Merge(dsChanged);

// Create the delta DS.
DataSet dsDifference = new DataSet();
dsDifference = dsOriginal.Clone();
foreach(DataRow row in dsOriginal.Tables[0].Rows)
{
if(row.RowState == DataRowState.Modified)
dsDifference.Tables[0].Rows.Add(row);
}

Thanks
Alex

 

[Cor Ligthert[MVP]]

Alex,

As you would have used a Microsoft DataGrid then the edit was done like you
in fact wrote on the Server side.

Now you are using in fact a raw kind of data without any status to compare
with your dataset.

As your grid cannot give back a rowstatus, then what other possibility you
suspect then simple comparing row by row and item by item?

Cor

 

[Alex]

I don't think it has to do with the UI. Actually, I'm using Infragistics
grids. Edits can be done on the client side with no post backs.

But at the end, you'll end up with two datasets of the same schema, the
original and an updated one. I would have thought that by merging the updated
one into the original one would change the row state so that I could get the
delta rows and act on them.

 

[Cor Ligthert[MVP]]

Alex,

For merging is in fact only one thing important.
(The dataset should have exactly the same DataTable names and columnames.)

As you are sure that you get back a DataSet with RowStatusses set, then it
should be possible.

http://www.vb-tips.com/dbpages.aspx?Search=merge

Cor

 

[Alex]

Well, I found a bug in my previous code. I forgot to add Acceptchanges(). But
here is what I get at the end. All my DataSets are the same. Same schema
(same table names, same primary keys etc). After the merge, when I take a
look at the RowState, each one is Modified.

I have three datasets: dsOriginal, dsUpdated and dsMerged. dsUpdated
contains the changes. I copy dsOriginal into dsMerge. I do a
dsMerge.AcceptChanges() and then a do dsMerge.Merge(dsUpdated). but the
RowState of all the rows in dsMerge are set to Modified which doesn't help
me. I'm sure I'm doing something wrong in the code but I can't find it.

DataSet dsOriginal = (DataSet)Session["ds"];
dsOriginal.Tables[0].PrimaryKey = new DataColumn[] {
dsOriginal.Tables[0].Columns["ObjId"] };

DataSet dsMerged = new DataSet();
dsMerged = dsOriginal.Copy();
dsMerged.Tables[0].PrimaryKey = new DataColumn[] {
dsMerged.Tables[0].Columns["ObjId"] };

DataSet dsUpdated = dsOriginal.Clone();
dsUpdated.Tables[0].PrimaryKey = new DataColumn[] {
dsUpdated.Tables[0].Columns["ObjId"] };
dsUpdated = base.GridToDataSet(UltraWebGrid1, dsUpdated);
dsUpdated.Tables[0].TableName = dsOriginal.Tables[0].TableName;

UltraWebGrid2.DataSource = dsOriginal;
UltraWebGrid2.DataBind();

dsMerged.AcceptChanges();

// Merge.
dsMerged.Merge(dsUpdated);


for(int i = 0; i < dsMerged.Tables[0].Rows.Count - 1; i++)
{
if(dsMerged.Tables[0].Rows.RowState ==
DataRowState.Modified)
Response.Write("Row " + i.ToString() + " was
changed<br>");

}

 

[Cor Ligthert[MVP]]

Alex,

You should not do Acceptchanges, the method has the most misleading name
ever created (you absolutely don't see it in Linq by instance)

Acceptchanges is reseting all rowsstates (and something more not important
for this message)

Therefore you never can do any update with your dataset anymore.

Is it not possible that your Grid gives back a DataSet where all rows have
the rowstate modified?

Cor

 

[Alex]

The acceptchanges is done when I populate the DS before the merge otherwise
all RowStates would say Added. I want to start from scratch, so AcceptChanges
sets it to Unchanged.

I don't think I can get the grid object from the client to the server side,
but I'll investigate. One of the options we have is to update a "RowState"
hidden column with the changes and then only get the rows with a specific
value, but the merge would have been nice to get it to work. It's simple,
easy and part of the framework.

Thanks for your help.