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

新客网 XKER.COM 2004-07-07 来源： 收藏本文

实施事务

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

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

}

}

• ·身份证函数 查看身份证地区信息
• ·VS2008 第一次安装心得及使用
• ·ASP.NET 2.0跨网页提交的三法
• ·编程实例 WebGroupBox(Aspx控件)
• ·asp.net mvc脚手架代码生成工具
• ·用独立的DLL来存储图片(资源文件)
• ·ASP.NET中多国语言的实现方法
• ·实例 .net生成静态页方法总结
• ·ASP.NET控件学习笔记之ViewState
• ·用递归在TreeView价节点
• ·经验总结 关于.NET 中的Event机制
• ·.NET应用程序开发标准化(z)
• ·在DataTable中查询应该注意的问题
• ·LINQ 中调用存储过程自动绑定列名
• ·如何用.NET技术在线生成网站LOGO
• ·对于访问IIS元数据库失败的解决