C# 使用 SqlBulkCopy 类批量复制数据到数据库
最近公司需要优化导入的问题，由于之前使用的方式是生成 Insert 语句插入数据库，数据量小的时候还行，但是随着发展数据量渐渐大了，之前的方法性能就跟不上了，于是发现了 SqlBulkCopy 这个类。
使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据。
public class Conn
{
private static string StrConn
{
get
{
return ConfigurationManager.ConnectionStrings["StrConn"].ToString();
//return ConfigurationManager.AppSettings["StrConn"].ToString();
}
}
public static SqlConnection SqlConn
{
get
{
return new SqlConnection(StrConn);
}
}
}
public class SqlHelper
{
public DataTable GetDataTable(string sql)
{
DataTable dt = new DataTable();
SqlConnection conn = null;
SqlDataAdapter sda = null;
try
{
conn = Conn.SqlConn;
sda = new SqlDataAdapter(sql, conn);
conn.Open();
sda.Fill(dt);
}
catch (Exception ex)
{
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
if (sda != null)
{
sda.Dispose();
}
}
return dt;
}
public DataSet GetDataSet(string sql)
{
DataSet ds = new DataSet();
SqlConnection conn = null;
SqlDataAdapter sda = null;
try
{
conn = Conn.SqlConn;
sda = new SqlDataAdapter(sql, conn);
conn.Open();
sda.Fill(ds);
}
catch (Exception ex)
{
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
if (sda != null)
{
sda.Dispose();
}
}
return ds;
}
/// <summary>
/// 使用事务插入方法
/// </summary>
/// <param name="dt">源数据</param>
/// <param name="tableName">目标表名</param>
public void InsertO(DataTable dt, string tableName)
{
using (SqlConnection conn = Conn.SqlConn)
{
using (SqlBulkCopy sqlBuleCopy = new SqlBulkCopy(conn.ConnectionString, SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.Default | SqlBulkCopyOptions.UseInternalTransaction))
{
try
{
//设置目标表名，即数据库表名
sqlBuleCopy.DestinationTableName = tableName;
//设置每一批次的行数，即达到指定的行数就插入一次数据库
sqlBuleCopy.BatchSize = 100000;
//设置超时之前完成的时间（秒）
sqlBuleCopy.BulkCopyTimeout = 3600;
for (int i = 0; i < dt.Columns.Count; i++)
{
//设置源数据列与目标表的列的映射关系，第一个参数为源数据列，第二个参数为目标表列
sqlBuleCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
sqlBuleCopy.WriteToServer(dt);
}
catch (Exception)
{
}
}
}
}
/// <summary>
/// 未使用事务插入方法
/// </summary>
/// <param name="dt">源数据</param>
/// <param name="tableName">目标表名</param>
public void InsertT(DataTable dt, string tableName)
{
using (SqlConnection conn = Conn.SqlConn)
{
using (SqlBulkCopy sqlBuleCopy = new SqlBulkCopy(conn))
{
try
{
conn.Open();
//设置目标表名，即数据库表名
sqlBuleCopy.DestinationTableName = tableName;
//设置每一批次的行数，即达到指定的行数就插入一次数据库
sqlBuleCopy.BatchSize = 100000;
//设置超时之前完成的时间（秒）
sqlBuleCopy.BulkCopyTimeout = 3600;
for (int i = 0; i < dt.Columns.Count; i++)
{
//设置源数据列与目标表的列的映射关系，第一个参数为源数据列，第二个参数为目标表列
sqlBuleCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
sqlBuleCopy.WriteToServer(dt);
}
catch (Exception)
{
conn.Close();
conn.Dispose();
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
}
}
我的源数据是使用 Excel 导入的数据，导入的方法就不说了，不是这里的重点，之后我会专门总结一下 Excel 导入的方法。然后查询目标表需要插入数据的字段，修改源数据表的字段名和类型，然后调用批量插入的方法。
protected void btnImport_Click(object sender, EventArgs e)
{
try
{
//获取导入的数据
DataSet ds = BI.ExecleDs(savePath, "");
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = ds.Tables[0];
//查询目标表需要插入的字段
string sql = " select U_No,U_Name,U_Pwd,P_Id from UserInfo ";
DataTable dt1 = sqlhelper.GetDataTable(sql);
if (dt1 != null)
{
for (int i = 0; i < dt1.Columns.Count; i++)
{
//修改源数据表的字段类型和字段名称
dt.Columns[i].DataType = dt1.Columns[i].DataType;
dt.Columns[i].ColumnMapping = dt1.Columns[i].ColumnMapping;
dt.Columns[i].ColumnName = dt1.Columns[i].ColumnName;
}
sqlhelper.InsertO(dt, "UserInfo");
}
}
}
catch (Exception ex)
{
throw;
}
}
如果源数据表的列和目标表的列的顺序或列名不相同，那就必须使用 ColumnMappings.Add() 方法设置映射关系。
参考：
http://www.cnblogs.com/zfanlong1314/archive/2013/02/05/2892998.html
