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

Question

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;
   }

Do you start the transaction in the body of your stored procedure? — Farhad Rahmanifard, Oct 30 '19 at 06:21
yes for the first time but when rollback did not occured i want to use transaction in .Net — hamid reza Heydari, Oct 30 '19 at 06:30
Can you provide some of your SQL code to find what might happen there? — Farhad Rahmanifard, Oct 30 '19 at 06:38
Anyway, both ways are equivalent. But in the case of the business layer, you can handle transaction through multiple query executions. — Farhad Rahmanifard, Oct 30 '19 at 06:42
Time-out is defined on the execution of an SQL command on its connection. — Farhad Rahmanifard, Oct 30 '19 at 08:17
Even when you execute an SQL script in SSMS, it creates a connection to the SQL server instance internally. — Farhad Rahmanifard, Oct 30 '19 at 08:19
@hamidrezaHeydari: Thanks for the marking as answer! will be helpful if you vote it up too! — Irfan, Oct 31 '19 at 04:00

Irfan · Accepted Answer · 2019-10-30T08:24:44.563

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

SQL Server side SqlTransaction
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();
}

You can not set transaction outside the Stored-Procedure in that case, but I don't think that is feasible to set transaction inside Stored-procedure even. So try to convert your code as I have defined above, that should work! — Irfan, Oct 30 '19 at 06:31
@hamidrezaHeydari: refer this for clear idea in script < https://stackoverflow.com/questions/15431285/call-multiple-sql-server-stored-procedures-in-a-transaction > — Irfan, Oct 30 '19 at 06:34
@hamidrezaHeydari: I don't think so, but you can use the `SqlException` Type in `catch`. Please see my edited answer part. — Irfan, Oct 30 '19 at 08:18

score 1 · Answer 2 · answered Oct 30 '19 at 08:48

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

score 0 · Answer 3 · answered Oct 30 '19 at 07:26

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.

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

3 Answers3

Linked