Skip to main content
Transaction .NET

Transactions in Dot Net – Part 1

In my 2-part blog series I will be explaining how transactions can be implemented in .Net and SQL servers respectively.  In this blog (Part 1), I will go into details on how transactions can be implemented in DotNet.

Introduction

Most applications deal with back-end databases. For instance, you are supposed to insert data into master and child

tables in the SQL Server. While doing this,  if you inserted one row into the master table and then some error occurred in that task, child data is not inserted and you must rollback the master table data also; otherwise inconsistency in data will happen. In such situations, transaction plays a vital role to make sure data operations that occur, happen successfully.

What is `Transaction’?

A transaction is a single unit of work which means either ALL or NONE. If a transaction is successful, all of the data operations are committed and become a durable part of the database. If a transaction encounters errors/exceptions and must be canceled or rolled back, then all of the data modifications/operations need to be removed.

Properties of Transaction

Transaction has the four standard properties, called by the acronym as ACID.

  • Atomicity
    Ensures that all operations within the work unit are completed successfully; If the transaction fails then operations are rolled back to their previous state.
  • Consistency
    Ensures that the database properly changes states upon a successfully committed transaction.
  • Isolation
    Enables transactions to operate independently of and transparent to each other.
  • Durability
    Ensures that a committed transaction persists in case of a system failure.

Properties of Transaction

Example:

Create two tables (Project Member, Project). In Project Member table, column ProjectID is a foreign key to Project table’s ProejctID column.

Project Member table

Here is the sample code to implement transaction in ADO.NET.

ADO.NET.

In the above code , it executes two SQL queries: first it inserts record to project, second it inserts record to projectMember table. First SQL statement executes properly but second sql statement throws exception because we are inserting projectId as 2 which is not present in project table. Transaction dictates either complete success or failure, there is nothing in between. So to avoid the above problem, we will use Transaction to make everything perfect. Here it uses SqlTransaction class and creates object by calling BeginTransaction() method of SqlConnection class. Transaction starts here. If everything goes fine then commit (save into database) it otherwise rollback (delete inserted record in scope in the transaction) it.

Using TransactionScope

It is present in namespace System.Transactions.TransactionScope

It has 3 main properties

  • Isolation Level
    It defines the locking mechanism to read data in another transaction. Available options are Read UnCommitted, Read Committed, Repeatable Read, Serializable. Default is Serializable.
  • Timeout
    How much time transaction object will wait to be completed. SqlCommand Timeout is different than Transaction Timeout. SqlCommand Timeout defines how much time the SqlCommand object will wait for a database operation to be completed. Available options are 10 minutes. Default is 1 minute.
  • TransactionScopeOption

It is an enum. Following are the options.

Option

Description

DisableThis component does not participate in a transaction. This is the default value.
Not SupportedThis component runs outside the context of a transaction.
RequiredIt is the default value for TransactionScope. If any already exists then it will join with that transaction otherwise create new one.
Requires NewWhen this option is selected a new transaction is always created. This transaction is independent with its outer transaction.
Suppress

When this option is selected, no transaction will be created. Even if is already  there.

You can set default time out in web.config like.

  1. <system.transactions>
  2. <defaultSettings timeout=”30″/>
  3. <machineSettings maxTimeout=”1200″/>
  4. </system.transactions>

System.Transactions is available by default; we need to add the reference.

System Transaction

In the below code sample, it creates TransactionScope class object and defines SQL queries to add records to Project table, ProjectMember table. Once everything is fine, it calls Complete() to commit the data. If exception occurs it rollsback to previous state.

5

Difference between TransactionScope and BeginTranaction()

  • TransactionScope is usually a better choice because it allows you to nest calls to other methods that might require a transaction without you having to pass the transaction state around.
  • With TransactionScope, as long as the scope exists, it will handle everything that registers with the current Transaction on the thread, making your code cleaner, and more maintainable.
  • TransactionScope uses MS DTC(Microsoft Distributed Transaction Coordinator) for transaction management.
  • Due to its ease of use and efficiency, it is recommended that you use the TransactionScope class when developing a transaction application.

 

Sneha Singh

Sneha Singh

Sneha Singh works with Trigent Software as Sr. Software Engineer. She has around 5.6 yrs of experience in .Net and has been working on Web-based applications. In her leisure time, Sneha likes reading fiction and listening to music..

One thought on “Transactions in Dot Net – Part 1

  1. I’ve located your write-up quite informative and interesting. I appreciate your points of view and I agree with so many. You’ve done a great job with generating this clear enough for anyone to understand.

Comments are closed.