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

Advertisements

5 thoughts on “Using Transactions in C#

  1. Good Article, will help to optimize code through Front-end too, rather that back-end alone.

    Which pratice is good using transaction in Stored Procedure or front-end code…

  2. Hi Sini,

    Thanks. Answering your question is bit tough. Because in some scenarios we will feel front end transactions are good, but in some cases backend. Sincerely speaking I always like to use transactions in backend. Because less frontend code, easy to maintain by editing the procedure, and a seperation of transact SQL statements from frontend.

  3. What do you mean by this ‘a seperation of transact SQL statements from frontend’

    Is it meant like this:
    If we try to execute more that one sql commands/SPs sequentially from front-end, better to use front-end transact SQL statements.

  4. Sini,

    By “Separation of Transact SQL statements from front end” I meant, all the queries and database related things can be written inside database itself rather than writing in front-end. Like how we create stored procedures.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s