Connection Lifetime and Unit of Work

[JoshClose]

I'm doing a WinRT (Windows Store) app.

How long should a connection live? Should I create it, do my immediate work and release it? Or can create it in the app start up, and use the same connection everywhere in my app, for the lifetime of the app running?

[ghost]

Good question. I would like to know what others are doing about the lifetime of connections also.

I have done some performance tests for keeping the connection open and disposing the connection after each use. In the end I decided to dispose the connection after each use with the using block because the performance was not that different.

[JoshClose]

I think I'm more concerned about threading issues; multiple threads using the same connection. That brings up another point...

If there is a single connection, is that connection per thread or a singleton?

[ghost]

This article goes into detail about multi threading with SQLite: http://dev.yorhel.nl/doc/sqlaccess

From what I gather it's best not to share a connection between threads so doing the work then immediately closing would seem like the right way to go.

[nverinaud]

If you want to use a single connection (in order to have better performance by limiting the open/close stuff), you will need to be very careful with multi-thread access because SQLiteConnection is not Thread-Safe. Use locking mechanisms or create one connection per thread. I don't know if there is some thread local storage in .NET like in Cocoa.

You should also have a look at SQLiteAsync.cs which uses locking strategies for multi-threading.

[praeclarum]

Please use SQLiteAsyncConnection for easy background execution. SQLiteConnection is improving on its thread safeness, but it's not complete yet.