Prevent T-SQL statement from committing application started transaction

Question

I am writing a program that uses ADO.NET's SqlCommand to execute a number of user provided batches of T-SQL statements.

My application opens a transaction in which all of the statements are executed to ensure that if there is an error executing any, the whole lot are rolled back.

The problem I have come across is that a badly placed COMMIT (without a matching BEGIN TRAN) in the user provided SQL will commit my all-important transaction and leave me unable to roll back.

Does anybody have any ideas how I can stop the user's SQL from messing with my transaction but still allow them to BEGIN/COMMIT/ROLLBACK their own nested transactions?

Answer 1

Could you programmatically check each T-SQL statments that get loaded into the SqlCommand for the word COMMIT, so that you could then flag up potential issues with running that T-SQL.

Maybe archive them somewhere to be manually looked at.

Answer 2

The user's SQL needs to be corrected - if it has badly placed COMMITs (or ROLLBACKs), then I'd class that as a bug that needs to be fixed. I don't know much about the specifics of your scenario, but I think this is really what you need to do. If you find yourself trying to implement a workaround, that's when alarm bells should start ringing as it will just make the system more complex/harder to maintain.

Answer 3

You could enlist the SqlCommand in a distributed transaction. Those cannot be committed with a COMMIT statement in the database. See the EnlistDistributedTransaction method of the Connection object.

Distributed transactions introduce a whole new class of performance and reliability issues. If you can fix the T-SQL batches like AdaTheDev suggests, that would be a much better option.