Thursday, February 23, 2012

Transaction in ASP.NET

I have seen many people asking questions in forum that how to use transaction in ASP.NET. Normally we do transaction from SQL. Here I am going to explain how to do the transaction from ASP.NET code behind.

Normally transaction will be used in the code where you do all your database operation.

Below is the sample code to show you how the code looks like when we use the transaction in ASP.NET code behind.

string strCon = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlConnection SQLCon = new SqlConnection(strCon);
SQLCon.Open();
    SqlTransaction SQLTran = SQLCon.BeginTransaction("MyTrans");
    SqlCommand Cmd = new SqlCommand();
    Cmd.Transaction = SQLTran;
    Cmd.Connection = SQLCon;
    try
{
        Cmd.CommandText ="First SQL query to update/delete record";
        Cmd.ExecuteNonQuery();
        Cmd.CommandText ="Second SQL query to update/delete record";
        Cmd.ExecuteNonQuery();        
        SQLTran.Commit();
    }
    catch (Exception ex)
    {
    lblError.Text = "Error!! <br>+" + ex.Message.ToString();
        SQLTran.Rollback();
    }

In the code you would have noticed that roll back code is kept inside the catch block which means whole execution will be rolled back if there is an exception.

I hope now you are clear with the Transaction concept in ASP.NET

No comments: