|
Using SqlBulkCopy class
This class can be used for performing bulk copy of data
from one table to another table in same database server or different server
similar to DTS and bcp utility. It can also be used to bulk copy from Sql server
2005 database table to Sql server 2000 database table, keeping in mind that
destination table will have all the columns matching with the one we are copying
from the source.
The data source can be anything, SQL Server or DataTable
or even DataReader object but with one condition, the destination should be a
SQL server.
This SqlBulkCopy class is packed with a method called
WriteToServer() which will help us doing the same. Below are the different
overloads of the method.
SqlBulkCopy.WriteToServer (DataRow[])
SqlBulkCopy.WriteToServer (DataTable)
SqlBulkCopy.WriteToServer (IDataReader)
SqlBulkCopy.WriteToServer (DataTable, DataRowState)
Moving forward, we will see how to upload the data rows
in DataTable object at a shot using SqlBulkCopy class.
For simple understanding, let’s assume you have a
DataTable object which has loaded with Employee details. Let’s read the content
of the DataTable object and do a bulk upload of the data rows using SqlBulkCopy
class.
At a high level, we can do this by following the below
steps,
1. Populate or get the DataTable
that needs to be uploaded.
2. Create a SqlBulkCopy instance
by providing an opened connection to the destination server.
3. Set the ColumnMappings
collection. The ColumnMappings collection of SqlBulkCopy needs to be populated
with the source and destination column name mapping.
4. Optionally, set the batch
size.
5. Set the Destination table name
in DestinationTableName property of SqlBulkCopy.
6. Call WriteToServer() method.
That’s it! You are done.
The below code does that,
//Get Data from your DataSource or other source
DataTable dt = GetAPILogData();
using (_dconn = new
SqlConnection(ConfigurationSettings.AppSettings["connection"]))
{
_dconn.Open();
//Do a bulk copy for gaining better
performance
using (SqlBulkCopy bulkCopy = new
SqlBulkCopy(_dconn))
{
bulkCopy.ColumnMappings.Add("Name",
"EmployeeName");
bulkCopy.ColumnMappings.Add("Age",
"EmployeeAge");
bulkCopy.ColumnMappings.Add("Address1", "StreetAddress1");
bulkCopy.ColumnMappings.Add("Address2", "StreetAddress2");
bulkCopy.ColumnMappings.Add("City",
"City");
bulkCopy.ColumnMappings.Add("State","State");
bulkCopy.ColumnMappings.Add("Country","CountryCode");
bulkCopy.ColumnMappings.Add("PIN","ZIP");
bulkCopy.ColumnMappings.Add("CreatedDate", "CreatedDate");
bulkCopy.ColumnMappings.Add("CreatedBy", "CreatedBy");
bulkCopy.BatchSize = 10000;
bulkCopy.BulkCopyTimeout =
int.Parse(ConfigurationSettings.AppSettings["timeout"]);
bulkCopy.DestinationTableName =
"EmployeeDetail";
bulkCopy.WriteToServer(dt.CreateDataReader());
}
}
|