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. In. Net2.0, several new classes are 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
Using System;
Using System.Data;
Using System.Configuration;
Using System.Collections;
Using System.Web;
Using System.Web.Security;
Using System.Web.UI;
Using System.Web.UI.WebControls;
Using System.Web.UI.WebControls.WebParts;
Using System.Web.UI.HtmlControls;
Using System.Data.SqlClient;
public partial class ASP_NET:System.Web.UI.Page
{
Private DateTime StartTime;
protected void Button1_Click (object sender, EventArgs e)
{
StartTime = DateTime.Now;
String srcconnstring = "";
String desconnstring = "";
SqlConnection srcconnection = new SqlConnection ();
SqlConnection desconnection = new SqlConnection ();
SqlCommand sqlcmd = new SqlCommand ();
SqlDataAdapter da = new SqlDataAdapter ();
DataTable dt = new DataTable ();
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 = new SqlBulkCopy (desconnstring,sqlbulkcopyoptions.useinternaltransaction);
Sbc. Bulkcopytimeout = 5000;
Sbc. Sqlrowscopied +=new Sqlrowscopiedeventhandler (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 void Onrowscopied (object sender, 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";
}
}
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
(
Aid int identity (primary key),
Adesc varchar (NOT NULL)
)
Go
/********** Stored Procedure **********************/
Create proc AddData
As
DECLARE @i int
Set @i=1
While @i < 1000000
Begin
INSERT into ABC values (' Testdescription ')
Set @i = @i + 1
End
Go
SELECT * into titles from Pubs.dbo.titles where 1> 3 replicate the table structure across databases
