.net中的事务处理（一）
点击次数：13 次 发布日期：2008-11-26 23:34:14 作者：源代码网
|
源代码网推荐 Building Distributed Applications with .NET 源代码网推荐 Priya Dhawan 源代码网推荐 Microsoft Developer Network 源代码网推荐 源代码网推荐 November 2001 源代码网推荐 源代码网推荐 Summary: This article describes how to run local and distributed transactions in your Microsoft .NET applications. (14 printed pages) 源代码网推荐 源代码网推荐 Contents 源代码网推荐 Introduction 源代码网推荐 Local and Distributed Transactions 源代码网推荐 Database Transactions 源代码网推荐 Manual Transactions 源代码网推荐 Automatic Transactions 源代码网推荐 Conclusion 源代码网推荐 源代码网推荐 Introduction 源代码网推荐 A transaction is a series of operations performed as a single unit of work. By binding a set of related operations together in a transaction, you ensure the consistency and reliability of the system despite any errors that occur. All operations in a transaction must complete successfully in order to make the transaction successful. 源代码网推荐 源代码网推荐 A transaction has a beginning and an end that specify its boundary within which it can span processes and machines. All resources within a transaction boundary participate in the same transaction. To maintain consistency across resources within a transaction boundary, a transaction must exhibit ACID properties, which are Atomicity, Consistency, Isolation, and Durability. See Processing Transactions in them Microsoft .NET Framework SDK and Transaction Processing in the Microsoft Platform SDK for details on transaction processing fundamentals. 源代码网推荐 源代码网推荐 In this article, we will show how you can run local and distributed transactions in your Microsoft .NET applications. 源代码网推荐 源代码网推荐 Local and Distributed Transactions 源代码网推荐 A local transaction is one whose scope is a single transaction-aware data resource, such as a Microsoft® SQL Server™ database or MSMQ message queue. For example, a single database system can enforce the ACID rules when it holds all the data involved in a transaction. In the case of SQL Server, there is an internal transaction manager that provides commit and rollback behavior. 源代码网推荐 源代码网推荐 Distributed transactions can span heterogeneous transaction-aware data resources and may include a wide range of activities such as retrieving data from a SQL Server database, reading messages from a Message Queue Server, and writing to other databases. Programming of distributed transactions is simplified by software that coordinates commit and abort behavior and recovery across several data resources. Microsoft Distributed Transaction Coordinator (DTC) is one such technology. It implements a two-phase commit protocol that ensures the transaction outcome is consistent across all data resources involved in a transaction. DTC only supports applications that have implemented compatible interfaces for management of transactions. These applications are referred to as Resource Managers (see Distributed Transactions in the .NET Framework Developer"s Guide for more information on this topic) and many are currently available, including MSMQ, Microsoft SQL Server, Oracle, Sybase, and others. 源代码网推荐 源代码网推荐 Database Transactions 源代码网推荐 Invoking a stored procedure that wraps required operations within the BEGIN TRANSACTION and COMMIT/ROLLBACK TRANSACTION statements yields the best performance by allowing you to run the transaction in a single round-trip to the database server. Database transactions also support nested transactions, which means you can start a new transaction from within an active transaction. 源代码网推荐 源代码网推荐 In the following code snippet, the BEGIN TRANSACTION statement begins a new transaction. You can end a transaction either by committing changes to the database using the COMMIT TRANSACTION statement or by undoing all the changes if any error occurs using the ROLLBACK TRANSACTION statement: 源代码网推荐 源代码网推荐 CREATE PROCEDURE Proc1 源代码网推荐 … 源代码网推荐 AS 源代码网推荐 -- Begin the transaction 源代码网推荐 BEGIN TRANSACTION 源代码网推荐 -- Do transaction operations 源代码网推荐 … 源代码网推荐 -- Check for any Error 源代码网推荐 If @@Error <> 0 源代码网推荐 -- Rollback the Transaction 源代码网推荐 ROLLBACK TRANSACTION 源代码网推荐 … 源代码网推荐 -- Commit the Transaction 源代码网推荐 COMMIT TRANSACTION 源代码网推荐 源代码网推荐 The next stored procedure accepts an XML representation of an Order as an input parameter. To make appropriate insertions into the Orders and OrderDetails tables, the stored procedure loads and parses the XML using the sp_xmlpreparedocument system stored procedure. As you see in the code, the stored procedure wraps all the operations in an explicit transaction so that if any of the operations fail to execute all the changes made are rolled back. 源代码网推荐 源代码网推荐 Note that the procedure sets XACT_ABORT to ON, which specifies that the SQL server will automatically roll back the transaction in case any of the statements fail to complete. 源代码网推荐 源代码网推荐 CREATE PROCEDURE InsertOrder 源代码网推荐 @Order NVARCHAR(4000) = NULL 源代码网推荐 , @OrderId int Output 源代码网推荐 AS 源代码网推荐 SET NOCOUNT ON 源代码网推荐 DECLARE @hDoc INT 源代码网推荐 DECLARE @PKId INT 源代码网推荐 -- Specify that the SQL Server automatically rolls back the current 源代码网推荐 -- transaction if a Transact-SQL statement raises a run-time error. 源代码网推荐 SET XACT_ABORT ON 源代码网推荐 -- Begin the transaction 源代码网推荐 BEGIN TRANSACTION 源代码网推荐 -- Load and Parse the incoming XML represeting an Order into an 源代码网推荐 -- XMLDocument 源代码网推荐 EXEC sp_xml_preparedocument @hDoc OUTPUT, @Order 源代码网推荐 -- Select order header from the Order node in the XMLDocument and 源代码网推荐 -- insert it into the Orders table 源代码网推荐 INSERT Orders(CustomerId, 源代码网推荐 OrderDate, 源代码网推荐 ShipToName, 源代码网推荐 ShipToAddressId, 源代码网推荐 OrderStatus) 源代码网推荐 SELECT CustomerId, CONVERT(DateTime,OrderDate), ShipToName, 源代码网推荐 ShipToAddressId, OrderStatus 源代码网推荐 FROM OPENXML(@hDoc, "/NewDataSet/Orders") 源代码网推荐 WITH ( CustomerId int "CustomerId", 源代码网推荐 OrderDate nvarchar(23) "OrderDate", 源代码网推荐 ShipToName nvarchar(40) "ShipToName", 源代码网推荐 ShipToAddressId int "ShipToAddressId", 源代码网推荐 OrderStatus int "OrderStatus") 源代码网推荐 -- Select the OrderId of the Order just inserted into the Orders table 源代码网推荐 -- to use it while inserting order details 源代码网推荐 SELECT @PKId = @@IDENTITY 源代码网推荐 -- Select order details from the Details node in the XMLDocument and 源代码网推荐 -- insert them into the OrderDetails table 源代码网推荐 INSERT OrderDetails (OrderId, 源代码网推荐 ItemId, 源代码网推荐 UnitPrice, 源代码网推荐 Quantity) 源代码网推荐 SELECT @PKId as OrderId, ItemId, UnitPrice, Quantity 源代码网推荐 FROM OPENXML(@hDoc, "/NewDataSet/Details") 源代码网推荐 WITH (ItemId int "ItemId", 源代码网推荐 UnitPrice money "UnitPrice", 源代码网推荐 Quantity int "Quantity") 源代码网推荐 -- Set the Output parameter 源代码网推荐 Select @OrderId = @PKId 源代码网推荐 -- Commit the transaction 源代码网推荐 COMMIT TRANSACTION 源代码网推荐 EXEC sp_xml_removedocument @hDoc 源代码网推荐 RETURN 0 源代码网推荐 GO 源代码网推荐 源代码网推荐 Although it provides good performance, you need to code in Transact SQL, which is not as easy to code in as a .NET-compatible language. 源代码网推荐 源代码网推荐 Manual Transactions 源代码网推荐 A manual transaction allows you to explicitly control the transaction boundary with explicit instructions to begin and end the transaction. This model also supports nested transactions that allow you to start a new transaction from within an active transaction. The trade off for this control is that there is an extra burden on you to enlist data resources with the transaction boundary and coordinating these data resources. There is no built-in support for distributed transactions and so it will be a lot of responsibility if you choose to control a distributed transaction manually; you will need to control every connection and resource enlistment, and provide implementation to maintain ACID properties of the transaction. 源代码网推荐 源代码网推荐 ADO.NET manual transactions 源代码网推荐 Both Microsoft ADO.NET data providers enable manual transactions by providing a set of objects that create a connection to the data store, begin a transaction, commit or abort the transaction, and finally close the connection. We will be using ADO.NET SQL managed provider for our examples. 源代码网推荐 源代码网推荐 To perform operations within a single transaction, you need to create an SQLTransaction object, begin the transaction using an SQLConnection object, ensure our database interaction occurs within a transaction, and commit or abort the transaction. The SQLTransaction object provides a variety of methods and properties to control a transaction. You can commit changes made to the database using the Commit method if every operation in the transaction completed successfully. To rollback changes use the Rollback method of the SQLTransaction object. 源代码网推荐 源代码网推荐 Note The Transaction property of the Command object must be set to an already started transaction for it to execute within the transaction. 源代码网推荐 Visual Basic .NET 源代码网推荐 源代码网推荐 Dim conn as SQLConnection 源代码网推荐 Dim cmd as SQLCommand 源代码网推荐 Dim txn As SQLTransaction 源代码网推荐 conn = New SQLConnection("ConnString") 源代码网推荐 cmd = New SQLCommand 源代码网推荐 " Open a conection 源代码网推荐 conn.Open() 源代码网推荐 " Begin a transaction 源代码网推荐 txn = conn.BeginTransaction() 源代码网推荐 " Set the Transaction in which the command executes 源代码网推荐 cmd.Transaction = Txn 源代码网推荐 … 源代码网推荐 源代码网推荐 Visual C# .NET 源代码网推荐 源代码网推荐 SQLConnection Conn = New SQLConnection("ConnString"); 源代码网推荐 SQLCommand Cmd = New SQLCommand; 源代码网推荐 // Open a connection 源代码网推荐 Conn.Open(); 源代码网推荐 // Begin a transaction 源代码网推荐 SQLTransaction Txn = Conn.BeginTransaction(); 源代码网推荐 // Set the Transaction in which the command executes 源代码网推荐 Cmd.Transaction = Txn; 源代码网推荐 … 源代码网推荐 源代码网推荐 In the following example, we execute two SQL commands within the boundary of a transaction. The first command inserts the order header of an order into the Orders table and returns the OrderId of the newly inserted order, which is used by the second command to insert the detail of the same order into the OrderDetails table. The transaction is aborted if either of the two commands fails to execute thereby preventing the rows from being added to the database. 源代码网推荐 源代码网推荐 Visual Basic .NET 源代码网推荐 源代码网推荐 Dim conn As SqlConnection 源代码网推荐 Dim cmd As SqlCommand 源代码网推荐 Dim tran As SqlTransaction 源代码网推荐 " Create a New Connection 源代码网推荐 conn = New SqlConnection("ConnString") 源代码网推荐 " Open the Connection 源代码网推荐 conn.Open() 源代码网推荐 " Create a new Command object 源代码网推荐 cmd = New SqlCommand() 源代码网推荐 " Create a new Transaction 源代码网推荐 tran = conn.BeginTransaction 源代码网推荐 " Set the Transaction within which the Commands execute 源代码网推荐 cmd.Transaction = tran 源代码网推荐 Try 源代码网推荐 " Insert the Order header 源代码网推荐 " Set the Command properties 源代码网推荐 With cmd 源代码网推荐 .CommandType = CommandType.StoredProcedure 源代码网推荐 .CommandText = "InsertOrderHeader" 源代码网推荐 .Connection = conn 源代码网推荐 " Add input and output parameters 源代码网推荐 .Parameters.Add("@CustomerId", SqlDbType.Int) 源代码网推荐 .Parameters("@CustomerId").Direction = ParameterDirection.Input 源代码网推荐 … 源代码网推荐 " Set the parameter values 源代码网推荐 .Parameters("@CustomerId").Value = 1 源代码网推荐 … 源代码网推荐 " Execute the command 源代码网推荐 .ExecuteNonQuery() 源代码网推荐 " Get the OrderId of the newly selected order header 源代码网推荐 OrderId = .Parameters("@OrderId").Value 源代码网推荐 " Clear the parameters for the next command 源代码网推荐 .Parameters.clear() 源代码网推荐 End With 源代码网推荐 源代码网推荐 " Insert the Order Details 源代码网推荐 " Set Command properties 源代码网推荐 With cmd 源代码网推荐 .CommandType = CommandType.StoredProcedure 源代码网推荐 .CommandText = "InsertOrderDetail" 源代码网推荐 .Connection = conn 源代码网推荐 " Add parameters 源代码网推荐 .Parameters.Add("@OrderId", SqlDbType.Int) 源代码网推荐 .Parameters("@OrderId").SourceColumn = "OrderId" 源代码网推荐 .Parameters("@OrderId").Direction = ParameterDirection.Input 源代码网推荐 … 源代码网推荐 " Set the parameter values 源代码网推荐 .Parameters("@OrderId").Value = OrderId 源代码网推荐 .Parameters("@ItemId").Value = 100 源代码网推荐 … 源代码网推荐 " Execute the command 源代码网推荐 .ExecuteNonQuery() 源代码网推荐 " Repeat the above few lines for each order detail 源代码网推荐 End With 源代码网推荐 源代码网推荐 " Commit the Transaction 源代码网推荐 tran.Commit() 源代码网推荐 Catch 源代码网推荐 " Rollback the Transaction 源代码网推荐 tran.Rollback() 源代码网推荐 Finally 源代码网推荐 " Cleanup Code 源代码网推荐 " Close the Connection 源代码网推荐 conn.Close() 源代码网推荐 End Try 源代码网推荐 源代码网推荐 As you see in the above code fragment, the two commands are being executed as part of a single transaction. If either of them fails, the transaction aborts and any changes made to the database are rolled back. Wrapping the code in a try/catch/finally block ensures that your transaction executes correctly; the transaction is committed at the very end of the try block when both the commands have been executed successfully. Any exception thrown is caught in the catch block where the transaction is aborted to undo changes made within the transaction. 源代码网推荐 源代码网推荐 Controlling a transaction through ADO.NET objects results in less efficient locking as compared to using explicit transactions in stored procedures. The reason is that an ADO.NET manual transaction takes at least as many round trips to the DBMS as there are operations to execute within the transaction in addition to trips that begin and end the transaction. You keep hold of locks while calls are sent back and forth between your ADO.NET code and the database server. 源代码网推荐 源代码网推荐 源代码网供稿. |
