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.