（转）sql server批量插入与更新两种解决方案

xml和表值函数的相对复杂些这里简单贴一下bcp和SqlDataAdapter进行批量跟新插入方法，未经整理还望见谅
若只是需要大批量插入数据使用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();
    }
}