/// <summary>
/// 使用SqlBulkCopy将DataTable中的数据批量插入数据库中
/// </summary>
/// <param name="strTableName">数据库中对应的表名</param>
/// <param name="dtData">数据集</param>
/// <param name="dicFields">中英文对应字典</param>
/// <returns></returns>
public string SqlBulkCopyInsert(string connStr, string strTableName, DataTable dtData, Dictionary<string, string> dicFields = null)
{
try
{
using (SqlBulkCopy sqlCopy = new SqlBulkCopy(connStr))//引用SqlBulkCopy
{
sqlCopy.BulkCopyTimeout = 6000;//6s没有操作完成失败 sqlCopy.DestinationTableName = strTableName;//数据库中对应的表名 foreach (DataColumn item in dtData.Columns)
{
try
{
if (dicFields != null)
{
//映射列头和字段名称
item.ColumnName = dicFields[item.ColumnName.Replace(" ", "")];
}
sqlCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName);
}
catch (Exception ex)
{
NLogHelper.Error("数据字典和字段{" + item.ColumnName + "}不对应！", ex);
}
}
sqlCopy.WriteToServer(dtData);//数据导入数据库 sqlCopy.Close();//关闭连接
}
return null;
}
catch (Exception ex)
{
NLogHelper.Error("批量插入数据出错！", ex);
return ex.Message;
}
}
//映射表头和数据库表字段的关系
public static Dictionary<string, string> dicFields = new Dictionary<string, string>() {
{"型号","Model"},{"品牌","Brand"},{"批号","MakeYear"}
};