使用 SqlBulkCopy 实现高效的批量异步更新操作
使用 SqlBulkCopy 实现高效的批量异步更新操作
使用 SqlBulkCopy 实现高效的批量更新操作
临时表法：
将需要更新的数据通过 SqlBulkCopy 导入临时表
使用 JOIN 语句将临时表与目标表关联进行更新
删除临时表
======================================
实际使用代码
1.Page 加上Async="true"
<%@ Page Language="C#" MasterPageFile="~/HongQuan.Master" AutoEventWireup="true" Async="true"
CodeBehind="OrderDetailPriceUpdate.aspx.cs" Inherits="HongQuan.OrderDetailPriceUpdate" %>
using System.Threading.Tasks;
2. Button_Insert_Click
protected async void Button_Insert_Click(object sender, EventArgs e)
{
try
{
await BulkUpdateWithTempTable();
// 更新UI提示用户操作完成
msg = "批量更新完成";
}
catch (Exception ex)
{
// 错误处理
msg = $"更新失败: {ex.Message}";
// 记录日志
}
Response.Redirect("OrderDetailPriceUpdate.aspx?DateTime=" + MyDateTime+"&NoPrice=1");
//BindData(); 不能重新绑定 使用 Response.Redirect
}
3. 创建DataTable
private DataTable CreateSampleUpdateData()
{
var dataTable = new DataTable();
dataTable.Columns.Add("OrderDetailID", typeof(int));
dataTable.Columns.Add("Cost", typeof(decimal));
MyDateTime = this.TextBox_DateTime.Text.Trim();
foreach (DataListItem item in this.DataList1.Items)
{
int OrderDetailID = Convert.ToInt32(DataList1.DataKeys[item.ItemIndex].ToString());
TextBox T_Cost = (TextBox)this.DataList1.Items[item.ItemIndex].FindControl("TextBox_Cost");
decimal Cost=Convert.ToDecimal(T_Cost.Text);
dataTable.Rows.Add(OrderDetailID,Cost);
}
return dataTable;
}
4.异步更新
public async Task BulkUpdateWithTempTable()
{
// 创建要更新的数据
var dataToUpdate = CreateSampleUpdateData();
using (var connection = new SqlConnection(connectionstring))
{
await connection.OpenAsync();
// 1. 创建临时表
using (var createTempTableCmd = new SqlCommand())
{
createTempTableCmd.Connection = connection;
createTempTableCmd.CommandText = @"
CREATE TABLE #TempOrderDetailCostUpdate(
OrderDetailID INT,
Cost Decimal(9,2)
)";
await createTempTableCmd.ExecuteNonQueryAsync();
}
// 2. 使用SqlBulkCopy将数据导入临时表
using (var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "#TempOrderDetailCostUpdate";
await bulkCopy.WriteToServerAsync(dataToUpdate);
}
// 3. 使用临时表更新目标表
using (var updateCmd = new SqlCommand())
{
updateCmd.Connection = connection;
updateCmd.CommandText = @"
UPDATE dbo.OrderDetail
SET Cost = t.Cost
FROM dbo.OrderDetail o
JOIN #TempOrderDetailCostUpdate t ON o.ID = t.OrderDetailID";
await updateCmd.ExecuteNonQueryAsync();
}
// 4. 删除临时表
using (var dropTempTableCmd = new SqlCommand())
{
dropTempTableCmd.Connection = connection;
dropTempTableCmd.CommandText = "DROP TABLE #TempOrderDetailCostUpdate";
await dropTempTableCmd.ExecuteNonQueryAsync();
}
}
}
private static string connectionstring = "";
private void BindConn()
{
HttpCookie Data = HttpContext.Current.Request.Cookies["Data"];
if (Data == null)
{
HttpContext.Current.Response.Redirect("/Login.aspx");
}
else
{
connectionstring = Data["DataBase"].Trim();
Encrypt MyE = new Encrypt();
connectionstring = MyE.DesDecrypt(connectionstring);
}
}
