In our Android and iOS MVVMCross application, we experience random SQLiteException: busy.
Based on the code below, we have several repositories, each of which creates an instance below and its associated connection to the Sqlite database. Imagine that we have a stock repository and a value repository, two SqliteDataService instances will be created: SqliteDataService with type Stocks and SqliteDataService with types. Ratings, each of which has a connection to the Sqlite database.
Actions in the repositories can work in the background thread, which means that we can try to insert stocks into the database at the same time as the ratings.
Now that each repository creates its own SqliteDataService, a connectObject lock will protect the same types of repository from accessing the database at the same time, and not protect stocks and ratings from accessing the database at the same time.
My questions:
Is it right to create connections for each repository, and if so, how do we protect SqliteException: busy?
Is there a better sample? those. Should I create a non-generic SqliteDataService class that uses the same threading connection? We tried this, but on Android we have fatal exceptions.
Does anyone have a solid Sqlite DAL template for Xamarin MVVMCross?
public class SqliteDataService<T> : IDataService<T> where T : new() { private static object lockObject = new object(); private static object connectionObject = new object(); private static ISQLiteConnection _connection; private static SqliteDataService<T> _instance; public SqliteDataService(ISQLiteConnectionFactory connectionFactory, string dbPath) { if (_connection == null) { _connection = connectionFactory.Create (dbPath); _connection.CreateTable<T> (); } } public static SqliteDataService<T> GetInstance(ISQLiteConnectionFactory connectionFactory, string dbPath) { if (_instance == null) { lock (lockObject) { _instance = new SqliteDataService<T> (connectionFactory, dbPath); } } return _instance; } public void CreateTable<T> () { } public void Insert(T value) { lock (connectionObject) { _connection.Insert (value, typeof(T)); } } public void InsertAll(IEnumerable<T> values) { lock (connectionObject) { _connection.Insert (values, typeof(T)); } } public IEnumerable<T> Read(Expression<Func<T, bool>> predicate) { lock (connectionObject) { return _connection.Table<T> ().Where (predicate); } } public T ReadFirst(Expression<Func<T, bool>> predicate) { lock (connectionObject) { return Read (predicate).FirstOrDefault (); } } public void Update(T value) { lock (connectionObject) { _connection.Update (value, typeof(T)); } } public void Delete(Expression<Func<T, bool>> predicate) { lock (connectionObject) { var valuesToDelete = Read (predicate); if (valuesToDelete == null) return; foreach (var value in valuesToDelete) { _connection.Delete (value); } }