Note that this pragma command must be run outside of a transaction if you run it while a transaction is active, it will silently do nothing. If you want the database to prevent you from inserting invalid foreign keys, then you must run PRAGMA foreign_keys = 1 to turn enforcement on. Turn on foreign key enforcementįoreign key constraints are not enforced by default in SQLite. It's worth reading that first, as this post covers different topics. The official documentation for Python's sqlite3 module already has a section on "Using sqlite3 efficiently". I'd like to share a few lessons I have learned on using SQLite effectively in Python. It is lightweight, reliable, well-documented, and better than the filesystem for persistent storage. Typically my database code follows the format of: using (SqlConnection connection = new SqlConnection(".I use SQLite as the database for my personal projects in Python. To associate the command with the transaction you'll need the following piece of code after each new command object created: cmd.Transaction = trans If this code were to be executed against SQL server or Oracle an exception would be thrown stating that all commands must be assigned the active transaction (if there is one). One thing I noticed, though, is that your command objects you've created are not associated with the transaction. Keep in mind though, disposing a transaction object will not explicitly close the underlying database connection. If the transaction has not been committed the transaction will be rolled back. If the transaction has been committed, nothing will be rolled back. (I like this method because the code is cleaner without try/catches everywhere - I only use try/catch when I can react accordingly) Remember, in almost all exception cases the objects in a using block will still be disposed, even if you don't catch the exception. In many cases with my work, if the transaction hits the end of a using statement without a commit it will roll it back without me coding an explicit try/catch. In this case, the catch block could be used to retry an operation with different parameters and such. In an unexpected error condition I would rather my data be left as is and not in a half-committed state, which is the point of a transaction. Transactions must be committed explicitly as Daniel said.What bothers me is WHY does it not make any difference wether OR NOT I use trans.Rollback() in the catch-block.Ģ.) I am using the "using(resource)"-statement so what will happen if the transaction succeeds/commits to the state of the connection ? Will it be closed? Just concern that I do not do use the `using(var trans = new SQLiteTransaction()) That is ok because I used a transaction and the. The connection from the transaction needs to be shared among the dataprovider methods.ġ.) When a SQLiteException occurs because of inserting same primary keys "John" there is not inserted any of the "John" values. SQLiteCommand cmd2 = con.CreateCommand() Ĭmd2.CommandText = "INSERT INTO TEST(Name) VALUES('John')" Īs I use SQLite its best practice to use the SQLiteTransaction class for every executed sql command. SQLiteCommand cmd1 = con.CreateCommand() Ĭmd1.CommandText = "INSERT INTO TEST(Name) VALUES('John')" Var con = new Source=A:\TransactionScopeTest\TransactionTest.db Foreign Keys=ON") ĭo 2 sqlite inserts with same Primary Key value to raise an exception Create the Connection Transaction: public SQLiteTransaction BeginTransaction()
0 Comments
Leave a Reply. |