How can I use single query to insert multiple records from Dataset into SQL Server 2005?

Question

I have a dataset in ADO.NET containing multiple records from user side. I need to insert all those rows in single query into the database, in order to avoid multiple queries

Answer 1

Maybe something like a Bulk copy would be an answer. The example in Code Project below show how to do it using a DataTable, but you should be able to change the example around to use a DataSet.

Below is an small part of the code which covers to conenction and exection in SQL Server (taken from CodeProject).

The key part to notice is the bulkcopy.WriteToServer(SourceTable); were the SourceTable would be the part of the DataSet you would pass to it

//First create a connection string to destination database
string connectionString;
connectionString = <EM>YourConnectionString</EM>and
    Initial Catalog=TestSMODatabase";

//Open a connection with destination database;
using (SqlConnection connection = 
       new SqlConnection(connectionString))
{
   connection.Open();

   //Open bulkcopy connection.
   using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
   {
    //Set destination table name
    //to table previously created.
    bulkcopy.DestinationTableName = "dbo.TestTable";

    try
    {
       bulkcopy.WriteToServer(SourceTable); // SourceTable would come from your DataSet
    }
    catch (Exception ex)
    {
       Console.WriteLine(ex.Message);
    }

    connection.Close();
   }
}