Category Archives: SQLite

Creating Complex Tables using SQLite.Net

Based on this earlier post, I returned to my test project to find that it didn’t actually pass anymore. The first issue was that the test couldn’t locate Sqllite3.dll.

Thanks to this question on every programmers favourite web site, I realised that I needed to add an extension for SQLite.

The next issue was when I tried to add a test to create a complex table:

complex1

Don’t know about [EntityName].

What I mean by a complex table, and the thing that CreateTable doesn’t like, is an entity that references another entity (the same is true if you try to reference the same entity).

Finally, you’re not allowed to use the System.Object type:

Complex2

A fix

It is my understanding that, if you can get it to work with SQLite, Entity Framework will solve this problem for you. I took more of a roll-your-own approach. The key issue here is that you want SQLite to not try to serialise the second object; and for this, you can use the [Ignore] attribute:

private ProductCategory _category;
 
[Ignore]
public ProductCategory Category
{
    get { return _category; }
    set
    {
        _category = value;
        RaisePropertyChanged();
    }
}

So, that stops it crashing. The next part is largely dependent on your data: if you have a basic primary / foreign key one-to-n mapping then this will work; however, anything more complex, and you’ll probably have to write a custom abstraction for the data. All that said, my solution starts with a base entity class:

public abstract class BaseDataEntity : INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged;
 
    protected void RaisePropertyChanged([CallerMemberName] string propertyName = null)
    {
        var handler = PropertyChanged;
        if (handler != null)
            handler(this, new PropertyChangedEventArgs(propertyName));
    }
 
    public abstract string Key { get; set; }
}

The PropertyChanged goes without saying (it’s like the roads), but the interesting part is the Key. Based on the code above, each entity needs a uniquely identifiable key; in this case, it’s a string; however, this can be any primitive type. In the derived class, you’ll need to map the type to the data; for example:

 
[Ignore]
public ProductCategory Category
{
    get { return _category; }
    set
    {
        _category = value;
        RaisePropertyChanged();
    }
}
 
public string CategoryRef
{
    get { return Category.Key; }
    set
    {
        if (Category == null)
        {
            Category = new ProductCategory()
            {
                Key = value
            };
        }
    }
}        

I’ll explain CategoryRef later on.

The next thing is to put some logic into the data access (I simply created a DataAccessBase class to sit on top of the SQLite data manipulation. Here is the abstraction to add a record:

 
public void Add(Type objType, object t)
{
    _connection.Insert(t);
 
    // Look for any ignored properties
    var ignored = objType                
        .GetProperties().Where(p =>
            p.CustomAttributes.Any(a => a.AttributeType == typeof(SQLite.Net.Attributes.IgnoreAttribute)));
 
    // For each, look for a reference
    foreach (var p in ignored)
    {
        // Determine the type first and get the value
        Type propertyType = p.PropertyType;
        var propertyValue = p.GetValue(t);
 
        // Recursively call this function to add the reference
        Add(propertyType, propertyValue);
    }
}

As you can see, for each reference, it simply recursively calls itself to add that, too. This will spectacularly fall down if you have a circular reference (so don’t do that, or don’t use this if you do).

Next is the Get:

 

public T Get<T>(string key) where T : class
{
    T data = _connection.Get<T>(key);
 
    var ignored = typeof(T)
        .GetProperties()
        .Where(p => p.GetCustomAttributes(typeof(SQLite.Net.Attributes.IgnoreAttribute), inherit: true).Any())
        .ToList();
 
    // For each, look for a reference
    foreach (var p in ignored)
    {
        // Get the reference property
        string propName = $"{p.Name}Ref";
        PropertyInfo refProp = typeof(T).GetProperty(propName);
 
        // Get its value (we know it's a string)
        string val = refProp.GetValue(data).ToString();
 
        // Determine the type first and get the value
        Type propertyType = p.PropertyType;
        
        var mapping = _connection.TableMappings.First(m => m.MappedType == propertyType);
        var rec = _connection.Find(val, mapping);
 
        // Update the property
        p.SetValue(data, rec);
 
    }
}

Here, I’m using a convention based approach; so, where I reference a separate class, I also have separate property called [Class]Ref; which you can see earlier on in the example of the entity code.

Conclusion

And that’s it; I make no assertions about edge cases, but for basic data that references other basic data, this works fine.

Acknowledgements

I have a great deal of help in creating this, but mainly just from random search results; however, I did ask a question on Stack Overflow

Check table exists in SQLite (in a UWP app)

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.