Modifying a SQLLite database schema and data via SubSonic 3 within a transaction

Question

I'm using Visual Studio 2008, C#, SQLite via System.Data.SQLite with SubSonic 3. My application has gotten to a state where I need to upgrade the database schema (add columns, triggers, etc) then add new records via SubSonic generated ActiveRecord objects - all within the scope of a single transaction so that any failures could be nicely rolled back.

Ideally, this is sort of what I was hoping to do:

Begin Transaction
try
  Update Schema to latest version
  Use SubSonic objects to add new records/modify existing
  Commit Transaction
catch
  Rollback Transaction

Unfortunately, this doesn't work. All the schema changes are currently being via SubSonic.Query.CodingHorror, which doesn't seem to respect transactions. Is there a way to perform both schema changes and SubSonic data changes within the scope of a single transaction?

Answer 1

I think I found the answer to my question: use the same database connection for all actions and use transaction on that one database connection. Since I hadn't told SubSonic how to handle all the database connections for me - it used the default method, one connection per lookup or created object or CodingHorror. Since transactions can't span database connections, the behavior I saw was fully expected.

Once I created my own database connection and did the lookups, creates and CodingHorror schema changes on it, all transaction stuff begin to work properly.