c# nested transaction with sql

Question

When I update sth to MS SQL 2005 database with c#, I want to use a nested sql transaction. I have tried the following code:

try
{
    conn.Open();
    cmd.Connection = conn;
    cmd.Transaction = conn.BeginTransaction();

    ////BEGIN TRAN
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "storeprocname";
    cmd.Parameters.Add("@xxx", SqlDbType.VarChar, 50);
    cmd.Parameters["@xxx"].Value = "string_value";
    cmd.ExecuteNonQuery();
    if (<sql can do>)
    {
        cmd.Transaction.Commit();
    }
    else
    {
        cmd.Transaction.Rollback();
    }
}
catch (Exception ex)
{
    cmd.Transaction.Rollback();
}

The above code open a transaction and put some data in sql. It can Sussessfully run if the data I put is correct. But if it isn't, it catched the following error which is totally different than I expected, which is :

[System.Data.SqlClient.SqlException] = {"Cannot roll back updatetransport. No transaction or savepoint of that name was found.\r\nTransaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2."}

and the code is sql:

begin tran updatetransport
update db set xxx=@xxx  where xxx=@xxx
if @@rowcount < 1 or @@error <> 0 -- no record updated  
begin
    rollback tran updatetransport /*updatran is the name of transaction*/
 return -1 --fail
    end  
   else
begin  
 commit tran updatetransport
 return 0 --success  
end

It appeared that the sql server is automatically rollback the transaction. And throw out error when I tried to rollback it. However when I tried to modify the code in following way, there is still no luck. Do I miss out anything? I have declare @tcount =@@TRANCOUNT just after begin transaction.

 if @tcount = @@TRANCOUNT
 begin
    rollback tran updatetransport
 end

Avoid nested transactions. Because they aren't. I've been coding for quite a while and never needed a nested transaction in code. The fact you think you need one suggests your design may be wrong. — Mitch Wheat
You already have a transaction in your stored procedure, you don't need another one in the c# code. — Zohar Peled
I understand that this is redundant, but sometimes there may be two people handling sql and c#. Also this is so frustrating where some idea logically works, but it happens not as expected. Right or wrong it has to be documented. — martin

Oleg Oleg · Accepted Answer · 2015-06-11T13:15:55

You should not start nested transaction if already exist outer, for this you could check @@trancount variable in begin procedure and in the end:

declare @trancount int = @@trancount

if @trancount = 0 
  begin tran updatetransport

update db set xxx=@xxx  where xxx=@xxx
if @@rowcount < 1 or @@error <> 0 -- no record updated  
begin
    if @trancount = 0 
      rollback tran updatetransport /*updatran is the name of transaction*/
 return -1 --fail
    end  
   else
begin  
  if @trancount = 0 
   commit tran updatetransport
 return 0 --success  
end

c# nested transaction with sql

2 Answers