傳統 DataAdapter.Update 逐筆送出 SQL,每筆皆有一次網路往返。ADO.NET 2.0 引入 UpdateBatchSize 可將多筆命令打包成批次執行,大幅提升效能。
protected void btnBatchUpdate_Click(object sender, EventArgs e)
{
const string connStr = "Data Source=.;Initial Catalog=Northwind;Integrated Security=True";
using (SqlConnection selectConn = new SqlConnection(connStr))
using (SqlConnection updateConn = new SqlConnection(connStr))
{
SqlDataAdapter adapter = new SqlDataAdapter();
// SelectCommand
adapter.SelectCommand = new SqlCommand("SELECT TOP 500 EmployeeID, Address, City, Region, Country FROM Employees", selectConn);
// UpdateCommand (參數化)
adapter.UpdateCommand = new SqlCommand(
"UPDATE Employees SET Address=@Address, City=@City, Region=@Region, Country=@Country WHERE EmployeeID=@EmployeeID",
updateConn);
adapter.UpdateCommand.Parameters.Add("@Address", SqlDbType.NVarChar, 60, "Address");
adapter.UpdateCommand.Parameters.Add("@City", SqlDbType.NVarChar, 15, "City");
adapter.UpdateCommand.Parameters.Add("@Region", SqlDbType.NVarChar, 15, "Region");
adapter.UpdateCommand.Parameters.Add("@Country", SqlDbType.NVarChar, 15, "Country");
adapter.UpdateCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID");
// 關鍵:啟用批次更新
adapter.UpdateBatchSize = 100; // 每 100 筆一批
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; // 批次模式必須設為 None 或 OutputParameters
DataTable dt = new DataTable();
adapter.Fill(dt);
// 模擬修改所有列
foreach (DataRow row in dt.Rows)
{
row["Address"] = "4445 W 77th Street, Suite 140";
row["City"] = "Edina";
row["Region"] = "MN";
row["Country"] = "USA";
}
// 監聽批次完成事件
adapter.RowUpdated += (s, args) =>
{
lblStatus.Text += $"批次完成至第 {args.RowCount} 筆<br/>";
};
try
{
updateConn.Open();
int affected = adapter.Update(dt); // 一次送出所有變更
lblStatus.Text += $"總計更新 {affected} 筆";
}
catch (Exception ex)
{
lblStatus.Text += $"錯誤: {ex.Message}";
}
}
}
| 屬性 | 建議值 | 說明 |
|---|---|---|
| UpdateBatchSize | 10~500 (視網路延遲測試) | 0=最大批次, 1=停用批次(預設), >1=指定批次筆數 |
| UpdatedRowSource | None / OutputParameters | 批次模式不可為 FirstReturnedRecord 或 Both |
| RowUpdated 事件 | 訂閱 | 每批次完成觸發一次,args.RowCount 為累計筆數 |
UpdateBatchSize (10, 50, 100, 500) 找出最佳值。SqlCommand.CommandTimeout 調整。EFCore.BulkExtensions、Dapper.Contrib 等。