Does SQLite support transactions across multiple databases?

Question

I did some searches and also read the FAQ on the SQLite website, but could not find the answer to my question.

It is possible that my approach to the database is wrong, but for now I would like to store my data in several SQLite3 databases, so this means separate files. I am very worried about data corruption due to a possible failure of my application or a power outage in the middle of a data change in my tables.

To ensure data integrity, I basically need to do this:

start transaction change table in database # 1 change table in database # 2 commit or rollback if error

Is it supported by SQLite? In addition, I am using sqlite.net, especially the latter, based on SQLite 3.6.23.1.

UPDATE

Another question is what do people usually add to their block tests? I always unit test the database, but never had such a case. And if so, how would you do it? This is almost the same as you need to pass another parameter to a method, for example, bool test_transaction, and if it is true, throw an exception between database calls. Then check after the call to make sure that the first data set does not end up in another database. But perhaps this is what is described in the SQLite tests, and should not appear in my test cases.

Answer 1

Yes, transactions work with a different sqlite database and even between sqlite and sqlserver. I tried a couple of times.

Some links and information

From here - Transaction between different data sources

Because the SQLite ADO.NET 2.0 Provider supports a transaction, you can not only execute a transaction that spans multiple SQLite data sources, but also spans other database engines, such as SQL Server.

Example:

using (DbConnection cn1 = new SQLiteConnection(" ... ") ) using (DbConnection cn2 = new SQLiteConnection(" ... ")) using (DbConnection cn3 = new System.Data.SqlClient.SqlConnection( " ... ") ) using (TransactionScope ts = new TransactionScope() ) { cn1.Open(); cn2.Open(); cn3.Open(); DoWork1( cn1 ); DoWork2( cn2 ); DoWork3( cn3 ); ts.Complete(); } 

How to connect a new database:

 SQLiteConnection cnn = new SQLiteConnection("Data Source=C:\\myfirstdatabase.db"); cnn.Open(); using (DbCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "ATTACH DATABASE 'c:\\myseconddatabase.db' AS [second]"; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT COUNT(*) FROM main.myfirsttable INNER JOIN second.mysecondtable ON main.myfirsttable.id = second.mysecondtable.myfirstid"; object o = cmd.ExecuteScalar(); }