|
三、更新數(shù)據(jù)集 首先需要說明的是我這里去掉了Order Details表，對兩個(gè)表的操作只是其中的幾個(gè)字段。下面是窗體界面：
圖 3-1單選框用來選擇不同的更新方法。 在DataAccess類中增加兩個(gè)類成員變量： private SqlDataAdapter _customerDataAdapter; //客戶數(shù)據(jù)適配器 private SqlDataAdapter _orderDataAdapter; //訂單數(shù)據(jù)適配器 customerDataAdapter在構(gòu)造函數(shù)中的初始化為 //實(shí)例化_customerDataAdapter SqlCommand selectCustomerComm = new SqlCommand("GetCustomer",_conn); selectCustomerComm.CommandType = CommandType.StoredProcedure; selectCustomerComm.Parameters.Add("@CustomerID",SqlDbType.NChar,5,"CustomerID"); SqlCommand insertCustomerComm = new SqlCommand("AddCustomer",_conn); insertCustomerComm.CommandType = CommandType.StoredProcedure; insertCustomerComm.Parameters.Add("@CustomerID",SqlDbType.NChar,5,"CustomerID"); insertCustomerComm.Parameters.Add("@CompanyName",SqlDbType.NVarChar,40,"CompanyName"); insertCustomerComm.Parameters.Add("@ContactName",SqlDbType.NVarChar,30,"ContactName"); SqlCommand updateCustomerComm = new SqlCommand("UpdateCustomer",_conn); updateCustomerComm.CommandType = CommandType.StoredProcedure; updateCustomerComm.Parameters.Add("@CustomerID",SqlDbType.NChar,5,"CustomerID"); updateCustomerComm.Parameters.Add("@CompanyName",SqlDbType.NVarChar,40,"CompanyName"); updateCustomerComm.Parameters.Add("@ContactName",SqlDbType.NVarChar,30,"ContactName"); SqlCommand deleteCustomerComm = new SqlCommand("DeleteCustomer",_conn); deleteCustomerComm.CommandType = CommandType.StoredProcedure; deleteCustomerComm.Parameters.Add("@CustomerID",SqlDbType.NChar,5,"CustomerID"); _customerDataAdapter = new SqlDataAdapter(selectCustomerComm); _customerDataAdapter.InsertCommand = insertCustomerComm; _customerDataAdapter.UpdateCommand = updateCustomerComm; _customerDataAdapter.DeleteCommand = deleteCustomerComm; 上面的代碼完全可以用設(shè)計(jì)器生成，覺得有些東西自己寫感覺更好，不過代碼還是很多。 對于_orderDataAdapter的初始化同上面的差不多，這里我們只看訂單增加的處理，下面是存儲(chǔ)過程： CREATE PROCEDURE AddOrder ( @OrderID INT OUT, @CustomerID NCHAR(5), @OrderDate DATETIME ) AS INSERT INTO Orders ( CustomerID , OrderDate ) VALUES ( @CustomerID , @OrderDate ) --SELECT @OrderID = @@IDENTITY //使用觸發(fā)器有可能出現(xiàn)問題 SET @OrderID = SCOPE_IDENTITY() GO OrderID自動(dòng)增長值的獲取通過輸出參數(shù)來完成，這個(gè)相當(dāng)不錯(cuò)，如果使用SqlDataAdapter.RowUpdated事件來處理那效率會(huì)很低。 對insertOrderComm對象的定義為： SqlCommand insertOrderComm = new SqlCommand("AddOrder",_conn); insertOrderComm.CommandType = CommandType.StoredProcedure; insertOrderComm.Parameters.Add("@OrderID",SqlDbType.Int,4,"OrderID"); insertOrderComm.Parameters["@OrderID"].Direction = ParameterDirection.Output; insertOrderComm.Parameters.Add("@OrderDate",SqlDbType.DateTime,8,"OrderDate"); insertOrderComm.Parameters.Add("@CustomerID",SqlDbType.NChar,5,"CustomerID"); 在實(shí)現(xiàn)數(shù)據(jù)的更新方法之前我們先來明確一些更新邏輯： 對于標(biāo)記為刪除的行，先刪除訂單表的數(shù)據(jù)，再刪除客戶表的數(shù)據(jù)； 對于標(biāo)記為添加的行，先添加客戶表的數(shù)據(jù)，再添加訂單表的數(shù)據(jù)。 （1）實(shí)現(xiàn)用獲取修改過的DataSet的副本子集來更新數(shù)據(jù)的方法。 這也是調(diào)用Xml Web Service更新數(shù)據(jù)的常用方法，先來看第一個(gè)版本，子集的獲取通過DataSet.GetChangs方法來完成。 //使用數(shù)據(jù)集子集更新數(shù)據(jù) public void UpdateCustomerOrders(DatasetOrders ds) { DataSet dsModified = ds.GetChanges(DataRowState.Modified);//獲取修改過的行 DataSet dsDeleted = ds.GetChanges(DataRowState.Deleted);//獲取標(biāo)記為刪除的行 DataSet dsAdded = ds.GetChanges(DataRowState.Added);//獲取增加的行 try { _conn.Open();//先添加客戶表數(shù)據(jù)，再添加訂單表數(shù)據(jù) if(dsAdded != null) { _customerDataAdapter.Update(dsAdded,"Customers"); _orderDataAdapter.Update(dsAdded,"Orders"); ds.Merge(dsAdded); } if(dsModified != null)//更新數(shù)據(jù)表 { _customerDataAdapter.Update(dsModified,"Customers"); _orderDataAdapter.Update(dsModified,"Orders"); ds.Merge(dsModified); } if(dsDeleted != null)//先刪除訂單表數(shù)據(jù)，再刪除客戶表數(shù)據(jù) { _orderDataAdapter.Update(dsDeleted,"Orders"); _customerDataAdapter.Update(dsDeleted,"Customers"); ds.Merge(dsDeleted); } } catch(Exception ex) { throw new Exception("更新數(shù)據(jù)出錯(cuò)",ex); } finally { if(_conn.State != ConnectionState.Closed) _conn.Close(); } } 上面的方法看上去比較清晰，不過效率不會(huì)很高，至少中間創(chuàng)建了三個(gè)DataSet，然后又進(jìn)行了多次合并。 （2）另一方法就是引用更新，不創(chuàng)建副本。 相對來說性能會(huì)高許多，但是如果用在Web服務(wù)上傳輸?shù)臄?shù)據(jù)量會(huì)更大(可以結(jié)合兩個(gè)方法進(jìn)行改進(jìn))。具體的實(shí)現(xiàn)就是通過DataTable.Select方法選擇行狀態(tài)來實(shí)現(xiàn)。 //引用方式更新數(shù)據(jù) public void UpdateCustomerOrders(DataSet ds) { try { _conn.Open(); //先添加客戶表數(shù)據(jù)，再添加訂單表數(shù)據(jù) _customerDataAdapter.Update(ds.Tables["Customers"].Select("","",DataViewRowState.Added)); _orderDataAdapter.Update(ds.Tables["Orders"].Select("","",DataViewRowState.Added)); //更新數(shù)據(jù)表 _customerDataAdapter.Update(ds.Tables["Customers"].Select("","",DataViewRowState.ModifiedCurrent)); _orderDataAdapter.Update(ds.Tables["Orders"].Select("","",DataViewRowState.ModifiedCurrent)); //先刪除訂單表數(shù)據(jù)，再刪除客戶表數(shù)據(jù) _orderDataAdapter.Update(ds.Tables["Orders"].Select("","",DataViewRowState.Deleted)); _customerDataAdapter.Update(ds.Tables["Customers"].Select("","",DataViewRowState.Deleted)); } catch(Exception ex) { throw new Exception("更新數(shù)據(jù)出錯(cuò)",ex); } finally { if(_conn.State != ConnectionState.Closed) _conn.Close(); } } 結(jié)合上面的兩個(gè)方法我們可想到調(diào)用Web Service有更合理的方法來完成。 （3）使用事務(wù) public void UpdateCustomerOrdersWithTransaction(DataSet ds) { SqlTransaction trans = null; try { _conn.Open(); trans = _conn.BeginTransaction(); _customerDataAdapter.DeleteCommand.Transaction = trans; _customerDataAdapter.InsertCommand.Transaction = trans; _customerDataAdapter.UpdateCommand.Transaction = trans; _orderDataAdapter.DeleteCommand.Transaction = trans; _orderDataAdapter.InsertCommand.Transaction = trans; _orderDataAdapter.UpdateCommand.Transaction = trans; _customerDataAdapter.Update(ds.Tables["Customers"].Select("","",DataViewRowState.Added)); _orderDataAdapter.Update(ds.Tables["Orders"].Select("","",DataViewRowState.Added)); _customerDataAdapter.Update(ds.Tables["Customers"].Select("","",DataViewRowState.ModifiedCurrent)); _orderDataAdapter.Update(ds.Tables["Orders"].Select("","",DataViewRowState.ModifiedCurrent)); _orderDataAdapter.Update(ds.Tables["Orders"].Select("","",DataViewRowState.Deleted)); _customerDataAdapter.Update(ds.Tables["Customers"].Select("","",DataViewRowState.Deleted)); trans.Commit(); } catch(Exception ex) { trans.Rollback(); throw new Exception("更新數(shù)據(jù)出錯(cuò)",ex); } finally { if(_conn.State != ConnectionState.Closed) _conn.Close(); } } 最后讓我們來看看窗體的按鈕更新事件的代碼： private void buttonUpdate_Click(object sender, System.EventArgs e) { //提交編輯數(shù)據(jù) this.BindingContext[this._ds].EndCurrentEdit(); if(radioButtonRef.Checked == true)//引用方式更新 _dataAccess.UpdateCustomerOrders((DataSet)_ds); else if(radioButtonTrans.Checked == true)//啟用事務(wù)更新數(shù)據(jù)表 _dataAccess.UpdateCustomerOrdersWithTransaction((DataSet)_ds); else { DatasetOrders changedData = (DatasetOrders)_ds.GetChanges(); if(radioButtonWeb.Checked == true)//Web服務(wù)的更正更新 { _dataAccess.UpdateCustomerOrders((DataSet)changedData); } else//創(chuàng)建副本合并方式更新 { _dataAccess.UpdateCustomerOrders(changedData); } //去除訂單表中添加的虛擬行 foreach(DataRow row in _ds.Orders.Select("","",DataViewRowState.Added)) _ds.Orders.RemoveOrdersRow((DatasetOrders.OrdersRow)row); //去除客戶表中添加的虛擬行 foreach(DataRow row in _ds.Customers.Select("","",DataViewRowState.Added)) _ds.Customers.RemoveCustomersRow((DatasetOrders.CustomersRow)row); _ds.Merge(changedData); } //提交數(shù)據(jù)集狀態(tài) _ds.AcceptChanges(); } 本文參考：《ADO.NET Core Reference》 Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=67037 |
|
|
