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();
   }
}

Answer 2

While SqlBulkCopy works for bulk inserts, you can't do bulk updates. This may or may not be an issue for you, but in any case you can use a stored procedure which utilises OPENXML to allow for bulk inserts and updates. You'll also need sp_xml_preparedocument and sp_xml_removedocument.

Another great advantage of this method is that you can get the ids of the entities you just bulk inserted, using the OUTPUT clause.

Example Of A Bulk Insert.

Assuming you have defined a parameter @p_XmlData VARCHAR(MAX) on the stored proc defining the data to insert or update. For an example of the XML you need to pass in, you can do:

SELECT TOP 1 *, 0 AS __ORDERBY FROM dbo.YourEntity AS YourEntity FOR XML AUTO, ROOT('ROOT')

Then the stored procedure will look something like this

DECLARE @hDoc INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, @p_XmlData

INSERT INTO dbo.YourEntity
(
        [Field1],
        [Field2]
)
SELECT
        XMLData.Field1,
        XMLData.Field2
FROM OPENXML (@hdoc, 'ROOT/YourEntity', 1)
WITH
(
        [Field1] int,
        [Field2] varchar(50),
        [__ORDERBY] int
) AS XMLData

EXEC sp_xml_removedocument @hDoc

Answer 3

I propose that you send your DataTables from you DataSet to a stored procedure using a Table Valued parameter. You can then perform one insert statement to populate the database with your dataset records.

http://msdn.microsoft.com/en-us/library/bb510489.aspx