SqlBulkCopy批量转移数据备注

今天测试了SqlBulkCopy，速度确实很快，备份备注记一下：

datatable中每个字段都要和数据表中进行对应，且不能缺少，自增的随便传递数据，sql那边会自己处理

 

       string moveConn = @"***";
       string connectionString = @"***";  
     public string move()
       {
           Stopwatch stopwatch = new Stopwatch();
           stopwatch.Start();
           string result = string.Empty;
           int count = 0;
           using (var SqlDB = new Voosay.DataBase.SqlDataBase(moveConn))
           {
               DataTable dt = new DataTable();
               dt.TableName = "test";
               dt.Columns.Add(new DataColumn("PkID", typeof(Int64)));
               dt.Columns.Add(new DataColumn("CustID", typeof(int)));
               dt.Columns.Add(new DataColumn("PayComID", typeof(long)));
               dt.Columns.Add(new DataColumn("TypeID", typeof(int)));
               dt.Columns.Add(new DataColumn("IsEnable", typeof(int)));
               dt.Columns.Add(new DataColumn("CityID", typeof(int)));
               dt.Columns.Add(new DataColumn("ClassID", typeof(int)));
               dt.Columns.Add(new DataColumn("CreateTime", typeof(DateTime)));
               string sql = "***;
               DataSet ds = SqlDB.Datasql(sql);
               if (ds.Tables[0].Rows.Count > 0)
               {
                   int i = 1;
                   foreach (DataRow row in ds.Tables[0].Rows)
                   {
                       DataRow newrows = dt.NewRow();
                       int t = Convert.ToInt32(row["PaymentPlatform"]);
                       newrows["PkID"] = 1;
                       newrows["CustID"] = 20100520;
                       newrows["PayComID"] = Convert.ToInt64(row["appkey"]);
                       newrows["TypeID"] = t;
                       newrows["IsEnable"] = Convert.ToInt32(row["Enable"]);
                       newrows["CityID"] = Convert.ToInt32(row["SiteID"]);
                       newrows["ClassID"] = 1;
                       if (t < 10)
                       {
                           newrows["ClassID"] = 0;
                       }
                       newrows["CreateTime"] = Convert.ToDateTime(row["Createdate"]);
                       dt.Rows.Add(newrows);
                       Console.WriteLine(string.Format("{0}|{1}|{2}|{3}|{4}|{5}|{6}",newrows["CustID"],newrows["PayComID"],newrows["TypeID"]
                           ,newrows["IsEnable"],newrows["CityID"],newrows["ClassID"],newrows["CreateTime"]));
                       i++;
                   }
                   movedata(dt);
                   count = ds.Tables[0].Rows.Count;
               }

           }
           stopwatch.Stop();
           result = string.Format("执行{0}条记录转移，运行时间为：{1}毫秒", count, stopwatch.ElapsedMilliseconds);
           return result;
       }
       private void movedata(DataTable dt)
       {
           SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString);
           sqlBulkCopy.DestinationTableName = "*";
           sqlBulkCopy.BatchSize = dt.Rows.Count;
           SqlConnection sqlConnection = new SqlConnection(connectionString);
           sqlConnection.Open();
           if (dt != null && dt.Rows.Count != 0)
           {
               sqlBulkCopy.WriteToServer(dt);
           }
           sqlBulkCopy.Close();
           sqlConnection.Close();
       }