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

}

}