SQLite Locks In Xamarin.Forms

Introduction
In mobile projects, whenever we do a parallel or concurrent task and insert data into multiple tables at the same time, then we need to implement locks on SQLite code. The reason behind this is SQLite is a file based database and cannot handle multiple calls to write in it. In Xamarin Forms we are using multiple platforms as Android, iOS and Windows and for this we need to implement dependency injection to initialize the SQLite DB.
We will start by interface first
The interface will have two functions, called "GetAsyncConnection" and "GetConnection". We will implement this function in respective projects as shown below.

1. using SQLite.Net;  
2. using SQLite.Net.Async;  
3.   
4. namespace Sample.Mobile.Services.Common  
5. {  
6.     public interface ISQLite  
7.     {  
8.         SQLiteAsyncConnection GetAsyncConnection();  
9.   
10.         SQLiteConnection GetConnection();  
11.     }  
12. }   

The Android Project SQLite File
In this we will implement the above interface functions by using dependency injection. In this we will store the DB File in APP folder itself. Here we will have a AsyncLockConnection which will tell the DB that the request will come more than one at the same time, so be ready with Locks. This file will pertain to Android projects.

1. using System.IO;  
2. using Sample.Mobile.Services.Common;  
3. using SQLite.Net;  
4. using SQLite.Net.Async;  
5. using Xamarin.Forms;  
6. using Sample.Mobile.Droid.Helper;  
7.   
8. [assembly: Dependency(typeof(SqLiteAndriod))]  
9.   
10. namespace Sample.Mobile.Droid.Helper  
11. {  
12.     public class SqLiteAndriod : ISQLite  
13.     {  
14.         public SqLiteAndriod()  
15.         {  
16.   
17.         }  
18.   
19.         public SQLiteAsyncConnection GetAsyncConnection()  
20.         {  
21.             const string fileName = "BPMobile.db3";  
22.   
23.             var documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);  
24.             var path = Path.Combine(documentsPath, fileName);  
25.   
26.             var platform = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid();  
27.             var cwLock = new SQLiteConnectionWithLock(platform, new SQLiteConnectionString(path, true));  
28.             var connection = new SQLiteAsyncConnection(() => cwLock);  
29.   
30.             return connection;  
31.         }  
32.   
33.         public SQLiteConnection GetConnection()  
34.         {  
35.             const string fileName = "BPMobile.db3";  
36.   
37.             var documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);  
38.             var path = Path.Combine(documentsPath, fileName);  
39.   
40.             var platform = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid();  
41.             var connection = new SQLiteConnection(platform, path);  
42.   
43.             return connection;  
44.         }  
45.     }  
46. }  

The iOS Project SQLite File
In this we will implement the above interface functions by using dependency injection. In this we will store the DB File in APP folder itself. Here we will have a AsyncLockConnection which will tell the DB that the request will come more than one at same time, so be ready with Locks. This file will pertain to iOS projects

1. using Sample.Mobile.Services.Common;  
2. using System;  
3. using SQLite.Net;  
4. using System.IO;  
5. using Xamarin.Forms;  
6. using Sample.Mobile.iOS.Helper;  
7. using SQLite.Net.Async;  
8.   
9. [assembly: Dependency(typeof(SqLiteiOS))]  
10. namespace Sample.Mobile.iOS.Helper  
11. {  
12.     public class SqLiteiOS : ISQLite  
13.     {  
14.         public SqLiteiOS()  
15.         {  
16.   
17.         }  
18.   
19.         public SQLiteAsyncConnection GetAsyncConnection()  
20.         {  
21.             const string fileName = "BPMobile.db3";  
22.             var documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);  
23.             var path = Path.Combine(documentsPath, fileName);  
24.             var platform = new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS();  
25.             var cwLock = new SQLiteConnectionWithLock(platform, new SQLiteConnectionString(path, true));  
26.             var connection = new SQLiteAsyncConnection(() => cwLock);  
27.             return connection;  
28.         }  
29.   
30.         public SQLiteConnection GetConnection()  
31.         {  
32.             const string fileName = "BPMobile.db3";  
33.             var documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);  
34.             var path = Path.Combine(documentsPath, fileName);  
35.             var platform = new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS();  
36.             var connection = new SQLiteConnection(platform, path);  
37.             return connection;  
38.         }  
39.     }  
40. }  

The Base Operations File
In this we’ll have a common function to insert a single entity or multiple entity. This file will be in your repository or services. In this we have two functions for inserting a single entity and multiple entity (List<entity>).

1. using System.Collections.Generic;  
2. using System.Threading.Tasks;  
3. using Sample.Mobile.Services.Async;  
4. using Sample.Mobile.Services.Common;  
5. using SQLite.Net;  
6. using SQLite.Net.Async;  
7. using Xamarin.Forms;  
8.   
9. namespace Sample.Mobile.Services.TableOperations  
10. {  
11.     public class BaseOperations  
12.     {  
13.         public SQLiteAsyncConnection Connection;  
14.         private static readonly AsyncLock AsyncLock = new AsyncLock();  
15.   
16.         public BaseOperations()  
17.         {  
18.             Connection = DependencyService.Get<ISQLite>().GetAsyncConnection();  
19.         }  
20.   
21.         public async Task<int> InsertAsync<T>(T entity)  
22.         {  
23.             try  
24.             {  
25.                 using (await AsyncLock.LockAsync())  
26.                 {  
27.                     if (entity != null) await Connection.InsertAsync(entity);  
28.                     return 1;  
29.                 }  
30.             }  
31.             catch (SQLiteException sqliteException)  
32.             {  
33.                 if (sqliteException.Result == SQLite.Net.Interop.Result.Busy ||  
34.                     sqliteException.Result == SQLite.Net.Interop.Result.Constraint)  
35.                 {  
36.                     return await InsertAsync(entity);  
37.                 }  
38.                 throw;  
39.             }  
40.   
41.         }  
42.   
43.         public async Task<int> InsertAllAsync<T>(List<T> entityList)  
44.         {  
45.             try  
46.             {  
47.                 using (await AsyncLock.LockAsync())  
48.                 {  
49.                     if (Connection != null) await Connection.InsertAllAsync(entityList);  
50.                     return 1;  
51.                 }  
52.             }  
53.             catch (SQLiteException sqliteException)  
54.             {  
55.                 if (sqliteException.Result == SQLite.Net.Interop.Result.Busy ||  
56.                     sqliteException.Result == SQLite.Net.Interop.Result.Constraint)  
57.                 {  
58.                     return await InsertAsync(entityList);  
59.                 }  
60.                 throw;  
61.             }  
62.         }  
63.     }  
64. }  

The AsyncLock File

This file will be in your Repository or Services.

1. using System;  
2. using System.Collections.Generic;  
3. using System.Linq;  
4. using System.Text;  
5. using System.Threading;  
6. using System.Threading.Tasks;  
7.   
8. namespace Sample.Mobile.Services.Async  
9. {  
10.     public class AsyncLock  
11.     {  
12.         private readonly AsyncSemaphore m_semaphore;  
13.         private readonly Task<Releaser> m_releaser;  
14.   
15.         public AsyncLock()  
16.         {  
17.             m_semaphore = new AsyncSemaphore(1);  
18.             m_releaser = Task.FromResult(new Releaser(this));  
19.         }  
20.   
21.         public Task<Releaser> LockAsync()  
22.         {  
23.             var wait = m_semaphore.WaitAsync();  
24.             return wait.IsCompleted ?  
25.                 m_releaser :  
26.                 wait.ContinueWith((_, state) => new Releaser((AsyncLock)state),  
27.                     this, CancellationToken.None,  
28.                     TaskContinuationOptions.ExecuteSynchronously, TaskScheduler.Default);  
29.         }  
30.   
31.         public struct Releaser : IDisposable  
32.         {  
33.             private readonly AsyncLock m_toRelease;  
34.   
35.             internal Releaser(AsyncLock toRelease) { m_toRelease = toRelease; }  
36.   
37.             public void Dispose()  
38.             {  
39.                 if (m_toRelease != null)  
40.                     m_toRelease.m_semaphore.Release();  
41.             }  
42.         }  
43.     }  
44. }  

The AsyncSemaPhore File

This file will be in your Repository or Services.

1. using System;  
2. using System.Collections.Generic;  
3. using System.Linq;  
4. using System.Text;  
5. using System.Threading.Tasks;  
6.   
7. namespace Sample.Mobile.Services.Async  
8. {  
9.     public class AsyncSemaphore  
10.     {  
11.         private readonly static Task s_completed = Task.FromResult(true);  
12.         private readonly Queue<TaskCompletionSource<bool>> m_waiters = new Queue<TaskCompletionSource<bool>>();  
13.         private int m_currentCount;  
14.   
15.         public AsyncSemaphore(int initialCount)  
16.         {  
17.             if (initialCount < 0) throw new ArgumentOutOfRangeException("initialCount");  
18.             m_currentCount = initialCount;  
19.         }  
20.   
21.         /// <summary>  
22.         /// To insert DB Lock  
23.         /// </summary>  
24.         /// <returns></returns>  
25.         public Task WaitAsync()  
26.         {  
27.             lock (m_waiters)  
28.             {  
29.                 if (m_currentCount > 0)  
30.                 {  
31.                     --m_currentCount;  
32.                     return s_completed;  
33.                 }  
34.                 else  
35.                 {  
36.                     var waiter = new TaskCompletionSource<bool>();  
37.                     m_waiters.Enqueue(waiter);  
38.                     return waiter.Task;  
39.                 }  
40.             }  
41.         }  
42.   
43.         /// <summary>  
44.         /// To Release DB Lock  
45.         /// </summary>  
46.         public void Release()  
47.         {  
48.             TaskCompletionSource<bool> toRelease = null;  
49.             lock (m_waiters)  
50.             {  
51.                 if (m_waiters.Count > 0)  
52.                     toRelease = m_waiters.Dequeue();  
53.                 else  
54.                     ++m_currentCount;  
55.             }  
56.             if (toRelease != null)  
57.                 toRelease.SetResult(true);  
58.         }  
59.     }  
60. }  

Finally, to call this functionality, we will create an object of Base operation file and call InsertAsync or InsertAllAsync function.