Batch updates using DataAdapter
The easiest way is to load the table with the required rows (the rows you want to update). Double check that the RowState is "Inserted". Assign the adapter's InsertCommand property using a stored procedure (wrapped in a SqlCommand) that performs an "update", this setting will update all the rows present in the table.
Fundamental here is: The DataAdapter runs an UpdateCommand on rows for which the state has been updated, runs an InsertCommand for rows that contain Inserted, and finally a DeleteCommand for rows for which the state has been deleted.
source to share
EDITED: Based on your comment, I would recommend using the bulk copy method to get to the intermediate table first. Then you can do one update on your real table based on the staging table.
=========
One way is to create the SQL Command yourself; however I would recommend reading about the self-protection capabilities of SQL Injection. There are other options depending on your situation and your platform.
For example, if you are dealing with a lot of data, then you can bulk import into a holding table from which you have to issue one update command. I also had good success transferring records in XML format (I found in my environment that it took me at least 50 lines to offset the cost of loading the DOM, and I knew scalability issues were not a factor in my case).
Some other things I've seen are people packing updates into a binary field, like one binary field for each column, then they have a function that unpacks the binary field. Again, you will want to test your environment.
Having said that, have you confirmed that simply calling one update command or stored procedure for every update you need is not enough? You might not even need a command command.
- Josh
Beware of premature optimization, it could be a killer!
source to share