|
r>
catch
{
throw;
}
finally
{
conn.Close();
}
}
上述代码使用了下列存储过程：
CREATE PROCEDURE CheckProductExistsWithCount
@ProductID int
AS
SELECT COUNT(*) FROM Products
WHERE ProductID = @ProductID
GO
如何编码ADO.NET手工事务
下列代码说明如何利用SQL Server. NET数据供应器提供的事务支持来保护事务的支金转帐操作。该操作在位于同一数据库中的两个帐户之间转移支金。
public void TransferMoney( string toAccount, string fromAccount, decimal amount )
{
using ( SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=SimpleBank" ) )
{
SqlCommand cmdCredit = new SqlCommand("Credit", conn );
cmdCredit.CommandType = CommandType.StoredProcedure;
cmdCredit.Parameters.Add( new SqlParameter("@AccountNo", toAccount) );
cmdCredit.Parameters.Add( new SqlParameter("@Amount", amount ));
SqlCommand cmdDebit = new SqlCommand("Debit", conn );
cmdDebit.CommandType = CommandType.StoredProcedure;
cmdDebit.Parameters.Add( new SqlParameter("@AccountNo", fromAccount) );
cmdDebit.Parameters.Add( new SqlParameter("@Amount", amount ));
conn.Open();
// Start a new transaction
using ( SqlTransaction trans = conn.BeginTransaction() )
{
// Associate the two command objects with the same transaction
cmdCredit.Transaction = trans;
cmdDebit.Transaction = trans;
try
{
cmdCredit.ExecuteNonQuery();
cmdDebit.ExecuteNonQuery();
// Both commands (credit and debit) were successful
trans.Commit();
}
catch( Exception ex )
{
// transaction failed
trans.Rollback();
// log exception details . . .
throw ex;
}
}
}
}
如何利用Transact-SQL执行事务
下列存储过程说明了如何在Transact-SQL过程内执行事务的支金转移操作。
CREATE PROCEDURE MoneyTransfer
@FromAccount char(20),
@ToAccount char(20),
@Amount money
AS
BEGIN TRANSACTION
-- PERFORM DEBIT OPERATION
UPDATE Accounts
SET Balance = Balance - @Amount
WHERE AccountNumber = @FromAccount
IF @@RowCount = 0
BEGIN
RAISERROR('Invalid From Account Number', 11, 1)
GOTO ABORT
END
DECLARE @Balance money
SELECT @Balance = Balance FROM ACCOUNTS
WHERE AccountNumber = @FromAccount
IF @BALANCE < 0
BEGIN
RAISERROR('Insufficient funds', 11, 1)
GOTO ABORT
END
-- PERFORM CREDIT OPERATION
UPDATE Accounts
SET Balance = Balance @Amount
WHERE AccountNumber = @ToAccount
IF @@RowCount = 0
BEGIN
RAISERROR('Invalid To Account Number', 11, 1)
GOTO ABORT
END
COMMIT TRANSACTION
RETURN 0
ABORT:
ROLLBACK TRANSACTION
GO
该存储过程使用BEGIN TRANSACTION， COMMIT TRANSACTION，和ROLLBACK TRANSACTION状态手工控制事务。
如何编码事务性的.NET类
下述例子是三种服务性的NET类，它们配置或用于自动事务。每个类都带有Transaction属性，它的值将决定是否启动新事务流或者对象是否共享即时调用程序的数据流。这些元素一起工作来执行银行支金转移。Transfer类配置有RequiresNew事务属性，而Debit和Credit类配置有Required属性。这样，在运行的时候三个对象共享同一个事务。
using System;
using System.EnterpriseServices;
[Transaction(TransactionOption.RequiresNew)]
public class Transfer : ServicedComponent
{
[AutoComplete]
public void Transfer( string toAccount,
string fromAccount, decimal amount )
{
try
{
// Perform the debit operation
Debit debit = new Debit();
debit.DebitAccount( fromAccount, amount );
// Perform the credit operation
Credit credit = new Credit();
credit.CreditAccount( toAccount, amount );
}
catch( SqlException sqlex )
{
// Handle and log exception details
// Wrap and propagate the exception
throw new TransferException( "Transfer Failure", sqlex );
}
}
}
[Transaction(TransactionOption.Required)]
public class Credit : ServicedComponent
{
[AutoComplete]
public void CreditAccount( string account, decimal amount )
{
SqlConnection conn = new SqlConnection(
"Server=(local); Integrated Security=SSPI"; database="SimpleBank");
SqlCommand cmd = new SqlCommand("Credit", conn );
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( new SqlParameter("@AccountNo", account) );
cmd.Parameters.Add( new SqlParameter("@Amount", amount ));
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException sqlex)
{
// Log exception details here
throw; // Propagate exception
}
}
}
[Transaction(TransactionOption.Required)]
public class Debit : ServicedComponent
{
public void DebitAccount( string account, decimal amount )
{
SqlConnection conn = new SqlConnection(
"Server=(local); Integrated Security=SSPI"; database="SimpleBank");
SqlCommand cmd = new SqlCommand("Debit", conn );
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( new SqlParameter("@AccountNo", account) );
cmd.Parameters.Add( new SqlParameter("@Amount", amount ));
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException sqlex)
{
// Log exception details here
throw; // Propagate exception back to caller
}
}
}
合作者
非常感谢下列撰稿者和审校者：
Bill Vaughn, Mike Pizzo, Doug Rothaus, Kevin White, Blaine Dokter, David Schleifer, Graeme Malcolm（内容专家）, Bernard Chen（西班牙人）, Matt Drucke（协调）和Steve kirk.
读者有什么样的问题、评论和建议？关于本文的反馈信息，请发E-mail至devfdbck®microsoft.com。
你希望学习并利用.NET的强大功能吗？与微软技术中心的技术专家一起工作，学习开发最佳方案。
[9] [10]
上一页 [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] 下一页
|
