What's the best way to manage SQLite connections in Windows from a .NET application?

Question

I'm using SQLite in Windows from a .NET app. The .NET app is using the Open Source System.Data.SQLite library.

In my app, there are many threads accessing the same database. Is it best to:

1. Create and destroy connections as needed (what's the maximum number I can have?)
2. Create a long lived connection and use it from different threads (is this safe?)
3. Something else?

I initially went with choice# 2 but got random/intermittent errors relating to transactions when accessing the connection from other threads. I've since moved to choice# 1 and there error has gone.

Answer 1

According to the SQLite FAQ, the latest version of SQLite does allow multiple threads to use the same connection, but only one of them can have a transaction at a time.

I'm not aware of any built-in limit on the number of connections that may be active at a time.

I recommend setting up a "database thread" that owns the connection, and performs tasks on behalf of the other threads. This is what I'm doing in my .NET 4 SQLite process, and it works quite well.

P.S. If you're interested, I recently wrote a blog post about using SQLite via Entity Framework 4.