Use the. NET sqlbulkcopy class for batch replication

Transferring data between different data sources is a common development task. If you have applied SQL Server, you will be familiar with the BCP (batch copy) command.

It allows you to quickly copy large files to SQL Server tables or views in batches. In. NET Framework 1.1, you can use BCP through the sqlcommand object, but the sqlbulkcopy class is added in. NET Framework 2.0 to simplify this tedious process.

SqlbulkcopyClass

Although you can still use the T-splbcp command, the sqlbulkcopy class has a strong performance advantage. You can only use this class to write data to SQL Server tables, but you can use any data source. The only thing to note is that the content of the data source must be loaded into a datatable object.

Before describing the usage of this class in detail, we will briefly summarize the functions of this class:

• You can copy data in batches from one data source to an SQL Server table.
• You can also perform multiple bulk copy operations.
• You can perform batch replication in database transactions.

Msdn provides more details about sqlbulkcopy methods and attributes. Its most important attribute is destination tablename, and the most important method is writetoserver.

The destinationtablename attribute specifies the table for which the replication record is accepted. Because it has a name composed of three parts (database. owningschema. Name), it follows the SQL Server syntax. You can use its database and all modes to limit the table name.

The database is specified in the connection string (through the initial catalog value ). In addition, if the table name uses an underscore or any other special character, you must use square brackets to avoid such names, such as [database. owningschema. Name].

In fact, the overloaded writetoserver method performs batch replication. It accepts datatable, datarow, and idatareader objects as copy data sources. You can also use a datatable object to include a datarowstate value, specifying to copy only matching rows.

The next Windows console application describes how to copy data from one database table to another. When copying data from its employees table to its backup table employees_bcp, it uses the standard SQL Server 2000 northwind database.

――――――――――――――――――――――――――――

 

View list A (corresponding VB. NET code in list B ). Basically, this code is used to connect to the database and read all values from the employees table into the sqldatareader object.

After the target table name is set, use the sqldatareader object to perform the batch copy operation (it is submitted as a unique parameter ). You can check the target table on the server to see if the data has been copied.

There is no difference in executing multiple updates-you only need to reuse the sqldatareader object. Copying from one table to another in the same database is misuse of BCP. On the other hand, inputting data from an external file is a common operation.

List C uses this method when inputting a text file into the table used in the previous example. (List D contains the corresponding VB. NET code .) This Code creates a new able object and sets columns for each type of data.

Each time you read a file row and separate it with commas (,), each data value is allocated to an appropriate column in the able. Next, the columnmappings attribute of the sqlbulkcopy class allows you to map a column in the data source (Our datatable) to the destination using the column name. After the ing is completed, the writetoserver method uses the able submitted to it to Perform Batch operations.

Best practices for data migration

Although data migration is a task that developers often execute, it is not a very popular task. Share your simplest (worst) data migration experience and best practices with the. NET community in the discussion section below.

―――――――――――――――――――――――――――

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

namespace BCP {

class Program {

static void Main(){

string cString = "Data Source=(local);User ID=tester;Password=tester;Initial Catalog=Northwind;";

using (SqlConnectionconn = new SqlConnection(cString))

{
conn.Open();
SqlCommand comm = new SqlCommand("SELECT EmployeeID, FirstName, LastName, HomePhone FROM Employees;", conn);
SqlDataReader reader = comm.ExecuteReader();

using (SqlConnection conn2 = new SqlConnection(cString)){

conn2.Open();

using (SqlBulkCopy bcp = new SqlBulkCopy(conn2))

{
bcp.DestinationTableName = "dbo.Employees_bcp";

try {
bcp.WriteToServer(reader);

} catch (Exception ex) {
Console.WriteLine(ex.Message);

} finally {
reader.Close();

} } } } } } }

 

―――――――――――――――――――――――――――

Imports System.Data.SqlClient

Module Module1

Sub Main()

Dim cString As String
cString = "Data Source=(local);User ID=tester;Password=tester;Initial Catalog=Northwind;"

Dim conn As New SqlConnection
conn = New SqlConnection(cString)
conn.Open()

Dim comm As SqlCommand
comm                                                                                   

Dim reader As SqlDataReader

reader = comm.ExecuteReader()

Dim conn2 As SqlConnection

conn2 = New SqlConnection(cString)

conn2.Open()

Dim bcp As SqlBulkCopy
bcp = New SqlBulkCopy(conn2)
bcp.DestinationTableName = "dbo.Employees_bcp"

Try
bcp.WriteToServer(reader)

Catch ex As Exception
Console.WriteLine(ex.Message)

Finally
reader.Close()

End Try

End Sub

End Module

 

 

 

 

 

 

 

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using System.IO;

using System.Data;

namespace BCP {

class Program{

static void Main(){

string cString = "Data Source=(local);User ID=tester;Password=tester;Initial Catalog=Northwind;";

using (SqlConnectionconn = new SqlConnection(cString)) {
conn.Open();
SqlCommandcomm = new SqlCommand("SELECT FirstName, LastName, HomePhone, Title FROM Employees;", conn);
SqlDataReader reader = comm.ExecuteReader();

using (SqlConnection conn2 = new SqlConnection(cString)) {

conn2.Open();

using (SqlBulkCopybcp = new SqlBulkCopy(conn2)){
DataTabledt = new DataTable();
DataRowdr;
DataColumn dc;
bcp.DestinationTableName = "dbo.Employees_bcp";

dc = new DataColumn();
dc.ColumnName = "Last";
dt.Columns.Add(dc);

dc = new DataColumn();
dc.ColumnName = "First";
dt.Columns.Add(dc);

dc = new DataColumn();
dc.ColumnName = "HomePhone";
dt.Columns.Add(dc);

dc = new DataColumn();
dc.ColumnName = "Title";
dt.Columns.Add(dc);
StreamReadersr = new StreamReader(@"c:emp.txt");

string input;

while ((input = sr.ReadLine()) != null) {

string[] s = input.Split(new char[] );
dr = dt.NewRow();
dr["Last"] = s[0];
dr["First"] = s[1];
dr["HomePhone"] = s[2];
dr["Title"] = s[3];
dt.Rows.Add(dr);

}
sr.Close();

try {
bcp.ColumnMappings.Add("Last", "LastName");
bcp.ColumnMappings.Add("First", "FirstName");
bcp.ColumnMappings.Add("Title","Title");
bcp.ColumnMappings.Add("HomePhone","HomePhone");
bcp.WriteToServer(dt);

} catch (Exception ex){
Console.WriteLine(ex.Message);

} finally {
reader.Close();

} } } } } } }

 

 

 

 

Imports System.Data

Imports System.Data.SqlClient

Imports System.IO

Module Module1

Sub Main()

Dim cString As String

Dim comma As Char

comma = ","
cString = "Data Source=(local);User ID=tester;Password=tester;Initial Catalog=Northwind;"

Dim conn As New SqlConnection
conn = New SqlConnection(cString)
conn.Open()

Dim comm As SqlCommand
comm = New SqlCommand("SELECT EmployeeID, FirstName, LastName, HomePhone FROM Employees;", conn)

Dim reader As SqlDataReader

reader = comm.ExecuteReader()

Dim conn2 As SqlConnection

conn2 = New SqlConnection(cString)

conn2.Open()

Dim bcp As SqlBulkCopy
bcp = New SqlBulkCopy(conn2)
bcp.DestinationTableName = "dbo.Employees_bcp"

Try

Dim dt As DataTable

Dim dr As DataRow

Dim dc As DataColumn
dt = New DataTable()
bcp.DestinationTableName = "dbo.Employees_bcp"

dc = New DataColumn()
dc.ColumnName = "Last"
dt.Columns.Add(dc)

dc = New DataColumn()
dc.ColumnName = "First"
dt.Columns.Add(dc)

dc = New DataColumn()
dc.ColumnName = "HomePhone"
dt.Columns.Add(dc)

dc = New DataColumn()
dc.ColumnName = "Title"
dt.Columns.Add(dc)

Dim sr As StreamReader
sr = New StreamReader("c:emp.txt")

Dim input As String

input = sr.ReadLine()

While Not (input Is Nothing)

Dim s As String()

s = input.Split(comma)
dr = dt.NewRow()
dr("Last") = s(1)
dr("First") = s(2)
dr("HomePhone") = s(3)
dr("Title") = s(4)
dt.Rows.Add(dr)

input = sr.ReadLine()

End While
sr.Close()
bcp.ColumnMappings.Add("Last", "LastName")
bcp.ColumnMappings.Add("First", "FirstName")
bcp.ColumnMappings.Add("Title", "Title")
bcp.ColumnMappings.Add("HomePhone", "HomePhone")
bcp.WriteToServer(dt)

Catch ex As Exception
Console.WriteLine(ex.Message)

Finally
reader.Close()

End Try

End Sub

End Module

Using sqlbulkcopy to copy data is faster, and the created table (created table) can also be used as the original table to copy data to the target table of the database!