多线程的SqlBulkCopy批量导入、事务和SqlBulkCopy使用的数据集中自定义映射字段的注意事项
class Program
{
static volatile bool result;
static void Main(string[] args)
{
DataSet ds = ExportDataSet();
//使用2个线程模拟并发操作
Thread t = new Thread(delegate() { result = Insert(ds); Console.WriteLine(result ? "导入成功" : "导入失败"); });
t.Start();
Thread t1 = new Thread(delegate() { result = Insert(ds); Console.WriteLine(result ? "导入成功" : "导入失败"); });
t1.Start();
Console.ReadLine();
}
/// <summary>
/// 获取数据DataSet
/// </summary>
/// <returns></returns>
static private DataSet ExportDataSet()
{
//局域网的某服务器模拟数据库远程连接
SqlConnection RemoteConn = new SqlConnection("Data Source=192.168.0.183;Initial Catalog=Northwind;User ID=sa;Password=sa");
using (
/*目标表与源表结构并不相同,目标表只包含OrderID、CustomerID、EmployeeID、ShipCountry这四个字段。注意这里字段是区分大小写的,不然SqlBulkCopy的WriteToServer方法会报运行时异常:“给定的 ColumnMapping 与源或目标中的任意列均不匹配”的处理方法。这个地方浪费了我1个小时才发现*/
SqlDataAdapter oda = new SqlDataAdapter("SELECT [OrderID], [CustomerID], [EmployeeID], [ShipCountry] FROM [Northwind].[dbo].[Orders]", RemoteConn))
//如果目标表与源表结构完全一致,则用下面语句即可,msdn的例子也只是这样
//SqlDataAdapter oda = new SqlDataAdapter("SELECT * FROM [Ednoland].[dbo].[Score]", RemoteConn))
{
DataSet ds = new DataSet();
oda.Fill(ds, "Orders");//给定表名
return ds;
}
}
/// <summary>
/// 将DataSet导入远程数据库(未来放在WebService中)
/// </summary>
/// <param name="ds"></param>
/// <returns></returns>
public static bool Insert(DataSet ds)
{
using (SqlConnection sqlconn = new SqlConnection("Data Source=.;Initial Catalog=Northwind;User ID=sa;Password=sa"))
{
sqlconn.Open();
SqlTransaction sqlbulkTransaction = sqlconn.BeginTransaction(IsolationLevel.ReadCommitted);
using (SqlBulkCopy sbc = new SqlBulkCopy(sqlconn, SqlBulkCopyOptions.KeepIdentity, sqlbulkTransaction))
{
sbc.BatchSize = 20000;//20000行每连接
sbc.BulkCopyTimeout = 50;//50秒超时
if (ds.Tables == null || ds.Tables.Count == 0)
return false;
if (ds.Tables.Count == 1)
{
return BulkInsert(sbc, ds.Tables[0], sqlbulkTransaction); ;
}
else
{
bool res = true;
foreach (DataTable dt in ds.Tables)
{
res = BulkInsert(sbc, dt, sqlbulkTransaction);
}
return res;
}
}
}
}
private static bool BulkInsert(SqlBulkCopy sbc, DataTable dt, SqlTransaction sqlbulkTransaction)
{
bool res = true;
try
{
//将DataTable表名作为待导入库中的目标表名
sbc.DestinationTableName = dt.TableName;
//将数据集合和目标服务器库表中的字段对应
for (int i = 0; i < dt.Columns.Count; i++)
{
//sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
sbc.ColumnMappings.Add(i,i);//可以避免字段大小写不一致造成无法映射的问题
}
sbc.WriteToServer(dt);
//提交事务
sqlbulkTransaction.Commit();
res = true;
}
catch (SqlException ex)
{
res = false;
sqlbulkTransaction.Rollback();
}
return res;
}
}