Inserting a dataset into a database table

Question

Hi guys,

I have table on a database on a server, that is identical to a table on my local. I have click once application that needs to download the version of records on the server down to my local.

At the moment i have webservice that pulls back the records on the server in batches, using asp.net datasets as containers. How do i commit the whole dataset to the table in my local? The table on my local is empty.

Cheers in advance!

Answer 1

There are several options. Here are the first two that come to mind.

• One would be to loop through the DataTable and build an SQL Insert statement on each loop and then execute the Insert statement against the local.
• Another would be to use the SQL Bulk Copy to insert the data

Answer 2

If you already have a DataSet, containing one or several DataTables, why don't you just use the SqlDataAdapter and call its ".Update()" method with your DataSet?

In the SqlDataAdapter, you can define an InsertCommand, an UpdateCommand, a DeleteCommand which will take care of the three basic insert/update/delete statements for your rows. All you need to do is define / write those three SQL Statements once, and the SqlDataAdapter will do the rest for you (looping through the rows, figuring out whether to insert, update or delete etc.).

If you want, you can even use your basic SELECT statement from the SelectCommand in your DataSet and use the SqlCommandBuilder to build the INSERT, UPDATE and DELETE statements based on your SELECT.

• MSDN Library doc on SqlDataAdapter
• SQL Data Adapter without SqlCommandBuilder
• MSDN Library doc on SqlCommandBuilder

Marc