.Net中的5種事務(wù)總結(jié) ?
.Net 2010-01-29 23:52:28 閱讀 164 評(píng)論 0 ? ??字號(hào)： 大 中 小 ? 訂閱
1、SQL事務(wù)
??? sql事務(wù)是使用SQL server自身的事務(wù)：在存儲(chǔ)過程中直接使用Begin Tran，Rollback Tran，Commit Tran實(shí)現(xiàn)事務(wù)：
優(yōu)點(diǎn)：執(zhí)行效率最佳
限制：事務(wù)上下文僅在數(shù)據(jù)庫中調(diào)用，難以實(shí)現(xiàn)復(fù)雜的業(yè)務(wù)邏輯。
Demo：（所有demo,都以SQL Server自帶的Northwind數(shù)據(jù)的表Region為例）
帶事務(wù)的存儲(chǔ)過程
CREATE PROCEDURE dbo.SPTransaction
??? (
??? @UpdateID int,
??? @UpdateValue nchar(50),
??? @InsertID int,
??? @InsertValue nchar(50)
??? )
AS
begin Tran
Update Region? Set where RegionID=@UpdateID
insert into Region Values (@InsertID,@InsertValue)
declare @RegionError int
select @RegionError=@@error
if(@RegionError=0)
COMMIT Tran
else
ROLLBACK Tran
GO
執(zhí)行帶事務(wù)的存儲(chǔ)過程
/**//// <summary>
??????? /// SQL事務(wù):
??????? /// </summary>
??????? public void SQLTran()
??????? {
??????????? SqlConnection conn = new SqlConnection("Da
??????????? SqlCommand cmd = new SqlCommand();
??????????? cmd.CommandText = "SPTransaction";
??????????? cmd.CommandType = CommandType.StoredProcedure;
??????????? cmd.Connection = conn;
??????????? conn.Open();
??????????? SqlParameter[] paras= new SqlParameter[]{
??????????????????????????????????????? new SqlParameter ("@UpdateID",SqlDbType.Int,32),
??????????????????????????????????????? new SqlParameter ("@UpdateValue",SqlDbType .NChar,50),
??????????????????????????????????????? new SqlParameter ("@InsertID",SqlDbType.Int ,32),
??????????????????????????????????????? new SqlParameter ("@InsertValue",SqlDbType.NChar ,50)};
??????????? paras[0].Value = "2";
??????????? paras[1].Value = "Update Value1";
??????????? paras[2].Value = "6";
??????????? paras[3].Value = "Insert Value1";
??????????? foreach (SqlParameter para in paras )
??????????? {
??????????????? cmd.Parameters.Add(para);
??????????? }
??????????? cmd.ExecuteNonQuery();??
??????? }
2、ADO.net事務(wù)
?? Ado.net事務(wù)可能是大家一般都用的
?優(yōu)點(diǎn)：簡單，效率和數(shù)據(jù)庫事務(wù)差不多。
?缺點(diǎn)：事務(wù)不能跨數(shù)據(jù)庫，只能在一個(gè)數(shù)據(jù)庫連接上。如果是兩個(gè)數(shù)據(jù)庫上就不能使用該事務(wù)了。
Demo:
ADO.net事務(wù)
/**//// <summary>
??????? /// 一般的ADO.net 事務(wù)
??????? /// </summary>
??????? public void ADONetTran1()
??????? {
??????????? SqlConnection conn = new SqlConnection("Da
??????????? SqlCommand cmd = new SqlCommand();
??????????? try
??????????? {
??????????????? cmd.CommandText = "Update Region Set where RegionID=@UpdateID";
??????????????? cmd.CommandType = CommandType.Text;
??????????????? cmd.Connection = conn;
??????????????? conn.Open();
??????????????? SqlParameter[] paras = new SqlParameter[]{
??????????????????????????????????????? new SqlParameter ("@UpdateID",SqlDbType.Int,32),
??????????????????????????????????????? new SqlParameter ("@UpdateValue",SqlDbType .NChar,50)};
??????????????? paras[0].Value = "2";
??????????????? paras[1].Value = "Update Value12";
??????????????? foreach (SqlParameter para in paras)
??????????????? {
??????????????????? cmd.Parameters.Add(para);
??????????????? }
??????????????? //開始事務(wù)
??????????????? cmd.Transaction = conn.BeginTransaction();
??????????????? cmd.ExecuteNonQuery();
??????????????? cmd.CommandText = "insert into Region values(@InsertID,@InsertValue)";
??????????????? cmd.CommandType = CommandType.Text;
??????????????? paras = new SqlParameter[]{
??????????????????????????????????????? new SqlParameter ("@InsertID",SqlDbType.Int ,32),
??????????????????????????????????????? new SqlParameter ("@InsertValue",SqlDbType.NChar ,50)};
??????????????? paras[0].Value = "7";
??????????????? paras[1].Value = "Insert Value";
??????????????? cmd.Parameters.Clear();
??????????????? foreach (SqlParameter para in paras)
??????????????? {
??????????????????? cmd.Parameters.Add(para);
??????????????? }
???????????????
??????????????? cmd.ExecuteNonQuery();
??????????????? //提交事務(wù)
??????????????? cmd.Transaction.Commit();
??????????? }
??????????? catch
??????????? {
??????????????? //回滾事務(wù)
??????????????? cmd.Transaction.Rollback();
??????????????? throw;
??????????? }
??????????? finally
??????????? {
??????????????? conn.Close();
??????????? }
??????? }
3、TransactionScope事務(wù)
? TransactionScope事務(wù)類，它可以使代碼塊成為事務(wù)性代碼。并自動(dòng)提升為分布式事務(wù)
?優(yōu)點(diǎn)：實(shí)現(xiàn)簡單，同時(shí)能夠自動(dòng)提升為分布式事務(wù)
Demo:
TransactionScope事務(wù)
?/**//// <summary>
??????? /// TransactionScope事務(wù):可自動(dòng)提升事務(wù)為完全分布式事務(wù)的輕型（本地）事務(wù)。
??????? /// 使用時(shí)要保證MSDTC服務(wù)(控制分布事務(wù))是開啟的可以使用:net start msdtc命令開啟服務(wù);
??????? /// </summary>
??????? public void ADONetTran2()
??????? {
???????????? SqlConnection conn = new SqlConnection("Da
???????????? SqlCommand cmd = new SqlCommand();
??????????? try
??????????? {
?????????????
??????????????? using (System.Transactions.TransactionScope ts = new TransactionScope())
??????????????? {
???????????????????
??????????????????? cmd.CommandText = "Update Region Set where RegionID=@UpdateID";
??????????????????? cmd.CommandType = CommandType.Text;
??????????????????? cmd.Connection = conn;
??????????????????? conn.Open();
??????????????????? SqlParameter[] paras = new SqlParameter[]{
??????????????????????????????????????? new SqlParameter ("@UpdateID",SqlDbType.Int,32),
??????????????????????????????????????? new SqlParameter ("@UpdateValue",SqlDbType .NChar,50)};
??????????????????? paras[0].Value = "2";
??????????????????? paras[1].Value = "Update Value12";
??????????????????? foreach (SqlParameter para in paras)
??????????????????? {
??????????????????????? cmd.Parameters.Add(para);
??????????????????? }
??????????????????? cmd.ExecuteNonQuery();
??????????????????? cmd.CommandText = "insert into Region values(@InsertID,@InsertValue)";
??????????????????? cmd.CommandType = CommandType.Text;
??????????????????? paras = new SqlParameter[]{
??????????????????????????????????????? new SqlParameter ("@InsertID",SqlDbType.Int ,32),
??????????????????????????????????????? new SqlParameter ("@InsertValue",SqlDbType.NChar ,50)};
??????????????????? paras[0].Value = "8";
??????????????????? paras[1].Value = "Insert Value";
??????????????????? cmd.Parameters.Clear();
??????????????????? foreach (SqlParameter para in paras)
??????????????????? {
??????????????????????? cmd.Parameters.Add(para);
??????????????????? }
??????????????????? cmd.ExecuteNonQuery();
??????????????????? //提交事務(wù)
??????????????????? ts.Complete();????????????? //提交事務(wù)之前conn不能關(guān)閉的(Not conn.Close())，否則報(bào)錯(cuò)
??????????????? }
??????????? }
??????????? catch
??????????? {
??????????????? throw;
??????????? }
??????????? finally
??????????? {
??????????????? conn.Close();
??????????? }
??????? }
4、COM+事務(wù)
? 在分布式應(yīng)用程序中，往往要同時(shí)操作多個(gè)數(shù)據(jù)庫，使用數(shù)據(jù)庫事務(wù)就不能滿足業(yè)務(wù)的要求了。在COM+中，提供完整的事務(wù)處理服務(wù)。很方便處理多個(gè)數(shù)據(jù)庫上的事務(wù)。
例一:
COM+事務(wù)
/**//// <summary>
??????? /// COM+事務(wù)
??????? /// </summary>
??????? public void ComTran()
??????? {
??????????? SqlConnection conn = new SqlConnection("Da
??????????? SqlCommand cmd = new SqlCommand();
??????????? ServiceConfig sc = new ServiceConfig();
??????????? //指定事務(wù)類型
??????????? sc.Transaction = TransactionOption.Required;
??????????? //設(shè)置啟動(dòng)跟蹤
??????????? sc.TrackingEnabled = true;
??????????? //創(chuàng)建一個(gè)上下文，該上下文的配置由作為 cfg 參數(shù)傳遞的 ServiceConfig 對(duì)象來指定。
??????????? //隨后，客戶端和服務(wù)器端的策略均被觸發(fā)，如同發(fā)生了一個(gè)方法調(diào)用。
??????????? //接著，新的上下文被推至上下文堆棧，成為當(dāng)前上下文
??????????? ServiceDomain.Enter(sc);
??????????? try
??????????? {
??????????????????? cmd.CommandText = "Update Region Set where RegionID=@UpdateID";
??????????????????? cmd.CommandType = CommandType.Text;
??????????????????? cmd.Connection = conn;
??????????????????? conn.Open();
??????????????????? SqlParameter[] paras = new SqlParameter[]{
??????????????????????????????????????? new SqlParameter ("@UpdateID",SqlDbType.Int,32),
??????????????????????????????????????? new SqlParameter ("@UpdateValue",SqlDbType .NChar,50)};
??????????????????? paras[0].Value = "2";
??????????????????? paras[1].Value = "Update Value22";
??????????????????? foreach (SqlParameter para in paras)
??????????????????? {
??????????????????????? cmd.Parameters.Add(para);
??????????????????? }
??????????????????? cmd.ExecuteNonQuery();
??????????????????? cmd.CommandText = "insert into Region values(@InsertID,@InsertValue)";
??????????????????? cmd.CommandType = CommandType.Text;
??????????????????? paras = new SqlParameter[]{
??????????????????????????????????????? new SqlParameter ("@InsertID",SqlDbType.Int ,32),
??????????????????????????????????????? new SqlParameter ("@InsertValue",SqlDbType.NChar ,50)};
??????????????????? paras[0].Value = "9";
??????????????????? paras[1].Value = "Insert Value";
??????????????????? cmd.Parameters.Clear();
??????????????????? foreach (SqlParameter para in paras)
??????????????????? {
??????????????????????? cmd.Parameters.Add(para);
??????????????????? }
??????????????????? cmd.ExecuteNonQuery();
??????????????????? //提交事務(wù)
??????????????????? ContextUtil.SetComplete();
??????????? }
??????????? catch
??????????? {
??????????????? //回滾事務(wù)
??????????????? ContextUtil.SetAbort();
??????????????? throw;
??????????? }
??????????? finally
??????????? {
??????????????? conn.Close();
??????????????? //觸發(fā)服務(wù)器端的策略，隨后觸發(fā)客戶端的策略，如同一個(gè)方法調(diào)用正在返回。
??????????????? //然后，當(dāng)前上下文被彈出上下文堆棧，調(diào)用 Enter 時(shí)正在運(yùn)行的上下文成為當(dāng)前的上下文。
??????????????? ServiceDomain.Leave();
??????????? }
需要特別補(bǔ)充的是:
如果你使用的是分布事務(wù)（TransactionScope事務(wù)和COM+事務(wù)），在默認(rèn)情況下你是要重新配置安裝SQL Server數(shù)據(jù)庫服務(wù)器和訪問數(shù)據(jù)庫的客戶端的.(如果沒有配置運(yùn)行會(huì)出現(xiàn)以下錯(cuò)誤：該伙伴事務(wù)管理器已經(jīng)禁止了它對(duì)遠(yuǎn)程/網(wǎng)絡(luò)事務(wù)的支持。 (異常來自 HRESULT:0x8004D025)
)下面是MSDN上關(guān)于配置分布式事務(wù)的一段原話:
配置分布式事務(wù)
要啟用分布式事務(wù)，可能需要通過網(wǎng)絡(luò)啟用 MS DTC，以便在使用應(yīng)用了最新的 Service Pack 的較新操作系統(tǒng)（例如 Windows XP 或 Windows 2003）時(shí)使用分布式事務(wù)。如果啟用了 Windows 防火墻（Windows XP Service Pack 2 的默認(rèn)設(shè)置），必須允許 MS DTC 服務(wù)使用網(wǎng)絡(luò)或打開 MS DTC 端口。
實(shí)際怎么配置呢,經(jīng)過我的實(shí)際使用:大致如下:打開'控制面板'->'管理工具'->'組件服務(wù)',點(diǎn)開'組件服務(wù)'->'計(jì)算機(jī)'->'我的電腦',在'我的電腦'上右擊屬性,點(diǎn)'MSDTC',然后點(diǎn)'安全性配置'。作為數(shù)據(jù)庫的服務(wù)器的配置如下：
而訪問數(shù)據(jù)庫的客戶端的配置和服務(wù)器端的稍有些差別：
在設(shè)置完上面的還有使防火墻MS DTC 服務(wù)使用網(wǎng)絡(luò)或打開 MS DTC 端口：運(yùn)行netsh firewall set allowedprogram %windir%\system32\msdtc.exe MSDTC enable命令就可以了
ASP.NET事務(wù)可以說是在.NET平臺(tái)上事務(wù)實(shí)現(xiàn)方式最簡單的一種，你僅僅需要一行代碼即可。在aspx的頁面聲明中加一個(gè)額外的屬性，即事務(wù)屬性 Transaction="Required" ，它有如下的值：Disabled（默認(rèn)）、NotSupported、Supported、Required和RequiresNew，這些設(shè)置和COM+及企業(yè)級(jí)服務(wù)中的設(shè)置一樣，典型的一個(gè)例子是如果你想在頁面上下文中運(yùn)行事務(wù)，那么要將其設(shè)置為Required。如果頁面中包含有用戶控件，那么這些控件也會(huì)包含到事務(wù)中，事務(wù)會(huì)存在于頁面的每個(gè)地方。
頁面聲明Transaction="Required"：
<%@ Page Transaction="Required" ?Language="C#" AutoEventWireup="true"
CodeBehind="WebForm3.aspx.cs" Inherits="WebApplication4.WebForm3" %>
頁面引用：using System.EnterpriseServices;。
然后，數(shù)據(jù)操作代碼：
protected void Button1_Click(object sender, EventArgs e)
{
??? try
??? {
??????? Work1();
??????? Work2();
??????? ContextUtil.SetComplete();?? // 提交事務(wù)
??? }
??? catch (System.Exception except)
??????? ContextUtil.SetAbort();????? // 撤銷事務(wù)
??????? Response.Write(except.Message);
??? }?
}?
private void Work1()
{
??? string conString = "da
????? password=";
??? SqlConnection myConnection = new SqlConnection(conString);
??? string strSql = "Insert Into P_Category(CategoryId,Name)values('1',
????? 'test1')";
??? SqlCommand myCommand = new SqlCommand(strSql, myConnection);
??? myConnection.Open();
??? int rows = myCommand.ExecuteNonQuery();
??? myConnection.Close();
}
private void Work2()
{
??? string conString = "da
????? password=";
??? SqlConnection myConnection = new SqlConnection(conString);
??? string strSql = "Insert Into P_Category(CategoryId,Name)values('2',
????? 'test2')";
??? SqlCommand myCommand = new SqlCommand(strSql, myConnection);
??? myConnection.Open();
??? int rows = myCommand.ExecuteNonQuery();
??? myConnection.Close();
}
ContextUtil是用于獲取 COM+ 上下文信息的首選類。由于此類的成員全部為static，因此在使用其成員之前不需要對(duì)此類進(jìn)行實(shí)例化。
ASP.NET頁面事務(wù)的優(yōu)勢和限制如下。
??? l限制：頁面的所有代碼都是同一個(gè)事務(wù)，這樣的事務(wù)可能會(huì)很大，而也許我們需要的是分開的、小的事務(wù)實(shí)現(xiàn)在Web層。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
