Wednesday, July 01, 2009

Fastest way to insert bulk records into SQL Server DB using bulkCopy in VB.NET

Fastest way to insert bulk records into SQL Server DB using SqlBulkCopy in VB.NET


There are multiple ways to insert bulk data into SQL server database table to increase better

performance and reduce multiple database hits.

Following example tested in

1) .NET Framework 2.0 and Visual Studio 2005

2) SQL Server 2005 and SQL Express

3) Microsoft Enterprises Library 3.0 for database operation


1. Passing XML string to Stored Procedure


In this method you can combine your data into XML and pass this XML as a string to

SQL server stored procedure, this way you can increase your database hit performance up to

70% instead of using Line By Line insert methods.


Example: I am giving example for two records you can increase records in XML (no limit)


Sample XML String


<Employees>

<Emp>

<Empno>1000</Empno>

<Ename>Ritesh</Ename>

<Deptno>A91</Deptno>

</Emp>

<Emp>

<Empno>2000</Empno>

<Ename>Rajiv</Ename>

<Deptno>B56</Deptno>

</Emp>

</Employees>


SQL Server Database table "Employee"


CREATE TABLE [dbo].[Employee](

[Empno] [int] NOT NULL,

[Ename] [nvarchar](100) NULL,

[Deptno] [nvarchar](100) NULL

) ON [PRIMARY]

END

GO


SQL Server Stored Procedure


ALTER PROCEDURE ADDXML_EMP

(

@prmEmployee XML,

@prmOutStatus INT OUT

)

AS

DECLARE @XMLDocPointer INT

EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @prmEmployee


BEGIN


SET NOCOUNT ON

INSERT INTO Employee

(

[Empno],

[Ename],

[Deptno]

)

SELECT

Empno,

Ename,

Deptno

FROM

OPENXML(@XMLDocPointer,'/Employees/Emp',2)

WITH (

Empno integer,

Ename nvarchar(100),

Deptno nvarchar(100)

)

SET @prmOutStatus = @@Error

RETURN @@Error

END


VB.NET Code to execute stored procedure


Namespace


Imports Microsoft.Practices.EnterpriseLibrary.Data

Imports System.Data.SqlClient

Imports System.Xml

Imports System.Data.Common


Dim XMLString As String

'USE GetXML method for XMLString


XMLString = "<Employees><Emp><Empno>1000</Empno><Ename>Ritesh

</Ename><Deptno>A91</Deptno></Emp><Emp><Empno>2000

</Empno><Ename>Rajiv</Ename><Deptno>B56</Deptno></Emp>

</Employees>"


'CreateDatabase method will create database object based on the connection name

Dim connectDB As Database = ConfigDatabaseFactory.CreateDatabase("SQLServerDBConnectionString")

Dim command As DbCommand

command = connectDB.GetStoredProcCommand("AddXML_EMP")

connectDB.AddInParameter(command, "@prmEmployee", DbType.Xml, XMLString)

connectDB.AddOutParameter(command, "@prmOutStatus", DbType.Int32, 0)

connectDB.ExecuteNonQuery(command)


Note: If you are reading XML from file then constructed your string using following lines of code


Dim xmlFilePath As String = "C:\Employees.xml"

Dim xmlDoc As New XmlDocument

xmlDoc.Load(xmlFilePath)

Dim XMLString As String = xmlDoc.OuterXml.ToString()


Data Inserted into Database table


Empno Ename Deptno

1000 Ritesh A91

2000 Rajiv B56


Drawback


XML methods is very reliable and fast performing method but If you have any special character in your data like notes field in database table column then this method will give error saying "Invalid character in XML" or if you have Image in the column then XML would help, to over come this another method to use SqlBulkCopy


Note: If you want to do above method for Oracle Stored procedure the see below link.

http://riteshk.blogspot.com/2008/09/sending-xml-string-as-input-parameter.html


2. Using "SqlBulkCopy" method to insert data into Database table


'Fill this data from any database like Oracle or SQL Server

'using some query like "Select * from Employee"

Dim dsServerData As New DataSet


'Create Databse

Dim localConnectDB As Database = ConfigDatabaseFactory.CreateDatabase("DBConString")

'Open Connection

Using localConnection As DbConnection = localConnectDB.CreateConnection()

'Open Connection

localConnection.Open()

'Create Transaction if you want

Using transaction As DbTransaction = localConnection.BeginTransaction()

'Create SqlBulkCopy

Using localCopy As New SqlBulkCopy(localConnection, SqlBulkCopyOptions.TableLock, transaction)

Try

'loop if you have multiple table in a dataset

For tableCount As Integer = 0 To dsServerData.Tables.Count - 1

'Define the BatchSize , it can be configurable too

localCopy.BatchSize = dsServerData.Tables(tableCount).Rows.Count

'Define the table name where you want to insert data

'if you have exact table name in dataset then use

'localCopy.DestinationTableName = dsServerData.Tables(tableCount).TableName

localCopy.DestinationTableName = "Employee"

'This "WriteToServer" will insert your complete table data into database table localCopy.WriteToServer(dsServerData.Tables(tableCount))

Next

transaction.Commit()

Catch ex As Exception

transaction.Rollback()

Finally

'Close Connection and bulkCopy

localCopy.Close()

localConnection.Close()

End Try

End Using

End Using

End Using


Note: This method inserts 0.28 minutes to insert 1 million records in database

Some other methods to load huge TextFile into SQL server database table

http://blog.stevienova.com/2008/01/16/net-fastest-way-to-load-text-file-to-sql-sqlbulkcopy/

4 comments:

Anonymous said...

hello, can be downloaded, or you could send by email?

please!! :(

E-mail: mascodigo.net@hotmail.com

http://riteshk.blogspot.com/2008/05/programatically-configuration-files.html

Anonymous said...

Hi !.
You re, I guess , perhaps curious to know how one can collect a huge starting capital .
There is no initial capital needed You may start earning with as small sum of money as 20-100 dollars.

AimTrust is what you need
AimTrust incorporates an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

Its head office is in Panama with affiliates everywhere: In USA, Canada, Cyprus.
Do you want to become a happy investor?
That`s your choice That`s what you really need!

I feel good, I started to take up income with the help of this company,
and I invite you to do the same. It`s all about how to choose a correct partner utilizes your funds in a right way - that`s the AimTrust!.
I make 2G daily, and my first investment was 500 dollars only!
It`s easy to get involved , just click this link http://dubopureq.1accesshost.com/isilus.html
and go! Let`s take our chance together to get rid of nastiness of the life

Anonymous said...

Good day !.
You re, I guess , perhaps curious to know how one can collect a huge starting capital .
There is no need to invest much at first. You may start to get income with as small sum of money as 20-100 dollars.

AimTrust is what you thought of all the time
The company represents an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

Its head office is in Panama with offices around the world.
Do you want to become really rich in short time?
That`s your choice That`s what you wish in the long run!

I`m happy and lucky, I started to take up income with the help of this company,
and I invite you to do the same. It`s all about how to choose a proper companion utilizes your savings in a right way - that`s the AimTrust!.
I take now up to 2G every day, and what I started with was a funny sum of 500 bucks!
It`s easy to get involved , just click this link http://bicyvafeb.freehostyou.com/rytaqys.html
and go! Let`s take our chance together to feel the smell of real money

Anonymous said...

Infatuation casinos? indorse this outcome [url=http://www.realcazinoz.com]casino[/url] move aside and waver online casino games like slots, blackjack, roulette, baccarat and more at www.realcazinoz.com .
you can also examine our in perfect accord [url=http://freecasinogames2010.webs.com]casino[/url] mastery at http://freecasinogames2010.webs.com and cover be realized to split genially misled !
another additional [url=http://www.ttittancasino.com]casino spiele[/url] purlieus is www.ttittancasino.com , in compensation german gamblers, along the incline via eleemosynary online casino bonus.