DataTable Update Problem

Question

Hello,

What is the best method for saving thousands of rows and after doing something, updating them.

Currently, I use a datatable, filling it, when done inserting by

MyDataAdapter.Update(MyDataTable)

After doing some change on MyDataTable, I again use MyDataAdapter.Update(MyDataTable) method.

Edit:

I am sorry for not providing more info.

There may be up to 200.000 rows which will be created from an XML file. There rows will be saved to the database. After than there will be some process for each row. And I will need to update each row in database.

Instead of updating row by row, I decided to update the datatable and using the same dataadapter to update the rows.

This is the best of me.

I think that there may be a smarter approach.

Answer 1

If I understand correctly you are doing two separate operations: loading rows to a database, and then updating those rows.

If the rows you are inserting come from another ADO.NET supported datasource then you can use SqlBulkCopy to insert the rows in batches, which will be more efficient than using a datatable.

Once the rows are in the database I would assume you would be better off executing a SQLCommand to modify their values.

If you can provide more details about what--and why--you're asking the question then perhaps we can better tailor an answer for it.

Answer 2

In Reacting to your comments:

An DataAdapter.Update() will Udate (and Insert/Delete) row by row. If you have individual changes there really is no faster way. If you have systematic changes, like SET Price = Price+ 2 WHERE SelByDate < '1/1/2010' you are better of by running a DbCommand against the database.

But maybe you should worry about transactions and error handling before performance.