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.
{
{
SqlCommand myCommand sourceConnection. Open ();
SqlDataReader reader
{
{
BulkCopy. BatchSize bulkCopy. policyafter bulkCopy. SqlRowsCopied bulkCopy. DestinationTableName bulkCopy. WriteToServer (reader );
}
}
Reader. Close ();
}
} Here 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.
{
DataTable sourceData
{
SqlCommand myCommand sourceConnection. Open ();
SqlDataReader reader
{
{
BulkCopy. ColumnMappings. Add (bulkCopy. DestinationTableName 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)
{
DataSet ds DataTable sourceData ds. ReadXml (
SourceData
{
{
BulkCopy. ColumnMappings. Add (
BulkCopy. DestinationTableName 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.
