How to Perform Transactions in ASP.NET

Transaction
A transaction is a group of certain operations either ALL or None which means if your transaction goes successfully then the operation would declare as Commit and if your transaction goes fail then the operation would declare as rollback. For this reason, whatever the modification was committed in a database that would be removed. Although, a single unit of a transaction can be done at web services that create common language runtime (CLR) system for the process management in a database.

Furthermore, a transaction is a T-SQL statement in a database server. Also, a transaction is very crucial and import for any industry which involves a lot of banking service connection with the corporate world. I will tell you about the transaction in ASP.NET.

Transaction types

The transaction contains its two types as follows:

  1. Implicit Transaction
  2. Explicit Transaction

Implicit Transaction

  • Implicit transactions perform DDL(Data Definition Language) and DML(Data Manipulation Language) statements.
  • DDL commands like Create, Alter, Drop and Truncate) and DML commands like (Insert, Updperforms.e) etc.
  • All of the statements work under an implicit transaction.
  • But if any error occurs with these commands during the transition individually then the SQL database will roll back the whole command.
  • For this reason, whatever the modification was done would be removed automatically.

Explicit Transaction

  • Programmers or Developers define the explicit transaction which performs only DML statements as the single unit of execution.
  • Also, note that SELECT command does not manipulate or update the data.
  • So, this command you can not include the transaction.

When to Use Transaction Database

As you know transaction supports multiple operations which should be either succeed all or nothing like a single unit. A Transaction happens when most of the rows you insert, update or delete at a time. And, this also happens when any modification happens in the two or more database server consequently. A transaction generates a distributed system where data can be manipulated in a database with different servers.

When a transaction happens, it creates a lock automatically on data for remaining changes to the database. As a result, A good benefit of the transaction, if the transaction gets locked then no one can take place on locked data for the further transaction. Until you do not release it. Also, you can check each and every security transaction through a database and you can lock complete database if you do not need any transaction.

Transaction Properties

A Transaction basically follows its properties ACID(Atomic, Consistent, Isolated, Durability).

Atomic

Atomicity: An atomic transaction defines the set of operations which is completed successfully. It simply gives that a transaction is either “PASS” or “FAIL” element. Atomic does not contain any middle state in between them like an update in the data. It also defines as “All or Nothing” formula.

Consistency

If any type of transaction exists at the user end, that must be conceived into a valid and legal state in the database.

Isolation

When multiple transactions execute at a single unit of time, noteworthy then the state of the database must be far all over the transaction period.

Durability

Finally, some common external factors like system crash, power failure, but failure should not affect the data once the transaction is completed on the server.

Transaction In ASP.NET using code

we will discuss transaction implementation through ASP.NET code. For demonstration, we need to create two tables tblTeam and tblTeammember. In table1 we have two arguments (TeamId and TName) where TeamId column contains the primary key which means this column field cannot blank. In table2 contain TeamMemberId of integer type value. Also, it contains the foreign key with the reference of the primary key.

— Table1 Script
CREATE TABLE tblTeam

(

TeamId int PRIMARY KEY,

TName varchar(50) NULL

);
— Table2 Script
CREATE TABLE tblTeammember

(

TeamMemberID int,

TeamId int foreign Key references Project(TeamId)

);

Now, we write a sample code to execute database operations for a transaction as follows.

string strConnString = "myconnstring1";

//you can take this from Web.config file

SqlTransaction objTrans1 = Null;

using (SqlConnection objConn1 = new SqlConnection1(strConnString))

{

objConn1.Open();

objTrans1 = objConn1.BeginTransaction();

SqlCommand objCmdX = new SqlCommand("insert into tblTeam values(1, 'TestTeam')", objConn1);

SqlCommand objCmdY = new SqlCommand("insert into tblTeamMember(TeamMemberID, TeamID) values(2, 1)", objConn1);

TRY

{

objCmdX.ExecuteNonQuery();

objCmdY.ExecuteNonQuery();

// when you run this code it will throws exception because of foreign key constraint

objTrans1.Commit();

}

catch (Exception)

{

objTrans1.Rollback();

}

finally

{

objConn1.Close();

// connection ends

}

}

As a result, in the code above two SQL queries executes for the transaction. First one creates records into the Table1(tblTeam) and another one creates records into the Table2(tblTeamMmeber). When you execute the first statement, it will run completely but another table will give you an exception error because we tried to insert TeamId as 2 which does not exist in the tblTeam table. As transaction says either transaction should pass or fail. So, to make this code perfect we will use the transaction to make this code correct.

For this reason, we will use Sqltransaction classes which create objects of Begin transaction() function in Sqlconnection string object. From here actual transaction will start, if it goes correct then commit the transaction and saved into the database else rollback. Also, we can get the transaction using TransactionScope method class.

TransactionScope Class

In this Transactionscope code statement converted into the transaction mode which cannot be inherited. It shows in the namespace like System.Transactions.TransactionScope. This scope of transaction contains three properties as follows.

Isolation Level: It locks the mechanism to read the data into the different transaction. There are certain options available as like Uncommitted, ReadCommited, and Serializable. The default value is Serializable.

Timeout: It defines the time limit that how long the transaction method object will have to wait to be successful. Transaction timeout is quite different from Sqlcommand timeout. Default time limit contains 1 minute.

TransactionScopeOption: It contains enum options as follows

  • Disable: Disable is a default value that does not affect the transaction.
  • Required: This is a default element for the transactionScope. It generates automatically with the transaction.
  • NotSupported: This element executes outside of the transaction content.
  • RequiresNew: This transaction totally dependent on its inside transaction.
  • Suppress: When you select this option then you cannot create any other transaction.

Now set the default timeout in web.config file as follows.

<system.transactions>

// It starts the system transaction from here.

<defaultSettings timeout="40"/>

<machineSettings maxTimeout1="1250"/>

</system.transactions>

// system transaction ends here.

System.transaction always there as default. We need to create reference a follows

This system generated transaction creates TransactionScope method class which tells to SQL commands to insert data into the table tblTeam and tblTeamMember. When you find everything worked perfectly then it will commit() the transaction else rollback the transaction to the old stage. Here is the code for the perfect transaction as follows.

Code Script
using (var txscope1 =new TransactionScope(TransactionScopeOption.RequiresNew1))

{

try

{

using (SqlConnection objConn1 = new SqlConnection(strConnString1))

{

objConn1.Open();

// here connection opens with obj.conn1 command

SqlCommand objCmdX = new SqlCommand("insert into tblTeam values(1, 'TestTeam')", objConn1);

SqlCommand objCmdY = new SqlCommand("insert into tblTeamMember(TeamMemberID, TeamId) values(2, 1)", objConn1);

objCmdX.ExecuteNonQuery();

objCmdY.ExecuteNonQuery();

// It gill give you an exception because of the foreign key constraint

txscope1.Complete();

// It shows the status as complete of you transaction process.

}

}

catch(Exception ex)

{

// Log error result

txscope1.Dispose();

}

}

Finally, execute the above code and you will know about the transaction.

Conclusion

I hope you enjoyed the transaction scenarios. A transaction is the most required process in the corporate world. Although, a transaction makes the transaction easier for you that you can do any single transaction anywhere in the world. I hope this will work for you.
However, do write your suggestions or query to me in the comment section.

Thank You for reading!!!

Leave a Comment