SqlDataAdapter RowUpdating Event Seems to Mutate Update Command

Question

I followed a tutorial to use the .Net SqlDataAdapater.Update method to generate a table update script. However, it appears that somewhere along the way the SqlCommands are getting mutated in such a way as to be syntactically incorrect. What is even stranger to me is that this problem seems to have popped up out of nowhere.

...
dataAdapter.RowUpdating += (sender, updatingEvent) => AppendToTableDataString(updatingEvent, tableDataStringBuilder);
var cmdBuilder = new SqlCommandBuilder(dataAdapter);
dataAdapter.DeleteCommand = cmdBuilder.GetDeleteCommand();
dataAdapter.InsertCommand = cmdBuilder.GetInsertCommand();
dataAdapter.UpdateCommand = cmdBuilder.GetUpdateCommand();
Console.WriteLine("Before call to Update: " + dataAdapter.UpdateCommand.CommandText);
dataAdapter.Update(DataTable);
Console.WriteLine("After call to Update: " + dataAdapter.UpdateCommand.CommandText);
...

private static void AppendToTableDataString(SqlRowUpdatingEventArgs updatingEvent, StringBuilder tableDataBuilder)
{
  if (updatingEvent.Command == null)
  {
    return;
  }

  Console.WriteLine("Before appending to StringBuilder: " + updatingEvent.Command.CommandText);
            tableDataBuilder.Append(updatingEvent.Command.Parameters.Cast<SqlParameter>()
                                    .Aggregate(updatingEvent.Command.CommandText, (current, aParameter) =>
                                               current.Replace(aParameter.ParameterName, aParameter.Value.ToString())) + Environment.NewLine);
  Console.WriteLine("After appending to StringBuilder: " + updatingEvent.Command.CommandText);
}

Output:

Before call to Update: UPDATE [schema].[table] SET [Id] = @p1, [Num] = @p2 WHERE (([Id] = @p3) AND ((@p4 = 1 AND [Num] IS NULL) OR ([Num] = @p5)))
Before appending to StringBuilder: UPDATE [schema].[table] SET [Id] = @p1, [Num] = @p2 WHERE (([Id] = @p3) AND ((@p4 = 1 AND [Num] IS NULL) OR ([Num] = @p5)))
After appending to StringBuilder: UPDATE [schema].[table] SET [Id] = @p1, [Num] = @p2 WHERE (([Id] = @p3) AND ((@p4 = 1 AND [Num] IS NULL) OR ([Num] = @p5)))
Before appending to StringBuilder: UPDATE [schema].[table] WHERE (([Id] = @p1) AND ((@p2 = 1 AND [Num] IS NULL) OR ([Num] = @p3)))
After appending to StringBuilder: UPDATE [schema].[table] WHERE (([Id] = @p1) AND ((@p2 = 1 AND [Num] IS NULL) OR ([Num] = @p3)))
Before appending to StringBuilder: UPDATE [schema].[table] WHERE (([Id] = @p1) AND ((@p2 = 1 AND [Num] IS NULL) OR ([Num] = @p3)))
After appending to StringBuilder: UPDATE [schema].[table] WHERE (([Id] = @p1) AND ((@p2 = 1 AND [Num] IS NULL) OR ([Num] = @p3)))
After call to Update: UPDATE [schema].[table] WHERE (([Id] = @p1) AND ((@p2 = 1 AND [Num] IS NULL) OR ([Num] = @p3)))

So you can see that the problem is that an incorrect script is being generated:

UPDATE [schema].[table] WHERE (([Id] = @p1) AND ((@p2 = 1 AND [Num] IS NULL) OR ([Num] = @p3)))

The output makes it look like the command is getting mutated somewhere after the first row update finishes executing, but before the second row update, however, I have no idea what would be causing this to happen. Can anyone offer any clues or spot what I'm doing wrong? As I indicate above, this was working fine and then seemingly seemed to break w/o reason.

UPDATE

I completely removed the StringBuilder operations (the RowUpdating event consisted solely of printing out the updating command), and I still saw the weird behavior.

However, I believe I was able to mitigate the problem by moving things around in my code a bit. Among other things, I set the RowUpdating event after setting the various commands on the adapter. I guess I'm not sure why this is affecting the problem (hence, I'm not listing this as an answer), but it appears to be fixing things for now at least.

SqlDataAdapter RowUpdating Event Seems to Mutate Update Command

0 Answers0