DAAB: Bug or Bad Impl? UpdateDataSet accepts changes for ALL tables

Topics: Data Access Application Block
May 17, 2007 at 8:21 PM
I have a dataset with 5 tables. To store everything in the database, I have to call the UpdateDataSet function for each table in the dataset. The problem is that the first time UpdateDataSet is called, all changes are accepted for the ENTIRE dataset, not just the table specified in the UpdateDataSet specified.

Here is my line of code to update the first table:

_db.UpdateDataSet(ds, firstTableName, insertCmd, updateCmd, deleteCmd, UpdateBehavior.Standard)

So, when I try the next table update,

_db.UpdateDataSet(ds, secondTableName, insertCmd, updateCmd, deleteCmd, UpdateBehavior.Standard)

there are no changes found in the dataset, and nothing is updated. Is this a bug, or am I coding something incorrectly?

This issue was brought up in 2004 here: http://www.csharphelp.com/board2/read.html?f=1&i=32977&t=32977, here: http://forums.asp.net/p/300822/300822.aspx#300822, and here: http://weblogs.asp.net/jdanforth/archive/2004/03/06/84916.aspx (to name a few places). All the posts I've read indicates that this is a bug.

Is there a better way to update multiple tables? Thanks!




May 18, 2007 at 4:10 PM
I double checked this functionality with the following code and it works as expected.

Database db = new SqlDatabase("Data Source=.;Initial Catalog=Test;Integrated Security=True");
DataSet ds = new DataSet();
 
db.LoadDataSet(CommandType.Text, "SELECT * FROM Customers", ds, new string[] { "Customers" });
db.LoadDataSet(CommandType.Text, "SELECT * FROM Orders", ds, new string[] { "Orders" });
 
ds.Tables[0].Rows[0]["Name"] = "John Doe";
ds.Tables[1].Rows[0]["Number"] = "99999";
 
DbCommand customersUpdateCommand =
    db.GetSqlStringCommand("Update Customers Set Name = @Name WHERE CustomerId = @CustomerId");
db.AddInParameter(customersUpdateCommand, "Name", DbType.String, "Name", DataRowVersion.Current);
db.AddInParameter(customersUpdateCommand, "CustomerId", DbType.Int32, "CustomerId", DataRowVersion.Current);
 
DbCommand ordersUpdateCommand =
    db.GetSqlStringCommand("Update Orders Set Number = @Number WHERE OrderId = @OrderId");
db.AddInParameter(ordersUpdateCommand, "Number", DbType.String, "Number", DataRowVersion.Current);
db.AddInParameter(ordersUpdateCommand, "OrderId", DbType.Int32, "OrderId", DataRowVersion.Current);
 
// Get Changes Before
DataTable customerChangesBefore = ds.Tables[0].GetChanges(DataRowState.Modified);
DataTable orderChangesBefore = ds.Tables[1].GetChanges(DataRowState.Modified);
 
db.UpdateDataSet(ds, "Customers", null, customersUpdateCommand, null, UpdateBehavior.Standard);
 
// Get Changes After Customer Update - Orders Still Has a Pending Change
DataTable customerChangesBetween = ds.Tables[0].GetChanges(DataRowState.Modified);
DataTable orderChangesBetween = ds.Tables[1].GetChanges(DataRowState.Modified);
 
db.UpdateDataSet(ds, "Orders", null, ordersUpdateCommand, null, UpdateBehavior.Standard);
 
// Get Changes After All Updates - DataTables are Null.
DataTable customerChangesAfter = ds.Tables[0].GetChanges(DataRowState.Modified);
DataTable orderChangesAfter = ds.Tables[1].GetChanges(DataRowState.Modified);

Regards,

Dave

______________________________

David Hayden
Microsoft MVP C#
May 18, 2007 at 7:40 PM
Thanks for testing that. Because it worked for you, I figured I was doing something wrong.

I checked out the relationships in my strongly typed DataSet. I had checked "Both Relation and Foreign Constraint" on all of my foreign key relationships. Then, I had selected "Cascade" for the Update, Delete, and Accept/Reject Rules. The cascade on the Accept/Reject Rule was giving me the problems. Once the parent table was updated and accepted, all of the child tables were automatically being accepted. Once I set the Accept/Reject Rule to "None," everything worked as expected (mostly--there's another issue with my typed DataSets not allowing null values for integer fields, but that's another topic).

I appreciate your help.
Jan 29, 2008 at 7:08 PM
Dave,

Will this code work for inserting and updating master/detail tables as well?

Thanks,
Ashish
Mar 30, 2008 at 12:05 AM
Hye !

You save me about the same Problem I had...
Please take a look to my post...
What do you think about the lake of control to set the adapter parameter
By default the next members of the Adaptr (in the GetAdapter() of SqlExDatabase in the DAAB)
AcceptChangesDuringFill = true;
AcceptChangesDuringUpdate = true;

After the invocation of the UpdateDataset, must We, with no other way, copy the Rows (with no Unchanged State) to preserve the Rowstate after the delete...for example to inform which Row has been upadted on DB and which not..like :

dt = MydatatabletoUpdate.Getchanges(); //copy of the Rows which will be updated
SqlExDatabase.Updatedataset( MydatatabletoUpdate., DBcommands, etc...)
Displays (dt);//because MydatatabletoUpdate.Getchanges() == null at this moment.....

if I want to preserve the changes How can I do with no data redondance ? What do you think about this optimisation question ?
Mar 30, 2008 at 12:05 AM
Edited Mar 30, 2008 at 12:10 AM
Sorry I had forgotten the link ...

http://www.codeplex.com/entlib/Wiki/View.aspx?title=EntLib4%20Backlog