Introduced:
In software development, copying data from one place to another is a common application. This can be done on many different occasions, including porting old systems to new systems, backing up data from different databases, and collecting data. ASP. NET 2.0 has a SqlBulkCopy class that helps you replicate data from different data sources to a SQL Server database. In this article I will demonstrate the 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 created 3 tables in the Northwind database. See the following database diagram for details.
Products_Archive and products_latest have the same structure as the Products table, while the products_topselling table differs from them. I will explain the purpose of the Products_topselling table later in this document. The Products_Archive table contains 770,000 rows. You don't have to worry about how this data is obtained, you just need to think about how to copy all of this data into the Products_latest table.
Copy data from the Products_Archive table to the Products_latest table:
SqlBulkCopy contains a method, WriteToServer, that is used to replicate data from the source of the data to the destination of the data. The WriteToServer method can handle data types with datarow[] arrays, DataTable, and DataReader. You can use different data types depending on the situation, but it's a good idea to choose 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 purpose of the following code is to copy the data from the source table to the destination table.PrivateStaticvoidPerformbulkcopy ()
{
stringConnectionString =@ "Server=localhost;database=northwind; Trusted_connection=true ";
//Source
using(SqlConnection sourceconnection =NewSqlConnection (connectionString))
{
SqlCommand mycommand =NewSqlCommand ("SELECT * from Products_Archive", sourceconnection);
Sourceconnection.open ();
SqlDataReader reader = Mycommand.executereader ();
//Purpose
using(SqlConnection destinationconnection =NewSqlConnection (connectionString))
{
//Open Connection
Destinationconnection.open ();
using(SqlBulkCopy bulkcopy =NewSqlBulkCopy (destinationconnection.connectionstring))
{
Bulkcopy.batchsize = 500;
Bulkcopy.notifyafter = 1000;
Bulkcopy.sqlrowscopied + =NewSqlrowscopiedeventhandler (bulkcopy_sqlrowscopied);
Bulkcopy.destinationtablename ="Products_latest";
Bulkcopy.writetoserver (reader);
}
}
Reader. Close ();
}
}
Here are a couple of points of knowledge to mention. First, I use DataReader to read data from tables in the database. Products_latest is the destination table because the data is copied from the Products_Archive table to the Products_latest table. The BulkCopy object provides a sqlrowcopied event that occurs each time the number of rows specified by the Notifyafter property is processed. In this case, the event is triggered once every 1000 lines have been processed, because Notifyafter is set to the BatchSize property is very important, how the program performance depends mainly on it. BatchSize means the number of rows in the same batch, and at the end of each batch, the rows in the batch are sent to the database. I set the batchsize to 500, which means that reader sends them to the database to perform a bulk copy operation every 500 lines read. The default value for BatchSize is "1", which means that each row is sent to the database as a batch. Setting different batchsize will bring you different results in performance. You should test according to your needs to determine the size of the batchsize.
Copying data between different mapping tables
In the example above, the two tables have the same structure. Sometimes you need to copy data between tables that have different structures. If you want to copy all the product names and their quantities from the Products_Archive table to the Products_topselling list. The two tables have different field names, which can be seen in the "Database Design" section above.PrivateStaticvoidPerformbulkcopydifferentschema ()
{
stringConnectionString =@ "Server=localhost;database=northwind; Trusted_connection=true ";
DataTable SourceData =NewDataTable ();
//Source
using(SqlConnection sourceconnection =NewSqlConnection (connectionString))
{
SqlCommand mycommand =NewSqlCommand ("SELECT TOP 5 * from Products_Archive", sourceconnection);
Sourceconnection.open ();
SqlDataReader reader = Mycommand.executereader ();
//Purpose
using(SqlConnection destinationconnection =NewSqlConnection (connectionString))
{
//Open Connection
Destinationconnection.open ();
using(SqlBulkCopy bulkcopy =NewSqlBulkCopy (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 collection is used to map columns between the source table and the destination table. To copy data from an XML file into a table in a database
The data source is not limited to the database tables, you can also use XML files to do the data source. Here is an example of a very simple batch copy operation using an XML file for a data source.
(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>PrivateStaticvoidPerformbulkcopyxmldatasource ()
{
stringConnectionString =@ "Server=localhost;database=northwind; Trusted_connection=true ";
DataSet ds =NewDataSet ();
DataTable SourceData =NewDataTable ();
Ds. READXML (@ "C:products.xml");
SourceData = ds. Tables[0];
//Purpose
using(SqlConnection destinationconnection =NewSqlConnection (connectionString))
{
//Open Connection
Destinationconnection.open ();
using(SqlBulkCopy bulkcopy =NewSqlBulkCopy (destinationconnection.connectionstring))
{
//Column Mappings
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 intent is to represent products_topselling, we must perform column mappings.
Conclusion
In this article I demonstrated how to use the SqlBulkCopy class introduced with. NET 2.0. The SqlBulkCopy class can easily replicate data from one data source to a SQL Server database.
Bulk copy data using the SqlBulkCopy class in ASP. NET 2.0
