Changing the behavior of the DataAdapter used in the DataBase Class

Topics: Data Access Application Block
Apr 2, 2007 at 8:23 PM
First of all my apologies for the long explanation but couldn't make it shorter... ;)

Anyway, I've been trying to find a way to change the behavior of the DbDataAdapter that gets created inside the DataBase abstract class during the execution of the UpdateDataSet method, but to no avail.
The reason for this is that this later method doesn't work well when one wants to pass a copy (or just the changes) of a DataSet on a client to some method on your business layer class. In this case the DataAdapter performs an AcceptChanges on the updated rows on the DataSet that gets passed to DataBase.UpdateDataSet method. This behavior prevents the client from merging the resulting "updated" DataSet back to the original DataSet, because at this point all rows changes where accepted. The following pseudo-code might better explain the problem:

Code on the client:
void MethodOnTheClient()
{
DataSet ds = CreateDataSetWithLotsOfRowsThatIDontWantToPassToMyBLL();

PerformSomeChangesToTheDataSet(ds); // Runs on the client

DataSet dsChanges = ds.GetChanges();

// The following calls a method on the Business Layer (through a WebService or
//Remoting Call)
MyBusinessObject.SaveDataSetChanges(dsChanges);

// Merge the changes from the DataBase back to the original DataSet to get for
// instance identity values, etc.
// Because UpdateDataSet's DataAdapter calls AcceptChanges on the updated rows
// the following Merge will not function as expected
ds.Merge(dsChanges);
}

Code on the business layer:

void SaveDataSetChanges(DataSet ds)
{
DataBase db = DatabaseFactory.CreateDatabase();

List<DataTable> orderedListOfTables = GetTablesOrderedForUpdate(ds);

foreach (DataTable table in orderedListOfTables)
{
// The following call will Perform AcceptChanges on the updated rows
db.UpdateDataSet(ds, table.TableName, AddCommand, UpdateCommand, DeleteCommand, UpdateBehavior);
}
}

A possible approach to change this behavior would be to declare the protected DataBase.GetDataAdapter virtual so that a derived class could intercept it's creation and act accordingly.
In my case I'd just hook up on RowUpdated event of the DataAdapter and there prevent the AcceptChanges to be called.

Can please someone comment on the possibility of changing the method signature to make this possible? Or at least suggest an alternative course of action.

Thanks in advance,
Vitor Castro
Apr 3, 2007 at 12:46 AM
Hi Vitor,

I guess I have the same problem, I noticed that after calling the UpdateDataSet the Dataset that is passed to this method cames out with the rows Accepted (if no errors occured), and it seems there's no way to avoid this behaviour in the current verision of the DAAB (setting the UpdateBehaviour to Continue on the UpdateDataset only work in cases where errors occur and not when there're no errors).

Other solution for the problem could be to have an UpdateBehavior option like SkipAllRows. This option could then be used on the OnSqlRowUpdated of the SqlDatabase class to set the SqlRowUpdatedEventArgs Status = UpdateStatus.SkipCurrentRow even if the RecordsAffected are not equal to zero and even if there're no errors. By using this the DataAdaptar class will not AcceptChanges on the rows that it sucessfully updates which give us the chance to merge back these changes to the orignal Dataset withou problems. (or change the 'private void OnSqlRowUpdated' to 'protected virtual OnSqlRowUpdated').

If someone from the DAAB team could look to this and provide a better solution this could avoid us to have to reimplment the SqlDatabase class (has this seems to be the only solution for the moment).

Thanks,
Nelson Morais
Apr 3, 2007 at 2:17 PM
Edited Apr 3, 2007 at 3:44 PM
I haven't really looked into the issue, but unfortunately this will not be looked at for Enterprise Library 3.0 as it is being packaged to go out the door any day now.

My recommendation would be to not change the existing behavior in SqlDatabase, but either:

1) Create your own custom Database Class that derives from SqlDatabase and override / add new behavior, or
2) Copy the SqlDatabase Class entirely, rename it to your own custom version, and override / add the new behavior

The problem with modifying the current SqlDatabase is that you end up having to manage those changes in new versions of Enterprise Library and also don't have access to the default functionality that SqlDatabase provided out-of-box. You might end up inheriting a project that expects the current UpdateDataSet functionality to work as is and your custom modification may create unexpected results.

Regards,

Dave

_____________________

David Hayden
Microsoft MVP C#
Apr 4, 2007 at 8:31 AM
Edited Apr 4, 2007 at 8:32 AM
Hi David, thanks for the quick reply.

I completely understand that at this point of the cycle these kinds of changes can't be made. Nevertheless, I would like to make one or two comments on the suggestions.

In fact the approach that I followed was the one you suggest in 1), but regarding this extension approach there are a few things that EntLib could supply to make our life easier.
For instance, if one needs more control over the update process one might be tempted to get a DataAdapter from the DataBase class and use it directly to one's needs. And this would be fine if the DataBase class would then expose some of it's internal/non virtual methods and classes.
What I mean on this later scenario is, for example, I would have to re implement all the code that deals with Transaction control under a TransactionScope, which the DataBase class already does for itself (on the UpdateDataset method), but doesn't expose these functionalities to its sub-classes.

Another possibility would be for the DataBase class to supply UpdateDataset overloads that allow more fine control over what gets updated, because the implemented behavior doesn't work correctly if you have a DataSet with multiple tables with multiple DataRow operations. In this case the DataBase class relies on the DataAdapter's judgment regarding the order of the updates, which in the case of DataSets with multiple related tables fails miserably. And performance wise you can't just separate your changes (inserts, updates and deletes) in several DataSets and feed those in the correct order to the UpdateDataset method and then merge the final result into your original DataSet.

These are just some of the difficulties that I faced while trying to use the DAAB in the real world.
IMHO I think that the users of the EntLib could get a much more pleasing and much less error prone job in the cases where extending the library is the only practical choice. And with just some tweaks and small changes to the existing (excellent I must say) piece of software that this library is I think that this could be an even better product.

What I ask is that these difficulties might be taken in consideration when an update to the Lib is planned, as I think that these changes would be beneficial to a lot of people. Please feel free to contact me should my humble suggestions be considered pertinent.

Thanks for your time,

Vitor Castro