0

Given

I use SQLite-Net NuGet package.
I have old synchronous API for database manipulations (SQLiteConnection).

What I need

I want to use new asynchronous API for database manipulations (SQLiteAsyncConnection)

Problem

Old synchronous API is big enough that I can't do quick migration to async API, so I need to use both synchronous and asynchronous API together using one database file, like this:

var database = new SQLiteConnection(connectionString); // Synchronous API
var databaseAsync = new SQLiteAsyncConnection(connectionString); // Asynchronous API

If i try to use two connections on one database file like above, I getting out of sync problems, such as creating table using synchronous connection not makes table available through asynchronous connection. I assume there is some inner cache inside connection or something...

Question

How can I use one database file and two (sync and async) connections together properly?
And.. can I?

Additional info

  • Synchronous and asynchronous API will be fully covered by locks. So I expect no conflicts with using both API together.
picolino
  • 4,856
  • 1
  • 18
  • 31

1 Answers1

1

Multiple SQLite processes can have access to the database simultanously.

No need to worry about write access as whenever you are writing using one of your connections, the object will lock the database for a short time.

Your "out of sync" problem are probably coming from the Write Ahead Logging option being desactivated by default.

To activate it, just append the following segment to your connection string : 

;PRAGMA journal_mode=WAL;

This should allow concurrent read and write access while you slowly move to the new asynchronous API.

On top of that, please also enable the Shared Cache option to share the same data cache between your instances. Please activate it using the following command :

PRAGMA cache=shared
rak007
  • 973
  • 12
  • 26
  • How about in-memory databases? Every connection create different database instance with different handle pointer. – picolino Feb 04 '20 at 12:27
  • @picolino `Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases `Source :[:inmemory documentation](https://www.sqlite.org/inmemorydb.html). Just use the ':inmemory' flag then – rak007 Feb 04 '20 at 12:33