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

REFERENCES

Steps to reproduce the problem

C# code

private static void SQLBU412467() {
DataTable table = new DataTable("table");
table.Columns.Add("c1", typeof(int));

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

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

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

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

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

adapter.RowUpdated += delegate(object sender, SqlRowUpdatedEventArgs args) {
Console.WriteLine("StatementType={0} RecordsAffected={1}", 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("\t@p1={0}", row[0, DataRowVersion.Current]);
break;
case DataRowState.Modified:
Console.WriteLine("\t@p1={0} @p2={1}", row[0, DataRowVersion.Current], row[0, DataRowVersion.Original]);
break;
case DataRowState.Deleted:
Console.WriteLine("\t@p1={0}", 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("repro")

        table.Columns.Add("c1", Type.GetType("System.Int32"))

 

        Dim affected As Integer

        Dim connectionstring As String = Configurationmanager.connectionstrings("trusted_2005").ConnectionString

        Dim connection As SqlConnection = New SqlConnection(connectionstring)

        

  connection.Open()

 

        'Create a temporary table to reproduce the problem.

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

        cmd.ExecuteNonQuery()

 

        Dim adapter As SqlDataAdapter = New SqlDataAdapter()

        adapter.UpdateBatchSize = 5

 

        'Add logic to submit new and modified rows.

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

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

        cmd.UpdatedRowSource = UpdateRowSource.None

        adapter.InsertCommand = cmd

 

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

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

        cmd.Parameters.Add("@p2", SqlDbType.Int, 4, "c1").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("Call to DataAdapter.Update affected {0} row or rows", 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("Call to DataAdapter.Update affected {0} row or rows", 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