ADO.NET中的多資料表操作淺析—修改

來源:互聯網
上載者:User
ado|資料 ADO.NET中的多資料表操作淺析—修改

作者:鄭佐??????? 2004-8-5


三、更新資料集

首先需要說明的是我這裡去掉了Order Details表,對兩個表的操作只是其中的幾個欄位。下面是表單介面:



圖3-1

單選框用來選擇不同的更新方法。

在DataAccess類中增加兩個類成員變數:

???? private SqlDataAdapter _customerDataAdapter; //客戶資料配接器

???? private SqlDataAdapter _orderDataAdapter; //訂單資料配接器

?

customerDataAdapter在建構函式中的初始化為

//執行個體化_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;

?

上面的代碼完全可以用設計器產生,覺得有些東西自己寫感覺更好,不過代碼還是很多。

對於_orderDataAdapter的初始化同上面的差不多,這裡我們只看訂單增加的處理,下面是預存程序:

CREATE PROCEDURE? AddOrder

(

???? @OrderID INT OUT,

???? @CustomerID NCHAR(5),

???? @OrderDate DATETIME

)

AS

INSERT INTO Orders

(

???? CustomerID ,

???? OrderDate

)

VALUES

(

???? @CustomerID ,

???? @OrderDate

)

--SELECT @OrderID = @@IDENTITY

SET @OrderID = SCOPE_IDENTITY()

GO

?

OrderID自動成長值的擷取通過輸出參數來完成,這個相當不錯,如果使用SqlDataAdapter.RowUpdated事件來處理那效率會很低。

對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");

?

在實現資料的更新方法之前我們先來明確一些更新邏輯:

對於標記為刪除的行,先刪除訂單表的資料,再刪除客戶表的資料;

對於標記為添加的行,先添加客戶表的資料,再添加訂單表的資料。

?

(1)實現用擷取修改過的DataSet的副本子集來更新資料的方法。

這也是調用Xml Web Service更新資料的常用方法,先來看第一個版本,子集的擷取通過DataSet.GetChangs方法來完成。

//使用資料集子集更新資料

public void UpdateCustomerOrders(DatasetOrders ds)

{????????????

???? DataSet dsModified = ds.GetChanges(DataRowState.Modified);//擷取修改過的行

???? DataSet dsDeleted = ds.GetChanges(DataRowState.Deleted);//擷取標記為刪除的行

???? DataSet dsAdded = ds.GetChanges(DataRowState.Added);//擷取增加的行

???? try

???? {???

???????? _conn.Open();//先添加客戶表資料,再添加訂單表資料

???????? if(dsAdded != null)

???????? {

????????????? _customerDataAdapter.Update(dsAdded,"Customers");

????????????? _orderDataAdapter.Update(dsAdded,"Orders");

????????????? ds.Merge(dsAdded);

???????? }

???????? if(dsModified != null)//更新資料表

???????? {

???????? ???? _customerDataAdapter.Update(dsModified,"Customers");

????????????? _orderDataAdapter.Update(dsModified,"Orders");

????????????? ds.Merge(dsModified);

???????? }

???????? if(dsDeleted != null)//先刪除訂單表資料,再刪除客戶表資料

???????? {

????????????? _orderDataAdapter.Update(dsDeleted,"Orders");

????????????? _customerDataAdapter.Update(dsDeleted,"Customers");

????????????? ds.Merge(dsDeleted);

???????? }?????????????????

???? }

???? catch(Exception ex)

???? {

???????? throw new Exception("更新資料出錯",ex);

???? }

???? finally

???? {

???????? if(_conn.State != ConnectionState.Closed)

????????????? _conn.Close();

???? }

}

上面的方法看上去比較清晰,不過效率不會很高,至少中間建立了三個DataSet,然後又進行了多次合并。

(2)另一方法就是引用更新,不棄置站台。

相對來說效能會高許多,但是如果用在Web服務上傳輸的資料量會更大(可以結合兩個方法進行改進)。具體的實現就是通過DataTable.Select方法選擇行狀態來實現。

//引用方式更新資料

public void UpdateCustomerOrders(DataSet ds)

{

???? try

???? {???

???????? _conn.Open();

???????? //先添加客戶表資料,再添加訂單表資料 ???? _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));???????????

???? }

???? catch(Exception ex)

???? {

???????? throw new Exception("更新資料出錯",ex);

???? }

???? finally

???? {

???????? if(_conn.State != ConnectionState.Closed)

????????????? _conn.Close();

???? }

}

結合上面的兩個方法我們可想到調用Web Service有更合理的方法來完成。

(3)使用事務

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("更新資料出錯",ex);

???? }

???? finally

???? {

???????? if(_conn.State != ConnectionState.Closed)

????????????? _conn.Close();

???? }

}

最後讓我們來看看表單的按鈕更新事件的代碼:

private void buttonUpdate_Click(object sender, System.EventArgs e)

{

????????????? //提交編輯資料

???? this.BindingContext[this._ds].EndCurrentEdit();

????

???? if(radioButtonRef.Checked == true)//引用方式更新

???????? _dataAccess.UpdateCustomerOrders((DataSet)_ds);

???? else if(radioButtonTrans.Checked == true)//啟用事務更新資料表

???????? _dataAccess.UpdateCustomerOrdersWithTransaction((DataSet)_ds);

???? else

???? {

???????? DatasetOrders changedData =? (DatasetOrders)_ds.GetChanges();

???????? if(radioButtonWeb.Checked == true)//Web服務的更正更新

???????? {?????????????????????

????????????? _dataAccess.UpdateCustomerOrders((DataSet)changedData);

???????? }

???????? else//棄置站台合并方式更新

???????? {?????????????????

????????????? _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);

???? }

???? //提交資料集狀態

???? _ds.AcceptChanges();

}

?

本文參考:《ADO.NET Core Reference》

歡迎交流:(秋楓的Blog)http://blog.csdn.net/zhzuo

?

?

?




相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。