Geek Answers Handbook

How to use a single query to insert multiple records from a dataset in SQL Server 2005?

I have a dataset in ADO.NET containing several user-side entries. I need to insert all these rows into a single query in the database to avoid multiple queries

+5
source share
3 answers

Perhaps something like Bulk Copy will be the answer. The example in the Code Project below shows how to do this using a DataTable, but you can change the example around to use a DataSet.

Below is a small piece of code that covers compression and allocation in SQL Server (taken from CodeProject ).

, , bulkcopy.WriteToServer(SourceTable); SourceTable DataSet,

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

SqlBulkCopy , . , , OPENXML, , sp_xml_preparedocument sp_xml_removedocument.

, , , OUTPUT.

.

, @p_XmlData VARCHAR(MAX) , . XML, , :

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

:

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
+3

DataTables DataSet , Table Valued. insert, .

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

0

More articles:


All Articles