Error handling and RollBack Transaction in SQLITE

Question

I'm Altering multiple sqlite tables with SQL script by calling ExecuteNonQuery. I want to do this operation in transaction and want to roll it back when anything fails.

I looked at BEGIN TRANSACTION and its clear that I have to call ROLLBACK TRANSACTION when anything goes wrong. But I don't know how could TRY...CATCH (Transact-SQL) kind of thing here.

NOTE: Whole of Sql Script file (which contains many other statements apart from these few statements which needs to be fired in one transaction) is read by .ReadToEnd() and then executed in one go as of now. I want to handle this in sql script file itself and don't want to change the code.

Answer 1

Please take a look at SQLite on conflict clause

Start with BEGIN TRANSACTION

You have to add ON CONFLICT ROLLBACK on your actions

And COMMIT TRANSACTION at the end ;-)