DBConcurrencyException Not Thrown For INSERT Command -- dotnet ado.net

Simon
5/21/2007 6:03:53 AM

In ADO.NET 2.0, it seems that when the TableAdapter (or its underlying
DataAdapter even) doesn't throw a DBConcurrencyException when the
InsertCommand SPROC returns Zero Rows Affected.

The MSDN Documentation Says:

"The DataAdapter examines the number of rows affected by the execution
of each insert, update, or delete operation, and throws this exception
if the number equals zero. This exception is generally caused by a
concurrency violation."

Which rather insinuates that it should!

All I've done is manually created the CRUD sprocs for a table with
optimistic concurrency (using the SQL TimeStamp Method). I added an
additional feature to my Add SPROC - which has an exists check on the
INSERT statement to verify that the row to be inserted doesn't already
exist (i.e. a Concurrency violation). In that case the INSERT
statement would return 0 rows affected and hence I would expect it to
raise a DBConcurrencyException from the TableAdapter's Update Method.

However, this is what actually happens if I try and add a row that
already exists in the table:

- The DataAdapter returns 0 Rows Affected (I can check this by
creating a custom partial class for the tableadapter and catching the
'_adapter_RowUpdated' event)
- The DataRow's RowState remains 'Added'
- The TableAdapter returns 0
- The Row's Error property is not set (and HasErrors returns false)
- No DBConcurrencyException is thrown

The first thought is that there was something wrong with the SPROC, so
it was correct and also that is contained SET NOCOUNT OFF.

To further test what was going on, I inverted over the Add and Modify
SPROCs in the TableAdapters UPDATE and INSERT commands, and then tried
calling the Add SPROC (by modifying an existing row) and this time it
correctly raised the DBConcurrencyException. However, this time Modify
SPROC did not raise the event when I executed it by saving a new
DataRow to the Database (it should have done because it would be
trying to modify a row that didn't exist as the key was new).

So it would seem from this that the DataAdapter's InsertCommand does
not Raise a DBConcurrencyException when the SQL Command returns 0 Rows
Affected

I have already installed the MS Hotfix 916002:

http://support.microsoft.com/kb/915880/en-us?spid=8291&sid=200

And that doesn't help, but I wouldn't expect it to as I'm only doing
single batch updates.

Please, does anybody have any ideas where I can go from here?

(I can easily provide example code if that would help.)

Simon
5/23/2007 2:10:29 AM

[quoted text, click to view]

FYI I have now registered this as a bug with Microsoft and it has been
accepted:

http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=277923

I'll post any progress.

dotnet ado.net : DBConcurrencyException Not Thrown For INSERT Command