Check table exists in SQLite (in a UWP app)

July 30, 2016

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

https://blogs.windows.com/buildingapps/2016/05/03/data-access-in-universal-windows-platform-uwp-apps/

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.