Microsoft KB Archive/915880

= FIX: The DataAdapter object does not throw a DBConcurrencyException exception as expected when rows in a batch update do not update =

Article ID: 915880

Article Last Modified on 12/3/2007

-

APPLIES TO


 * Microsoft .NET Framework 2.0

-





SYMPTOMS
In the Microsoft .NET Framework 2.0, when rows in a batch update do not update, the DataAdapter object does not throw a DBConcurrencyException exception as expected. Instead, the DataAdapter object continues to submit changes. This problem occurs when all the following conditions are true:
 * You use either an SqlDataAdapter object or an OracleDataAdapter object to perform the batch update.
 * The batch update fails because another user modifies rows.



RESOLUTION
A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the hotfix. For a complete list of Microsoft Product Support Services telephone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

Prerequisites
You must apply the hotfix that is described in Microsoft Knowledge Base article 916002.

For more information about how to obtain this hotfix, click the following article number to view the article in the Microsoft Knowledge Base:

916002 FIX: Error message when you try to use the SqlClient class in an ADO.NET 2.0-based application to connect to an instance of SQL Server 2005: &quot;New request is not allowed to start because it should come with valid transaction descriptor&quot;

Download information
To resolve this problem, download the appropriate file for the operating system.

For an x86-based operating system
The following file is available for download from the Microsoft Download Center:

Download the NDP20-KB916002-X86.exe package now.

For an x64-based operating system
The following file is available for download from the Microsoft Download Center:

Download the NDP20-KB916002-X64.exe package now.

For an IA-64-based operating system
The following file is available for download from the Microsoft Download Center:

Download the NDP20-KB916002-IA64.exe package now.

For more information about how to download Microsoft support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to obtain Microsoft support files from online services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help prevent any unauthorized changes to the file.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



MORE INFORMATION
For more information, click the following article number to view the article in the Microsoft Knowledge Base:

824684 Description of the standard terminology that is used to describe Microsoft software updates



When an SqlDataAdapter object or an OracleDataAdapter object submits pending changes in batches, the DataAdapter assumes that the whole batch succeeded if the following conditions are true:
 * The database reports no errors.
 * The database reports that the query affected one or more rows.

If you use concurrency checks in the updating logic of your DataAdapter object, an attempt to submit a change to a row that has been modified by another user does not fail. Instead, the attempt to submit a change to a row that has been modified by another user causes the database to report that the query affected no rows. Whenever the database reports an error or the database reports that the query affected no rows, the DataAdapter Class should throw a DBConcurrencyException exception. The DBConcurrencyException exception alerts the user that the update attempt failed.

Before you apply this hotfix, you experience the following behavior:
 * Both the SqlDataAdapter class and the OracleDataAdapter class recognize update failures that result from a failed concurrency check when each update attempt contains only one row.

Notes
 * When the UpdateBatchSize property on the DataAdapter object is set to 1, each update attempt contains only one row.
 * By default, the value of the UpdateBatchSize property on the DataAdapter object is set to 1.
 * Both the SqlDataAdapter class and the OracleDataAdapter class do not recognize partial batch failures that are caused by a failed concurrency check. A partial batch failure occurs when at least one update attempt in the batch succeeds and at least one update attempt in the batch fails.
 * Because the SqlDataAdapter class and the OracleDataAdapter class do not recognize partial batch failures, the DataAdapter object assumes that the whole batch succeeded. The DataAdapter object marks all rows for the batch as successfully updated, and the DataAdapter does not throw a DBConcurrencyException exception.

After you apply this hotfix, you experience the following behavior:
 * Both the SqlDataAdapter class and the OracleDataAdapter class recognize partial batch failures that are caused by a failed concurrency check.
 * Both the SqlDataAdapter class and the OracleDataAdapter class throw a DBConcurrencyException exception to notify you that the update attempt failed.

Notes  This hotfix does not affect .NET data providers other than the SqlDataAdapter class and the OracleDataAdapter class. If you experience the problem that is mentioned in the &quot;Symptoms&quot; section when you use a .NET data provider that is not part of the .NET Framework, contact the manufacturer of the .NET data provider. The hotfix that is described in this article adds a protected method to the DbDataAdapter class. The protected method is named the GetBatchedRecordsAffected method. The DbDataAdapter class calls the GetBatchedRecordsAffected method to determine the success or the failure of individual entries in the batch. All .NET data providers that support update batching through the infrastructure of the DbDataAdapter class should override the GetBatchedRecordsAffected method. The GetBatchedRecordsAffected method should return True if the update attempt is handled by the database. The RecordsAffected parameter should be set to the number of rows that are affected by the update attempt. To set the Error property, use one of the following values:  If the database reports error information for the update attempt, set the Error property to an appropriate exception. If the database does not report error information for the update attempt, set the Error property to one of the following values.  </li></ul>

<div class="references_section">

Steps to reproduce the problem
Modify the connection strings on the attached file to connect to your SQL Server database or to your Oracle database. Then, run the following code.

C# code
private static void SQLBU412467 { DataTable table = new DataTable(&quot;table&quot;); table.Columns.Add(&quot;c1&quot;, typeof(int));

int affected; ConnectionStringSettings config = ConfigurationManager.ConnectionStrings[&quot;trusted_2005&quot;]; using(SqlConnection connection = new SqlConnection(config.ConnectionString)) { connection.Open;

affected = (new SqlCommand(&quot;create table #table1 (c1 int)&quot;, connection)).ExecuteNonQuery; Console.WriteLine(affected);

SqlDataAdapter adapter = new SqlDataAdapter; adapter.UpdateBatchSize = 5; adapter.InsertCommand = new SqlCommand(&quot;insert into #table1 (c1) values(@p1); update #table1 set c1=@p1+1 where c1=@p1; update #table1 set c1=@p1 where c1=@p1+1; &quot;, connection); adapter.InsertCommand.Parameters.Add(&quot;@p1&quot;, SqlDbType.Int, 4, &quot;c1&quot;).SourceVersion = DataRowVersion.Current; adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

adapter.UpdateCommand = new SqlCommand(&quot;update #table1 set c1=@p1 where c1=@p2; update #table1 set c1=666 where c1=999&quot;, connection); adapter.UpdateCommand.Parameters.Add(&quot;@p1&quot;, SqlDbType.Int, 4, &quot;c1&quot;).SourceVersion = DataRowVersion.Current; adapter.UpdateCommand.Parameters.Add(&quot;@p2&quot;, SqlDbType.Int, 4, &quot;c1&quot;).SourceVersion = DataRowVersion.Original; cmd.UpdatedRowSource = UpdateRowSource.None;

adapter.RowUpdating += delegate(object sender, SqlRowUpdatingEventArgs args) { Console.WriteLine(&quot;StatementType={0} CommandText='{1}' @p1={2}&quot;, args.StatementType, args.Command.CommandText, args.Row[0]); };

adapter.RowUpdated += delegate(object sender, SqlRowUpdatedEventArgs args) { Console.WriteLine(&quot;StatementType={0} RecordsAffected={1}&quot;, args.StatementType, args.RecordsAffected); DataRow[] rows = new DataRow[args.RowCount]; args.CopyToRows(rows); foreach(DataRow row in rows) { switch(row.RowState) { case DataRowState.Added: Console.WriteLine(&quot;\t@p1={0}&quot;, row[0, DataRowVersion.Current]); break; case DataRowState.Modified: Console.WriteLine(&quot;\t@p1={0} @p2={1}&quot;, row[0, DataRowVersion.Current], row[0, DataRowVersion.Original]); break; case DataRowState.Deleted: Console.WriteLine(&quot;\t@p1={0}&quot;, row[0, DataRowVersion.Original]); break; } } };

for(int i = 1; i <= 10; ++i) { table.Rows.Add(new object[] { i }); } affected = adapter.Update(table); Console.WriteLine(affected);

for(int i = 0; i < 10; ++i) { table.Rows[i][0] = 10 - i; } // Uncomment this code to cause failure. //table.Rows[0].AcceptChanges; //table.Rows[0][0] = 50; try { affected = adapter.Update(table); Console.WriteLine(affected); } catch(Exception e) { Console.WriteLine(e);

foreach(DataRow row in table.Rows) { Console.WriteLine(row.RowError); } } } }

Visual Basic code
Private Sub Repro

Dim table As DataTable = New DataTable(&quot;repro&quot;)

table.Columns.Add(&quot;c1&quot;, Type.GetType(&quot;System.Int32&quot;))

Dim affected As Integer

Dim connectionstring As String = Configurationmanager.connectionstrings(&quot;trusted_2005&quot;).ConnectionString

Dim connection As SqlConnection = New SqlConnection(connectionstring)

connection.Open

'Create a temporary table to reproduce the problem.

Dim cmd As SqlCommand = New SqlCommand(&quot;create table #repro (c1 int)&quot;, connection)

cmd.ExecuteNonQuery

Dim adapter As SqlDataAdapter = New SqlDataAdapter

adapter.UpdateBatchSize = 5

'Add logic to submit new and modified rows.

cmd = New SqlCommand(&quot;insert into #repro (c1) values(@p1);&quot;, connection)

cmd.Parameters.Add(&quot;@p1&quot;, SqlDbType.Int, 4, &quot;c1&quot;).SourceVersion = DataRowVersion.Current

cmd.UpdatedRowSource = UpdateRowSource.None

adapter.InsertCommand = cmd

cmd = New SqlCommand(&quot;update #repro set c1=@p1 where c1=@p2;&quot;, connection)

cmd.Parameters.Add(&quot;@p1&quot;, SqlDbType.Int, 4, &quot;c1&quot;).SourceVersion = DataRowVersion.Current

cmd.Parameters.Add(&quot;@p2&quot;, SqlDbType.Int, 4, &quot;c1&quot;).SourceVersion = DataRowVersion.Original

cmd.UpdatedRowSource = UpdateRowSource.None

adapter.UpdateCommand = cmd

'Add 10 DataRow objects to the DataTable.

Dim i As Integer

For i = 1 To 10

table.Rows.Add(i)

Next

'Submit the 10 new rows to the database.

affected = adapter.Update(table)

Console.WriteLine(&quot;Call to DataAdapter.Update affected {0} row or rows&quot;, affected)

'Modify all rows.

i = 0

For i = 0 To 10 - 1

table.Rows(i)(0) = 10 - i

Next

'Uncomment the following lines of code to cause update failure.

'This code will cause the DataRow object to be out of sync with the corresponding

'row in the database table. The query to update the row will not modify any rows.

'This behavior should cause a DBConcurrencyException exception when you call the DataAdapter.Update method.

'table.Rows(2).AcceptChanges

'table.Rows(2)(0) = 50

Try

'Submit the pending changes.

affected = adapter.Update(table)

Console.WriteLine(&quot;Call to DataAdapter.Update affected {0} row or rows&quot;, affected)

catch e as Exception

Console.WriteLine(e)

Dim row As DataRow

For Each row In table.Rows

Console.WriteLine(row.RowError)

Next

End Try

End Sub

Keywords: kbtshoot kbbug kbfix kbqfe kbpubtypekc KB915880

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.