ASP.net C# -- Merging a dataset that came from MySQL into a SQL Server 2008 table

I load data from a MySQL database table into a DataSet.

Example:

MySqlConnection myConnection = new MySqlConnection(/*connection string*/);
MySqlCommand collectDataCommand = new MySqlCommand(/*Select comman*/, myConnection);

MySqlDataAdapter myDataAdapter = new MySqlDataAd开发者_如何学Goapter(collectDataCommand);
myConnection.Open();

DataSet tempData = new DataSet();
myDataAdapter.Fill(tempData);

Now that I have this info in a DataSet, I need to MERGE (or UPSERT) this info into a matching table in a SQL Server 2008 database.

What would be the most efficient way of doing this? Is this even possible?

Many Thanks!!!

The most efficient way to do this is to use Table-Valued Parameters (TVP), supported by SQL Server 2008.

It is easy to implement both on the SQL Server side as well as the C# client side (the parameter will be of SqlDbType.Structured). Basically, the steps are:

• Define a new "table type" in your database
• Add a parameter in your stored procedure, having the type that you just defined
• Add the corresponding parameter in your client C# code

Here's an extract from the relevant MSDN article:

"Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

"Column values in table-valued parameters can be accessed using standard Transact-SQL SELECT statements. Table-valued parameters are strongly typed and their structure is automatically validated. The size of table-valued parameters is limited only by server memory."