How To Handle 'No Longer Usable SqlTransaction' Error In C#

Today, I was working with one of my project and faced a problem. It was an exception: “This SqlTransaction has completed; it is no longer usable”.

This type of error comes in the project when SQL connection and transaction are handled properly in the project. You are doing some transaction in the code and let say by any reason the you suffer lost connection. Then it will throw this type of exception.

Mostly project creates a connection one time and use it across the application. If connection is being closed in between the process and you are going to roll back the transaction the error occurs. 

Error would be like the following:

This SqlTransaction has completed; it is no longer usable.

at System.Data.SqlClient.SqlTransaction.Zo eck()
at System.Data.SqlClient.SqlTransaction.Rollback()

Here's the code,

SqlConnection con = new SqlConnection("server=Mukesh-pc;database=dotnetTest;integrated security=true");
SqlTransaction myTransaction;
con.Open();
myTransaction = con.BeginTransaction();
try
{
    con.Close();
    SqlCommand cmd = new SqlCommand("insert into Employee values (100,'Mukesh')");
    cmd.Connection = con;
    cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
    //Not checking connection is open or not.
    myTransaction.Rollback();
}

In above code, you can see that on the time of RollBack transaction there is no checking for connection is open or not.

To resolve this issue, you can do two things.

Firstly, you can handle the exception into the catch block and show a proper message for it. See the following code.

SqlTransaction myTransaction;
con.Open();
myTransaction = con.BeginTransaction();
try
{
    con.Close();
    SqlCommand cmd = new SqlCommand("insert into Employee values (100,'Mukesh')");
    cmd.Connection = con;
    cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
    try
    {
        myTransaction.Rollback();
    }
    catch(Exception ex1)
    {
        //Hanlde the exception and log the proper message for this..
        Console.WriteLine("Rollback Exception Type", ex1.StackTrace.ToString());
    }
}

Secondly, if you are using the Web.Config file, there you can add the following lines of code.

<defaultSqlTimeoutSecs value="1000" />
<longSqlTimeoutSecs value="1500" />

And increase the timeout value to resolve this issue.

 

Thanks for reading the article. Hope you enjoyed it.