c# nested transaction with sql

When I update sth to mssql 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.rnTransaction 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


Source: sql

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.