We generally operate insert operation using either raw ADO.NET Command object or LINQ to SQL or any other ORM tool.These are fine and best if the number of records to insert is limited.But if you need bulk insert of thousands of records, these methods become trivial and its performance gets quite worse.In this scenario i am going to explain how to improve efficiency of your application. I have tested it on .NET 4.0 and SQL Server 2008 R2 database.
Hope same approach will be applicable to all other versions.
First of all create a Stored Procedure in your database that takes varchar type parameter. From client application XML formatted data will be sent to this Stored Procedure, where client is responsible for creating XML data that encapsulates all the data to be inserted.Stored Procedure will then process that XML data and insert those data into respective columns of table. This way processing speed is significantly improved and response time gets better.
Before proceeding further make sure that you know how to create Stored Procedure in SQL server, should know LINQ to SQL and basic concepts of .NET OOps.
Below is the Stored Procedure code that manipulated the XML data and inserts data to respective table :
CREATE PROCEDURE [dbo].[TakeAndInsertXML]
declare @xmldoc xml
declare @docHandle int
SET NOCOUNT ON;
set @xmldoc = Convert(XML,@x);
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmldoc;
insert t_test select * from openxml(@docHandle,'/Info/Data') with t_test;
EXEC sp_xml_removedocument @docHandle
And below is the .NET client application helper method that creates the required XML data(as String datatype) :
private static string CreateXML(List People)
StringBuilder sb = new StringBuilder();
foreach (Person p in People)
sb.Append("<Data Name='"+ p.Name +"'");
sb.Append(" Age='"+ p.Age +"'");
Using the above helper method you can create XML data and pass it to Stored Procedure using raw ADO.NET Command objects or LINQ to SQL or any other ORM tool. Here i will explain using LINQ to SQL :
using (PeopleDataContext dc =
List people = new List();
// Logic to populate this list ....
string data = CreateXML(people);
Calling the Stored Procedure will insert all data in XML string within a moment.