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

实施事务

既然我们已经看了类和成员，让我们来看一下基本的实施情况．接下来的代码是一个简单的情况，使用事务来保证两个存储过程－一个从表中删除库存，另一个增加库存在另个表中，或同时执行，或失败．

 

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 SPTransaction(int partID, int numberMoved, int siteID)
{
   // 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 and related parameters.
   // cmdDebit debits inventory from the WarehouseInventory
   // table by calling the DebitWarehouseInventory
   // stored procedure.
   SqlCommand cmdDebit =
      new SqlCommand("DebitWarehouseInventory", conn);
   cmdDebit.CommandType = CommandType.StoredProcedure;
   cmdDebit.Parameters.Add("@PartID", SqlDbType.Int, 0, "PartID");
   cmdDebit.Parameters["@PartID"].Direction =
      ParameterDirection.Input;
   cmdDebit.Parameters.Add("@Debit", SqlDbType.Int, 0, "Quantity");
   cmdDebit.Parameters["@Debit"].Direction =
ParameterDirection.Input;

   // cmdCredit adds inventory to the SiteInventory
   // table by calling the CreditSiteInventory
   // stored procedure.
   SqlCommand cmdCredit =
new SqlCommand("CreditSiteInventory", conn);
   cmdCredit.CommandType = CommandType.StoredProcedure;
   cmdCredit.Parameters.Add("@PartID", SqlDbType.Int, 0, "PartID");
   cmdCredit.Parameters["@PartID"].Direction =
ParameterDirection.Input;
   cmdCredit.Parameters.Add
("@Credit", SqlDbType.Int, 0, "Quantity");
   cmdCredit.Parameters["@Credit"].Direction =
ParameterDirection.Input;
   cmdCredit.Parameters.Add("@SiteID", SqlDbType.Int, 0, "SiteID");
   cmdCredit.Parameters["@SiteID"].Direction =
ParameterDirection.Input;

   // Begin the transaction and enlist the commands.
   SqlTransaction tran = conn.BeginTransaction();
   cmdDebit.Transaction = tran;
   cmdCredit.Transaction  = tran;

   try
   {
      // Execute the commands.
      cmdDebit.Parameters["@PartID"].Value = partID;
      cmdDebit.Parameters["@Debit"].Value = numberMoved;
      cmdDebit.ExecuteNonQuery();

      cmdCredit.Parameters["@PartID"].Value = partID;
      cmdCredit.Parameters["@Credit"].Value = numberMoved;
      cmdCredit.Parameters["@SiteID"].Value = siteID;
      cmdCredit.ExecuteNonQuery();

      // Commit the transaction.
      tran.Commit();
   }
   catch(SqlException ex)
   {
      // Roll back the transaction.
      tran.Rollback();

      // Additional error handling if needed.
   }
   finally
   {
      // Close the connection.
conn.Close();
   }
}
 

 

      // 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();

   }

}