|
在以前版本的 ADONET 中使用 DataSet 中的更改来更新数据库时DataAdapter 的 Update 方法每次更新数据库的一行因为该方法循环访问指定 DataTable 中的行所以会检查每个 DataRow确定是否已修改如果该行已修改将根据该行的 RowState 属性值调用相应的 UpdateCommandInsertCommand 或 DeleteCommand每一次行更新都涉及网络与数据库之间的双向数据传输 在ADONET 中DataAdapter 公开了UpdateBatchSize 属性将 UpdateBatchSize 设置为正整数值将使对数据库的更新以指定大小的批次进行发送例如如果将 UpdateBatchSize 设置为 会将 个独立的语句组合在一起并作为一批提交将 UpdateBatchSize 设置为 将导致 DataAdapter 使用服务器可以处理的最大批次的大小如果将其设置为 则禁用批量更新因为此时每次发送一行 执行非常大的批次可能会降低性能因此在实现应用程序之前应测试最佳的批次大小设置 使用 UpdateBatchSize 属性 启用了批量更新后DataAdapter 的 UpdateCommandInsertCommand 和 DeleteCommand 的 UpdatedRowSource 属性值应设置为 None 或 OutputParameters在执行批量更新时命令的FirstReturnedRecord 或 Both 的UpdatedRowSource 属性值无效 下面的过程演示如何使用 UpdateBatchSize 属性该过程采用两个参数一个 DataSet 对象其中包含代表 ProductionProductCategory 表中的ProductCategoryID 和 Name 字段的列一个代表批次大小的整数（批次中的行数）该代码创建一个新的 SqlDataAdapter 对象设置其 UpdateCommandInsertCommand 和 DeleteCommand 属性该代码假定 DataSet 对象已修改了行它设置 UpdateBatchSize 属性并执行更新 protected void btnUpdateAddress_Click(object sender EventArgs e) { SqlDataAdapter EmpAdapter = new SqlDataAdapter(); DataTable EmpDT = new DataTable(); SqlConnection DBConSelect = new SqlConnection(); SqlConnection DBConUpdate = new SqlConnection(); SqlCommand SelectCommand = new SqlCommand(); SqlCommand UpdateCommand = new SqlCommand(); // Using different connection objects for select and updates from the // Northwind database DBConSelectConnectionString = ConfigurationManagerConnectionStrings[DSN_NorthWind]ConnectionString; DBConUpdateConnectionString = ConfigurationManagerConnectionStrings[DSN_NorthWind]ConnectionString; // Reading all records from the Employees table SelectCommandCommandText = SELECT top * FROM EMPLOYEES; SelectCommandCommandType = CommandTypeText; SelectCommandConnection = DBConSelect; UpdateCommandCommandText = UPDATE EMPLOYEES SET Address=@Address + City=@City Region=@Region Country=@Country; UpdateCommandCommandType = CommandTypeText; UpdateCommandConnection = DBConUpdate; SqlParameter AddressParam; AddressParam = new SqlParameter(@Address SqlDbTypeVarChar Address); SqlParameter CityParam; CityParam = new SqlParameter(@City SqlDbTypeVarChar City); SqlParameter RegionParam; RegionParam = new SqlParameter(@Region SqlDbTypeVarChar Region); SqlParameter CountryParam; CountryParam = new SqlParameter(@Country SqlDbTypeVarChar Country); UpdateCommandParametersAdd(AddressParam); UpdateCommandParametersAdd(CityParam); UpdateCommandParametersAdd(RegionParam); UpdateCommandParametersAdd(CountryParam); // Setting up Data Adapter with the Select and Update Commands // The Select command will be used to retrieve all employee // information from the Northwind database and the Update command // will be used to save changes back to the database EmpAdapterSelectCommand = SelectCommand; EmpAdapterUpdateCommand = UpdateCommand; EmpAdapterFill(EmpDT); DBConSelectClose(); // Looping through all employee records and assigning them the new // address foreach (DataRow DR in EmpDTRows) { DR[Address] = W th Street Suite ; DR[City] = Edina; DR[Region] = Minnesota; DR[Country] = USA; } // Adding an event handler to listen to the RowUpdated event // This event will will fire after each batch is executed EmpAdapterRowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated); lblCounterText = ; EmpAdapterUpdateBatchSize = ; // It is important to set this property for batch processing of // updated records since batch updates are incapable of // updating the source with changes from the database UpdateCommandUpdatedRowSource = UpdateRowSourceNone; try { DBConUpdateOpen(); EmpAdapterUpdate(EmpDT); } catch (Exception ex) { lblCounterText += exMessage + <Br>; } finally { if (DBConUpdateState == ConnectionStateOpen) { DBConUpdateClose(); } } } private void OnRowUpdated(object sender SqlRowUpdatedEventArgs args) { lblCounterText += Batch is processed till row number = + argsRowCountToString() + <br>; } |
