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