在ADO.NET中使用事务保护数据的完整性(5)

类别:VC语言 点击:0 评论:0 推荐:

 

使用Savepoints

当使用savepoints, 记住仅仅是回滚savepoints并不是足够的.当局部回滚后事务必须仍被提交.同时,如果你选择实施它们,如何通知用户或处理局部完成事务是非常重要的.

接下来的代码描述创建一个新的客户,同时处理客户请求.你不得不执行慢的连接,或某些原因在检查库以前的操作库存等级成本是被禁止的.时间的99.9%整个过程应该被成功结束.然而,如果在SiteInventory表 保持最小的库存等级限制,它将会失败.你可以看一下的代码:

using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

 

…public void SavepointTransaction()

{

   // Create and open the connection.

   SqlConnection conn = new SqlConnection();

   string connString = "Server= SqlInstance;Database=Test;"

      + "Integrated Security=SSPI";

   conn.ConnectionString = connString;

   conn.Open();

 

   // Create the commands.

   // cmdInsertCustomer creates a new customer record

   // by calling the DebitWarehouseInventory

   // stored procedure.

   SqlCommand cmdInsertCustomer =

      new SqlCommand("CreateCustomer", conn);

   cmdInsertCustomer.CommandType = CommandType.StoredProcedure;

   cmdInsertCustomer.Parameters.Add

      ("@FirstName", SqlDbType.NVarChar, 50, "FirstName");

   cmdInsertCustomer.Parameters.Add

      ("@LastName", SqlDbType.NVarChar, 50, "LastName");

   cmdInsertCustomer.Parameters.Add

      ("@Email", SqlDbType.NVarChar, 50, "Email");

   cmdInsertCustomer.Parameters.Add("@CID", SqlDbType.Int, 0);

   cmdInsertCustomer.Parameters["@FirstName"].Direction =

      ParameterDirection.Input;

   cmdInsertCustomer.Parameters["@LastName"].Direction =

      ParameterDirection.Input;

   cmdInsertCustomer.Parameters["@Email"].Direction =

      ParameterDirection.Input;

   cmdInsertCustomer.Parameters["@CID"].Direction =

      ParameterDirection.Output;

 

   // cmdRequestMaterials creates a pick list

   // of the materials requested by the customer

   // by calling the InsertMaterialsRequest

   // stored procedure.

   SqlCommand cmdRequestMaterials =

      new SqlCommand("InsertMaterialsRequest", conn);

   cmdRequestMaterials.CommandType = CommandType.StoredProcedure;

   cmdRequestMaterials.Parameters.Add

      ("@CustomerID", SqlDbType.Int, 0, "CustomerId");

   cmdRequestMaterials.Parameters.Add

      ("@RequestPartID", SqlDbType.Int, 0, "PartId");

   cmdRequestMaterials.Parameters.Add

      ("@Number", SqlDbType.Int, 0, "NumberRequested");

   cmdRequestMaterials.Parameters["@CustomerID"].Direction =

      ParameterDirection.Input;

   cmdRequestMaterials.Parameters["@RequestPartID"].Direction =

      ParameterDirection.Input;

   cmdRequestMaterials.Parameters["@Number"].Direction =

      ParameterDirection.Input;

 

   // cmdUpdateSite debits the requested materials

   // from the inventory of those available by calling

   // the UpdateSiteInventory stored procedure.

   SqlCommand cmdUpdateSite =

      new SqlCommand("UpdateSiteInventory", conn);

   cmdUpdateSite.CommandType = CommandType.StoredProcedure;

   cmdUpdateSite.Parameters.Add

      ("@SiteID", SqlDbType.Int, 0, "SiteID");

   cmdUpdateSite.Parameters.Add

      ("@SitePartID", SqlDbType.Int, 0, "PartId");

   cmdUpdateSite.Parameters.Add

      ("@Debit", SqlDbType.Int, 0, "Debit");

   cmdUpdateSite.Parameters["@SiteID"].Direction =

      ParameterDirection.Input;

   cmdUpdateSite.Parameters["@SitePartID"].Direction =

      ParameterDirection.Input;

   cmdUpdateSite.Parameters["@Debit"].Direction =

      ParameterDirection.Input;

        

   // Begin the transaction and enlist the commands.

   SqlTransaction tran = conn.BeginTransaction();

   cmdInsertCustomer.Transaction = tran;

   cmdUpdateSite.Transaction  = tran;

   cmdRequestMaterials.Transaction  = tran;

 

   try

   {

      // Execute the commands.

      cmdInsertCustomer.Parameters["@FirstName"].Value

         = "Mads";

      cmdInsertCustomer.Parameters["@LastName"].Value

         = "Nygaard";

      cmdInsertCustomer.Parameters["@Email"].Value

         = "[email protected]";

      cmdInsertCustomer.ExecuteNonQuery();

 

      tran.Save("Customer");

 

      cmdRequestMaterials.Parameters["@CustomerID"].Value

         = cmdInsertCustomer.Parameters["@CID"].Value;

      cmdRequestMaterials.Parameters["@RequestPartID"].Value

         = 3;

      cmdRequestMaterials.Parameters["@Number"].Value

         = 22;

      cmdRequestMaterials.ExecuteNonQuery();

 

      cmdUpdateSite.Parameters["@SitePartID"].Value

         = 3;

      cmdUpdateSite.Parameters["@Debit"].Value

         = 22;

      cmdUpdateSite.Parameters["@SiteID"].Value

         = 4;

      cmdUpdateSite.ExecuteNonQuery();

 

      // Commit the transaction.

      tran.Commit();

 

   }

   catch(SqlException sqlEx)

   {

      try

      {

         // Roll back the transaction

         // to the savepoint.

         Console.WriteLine(sqlEx.Message);

         tran.Rollback("Customer");

         tran.Commit();

 

         // Add code to notify user or otherwise handle

         // the fact that the procedure was only

         // partially successful.

      }

      catch(SqlException ex)

      {

         // If the partial rollback fails,

         // roll back the whole transaction.

         Console.WriteLine(ex.Message);

         tran.Rollback();

 

         // Additional error handling if needed.

      }

   }

   finally

   {

      // Close the connection.

      conn.Close();

   }

}

使用内嵌事务

内嵌事务如savepoints一样考虑同样的注意事项.为了确认局部回滚仍然需要提交,处理局部完成过程.

以下的代码示例描述创建一个新的订单,同时创建了订单项目. 如果创建订单列表失败-可能是PartID无效的对于选择的位置来说-你仍然需要进入订单,在其它方面选取. 内嵌的try/catch 块封装了内嵌事务用来处理选取列表,允许内部事务提交或失败依赖于外部的事务.外部的catch 块回滚整个事务如果订单创建过程失败.

using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.Data;

using System.Data.OleDb;

using System.Text;

…public void NestedTransaction(string UID, string pwd)

{

   // Create and open the connection.

   OleDbConnection conn = new OleDbConnection();

   StringBuilder sb = new StringBuilder();

   sb.Append("Jet OLEDB:System database=");

            sb.Append(@"C:\Databases\system.mdw;");

            sb.Append(@"Data Source=C:\Databases\orders.mdb;");

            sb.Append("Provider=Microsoft.Jet.OLEDB.4.0;");

            sb.Append("User ID=" + UID + ";Password=" + pwd);

   string connString = sb.ToString();

   conn.ConnectionString = connString;

   conn.Open();

 

   // Create the commands.

   string cmdString = "Insert into Orders"

      + " (OrderID, OrderDate, CustomerID)"

      + " values('ABC60', #4/14/04#, 456)";

   OleDbCommand cmdInsertOrder = new OleDbCommand(cmdString,conn);

 

   //No need to insert OrderLineID, as that is an AutoNumber field.

   cmdString = "Insert into OrderLines (OrderID, PartID, Quantity)"

      + " values('ABC60', 25, 10)";

   OleDbCommand cmdInsertOrderLine =

new OleDbCommand(cmdString,conn);

 

   cmdString = "Insert into PickList (OrderID, PartID, Quantity)"

      + " values('ABC60', 25, 10)";

   OleDbCommand cmdCreatePickList =

new OleDbCommand(cmdString,conn);

 

   // Begin the outer transaction and

   // enlist the order-related commands.

      OleDbTransaction tran = conn.BeginTransaction();

   cmdInsertOrder.Transaction = tran;

   cmdInsertOrderLine.Transaction = tran;

           

   try

   {

      // Execute the commands

      // to create the order and order

      // line items.

      cmdInsertOrder.ExecuteNonQuery();

      cmdInsertOrderLine.ExecuteNonQuery();

 

      // Create a nested transaction

      // that allows the pick list

      // creation to succeed or fail

      // separately if necessary.

      OleDbTransaction nestedTran = tran.Begin();

           

      // Enlist the pick list command.

      cmdCreatePickList.Transaction = nestedTran;

 

      try

      {

 

         // Execute the pick list command.

         cmdCreatePickList.ExecuteNonQuery();

 

         // Commit the nested transaction.

         nestedTran.Commit();

      }

      catch(OleDbException ex)

      {

         //Roll back the transaction.

         nestedTran.Rollback();

 

         // Add code to notify user or otherwise handle

         // the fact that the procedure was only

         // partially successful.

      }

 

      // Commit the outer transaction.

      tran.Commit();

   }

   catch(OleDbException ex)

   {

      //Roll back the transaction.

      tran.Rollback();

 

      //Additional error handling if needed.

   }

   finally

   {

      // Close the connection.

      conn.Close();

   }

}

注: 引擎提供者为Microsoft OLE DB,其支持内嵌事务.

总结

事务是一个强有力的方式,用来保证数据修改任务得到合适的处理,同时ado.net让我们处理数据存取的能力更容易.当你构建你的数据读取和使用方案的时候考虑到这些情况,你将会保证你数据的完整性.

本文地址:http://com.8s8s.com/it/it831.htm