Batch Updates using DataAdapter

Question

I have a situation where I have a bunch of SQL Update commands that all need to be executed. I know that DataSets can do batch updates, but the only way I've been able to accomplish it is to load the whole table into a dataset first. What if I want to only update a subset of the records in a table?

Answer 1

EDITED: Based on your comment, I'd recommend using the Bulk Copy method to go to a staging table first. Then you can do a single update on your real table based on the staging table.

=========

One way is to build the SQL Command yourself; however, I'd recommend reading about the SQL Injection possibilities to protect yourself. Depending on your situation and your platform there are other options.

For example if you are dealing with a lot of data, then you could do a bulk import into a holding table, which you would then issue a single update command off of. I've also had good success passing records in as XML (I found in my environment that I needed at least 50 rows to offset the cost of loading the DOM, and I knew that the scalability issues were not a factor in my case).

Some other things I've seen was people package the updates into a binary field, for example one binary field for each column, then they have a function which unpacks the binary field. Again you will want to test with your environment.

With that said, have you verified that simply calling a single update command or stored procedure for each update you need is not sufficent? You might not even need to batch the commands up.

• Josh

Watch out for Premature Optimization it can be killer!

Answer 2

The easiest way out here is load table with the required rows (rows you wan to update). Double check that the RowState is "Inserted". Assign the InsertCommand property of the adapter with your stored procedure (wrapped in an SqlCommand) that does the "update", this tweak will ensure that all the rows present in the table gets updated.

The fundamental here being: The DataAdapter runs the UpdateCommand on rows for which state is Updated, runs InsertCommand for rows which state is Inserted and finally DeleteCommand for the rows for which state is Deleted.