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;?? ?????? }?? ?? }?
