SqlTransaction rollback exception handling

Question

I have two stored procedures that I want to execute wrapped in a transaction. For various reasons, I need to process the transaction in the application code, and not in the database.

At the moment, my code is as follows:

try { using (SqlConnection conn = Connection()) { conn.Open(); using (SqlTransaction sqlTrans = conn.BeginTransaction()) { try { using (SqlCommand cmd1 = new SqlCommand("Stored_Proc_1", conn, sqlTrans)) { cmd1.CommandType = CommandType.StoredProcedure; cmd1.ExecuteNonQuery(); } using (SqlCommand cmd2 = new SqlCommand("Stored_Proc_2", conn, sqlTrans)) { cmd2.CommandType = CommandType.StoredProcedure; cmd2.ExecuteNonQuery(); } sqlTrans.Commit(); } catch { sqlTrans.Rollback(); throw; } } conn.Close(); } } catch (SqlException ex) { // exception handling and logging code here... } 

When one of the stored procs throws an error, the exception message that I see looks like this:

 Error message from raiserror within stored procedure. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0. 

This makes sense, because at the first catch, the transaction is not yet rollback.

But I want a “clean” error (without a tran count message - this does not interest me because I roll back the transaction) for my exception handling code. Is there a way I can restructure my code to achieve this?

EDIT:

The basic structure of my stored processes is as follows:

 create proc Stored_Proc_1 as set nocount on begin try begin transaction raiserror('Error raised by Stored_Proc_1', 16, 1) commit end try begin catch if (@@trancount > 0) rollback declare @ErrMsg nvarchar(4000), @ErrSeverity int, @ErrProc sysname, @ErrLine varchar(10) select @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY(), @ErrProc = ERROR_PROCEDURE(), @ErrLine = ERROR_LINE() -- log the error -- sql logging code here... raiserror(@ErrMsg, @ErrSeverity, 1) end catch 

UPDATE: I took transaction processing from my stored procedures and seems to have solved the problem. Obviously, I did it wrong, but I still would like to know how to do it right. Does a transaction remove the best solution from stored procedures?

Answer 1

Well, conn.Close() can go anyway - it will be closed using (if you think about it, it's odd that we only Close() after the exception).

Does any of your stored procedures execute any transaction code within itself (which is not rollback / commit)? It seems that this is exactly the problem ...? In any case, the error message tells me that one of the stored procedures executes COMMIT , even if it did not start the transaction - perhaps due to the (incorrect) approach:

 -- pseduo-TSQL IF @@TRANCOUNT = 0 BEGIN TRAN -- ... IF @@TRANCOUNT > 0 COMMIT TRAN -- or maybe = 1 

(if you are doing conditional transactions in TSQL, you must track (via the bool flag) whether you created the transaction - and only COMMIT if you did)

Another option - using TransactionScope is easier to use (you don't need to install it against every command, etc.), but a little less efficient:

 using(TransactionScope tran = new TransactionScope()) { // create command, exec sp1, exec sp2 - without mentioning "tran" or // anything else transaction related tran.Complete(); } 

(note that there is no rollback, etc. Dispose() (via using ) will roll back if necessary.

Answer 2

Do not make transactions in your database / stored procedures if you do this in your application! This is likely to simply create confusion. Select a layer and stick to it. Make sure you have a good normalized database, and exceptions should seep up.

Answer 3

I agree with Mark that the problem is likely to be in the stored procedures themselves. There's a pretty interesting article outlining a few questions here .

Answer 4

If the stored procedure includes this code:

 BEGIN TRY SET @now = CAST(@start AS datetime2(0)) END TRY BEGIN CATCH SET @now = CURRENT_TIMESTAMP END CATCH 

and you pass, for example. 'now' as @start, CAST in an attempt to fail. This means that the transaction is considered a rollback, even if the error itself was committed and processed. Therefore, until you get exceptions from the above code, the transaction cannot be completed. If your stored procedures have this code, you must rewrite it to avoid try / catch.