22. February 2020

SQL-net with a must have extension

I was just adding SQL functionality to my app and was using Frank Krugers sql-net nuget for the first time. Yes I know.. I have managed to avoid apps with a database. Every app has used real-time API updates and data caching..

I’m going to show you what I learned creating a sql file database on a mobile divice.

The basics

Start by adding this attribute and Id to your entities for your primary keys

 [PrimaryKey, AutoIncrement]
 public int Id { get; set; }

And if you have a foreign key relationship you add this to your class

 [Index]
 public int StockId { get; set; }

That would then look like this

 public class Stock		
 {		
    [PrimaryKey, AutoIncrement]		
    public int Id { get; set; }
    
    public string Name {get;set;}
 }		
    		
 public class Valuation		
 {		
    [PrimaryKey, AutoIncrement]		
    public int Id { get; set; }		
    [Indexed]		
    public int StockId { get; set; }	
    
    public decimal Price { get; set; }
 }

Create your tables and get your connection

 var db = new SQLiteConnection("databaseFullPathString");		
 db.CreateTable<Stock>();		
 db.CreateTable<Valuation>();

Then you insert your data like this

 db.Insert(new Stock() {		
        Name = "Tomatos"		
    });

Insert does not return your inserted stockId!

Although the docs say the following

You can insert rows in the database using Insert. If the table contains an auto-incremented primary key, then the value for that key will be available to you after the insert

it doesn’t return the inserted id. The API explicitly says Insert returns “The number of rows added to the table.”

Inserting data for two connected tables

This was my first solution. And its rather barbaric for my taste. To many steps…

 var stock = new Stock() { Name = "Tomatos" };
 
 // first insert the stock object
 db.Insert(stock);
 
 // get the index of the object right after it was inserted
 var stockId = (int)SQLite3.LastInsertRowid(connection.Handle);
 
 // if you are using SQLiteAsyncConnection() you get the Handle like this
 // db.GetConnection().Handle
 
 // add the stockId to the Valuation object to connect them
 var valuation = new Valuation() {
         StockId = stockId;
         Price = 2.2;
     };
 
 // And then save the valuation object that now has the id to the stock one!
 db.Insert(valuation);

Getting the data back

And then you will have to write a none-strongly-typed query for the data like this.

 var valuation =  db.Query<Valuation> ("select * from Valuation where StockId = ?", stock.Id);

Since I am new to SQLite I assume you can do this some other way but the SQLite-net docs are not that great (sorry) and I got lost fast on the ones at SQLite.org.

So I did a quick googling and found this gem sqlite-net-extensions.

SQLite-net Extensions

By using this nuget package that works on-top of SQLite-net you can insert data like this

 Install-Package SQLiteNetExtensions -Version 2.1.0

Insert the data

 db.InsertWithChildren(tomatoStock);

and to get it back

 var storedValuation = db.GetWithChildren<Valuation>(valuation.Id);

 if(tomatoStock.Name.Equals(storedValuation.Stock.Name))
    Debug.WriteLine("Object relation is working");

But make this work you need to do the following

add this code to the Stock class

 [OneToMany(CascadeOperations = CascadeOperation.All)]     
 public List<Valuation> Valuations { get; set; }

add this code to the Valuation class

 [ForeignKey(typeof(Stock))]     
 public int StockId { get; set; }

 [ManyToOne]      
 public Stock Stock { get; set; }

The classes should look like this

 public class Stock
 {
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [MaxLength(8)]
    public string Name { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]  
    public List<Valuation> Valuations { get; set; }
}

 public class Valuation
 {
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [ForeignKey(typeof(Stock))]    
    public int StockId { get; set; }
   
    public decimal Price { get; set; }
    
    [ManyToOne]    
    public Stock Stock { get; set; }
 }

And then add the data like this

 var valuation = new Valuation {
    Price = 15
 };

 var tomatoStock = new Stock {
    Name = "Tomato",
    // or tomatoStock.Valuations.Add(valuation);
    Valuations = new List<Valuation> { valuation } 
 };

Much better right?

Async version

Use the async SQLiteAsyncConnection() instead of SQLiteConnection() and use the SQLiteNetExtensions.Async package to avoid locking up your UI

 Install-Package SQLiteNetExtensions.Async -Version 2.1.0

Add this using statement

 using SQLiteNetExtensionsAsync.Extensions;

and use this code

 var db = new SQLiteAsyncConnection("databaseFullPathString");

 await db.CreateTableAsync<Stock>();
 await db.CreateTableAsync<Valuations>();

 await db.InsertWithChildrenAsync(tomatoStock);

 // get the insert id
 int insertedId =  (int)SQLite3.LastInsertRowid(
 db.Connection.GetConnection().Handle)

 // add the id to your tomatoStock to use somewhere 
 // right way (no need to query again)
 tomatoStock.StockId = insertedId;

Relationship types

The extensions package offers 4 different relationships through attributes so choose what suits your need.

One-to-one
One-to-many
Many-to-one
Many-to-many

Singleton DbConnection

I just wanted to include in here also a singleton use of my MyDb. With it I can do the following where I implement e.g my GetStockById(int id) and SaveStock etc. methods

 public async Task<int> SaveStockAsync(Stock stock)
 {
    if(stock.Id !=0)
      MyDb.UpdateAsync(stock);
    else
      await MyDb.InsertWithChildrenAsync(stock);
 }

Here is the singleton code. I just can’t remember where I got the code from but its borrowed (like probably all code)

 public sealed class MyDb
 {
    public static MyDb Instance { get; } = new MyDb();
        
    public SQLiteAsyncConnection Connection => lazyInitializer.Value;

    private static readonly Lazy<SQLiteAsyncConnection> lazyInitializer 
    = new Lazy<SQLiteAsyncConnection>(() 
    => new SQLiteAsyncConnection(DatabasePath, Flags));

    private static bool initialized = false;

    static MyDb()
    {
    }

    private MyDb()
    {
        InitializeAsync().SafeFireAndForget(false);
    }

    public const SQLite.SQLiteOpenFlags Flags =
        // open the database in read/write mode
        SQLite.SQLiteOpenFlags.ReadWrite |
        // create the database if it doesn't exist
        SQLite.SQLiteOpenFlags.Create |
        // enable multi-threaded database access
        SQLite.SQLiteOpenFlags.SharedCache;

    public const string DatabaseFilename = "YourDbName.db";

    private static string DatabasePath
    {
        get
        {
            var basePath = Environment.GetFolderPath(
            Environment.SpecialFolder.LocalApplicationData);
            return Path.Combine(basePath, DatabaseFilename);
        }
    }

    private async Task InitializeAsync()
    {
        if (!initialized)
        {
            // Create the tables they haven't been already
            if (!Connection.TableMappings.Any(m => 
            (m.MappedType.Name == typeof(Stock).Name) 
            || (m.MappedType.Name == typeof(Valiation).Name)))
            {
                await Connection.CreateTablesAsync(CreateFlags.None,
                typeof(Stock), typeof(Valuation));
                initialized = true;
            }
        }
    }
 }

Then you need to add this extension class for the SafeFireAndForget() method.

 public static class TaskExtensions
 {
    // NOTE: Async void is intentional here. This provides a way
    // to call an async method from the constructor while
    // communicating intent to fire and forget, and allow
    // handling of exceptions
    public static async void SafeFireAndForget(this Task task,
        bool returnToCallingContext,
        Action<Exception> onException = null)
    {
        try
        {
            await task.ConfigureAwait(returnToCallingContext);
        }

        // if the provided action is not null, catch and
        // pass the thrown exception
        catch (Exception ex) when (onException != null)
        {
            onException(ex);
        }
    }
 }

You can look up other singletons variations in Jon Skeets blog post.