Click here to Skip to main content
Click here to Skip to main content

SQL Bulk Copy with C#.Net

, 24 Jan 2007
Rate this:
Please Sign up or sign in to vote.
An article about copying data to SQL within your application.

Introduction

Programmers usually need to transfer production data for testing or analyzing. The simplest way to copy lots of data from any resources to SQL Server is BulkCopying. .NET Framework 2.0 contains a class in ADO.NET "System.Data.SqlClient" namespace: SqlBulkCopy. The bulk copy operation usually has two separated phases.

In the first phase you get the source data. The source could be various data platforms such as Access, Excel, SQL.. You must get the source data in your code wrapping it in a DataTable, or any DataReader class which implements IDataReader. After that, in the second phase, you must connect the target SQL Database and perform the bulk copy operation.

The bulk copy operation in .Net is a very fast way to copy large amount of data somewhere to SQL Server. The reason for that is the Bulkcopy Sql Server mechanism. Inserting all data row by row, one after the other is a very time and system resources consuming. But the bulkcopy mechanism process all data at once. So the data inserting becomes very fast.

Solution walkthrough

While you are programming for bulk copy, first open a connection for the source data. In this sample we are connecting a SQL Server named SQLProduction. We are using SqlConnectionStringBuilder to build our connection string.

// Establishing connection
SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(); 
cb.DataSource = "SQLProduction"; 
cb.InitialCatalog = "Sales"; 
cb.IntegratedSecurity = true;
SqlConnection cnn = new SqlConnection(cb.ConnectionString);  

Then we are retrieving data from the source with SqlCommand and SqlDataReader classes. 

// Getting source data
SqlCommand cmd = new SqlCommand("SELECT * FROM PendingOrders",cnn); 
cnn.Open(); 
SqlDataReader rdr = cmd.ExecuteReader(); 

Now we have a data in rdr variable. It's time to initialize a SqlBulkCopy object and copy the data. The SqlBulkCopy class needs a connection to copy data into a Sql server. You can establish a second connection explicitly or the class will do it for you. We are using the second alternative with creating a SqlBulkCopy object. We are passing a connection string as a parameter in constructor method.

// Initializing an SqlBulkCopy object
SqlBulkCopy sbc = new SqlBulkCopy("server=.;database=ProductionTest;" +<BR>                                  "Integrated Security=SSPI"); 

OK. The sbc object is ready to copy. Now you must tell the object the destination table name, start the copying process calling WriteToServer method and pass the method the SqlDataReader variable rdr as parameter.

// Copying data to destination
sbc.DestinationTableName = "Temp"; 
sbc.WriteToServer(rdr); 

At the end, close all SqlConnection, SqlDataReader and SqlBulkCopy objects.

// Closing connection and the others
sbc.Close(); 
rdr.Close(); 
cnn.Close(); 

That's all. Just a few lines and in a few seconds...

// Establishing connection
SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(); 
cb.DataSource = "SQLProduction"; 
cb.InitialCatalog = "Sales"; 
cb.IntegratedSecurity = true;
SqlConnection cnn = new SqlConnection(cb.ConnectionString);  

// Getting source data
SqlCommand cmd = new SqlCommand("SELECT * FROM PendingOrders",cnn); 
cnn.Open(); 
SqlDataReader rdr = cmd.ExecuteReader(); 

// Initializing an SqlBulkCopy object
SqlBulkCopy sbc = new SqlBulkCopy("server=.;database=ProductionTest;" +<BR>                                  "Integrated Security=SSPI"); 

// Copying data to destination
sbc.DestinationTableName = "Temp"; 
sbc.WriteToServer(rdr); 

// Closing connection and the others
sbc.Close(); 
rdr.Close(); 
cnn.Close(); 

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Kadir Camoglu.
Web Developer
Turkey Turkey
Kadir Çamoğlu lives in İstanbul, Turkey.
He has been programming since 1990.
He is a writer also. He wrote two books in Turkish. SQL Server 2005 Express Edition and Visual Basic 2005 Express Edition.
Recently he is working for New Horizons Turkey, Bilimer Bilişim Akademisi.

Comments and Discussions

 
GeneralMy vote of 4 Pinmembernandakishoreroyal13-Sep-13 0:28 
QuestionHaving problem Pinmemberrakibbcse1-Sep-13 2:30 
QuestionInsert Data Two time Pinmemberkeyur soni22-May-13 3:00 
GeneralMy vote of 1 Pinmembersajad_zero3-Dec-12 22:27 
This article was poorly
GeneralMy vote of 5 PinmemberP.Salini21-Jun-12 22:15 
QuestionNice tutorial Pinmembertherealmrcool31-May-12 3:49 
GeneralMy vote of 2 Pinmemberyanezricardo25-May-12 10:14 
GeneralHi, help me some thing ! PinmemberMember 20088263-Jun-11 1:23 
GeneralNice Article PinmemberAJMAL SHAHZAD4-Mar-11 19:12 
GeneralMy vote of 5 Pinmembermedmanager13-Nov-10 3:27 
GeneralMy vote of 5 Pinmembersudhakarareddy21-Sep-10 17:53 
GeneralDoes this work for Access PinmemberSteve Hackathorn18-May-10 11:02 
GeneralThanx Pinmemberinfinitess24-Jan-10 17:57 
QuestionHow I know ??? Pinmemberarthur_hega29-Sep-09 7:06 
AnswerRe: How I know ??? PinmemberRaviRanjankr18-Mar-11 4:25 
GeneralThanks PinmemberKilManish@gmail.com6-Aug-09 10:41 
General.Net Device application PinmemberMasrooq-ul-islam24-May-09 23:09 
GeneralCannot access destination table PinmemberPragenshMPatel7-May-08 22:40 
GeneralRe: Cannot access destination table PinmemberPragenshMPatel12-May-08 1:59 
GeneralThanks... Pinmembermzielonka11-Apr-08 0:37 
Generalbulk Copy in a MS Access database file Pinmembermbv80020-Nov-07 9:33 
QuestionCan SqlBulkCopy copy data to the local temporary table which is dynamicly generated in a store procedure? Pinmemberdddd2181-Oct-07 6:05 
Answerthat is to say, how can I use SqlBulkCopy to copy data to the local temporary table which is dynamicly generated in a store procedure? Pinmemberdddd2181-Oct-07 6:10 
QuestionHow about direct Bulk Copy from CSV to SQL using "Format file"? PinmemberRenaudDev10-Apr-07 6:36 
AnswerRe: How about direct Bulk Copy from CSV to SQL using "Format file"? Pinmemberrobinson mike3-Oct-07 11:49 
GeneralRe: How about direct Bulk Copy from CSV to SQL using "Format file"? PinmemberRenaudDev3-Oct-07 23:45 
GeneralRe: How about direct Bulk Copy from CSV to SQL using "Format file"? Pinmemberrobinson mike8-Oct-07 6:47 
GeneralRe: How about direct Bulk Copy from CSV to SQL using "Format file"? Pinmemberrobinson mike8-Oct-07 8:50 
QuestionPrimary Key Control Pinmember011016-Apr-07 2:25 
AnswerRe: Primary Key Control PinmemberKadir Camoglu.6-Apr-07 21:36 
GeneralRe: Primary Key Control Pinmember011017-Apr-07 9:38 
GeneralBackup entire database instead of one table Pinmembermsmith_1214-Mar-07 4:51 
GeneralRe: Backup entire database instead of one table PinmemberKadir Camoglu.6-Apr-07 21:29 
QuestionHow to import MS Excel sheets PinmemberRavindraThakur1-Feb-07 19:17 
AnswerRe: How to import MS Excel sheets PinmemberLyndarEverdead2-Feb-07 11:40 
AnswerRe: How to import MS Excel sheets PinmemberKadir Camoglu.2-Feb-07 23:09 
GeneralRe: How to import MS Excel sheets PinmemberRavindraThakur5-Feb-07 2:45 
QuestionSql mobile PinmemberFilip Landr2-Jan-07 22:07 
AnswerRe: Sql mobile PinmemberK. Camoglu8-Jan-07 20:25 
GeneralHelpful -- thanks! Pinmemberlukner31-Dec-06 7:12 
AnswerRe: Helpful -- thanks! PinmemberK. Camoglu1-Jan-07 4:33 
GeneralRe: Helpful -- thanks! Pinmemberlukner1-Jan-07 7:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141220.1 | Last Updated 25 Jan 2007
Article Copyright 2006 by Kadir Camoglu.
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid