MySqlCommandBuilder creates unnecessarily complicated commands
Posted by: Jonathan Pryce
Date: November 07, 2016 09:40AM

I am using MySQL 5.7, with the .NET adapter. The code to read a 24-column table from the database looks something like this C#:

DataTable dataTable;
MySqlDataAdapter adapter;
adapter = new MySqlDataAdapter("SELECT * FROM `My Table` ORDER BY Col1"), mySqlConnection);
MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
adapter.UpdateCommand = builder.GetUpdateCommand();

(I don't think that last line is necessary but it helped my debugging to see the command in adapter's UpdateCommand property)

Now, the code that updates the database with any changes:

DataTable changes = dataTable.GetChanges();
if (changes != null) adapter.Update(changes);

This all works fine (Of course! I am using MySQL :-). My question concerns the Update() call. Let's says I just change the value of one column "col3" to "newCol3Value" in one row. The update command is about 12' long! After substituting all the parameters and removing the NULL check conditionals it says something like (from memory):

UPDATE `My Table` SET Col3 = 'newCol3Value' WHERE ((Col1 = 'col1Value') AND (Col2 = col2Value) AND (Col3 = 'col3Value') AND ... {20 more AND's} ... AND (Col24 = 'col24Value'))

So it specifies every single column value of the row in order to identify the correct row to update. But surely this is unnecessary? My table has a Primary Key (as it should) in Col1, so shouldn't the update command be just:

UPDATE `My Table` SET Col3 = 'value1' WHERE (Col1 = 'col1Value')

Or am I missing something / doing something wrong?

Options: ReplyQuote

Written By
MySqlCommandBuilder creates unnecessarily complicated commands
November 07, 2016 09:40AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.