今天测试了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();
}
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();
}