Use the sqlbulkcopy class in Asp.net 2.0 to copy data in batches

Use the sqlbulkcopy class in Asp.net 2.0 to copy data in batches

Introduction:
In software development, copying data from one place to another is a common application. This operation is performed in many different scenarios, including porting the old system to the new system, backing up and collecting data from different databases. ASP. NET 2.0 has a sqlbulkcopy class that helps you copy data from different data sources to the SQL Server database. In this article, I will demonstrate different applications of the sqlbulkcopy class.

Database Design:
The design of this database is quite simple. It is based on the products table of the northwind database. In addition, I have created three tables in the northwind database. For more information, see the database relationship diagram under "tables.

Products_archive and products_latest have the same structure as the products table, while the products_topselling table is different from them. Later, I will explain the usage of the products_topselling table in this article.

The products_archive table contains 770,000 rows. You don't have to worry about how the data is obtained. You just need to consider how to copy all the data to the products_latest table.

Copy data from the products_archive table to the products_latest table:
Sqlbulkcopy contains the writetoserver method, which is used to copy data from the data source to the data destination. The writetoserver method can process datarow [] arrays, able, and datareader data types. You can use different data types according to different situations, but it is a good idea to select datareader more often. This is because datareader is a forward-only, read-only data stream that does not save data, so it is faster than datatable and datarows. The following code copies data from the source table to the target table.

Private Static void merge mbulkcopy ()
{
String connectionstring = @ "Server = localhost; database = northwind; trusted_connection = true ";

// Source
Using (sqlconnection sourceconnection = new sqlconnection (connectionstring ))
{
Sqlcommand mycommand = new sqlcommand ("select * From products_archive", sourceconnection );
Sourceconnection. open ();
Sqldatareader reader = mycommand. executereader ();

// Purpose
Using (sqlconnection destinationconnection = new sqlconnection (connectionstring ))
{
// Open the connection
Destinationconnection. open ();

Using (sqlbulkcopy bulkcopy = new sqlbulkcopy (destinationconnection. connectionstring ))
{
Bulkcopy. batchsize = 500;
Bulkcopy. policyafter = 1000;
Bulkcopy. sqlrowscopied + = new sqlrowscopiedeventhandler (bulkcopy_sqlrowscopied );
Bulkcopy. destinationtablename = "products_latest ";
Bulkcopy. writetoserver (Reader );
}
}

Reader. Close ();

}
}

There is a pair of knowledge points to be mentioned. First, I use datareader to read data from the database table. Products_latest is the destination table, because data needs to be copied from the products_archive table to the products_latest table. The bulkcopy object provides a sqlrowcopied event that occurs when the number of rows specified by the policyafter attribute is processed each time. In this example, this event is triggered every time 1000 rows are processed, because policyafter is set to 1000

The batchsize attribute is very important, and the program performance depends on it. Batchsize indicates the number of rows in each batch. At the end of each batch, the rows in this batch are sent to the database. I set the batchsize to 500, which means that the reader sends them to the database every 500 rows read to perform the batch copy operation. The default value of batchsize is "1", which means that each row is sent to the database as a batch.

Setting different batchsize results in different performance. You should test the batchsize according to your needs.

Copy data between different ing tables
In the preceding example, the two tables have the same structure. Sometimes, you need to copy data between tables with different structures. Suppose you want to copy all product names and their quantity from the products_archive table to the products_topselling table. The two tables have different field names. For details, refer to the section "Database Design" above.

Private Static void merge mbulkcopydifferentschema ()
{
String connectionstring = @ "Server = localhost; database = northwind; trusted_connection = true ";

Datatable sourcedata = new datatable ();

// Source
Using (sqlconnection sourceconnection = new sqlconnection (connectionstring ))
{
Sqlcommand mycommand = new sqlcommand ("select top 5 * From products_archive", sourceconnection );
Sourceconnection. open ();
Sqldatareader reader = mycommand. executereader ();

// Purpose
Using (sqlconnection destinationconnection = new sqlconnection (connectionstring ))
{
// Open the connection
Destinationconnection. open ();

Using (sqlbulkcopy bulkcopy = new sqlbulkcopy (destinationconnection. connectionstring ))
{
Bulkcopy. columnmappings. Add ("productid", "productid ");
Bulkcopy. columnmappings. Add ("productname", "name ");
Bulkcopy. columnmappings. Add ("quantityperunit", "quantity ");
Bulkcopy. destinationtablename = "products_topselling ";
Bulkcopy. writetoserver (Reader );
}
}

Reader. Close ();

}
}

The columnmappings set is used to map columns between the source table and the target table.

Copy data from an XML file to a database table
Data sources are not limited to database tables. You can also use XML files as data sources. Here is a simple example of using XML files for data source batch copying.
(Products. XML)

<? XML version = "1.0" encoding = "UTF-8"?>
<Products>
<Product productid = "1" productname = "chai"/>
<Product productid = "2" productname = "football"/>
<Product productid = "3" productname = "Soap"/>
<Product productid = "4" productname = "Green Tea"/>
</Products>

 

Private Static void extends mbulkcopyxmldatasource ()
{
String connectionstring = @ "Server = localhost; database = northwind; trusted_connection = true ";

Dataset DS = new dataset ();
Datatable sourcedata = new datatable ();
DS. readxml (@ "C: products. xml ");

Sourcedata = Ds. Tables [0];

// Purpose
Using (sqlconnection destinationconnection = new sqlconnection (connectionstring ))
{
// Open the connection
Destinationconnection. open ();

Using (sqlbulkcopy bulkcopy = new sqlbulkcopy (destinationconnection. connectionstring ))
{
// Column ing
Bulkcopy. columnmappings. Add ("productid", "productid ");
Bulkcopy. columnmappings. Add ("productname", "name ");

Bulkcopy. destinationtablename = "products_topselling ";
Bulkcopy. writetoserver (sourcedata );
}
}
}

First, read the XML file into the datatable, and then use the writetoserver method of the sqlbulkcopy class. Because the purpose is products_topselling, we must perform column ing.

Conclusion
This article demonstrates how to use the sqlbulkcopy class introduced by. NET 2.0. The sqlbulkcopy class can easily copy data from a data source to the SQL Server database.

I hope you will like this article and wish you a pleasant programming!