Query regarding error behaviour with SqlBatch

[rkaushik15]

Hello!

I had some queries regarding error handling with SqlBatch.

From my local tests I can observe the following behavior:

With default case - SqlBatch.Transaction is null, in case any statement in the batch fails, the changes from non-failing DMLs in the batch get committed.
QUESTION:
Is my understanding correct? Does the SqlBatch.Execute* API internally create a local transaction to rollback or commit the changes?

If SqlBatch.Transaction is NOT null, and a statement in the batch fails, even the non-failing DML changes are not committed.
QUESTION:
Is my understanding correct? Does the provider automatically rollback the changes in case an error is encountered? And will it automatically commit when no errors are there?

Thanks in advance.

[Wraith2]

Try it and see? I expect it depends on the exact definition of the sql server interaction between batches and transactions so thing like xact_abort can change it.

[benrr101]

In general your understanding is correct - however, if there is a transaction, it shouldn't be automatically committed. You would need to commit the transaction when you've finished executing the changes.

But as @Wraith2 said, you may need to try it and experiment a bit. We need to update our docs to give a better understanding of this behavior to developers.