Handling SqlException during DataAdapter.Update

  • Thread starter Thread starter Ramon de Klein
  • Start date Start date
R

Ramon de Klein

I am using ADO.NET datasets to transfer information
between my client and business logic. When I fill the
dataset and some dataset contraint is triggered, then the
FillError event is triggered, that can be handled. My
database contains some constraints that can be triggered
when propagating the dataset changes back to the database.
I use the SqlDataAdapter.Update to send this information
to the database. Unfortunately, there is no UpdateError
event that can be handled. I would have liked an event for
these errors that specify the datatable, row and
SqlException (a bit like the FillErrorEventArgs).

I have created a workaround, but this looks horrible. I
call the dataadapter's Update method and catch the
SqlException. Then I will iterate through the dataset and
find the first non-Unchanged row. That should have
been the row that has failed. Things become even more
complex when relational datasets are being updated,
because they are updated in a different order. When a
UpdateError event would have existed, then this would have
been much more easy.

Now I investigate the SqlException by checking the message
number and parsing the message string. I don't like this
behaviour, but I don't think there is a better way. Anyone
have a suggestion?
 
Hi Ramon,

From .net help files:
When using Update, the order of execution is as follows:

1.. The values in the DataRow are moved to the parameter values.
2.. The OnRowUpdating event is raised.
3.. The command executes.
4.. If the command is set to FirstReturnedRecord, then the first returned
result is placed in the DataRow.
5.. If there are output parameters, they are placed in the DataRow.
6.. The OnRowUpdated event is raised.
7.. AcceptChanges is called.
You see that OnRowUpdating (or RowUpdating for SqlDataAdapter) event is
called before row is updated to database.
You might place here code that stores the row being updated.
So, when you catch exception you'll know which rows triggered it.
 
Back
Top