sql server中批量插入与更新两种解决方案分享(asp.net)

若只是需要大批量插入数据使用bcp是最好的，若同时需要插入、删除、更新建议使用sqldataadapter我测试过有很高的效率，一般情况下这两种就满足需求了
bcp方式

/// <summary>
/// 大批量插入数据(2000每批次)
/// 已采用整体事物控制
/// </summary>
/// <param name="connstring">数据库链接字符串</param>
/// <param name="tablename">数据库服务器上目标表名</param>
/// <param name="dt">含有和目标数据库表结构完全一致(所包含的字段名完全一致即可)的datatable</param>
public static void bulkcopy(string connstring, string tablename, datatable dt)
{
using (sqlconnection conn = new sqlconnection(connstring))
{
conn.open();
using (sqltransaction transaction = conn.begintransaction())
{
using (sqlbulkcopy bulkcopy = new sqlbulkcopy(conn, sqlbulkcopyoptions.default, transaction))
{
bulkcopy.batchsize = 2000;
bulkcopy.bulkcopytimeout = _commandtimeout;
bulkcopy.destinationtablename = tablename;
try
{
foreach (datacolumn col in dt.columns)
{
bulkcopy.columnmappings.add(col.columnname, col.columnname);
}
bulkcopy.writetoserver(dt);
transaction.commit();
}
catch (exception ex)
{
transaction.rollback();
throw ex;
}
finally
{
conn.close();
}
}
}
}
}

sqldataadapter

/// <summary>
/// 批量更新数据(每批次5000)
/// </summary>
/// <param name="connstring">数据库链接字符串</param>
/// <param name="table"></param>
public static void update(string connstring, datatable table)
{
sqlconnection conn = new sqlconnection(connstring);
sqlcommand comm = conn.createcommand();
comm.commandtimeout = _commandtimeout;
comm.commandtype = commandtype.text;
sqldataadapter adapter = new sqldataadapter(comm);
sqlcommandbuilder commandbulider = new sqlcommandbuilder(adapter);
commandbulider.conflictoption = conflictoption.overwritechanges;
try
{
conn.open();
//设置批量更新的每次处理条数
adapter.updatebatchsize = 5000;
adapter.selectcommand.transaction = conn.begintransaction();/////////////////开始事务
if (table.extendedproperties["sql"] != null)
{
adapter.selectcommand.commandtext = table.extendedproperties["sql"].tostring();
}
adapter.update(table);
adapter.selectcommand.transaction.commit();/////提交事务
}
catch (exception ex)
{
if (adapter.selectcommand != null && adapter.selectcommand.transaction != null)
{
adapter.selectcommand.transaction.rollback();
}
throw ex;
}
finally
{
conn.close();
conn.dispose();
}
}