数据批量拷贝
前几天工作需要批量数据数据拷贝,就写了个小系统.
由于是在局域网进行拷贝,所以没有测试外网.
拷了大约100万条记录,用时约32.750秒.
下面是设计方法:
1. 获取源数据库,和目标数据库
(1) 这里用本机的数据库作为源数据库,
(2) 枚举出局域网内的数据库实例
2. 选择需要拷贝的表,和目标表
3. 选择拷贝拷贝方式,( 整表拷贝还是配置特定字段拷贝,如果表的结构不同应该用用配置拷贝 )
4. 如果是配置拷贝,则需要配置拷贝字段,
5 拷贝.
主要实现代码:
1. 枚举局域网数据库实例服务器
/// <summary>
/// 枚举本地网络内的所有可用 SQL Server 数据库务器名称
/// </summary>
/// <returns></returns>
public static object[] GetDataBaseEntitys()
{
// SqlDataSourceEnumerator
//提供了一种枚举本地网络内的所有可用 SQL Server 实例的机制
//
SqlDataSourceEnumerator sse = SqlDataSourceEnumerator.Instance;
DataTable dt = sse.GetDataSources();
if (dt == null) return (new object[0]);
if (dt.Rows.Count == 0) return (new object[0]);
List<object> obj = new List<object>();
for (int i = 0; i < dt.Rows.Count; i++)
{
//if (dt.Rows[i][1].ToString() != string.Empty)
obj.Add(dt.Rows[i][0] + "//" + dt.Rows[i][1]);
//注意界面上的服务器列表,如果其选项 类似:xiage/ 请把 / 去掉
//原则上可以拷贝所有.net支持的数据库数据到 MSSQL Server
}
object[] cache = new object[obj.Count];
for (int i = 0; i < obj.Count; i++)
{
cache[i] = obj[i];
}
return cache;
}
2. 列举出一个数据库服务器中的所有数据库
/// <summary>
/// 枚举一个数据库服务器实体中的所有数据库
/// </summary>
/// <returns></returns>
public static object[] GetDataBases()
{
List<object> objList = new List<object>();
if (con.State != ConnectionState.Open)
try
{
con.ConnectionString = ConnectionString;
con.Open();
}
catch
{
return (new object[0]);
}
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = con;
//从系统表里查找所有的数据库名称
cmd.CommandText = "select name from master..sysdatabases";
cmd.CommandType = CommandType.Text;
using (System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader())
{
object obj = null;
while (dr.Read())
{
string name = dr.GetString(0);
//排除系统表
if (name != "master" && name != "model" && name != "tempdb" && name != "msdb" && name != "pubs")
{
obj = new object();
obj = name;
objList.Add(obj);
}
}
}
object[] cache = new object[objList.Count];
for (int i = 0; i < objList.Count; i++)
{
cache[i] = objList[i];
}
return cache;
}
}
3. 列举出数据库中的所有用户表
/// <summary>
/// 枚举数据库中的表
/// </summary>
/// <param name="databaseName">数据库名称</param>
/// <returns></returns>
public static object[] GetTables(string databaseName)
{
//查询系统表中存储所有类型为 U 的表,即用户定义的表。
string sqlCommand = "select * from sysobjects where xtype='U'";
return GetDataBaseInfo(sqlCommand,databaseName);
}
4. 如果是配置拷贝则需要配置字段,我们需要把标的字段列举出来
查询系统表 syscolumns(列), sysobjects(表) 可以得到列
/// <summary>
/// 枚举数据库表的字段
/// </summary>
/// <param name="tableName">表名称</param>
/// <param name="databaseName">数据库名称</param>
/// <returns></returns>
public static object[] GetColumns(string tableName,string databaseName)
{
if (tableName == string.Empty) return (new object[0]);
//可以在界面上多显示一些信息,这里只用到字段名称,其他属性例如字段类型、大小等都没显示。。。
string sqlCommand = @"select a.name ,d.name,b.name,a.length,a.isnullable
from syscolumns a, systypes b,sysobjects d
where a.xtype=b.xusertype and a.id=d.id and d.name='" + tableName + "'";
return GetDataBaseInfo(sqlCommand, databaseName);
}
5. GetDataBaseInfo
/// <summary>
/// 根据制定条件获取数据库信息
/// </summary>
/// <param name="sqlCommand">sql命令</param>
/// <param name="databaseName">数据库名称</param>
/// <returns></returns>
public static object[] GetDataBaseInfo(string sqlCommand,string databaseName)
{
if (databaseName == string.Empty) return (new object[0]);
List<object> objList = new List<object>();
using (con = new System.Data.SqlClient.SqlConnection())
{
con.ConnectionString = PartCon + databaseName;
try
{
con.Open();
}
catch
{
return (new object[0]);
}
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = con;
cmd.CommandText = sqlCommand;
cmd.CommandType = CommandType.Text;
using (System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader())
{
object obj = null;
while (dr.Read())
{
obj = new object();
obj = dr[0];
objList.Add(obj);
}
}
}
}
object[] cache = new object[objList.Count];
for (int i = 0; i < objList.Count; i++)
{
cache[i] = objList[i];
}
return cache;
}
}
5. 拷贝数据
/// <summary>
/// 拷贝数据
/// </summary>
/// <param name="isAll">是否正表拷贝</param>
/// <param name="sourceItem">原表字段</param>
/// <param name="targetItem">目标字段</param>
/// <param name="sourceCon">原数据库连接字符串</param>
/// <param name="targetCon">目标数据库连接字符串</param>
/// <param name="sourceTable">原表名称</param>
/// <param name="targetTable">目标表名称</param>
/// <returns></returns>
public static OptResult BulkCopy(int isAll,object [] sourceItem,object [] targetItem,string sourceStr,string targetStr,string sourceTable,string targetTable)
{
OptResult opt = new OptResult();
DateTime sTime = DateTime.Now;
using (System.Data.SqlClient.SqlConnection sourceCon = new System.Data.SqlClient.SqlConnection(sourceStr))
{
try
{
sourceCon.Open();
opt.IsSuccess = true;
}
catch(Exception openErr)
{
opt.IsSuccess = false;
opt.ErrMsg = openErr.Message;
return opt;
}
DataTable dt = null;
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("select * from " + sourceTable.Trim(), sourceCon))
{
using (System.Data.SqlClient.SqlDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter(cmd))
{
dt = new DataTable();
adp.Fill(dt);
}
}
//定义拷贝对象
System.Data.SqlClient.SqlBulkCopy LargeCopy = new System.Data.SqlClient.SqlBulkCopy(targetStr, System.Data.SqlClient.SqlBulkCopyOptions.UseInternalTransaction);
//超时时间
LargeCopy.BulkCopyTimeout = 100000;
//需要拷贝的数量
LargeCopy.NotifyAfter = dt.Rows.Count;
System.Windows.Forms.Application.DoEvents();
try
{
//如果是制定 字段到字段的拷贝,下面是指定相互对应的字段
if (isAll == 0)
{
for (int i = 0; i < sourceItem.Length; i++)
{
LargeCopy.ColumnMappings.Add(sourceItem[i].ToString(), targetItem[i].ToString());
}
}
//目标表的名称
LargeCopy.DestinationTableName = targetTable;
//开始写入数据
LargeCopy.WriteToServer(dt);
opt.IsSuccess = true;
DateTime eTime = DateTime.Now;
TimeSpan ts = eTime.Subtract(sTime);
opt.ErrMsg = "成功拷贝:" + dt.Rows.Count.ToString() + "条数据./n用时:"+ts.Seconds+"."+ts.Milliseconds+"秒";
}
catch (Exception CopyErr)
{
opt.ErrMsg = CopyErr.Message;
opt.IsSuccess = false;
}
}
return opt;
}
小结:
SqlBulkCopy类提供数据的批量拷贝功能,它的数据源可事实任何.net支持的数据源.但是目标只能是MsSqlServer.如果数据量过大可以分批拷贝,循环一下每次读取适量数据到DataTable,不然内存扛不住.每次拷贝几千条数据,也很快的.
时间有限只写了mssql to mssql的 支持,用数据工厂就可以可扩展成所有.net支持的数据库到 mssql
水平有限,时间仓促,肯定有很多的bug,这里只是简单的实现了功能.
欢迎各位指导交流.不吝赐教!