时间:2021-07-01 10:21:17 帮助过:4人阅读
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();
}
}
sql server中批量插入与更新两种解决方案分享
标签: