.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Articles >> .Net Framework >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Using Manual Transactions

Posted By:Shashi Ray       Posted Date: November 30, 2009    Points: 25    Category: .Net Framework    URL: http://www.dotnetspark.com  

Using Manual Transactions

With manual transactions, you write code that uses the transaction support features of either ADO.NET or Transact-SQL directly in your component code or stored procedures, respectively. In most cases, you should opt for controlling transactions in your stored procedures because this approach offers superior encapsulation, and from a performance perspective is comparable to performing transactions with ADO.NET code.

Performing Manual Transactions with ADO.NET

ADO.NET supports a transaction object that you can use to begin a new transaction and then explicitly control whether it should be committed or rolled back. The transaction object is associated with a single database connection and is obtained by the BeginTransaction method of the connection object. Calling this method does not implicitly mean that subsequent commands are issued in the context of this transaction. You must explicitly associate each command with the transaction, by setting the Transaction property of the command. You can associate multiple command objects with the transaction object, thereby grouping multiple operations against the single database in a single transaction.

More Information

  • The default isolation level for an ADO.NET manual transaction is Read Committed, which means that the database holds shared locks while data is being read, but data can be changed before the end of the transaction. This can potentially result in non-repeatable reads, or phantom data. You can change the isolation level by setting the transaction object's IsolationLevel property to one of the enumerated values defined by the IsolationLevel enumerated type.
  • You must give careful consideration to choosing an appropriate isolation level for your transactions. The tradeoff is one of data consistency versus performance. The highest isolation level (Serialized) offers absolute data consistency, but at the price of overall system throughput. Lower isolation levels can make an application more scalable, but at the same time, increase the possibility of errors resulting from data inconsistency. For systems that read data most of the time, and write data rarely, lower isolation levels might be appropriate.

Performing Manual Transactions with Stored Procedures

You can also control manual transactions directly by using Transact-SQL statements in your stored procedures. For example, you could perform transactional operations by using a single stored procedure that employs Transact-SQL transaction statements such as BEGIN TRANSACTION, END TRANSACTION, and ROLLBACK TRANSACTION.

More Information

  • If required, you can control the transaction isolation level by using the SET TRANSACTION ISOLATION LEVEL statement in the stored procedure. Read Committed is the SQL Server default
  • For a code sample that shows how to perform transactional updates using Transact-SQL transaction statement.



Shashi Ray

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend