transactional sqlite? in C#

Question

I am writing a prototype application. Right now some things fail such as inserting a non unique column. In this case i would like to rollback everything i have did. How do i do that? I also notice in sqlite i need to commit the data, in C# it seems to do it automatically which makes me suspect there is an automatic rollback option?

Answer 1

SQLite.org says:

The changes to locking and concurrency control in SQLite version 3 also introduce some subtle changes in the way transactions work at the SQL language level. By default, SQLite version 3 operates in autocommit mode. In autocommit mode, all changes to the database are committed as soon as all operations associated with the current database connection complete.

The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword is optional) is used to take SQLite out of autocommit mode. Note that the BEGIN command does not acquire any locks on the database. After a BEGIN command, a SHARED lock will be acquired when the first SELECT statement is executed. A RESERVED lock will be acquired when the first INSERT, UPDATE, or DELETE statement is executed. No EXCLUSIVE lock is acquired until either the memory cache fills up and must be spilled to disk or until the transaction commits. In this way, the system delays blocking read access to the file file until the last possible moment.

Answer 2

Rollback: It looks like what you are looking for is the command text "INSERT OR ROLLBACK ... "

Transaction: sqlite automatically puts each command into its own transaction unless you specifically state when to begin. EDIT: TML explains this portion a bit more in-depth in his answer

example of explicitly opening/committing a transaction is:

using (DbTransaction dbTrans = myDBConnection.BeginTransaction())
{
     using (DbCommand cmd = myDBConnection.CreateCommand())
     {
         ...
     }
     dbTrans.Commit();
}