Multithreading in opennetcf.orm (how to use SqlCeDataStore)

Question

I just started using the OpenNETCF.ORM framework, and I ran into a problem. What is the correct way to use SqlCeDataStore in a multithreaded application?

In a single-threaded application I would simply use a static field:

public class MyApp
{
    private static SqlCeDataStore _store;
    public static SqlCeDataStore Store
    {
        get {
            if (_store == null) {
                _store = new SqlCeDataStore("database.sdf");
                // other initialization stuff, DiscoverTypes() etc...
            }
            return _store;
        }
    }
}

And then I would use it like so:

var customers = MyApp.Store.Select<Customer>().ToArray();

After some research on SQL Server Compact, I found out that connections aren't thread safe, so each thread should have it's own connection. OpenNETCF.ORM does have an option to use a new connection each time you connect to the database. Should I just use that?

Another option would be to create a new SqlCeDataStore for each thread. Is that better?

What is the correct way?

Answer 1

We use SQL Compact in a variety of heavily multithread applications using the OpenNETCF ORM without any problems. We run these on full Windows and Windows CE.

We use the "Maintain Maintenance Connection" connection behavior, where a new connection is created for all CRUD calls, but a single-background one is kept for doing maintenance work (creating tables, etc). This gives good performance and a reasonable amount of thread safety.