Need For a TransactionScope Which Won't Rollback With An Exception

Question

I have Some Command Like Insert, Update, Create and so on that i want to run them in some database in a SQL instance. i use transaction scope for this and i want to run all of the commands on all databases even if some errors occurred. if some error had happened i want to show all errors to user without committing the right commands. but in transaction when it reaches the first error, it'll be rolled back and i can't continue running other commands. my commands are like this :

using Trans as new TransactionScope

con.open()

for i as integer = 0 to n

Try
    com.commandtext = coms(i)
    com.executenonquery()

catch ex as exception
    errorCollection.add(ex.message) 
    continue for

end try

next

end using

Answer 1

The way transactions work is that SQL Server is actually keeping track of any errors occurring. If any error occurs in the transaction, SQL Server declares the transaction faulty and will not allow any further commends to be sent under the same transaction. Thus, even if the SQL error is handled by a try/catch block in your application, the transaction has already failed on the SQL Server and will thus fail when you send the next command.

The only viable option would be to basically run each command in a transaction and then roll them back, whether they fail and succeed. If all succeed, you can then run them all in one big transaction and commit that. This will only work if they're completely independent though.

Your best option is probably to work around it, basically accepting you may only get the first error.

Answer 2

Move the exception handling into SQL, see Exception Handling and Nested Transactions. Decide what exceptions can be safely handled and what must be raised further and what can be safely ignored. The vast majority of exception cannot be ignored. Even those that may be ignored, the vast majority must consider the entire transaction context so that they do not leave the data inconsistent. Note that not all exception can be handled by rollback to savepoint, certain exceptions will be raised after already destroy the transaction (eg. deadlock).

Overall I would classify your requirement in the category extremely poor judgement ideas.