Using Transactions in C#

When programming with c# and SQLServer, you will come into a situation where you need to execute more than one SQL statements, and ensure all commands executed successfully. If one fails, then the entire process should be aborted and moved to the initial stage.

In this scenario we need to make use of transactions. Transactions can be written inside a stored procedure or it can be written inside a C# program. ADO.NET comes with a SQLTransaction class, which can be used for this. This post explains transactions with working example.

We use pubs database for the examples. Executing following scripts will create two tables, Table1 and Table2.

CREATE TABLE [Table1] (
[EmpName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmpAge] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [Table2] (
[EmpAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Explaining insertion without using Transactions

Why we use transactions ? Consider the following class that inserts data into Table1 and then to Table2.

class NoTransaction
{
public NoTransaction()
{
string FirstQuery = "INSERT INTO Table1 VALUES('Navaneeth',24)";
string SecondQuery = "INSERT INTO Table2 VALUES('MyAddress')";
int ErrorVar = 0;
using (SqlConnection con = new SqlConnection("Server=con;UID=harish;PWD=;database=pubs;"))
{
SqlCommand ObjCommand = new SqlCommand(FirstQuery, con);
con.Open();
//Executing first query
try
{
ObjCommand.ExecuteNonQuery();   //Exected first query
int a = 10 / ErrorVar; //Generating error. This will stop executing next statement.
ObjCommand.CommandText = SecondQuery;
ObjCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Error not rollbacking");
Console.WriteLine(ex.Message);
}
con.Close();
}
}
}

After inserting into Table1, I am generating an error which will execute the catch block specified. In this stage first query has already executed and second query failed to execute. Following class uses transactions to make sure that both queries are executed, if any error happened in between the process, it will be rollback.

class WithTransaction
{
public WithTransaction()
{
string FirstQuery = "INSERT INTO Table1 VALUES('Vineeth',24)";
string SecondQuery = "INSERT INTO Table2 VALUES('HisAddress')";
int ErrorVar = 0;
using (SqlConnection con = new SqlConnection("Server=con;UID=harish;PWD=;database=pubs;"))
{
SqlCommand ObjCommand = new SqlCommand(FirstQuery, con);
SqlTransaction trans;
con.Open();
trans = con.BeginTransaction();
ObjCommand.Transaction = trans;
//Executing first query
try
{
ObjCommand.ExecuteNonQuery();  //Exected first query
int a = 10 / ErrorVar; //Generating error. This will stop executing next statement.
ObjCommand.CommandText = SecondQuery;
ObjCommand.ExecuteNonQuery();

//Everything gone fine. So commiting
ObjCommand.Transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine("Error but we are rollbacking");
ObjCommand.Transaction.Rollback();
}
con.Close();
}
}
}

In the above example, I have used SQLTransaction class to maintain transactions. A transaction can either commit or rollback. Here I have caught the exception and invoking rollback. Using transactions in such a way ensures all the specified command inside transactions are executed properly.

Happy programming