I’ve recently started investigating the use of SQLite for the purpose of storing data in my UWP application. Before I start, the following are the best tutorials that I could find on the net to get started with SQLite:
http://blogs.u2u.be/diederik/post/2015/09/08/Using-SQLite-on-the-Universal-Windows-Platform.aspx
The SQLite home page is here.
Finally, the nuget package to integrate SQLIte with your UWP app is here.
I didn’t find too much information on checking if a table exists, and I think it’s quite a key thing to be able to do, as you can’t really have a deployment script that runs when you deploy your UWP app. As a result I create a base data access class. Let’s start with the interface:
public interface IDataAccess { void Connect(); bool DoesTableExist<T>() where T : class; bool RemoveTable<T>() where T : class; bool CreateTable<T>() where T : class; }
Next, I created a unit test to make sure that the method will work:
[TestClass] public class TestBaseMethods { [DataTestMethod] [DataRow("MyApp.DataAccess.SQLLiteDataAccess, MyApp, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null")] public void TablesCreate(string dataAccess) { // Arrange IDataAccess bda = Activator.CreateInstance(Type.GetType(dataAccess)) as IDataAccess; bda.Connect(); Assert.AreEqual(false, bda.DoesTableExist<TestEntity>()); // Act bda.CreateTable<TestEntity>(); // Assert Assert.AreEqual(true, bda.DoesTableExist<TestEntity>()); // Clean-up bda.RemoveTable<TestEntity>(); Assert.AreEqual(false, bda.DoesTableExist<TestEntity>()); }
If you want more information about DataRow, then see my recent post
Implementation
We’ll start with a basic class that provides a Connect method and cleans up after itself:
namespace MyApp.DataAccess { public class SQLLiteDataAccess : IDisposable, IDataAccess { SQLiteConnection _connection; public void Connect() { _connection = new SQLiteConnection(new SQLitePlatformWinRT(), Path.Combine(ApplicationData.Current.LocalFolder.Path, "Storage.sqlite")); } public void Dispose() { _connection.Dispose(); }
Then we need to add the functionality from the interface:
/// <summary> /// Generic method to determine is an object type exists within the DB /// </summary> /// <param name="type"></param> /// <param name="name"></param> /// <returns></returns> private bool DoesTypeExist(string type, string name) { SQLiteCommand command = _connection.CreateCommand("SELECT COUNT(1) FROM SQLITE_MASTER WHERE TYPE = @TYPE AND NAME = @NAME"); command.Bind("@TYPE", type); command.Bind("@NAME", name); int result = command.ExecuteScalar<int>(); return (result > 0); } /// <summary> /// Check whether a specific table exists in the database /// </summary> /// <typeparam name="T"></typeparam> /// <returns>true if the specified table exists</returns> public bool DoesTableExist<T>() where T : class { bool exists = DoesTypeExist("table", typeof(T).Name); return exists; } /// <summary> /// Create a new table in the DB /// </summary> /// <typeparam name="T">Object to base the table on</typeparam> /// <returns></returns> public bool CreateTable<T>() where T : class { bool exists = DoesTableExist<T>(); if (!exists) { int error = _connection.CreateTable<T>(); exists = (error == 0); } return exists; } /// <summary> /// Remove / drop the specified table from the DB /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public bool RemoveTable<T>() where T : class { bool exists = DoesTableExist<T>(); if (exists) { int error = _connection.DropTable<T>(); exists = (error != 0); } return exists; }
That’s it; I’m still not sure how I feel about SQLite. For a small, local storage, it feels like it could be an XML file; but I suppose it comes into its own when we have a potentially large local storage.