我们进行进行系统开发,很多时候都是在旧有的系统上升级或者是改版,这样不可避免的就会涉及到一个数据迁移的问题.迁移数据感觉比较麻烦的是:1:数据库接口异构;2:数据库系统异构,比如说sql和oracle.3:迁移的速度
现在这里介绍一个使用ado.net中的sqlbulkcopy进行快速数据复制 有百万每分钟的速度哦
下面列举一个简单例子:
string connectionString = ConfigurationManager.ConnectionStrings["OldCity"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connectionString);
SqlCommand myCommand = new SqlCommand("select * from tb_bbs_data where status=0", myConnection);
myCommand.CommandTimeout = 36000;
myConnection.Open();
SqlDataReader dr = myCommand.ExecuteReader();
SqlBulkCopy bulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["default"].ConnectionString);
//bulk.BatchSize = 100;
bulk.BulkCopyTimeout = 36000;
bulk.DestinationTableName = "tb_Article_New";
bulk.ColumnMappings.Add("boardid", "CityId");
bulk.ColumnMappings.Add("title", "Title");
bulk.ColumnMappings.Add("content", "Content");
bulk.ColumnMappings.Add("author_id", "UserId");
bulk.ColumnMappings.Add("author_name", "NickName");
bulk.ColumnMappings.Add("flower_count", "FlowerNum");
bulk.ColumnMappings.Add("egg_count", "EegNum");
bulk.ColumnMappings.Add("huifu_count", "ReplyNum");
bulk.ColumnMappings.Add("view_count", "Views");
bulk.ColumnMappings.Add("submit_time", "PubTime");
try
{
bulk.WriteToServer(dr);
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
dr.Close();
bulk.Close();
}
我使用这个来转移旧同城的88w条主贴数据,只要了一分钟多点,oh,my god!
现在这里介绍一个使用ado.net中的sqlbulkcopy进行快速数据复制 有百万每分钟的速度哦
下面列举一个简单例子:
string connectionString = ConfigurationManager.ConnectionStrings["OldCity"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connectionString);
SqlCommand myCommand = new SqlCommand("select * from tb_bbs_data where status=0", myConnection);
myCommand.CommandTimeout = 36000;
myConnection.Open();
SqlDataReader dr = myCommand.ExecuteReader();
SqlBulkCopy bulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["default"].ConnectionString);
//bulk.BatchSize = 100;
bulk.BulkCopyTimeout = 36000;
bulk.DestinationTableName = "tb_Article_New";
bulk.ColumnMappings.Add("boardid", "CityId");
bulk.ColumnMappings.Add("title", "Title");
bulk.ColumnMappings.Add("content", "Content");
bulk.ColumnMappings.Add("author_id", "UserId");
bulk.ColumnMappings.Add("author_name", "NickName");
bulk.ColumnMappings.Add("flower_count", "FlowerNum");
bulk.ColumnMappings.Add("egg_count", "EegNum");
bulk.ColumnMappings.Add("huifu_count", "ReplyNum");
bulk.ColumnMappings.Add("view_count", "Views");
bulk.ColumnMappings.Add("submit_time", "PubTime");
try
{
bulk.WriteToServer(dr);
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
dr.Close();
bulk.Close();
}
我使用这个来转移旧同城的88w条主贴数据,只要了一分钟多点,oh,my god!