多线程的SqlBulkCopy批量导入、事务和SqlBulkCopy使用代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】多线程的SqlBulkCopy批量导入、事务和SqlBulkCopy使用
作者:Dezai.CN / 发布于2011/5/4/ 1285
<div>class Program
 {
 static volatile bool result;
 static void Main(string[] args)
 {
 DataSet ds = ExportDataSet();</div>
<div> //使用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();
 }
<div> /// <summary>
 /// 获取数据DataSet
 /// </summary>
 /// <returns></returns></div>
<div> static private DataSet ExportDataSet()
 {</div>
<div> //局域网的某服务器模拟数据库远程连接
 SqlConnection RemoteConn = new SqlConnection("Data Source=192.168.0.183;Initial Catalog=Northwind;User ID=sa;Password=sa");</div>
<div></div>
<div> using (</div>
<div> /*目标表与源表结构并不相同，目标表只包含OrderID、CustomerID、EmployeeID、ShipCountry这四个字段。注意这里字段是区分大小写的，不然SqlBulkCopy的WriteToServer方法会报运行时异常：&ldquo;给定的 ColumnMapping 与源或目标中的任意列均不匹配&rdquo;的处理方法。这个地方浪费了我1个小时才发现*/
 SqlDataAdapter oda = new SqlDataAdapter("SELECT [OrderID], [CustomerID], [EmployeeID], [ShipCountry] FROM [Northwind].[dbo].[Orders]", RemoteConn))</div>
<div> //如果目标表与源表结构完全一致，则用下面语句即可，msdn的例子也只是这样</div>
<div> //SqlDataAdapter oda = new SqlDataAdapter("SELECT * FROM [Ednoland].[dbo].[Score]", RemoteConn))
 {
 DataSet ds = new DataSet();</div>
<div> oda.Fill(ds, "Orders");//给定表名</div>
<div> return ds;</div>
<div> }
<div> }
<div> /// <summary>
 /// 将DataSet导入远程数据库(未来放在WebService中)
 /// </summary>
 /// <param name="ds"></param>
 /// <returns></returns>
 public static bool Insert(DataSet ds)
 {</div>
<div>
 using (SqlConnection sqlconn = new SqlConnection("Data Source=.;Initial Catalog=Northwind;User ID=sa;Password=sa"))
 {
 sqlconn.Open();
 SqlTransaction sqlbulkTransaction = sqlconn.BeginTransaction(IsolationLevel.ReadCommitted);</div>
<div></div>
<div> 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;</div>
<div> if (ds.Tables.Count == 1)
 {</div>
<div>
 return BulkInsert(sbc, ds.Tables[0], sqlbulkTransaction); ;</div>
<div> }
 else
 {
 bool res = true;
 foreach (DataTable dt in ds.Tables)
 {</div>
<div> res = BulkInsert(sbc, dt, sqlbulkTransaction);</div>
<div> }
 return res;
 }
<div> }
 }
<div> }
<div> private static bool BulkInsert(SqlBulkCopy sbc, DataTable dt, SqlTransaction sqlbulkTransaction)
 {
 
 bool res = true;
 try
 {</div>
<div> //将DataTable表名作为待导入库中的目标表名
 sbc.DestinationTableName = dt.TableName;</div>
<div></div>
<div> //将数据集合和目标服务器库表中的字段对应
 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);//可以避免字段大小写不一致造成无法映射的问题
 }
<div> sbc.WriteToServer(dt);</div>
<div> //提交事务
 sqlbulkTransaction.Commit();
 res = true;
 }
 catch (SqlException ex)
 {
 res = false;
 sqlbulkTransaction.Rollback();
 }
<div> return res;
 }
 }
</div>
试试其它关键字
　SqlBulkCopy　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)