SqlBulkCopy快速批量上传大量数据到SqlServer

SqlBulkCopy快速批量上传大量数据到SqlServer

private static bool DataTableToSQLServer(DataTable dt,ref string strMessage)

{
string connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
bool flag = true;
using (SqlConnection destinationConnection = new SqlConnection(connectionString))
{
destinationConnection.Open();

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
{
try
{
bulkCopy.DestinationTableName = "TEST";//要插入的表的表明
bulkCopy.BatchSize = 100000;
bulkCopy.BulkCopyTimeout = 1800;
bulkCopy.ColumnMappings.Add("厂区", "Plant");//映射字段名 DataTable列名 ,数据库对应的列名
bulkCopy.ColumnMappings.Add("部门", "Depnam");
bulkCopy.ColumnMappings.Add("工号", "Emplid");
bulkCopy.ColumnMappings.Add("姓名", "Chinam");
bulkCopy.ColumnMappings.Add("年龄", "Age");
bulkCopy.ColumnMappings.Add("日期", "Wrkdat");
bulkCopy.ColumnMappings.Add("刷卡入厂", "Entdat");
bulkCopy.ColumnMappings.Add("刷卡离厂", "Stwkhr");
bulkCopy.ColumnMappings.Add("班别", "Sftcod");
bulkCopy.ColumnMappings.Add("加班开始", "Stovhr");
bulkCopy.ColumnMappings.Add("加班结束", "Enovhr");
bulkCopy.ColumnMappings.Add("加班时数", "Overhr");
bulkCopy.ColumnMappings.Add("加班系数", "Holcod");
bulkCopy.ColumnMappings.Add("事由", "Overmk");
bulkCopy.ColumnMappings.Add("到职日", "Frmdat");
bulkCopy.ColumnMappings.Add("员工别", "Wpctgy");
bulkCopy.ColumnMappings.Add("出生日期", "Birdat");
bulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
strMessage=ex.Message;
flag = false;
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
destinationConnection.Close();
}
}
}
return flag;
}