SqlBulkCopy of the new features of ADO (bulk inserting large amounts of data)

Transferred from: http://blog.csdn.net/huaer1011/article/details/2312361

In. Net1.1, it is not convenient whether to bulk insert all the data in the entire DataTable into the database, or to migrate between different data sources. And in. In Net2.0, several new classes have been added under the SqlClient namespace to help us migrate data in a DataTable or DataReader batch. The data source can come from a relational database or an XML file, or even webservice return results. One of the most important classes is the SqlBulkCopy class, which can be used to help us migrate data from the source to the target database conveniently.
Let's start with a simple example of how this class is used:

First: Web. config

<connectionStrings>    <add name="srcdbconnection" connectionstring="  server=.; database=pubs;uid=sa;pwd="/>    <add name="desdbconnection " connectionstring="server=.; database=northwind;uid=sa;pwd="/>  </connectionStrings>

C # File: The foreground does not copy, just a button, a label

usingSystem;usingSystem.Data;usingSystem.Configuration;usingSystem.Collections;usingsystem.web;usingSystem.Web.Security;usingSystem.Web.UI;usingSystem.Web.UI.WebControls;usingSystem.Web.UI.WebControls.WebParts;usingSystem.Web.UI.HtmlControls;usingSystem.Data.SqlClient; Public Partial classasp_net:system.web.ui.page{PrivateDateTime StartTime; protected voidButton1_Click (Objectsender, EventArgs e) {StartTime=DateTime.Now; stringSrcconnstring =""; stringDesconnstring =""; SqlConnection srcconnection=NewSqlConnection (); SqlConnection desconnection=NewSqlConnection (); SqlCommand sqlcmd=NewSqlCommand (); SqlDataAdapter da=NewSqlDataAdapter (); DataTable DT=NewDataTable (); //srcconnstring = configurationmanager.connectionstrings["Srcdbconnection"]. ConnectionString;desconnstring = configurationmanager.connectionstrings["desdbconnection"].        ToString (); //srcconnection.connectionstring = srcconnstring;Srcconnection.connectionstring =desconnstring; sqlcmd. Connection=srcconnection; //sqlcmd.commandtext = "SELECT * from Jobs";Sqlcmd.commandtext ="SELECT * FROM ABC"; Sqlcmd.commandtype=CommandType.Text; sqlcmd.        Connection.Open (); Da. SelectCommand=sqlcmd; Da.        Fill (DT); SqlBulkCopy SBC=NewSqlBulkCopy (desconnstring,sqlbulkcopyoptions.useinternaltransaction); Sbc. Bulkcopytimeout= the; Sbc. Sqlrowscopied+=NewSqlrowscopiedeventhandler (onrowscopied); Sbc. Notifyafter=dt.        Rows.Count; Try        {           //SBC. DestinationTableName = "Jobs";Sbc. DestinationTableName ="BCD"; Sbc.        WriteToServer (DT); }        Catch(Exception ex) {Lblcounter.text=Ex.        Message.tostring (); }        finally{sqlcmd.            Clone ();            Srcconnection.close ();                    Desconnection.close (); }    }    Private voidOnrowscopied (Objectsender, Sqlrowscopiedeventargs args) {Lblcounter.text+ = args. Rowscopied.tostring () +"rows are copied<br>"; TimeSpan CopyTime= DateTime.Now-StartTime; Lblcounter.text+="Copy Time:"+ copyTime.Seconds.ToString () +"."+ copyTime.Milliseconds.ToString () +"seconds"; }}

View Code

Code Analysis:

SqlBulkCopy SBC = new SqlBulkCopy (desconnstring,sqlbulkcopyoptions.useinternaltransaction);
Mr. Cheng SqlBulkCopy instance, the constructor specifies the target database, Using sqlbulkcopyoptions.useinternaltransaction means that the migration action is specified in a transaction, and if an error or exception occurs during data migration, the rollback will occur.

Sbc.    Bulkcopytimeout = 5000000; Specify timeout time for operation completion

Sbc. Sqlrowscopied +=new Sqlrowscopiedeventhandler (onrowscopied);
Sbc. Notifyafter = dt. Rows.Count;

Try
{
Sbc. DestinationTableName = "Jobs";
Sbc. DestinationTableName = "BCD";
Sbc. WriteToServer (DT);
}
The Notifyafter property specifies the number of rows of data to be processed before the notification notification event, which is specified here as the number of rows in the table, and adds the time that the Sqlrowscopied event outputs the entire migration process. The WriteToServer method is to copy the data source to the target database. Before you can use the WriteToServer method, you must first specify the DestinationTableName property, which is the table name of the target database.

Performance: I inserted 680,000 data in SQL with proc for nearly 8 minutes, spent 53.234 seconds with SqlBulkCopy, 7 times times more efficient! But now also do not do this aspect of the bottom, hehe, write yourself a test stored procedure also paste it, convenient to learn

CREATE TABLE ABC (AidintIdentity1,1) primary key, Adesc varchar ( -) notNULL) Go/********** stored Procedure **********************/CREATE proc AddData asDECLARE @iintSet@i=1 while@i <1000000Begininsert into ABC values ('testdescription')Set@i = @i +1EndgoSelect* Into titles fromPubs.dbo.titleswhere  1>3Copy the table structure across databases

View Code