CODEWITHSUNDEEP
c#sql-servertransactions
hamid reza Heydari
hamid reza Heydari

Reputation: 62

Which one works better? Transaction in SQL Server or in C#?

I write stored procedures with read uncommitted transaction isolation level, but when connection time out the rollback in catch in SQL doesn't work.

When I use SqlTransaction in my business layer in .Net, the problem is solved and I can support any errors occurred in SQL in my try... catch in .Net.

Is this what I did right?

using (SqlConnection conn = new SqlConnection(Data.DataProvider.Instance().ConnectionString))
{
    conn.Open();

    SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadUncommitted, "Trans");

    try
    {
        object ores = SqlHelper.ExecuteScalar(.......)
        string res = ores.ToString();

        if (string.IsNullOrEmpty(res))
        {
            tran.Rollback();
            info.TrackingCode = "";
            return 0;
        }
        else if (res == "-4")
        {
            tran.Rollback();
            return -4;
        }
        else if (res == "-1")
        {
            tran.Rollback();
            return -1;
        }
        else
        {
            tran.Commit();
            return 1;
        }
   }
   catch (Exception)
   {
       tran.Rollback();
       info.TrackingCode = "";
       return 0;
   }

Upvotes: 1

Views: 2111

Answers (3)

hamid reza Heydari
hamid reza Heydari

Reputation: 62

When a client timeout event occurs (.net CommandTimeout for example), the client sends an "ABORT" to SQL Server. SQL Server then simply abandons the query processing. No transaction is rolled back, no locks are released. I solved this problem with Sqltransaction in my .Net Code Instead Sql and Mange Exceptions with SqlExceptions

Upvotes: 1

Irfan
Irfan

Reputation: 695

As per your requirement, there can be two ways to define SqlTransactions

  1. SQL Server side SqlTransaction
  2. C#.NET (business layer) SqlTransaction.

(Both can not be mixed)

In your case, you tried to define SqlTransaction at the business layer. So better you call Stored-procedure too in the business layer. So the Business layer will rectify the SqlTransaction and time-Out error won't occur.

So first include your Stored-Procedure code as a Command execution (at business layer) and then execute. Change your code as below with your required conditions. 

                // Command Objects for the transaction
                SqlCommand cmd1 = new SqlCommand("YourStoredProcedureName", cnn);
                cmd1.CommandType = CommandType.StoredProcedure;

                //If you are using parameter for the Stored-procedure
                cmd1.Parameters.Add(new SqlParameter("@Param1", SqlDbType.NVarChar, 50));
                cmd1.Parameters["@Param1"].Value = paramValue1;

                //Execute stored procedure through C# code
                cmd1.ExecuteNonQuery();
                transaction.Commit();

EDITED: Reference link

catch (SqlException sqlEx)
{
    if (sqlEx.Number == -2)
    {
       //handle timeout
    }
    transaction.Rollback();
}

Upvotes: 1

Farhad Rahmanifard
Farhad Rahmanifard

Reputation: 688

Both ways are equivalent. But in the case of the business layer, you can handle the transaction through multiple query executions.
Here, you should handle conn.Open() for possible exceptions. Also, check SqlHelper uses your created connection and transaction everywhere in its code.

Upvotes: 0

Related Questions