Xamarin Forms applications can use the sqlite-net-pcl NuGet package to incorporate data base operations in to shared code by referencing
the SQLite classes that ship in the NuGet. Database operations can be defined
in the Portable Class Library (PCL) project of the Xamarin.Forms solution, with
platform-specific projects returning a path to where the database will be stored. Create
a Xamarin forms application and add the sql-net-pcl nuget package version 1.2.1
to PCL and platform projects as below.
If you are using Windows Phone 8.1 project, install the extension SQLite for Windows
Phone 8.1 and Microsoft Visual C++ 2013 runtime package for windows phone in
addition.
Create a new interface ISQLite in PCL to abstract the platform specific functionality.
public interface ISQLite
{
SQLiteAsyncConnection GetConnection();
}
Class implementation as Dependency Service in UWP project as below.
[assembly: Xamarin.Forms.Dependency(typeof(SQLiteUWP))]
namespace XamarinFormsSqlite.UWP.DependencyServices
{
public class SQLiteUWP : ISQLite
{
public SQLiteAsyncConnection GetConnection()
{
var sqliteFilename = "SQLTest.db3";
string path = Path.Combine(ApplicationData.Current.LocalFolder.Path, sqliteFilename);
return new SQLiteAsyncConnection(path, storeDateTimeAsTicks: true);
}
}
}
Define the Database model as below.
public class UserProfile
{
[PrimaryKey, AutoIncrement]
public int UserId { get; set; }
public string UserName { get; set; }
public string Department { get; set; }
}
Since all the DB operations are asynchronous, we should implement the data base locking
with SemaphoreSlim class.
public class AsyncLock
{
#region fields
/// <summary>
/// The semaphore
/// </summary>
private readonly SemaphoreSlim mutexSemaphore;
/// <summary>
/// The releaser
/// </summary>
private readonly Task<Releaser> mutexTaskReleaser;
#endregion
#region constructor
/// <summary>
/// Initializes a new instance of the <see cref="AsyncLock"/> class.
/// </summary>
public AsyncLock()
{
this.mutexSemaphore = new SemaphoreSlim(1);
this.mutexTaskReleaser = Task.FromResult(new Releaser(this));
}
#endregion
#region methods
/// <summary>
/// Locks the asynchronous.
/// </summary>
/// <returns>The lock</returns>
public Task<Releaser> LockAsync()
{
var wait = this.mutexSemaphore.WaitAsync();
return wait.IsCompleted ?
this.mutexTaskReleaser : wait.ContinueWith((_, state) => new Releaser((AsyncLock)state), this, CancellationToken.None, TaskContinuationOptions.ExecuteSynchronously, TaskScheduler.Default);
}
#endregion
#region structure
/// <summary>
/// Structure Releaser
/// </summary>
/// <seealso cref="System.IDisposable" />
public struct Releaser : IDisposable
{
#region fields
/// <summary>
/// The m to release.
/// </summary>
private readonly AsyncLock mutexToRelease;
#endregion
#region methods
/// <summary>
/// Initializes a new instance of the <see cref="Releaser"/> struct.
/// </summary>
/// <param name="toRelease">To release.</param>
internal Releaser(AsyncLock toRelease)
{
this.mutexToRelease = toRelease;
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting
unmanaged resources.
/// </summary>
public void Dispose()
{
if (this.mutexToRelease != null)
{
this.mutexToRelease.mutexSemaphore.Release();
}
}
#endregion
}
#endregion
}
Create a SQLDataAccess class to define all data base operations as below.
public static class SQLiteDataAccess
{
public const int LASTDATABASEVERSION = 1;
private static readonly AsyncLock Locker = new AsyncLock();
private static SQLiteAsyncConnection DataBase;
public static void CreateTables()
{
var service = DependencyService.Get<ISQLite>();
if (service != null)
{
DataBase = service.GetConnection();
UpgradeDatabaseIfNecessary();
}
}
public static async Task<UserProfile> GetUserProfileById(int id)
{
using (await Locker.LockAsync())
{
return await DataBase.Table<UserProfile>().Where(x => x.UserId.Equals(id)).FirstOrDefaultAsync();
}
}
public static async Task InsertAsync(object entity)
{
using (await Locker.LockAsync())
{
await DataBase.InsertAsync(entity);
}
}
public static async Task InsertAllAsync(IEnumerable items)
{
using (await Locker.LockAsync())
{
await DataBase.InsertAllAsync(items);
}
}
public static async Task UpdateAllAsync(IEnumerable items)
{
using (await Locker.LockAsync())
{
await DataBase.UpdateAllAsync(items);
}
}
public static async Task DeleteAsync(object entity)
{
using (await Locker.LockAsync())
{
await DataBase.DeleteAsync(entity);
}
}
private static async void UpgradeDatabaseIfNecessary()
{
int currentDbVersion = await GetDatabaseVersion();
await DataBase.ExecuteAsync("PRAGMA auto_vacuum = FULL");
if (currentDbVersion < LASTDATABASEVERSION)
{
////we have to ignore the current database updates, so start from the next
int startUpgradingFrom = currentDbVersion + 1;
switch (startUpgradingFrom)
{
case 1: ////starting from
await DataBase.CreateTableAsync<UserProfile>().ConfigureAwait(false);
await DataBase.ExecuteAsync("PRAGMA foreign_keys = ON");
break;
}
SetDatabaseToVersion(LASTDATABASEVERSION);
}
}
private static async Task<int> GetDatabaseVersion()
{
return await DataBase.ExecuteScalarAsync<int>("PRAGMA user_version");
}
private static async void SetDatabaseToVersion(int version)
{
await DataBase.ExecuteAsync("PRAGMA user_version = " + version);
}
}
From above, CreateTables() is a method to create all data base tables.
UpgradeDatabaseIfNecessary() is a method to make data base table changes (CRUD) whenever upgrading to new app
version.
In this, define a constant with current db version, increment this value whenever
releasing a new app version where you have few db schema changes.
Get/Set the DB version with this constant value and make the appropriate DB changes
as implemented in UpgradeDatabaseIfNecessary() method. Example to call SQLiteDataAccess
functions as below.
var userProfile = new UserProfile();
userProfile.UserName = txtUserName.Text;
userProfile.Department = txtDepartment.Text;
await SQLiteDataAccess.InsertAsync(userProfile);
var dbUserProfile = await SQLiteDataAccess.GetUserProfileById(userProfile.UserId);
lblUserName.Text = dbUserProfile.UserName;
lblDepartment.Text = dbUserProfile.Department;
Download the working sample.