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
Add a comment
|
3 Answers
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();
}
}
1 Comment
Winston Smith
Setting an appropriate BatchSize and using SqlBulkCopyOptions.TableLock may result in much better performance. Test for your scenario. See sqlblog.com/blogs/alberto_ferrari/archive/2009/11/30/… for detailed analysis.
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
1 Comment
kevchadders
Nice expansion on my answer + 1
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.
