SQLite has become one of the most pervasive embedded databases around. It's built into Android, iOS and OSX and is a part of many applications. It has also become the recommended client side database for WinRT applications
The go-to solution for using SQLite in .net is
sqlite.net. It’s a simple ORM that comes
as one (or two if you want async support) source files. You can get the full source from github or just
the main files from Nuget.
Sqlite.net lets you store nearly any object in the database without needing to descend from a specific type. However to make it work well, you need to decorate your objects with data attributes denoting primary keys, indexes and so on.
For example:
public class Valuation
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
[Indexed]
public int StockId { get; set; }
[Indexed]
public DateTime Time { get; set; }
public decimal Price { get; set; }
}
On startup, you register your class with SQLite as follows:
var myConnection =
new SQLiteConnection ("Stocks.db");
myConnection.CreateTable<Valuation> ();
This will check to see if the table exists in the database
and create or update it if required.
You can then start using the database in your code.
var valuations = myConnection.Table<Valuation>().Where(...);
However for my use case (cross platform application with
data objects defined in a portable class library and used in WPF, WinRT and MVC
applications), the attribute approach didn’t work. The nice thing about open source though is
that you can always change things…
The latest github version of sqlite.net now has an optional
argument for CreateTable that allows defining indexes by convention.
Acceptable values are:
[Flags]
public enum CreateFlags
{
None = 0,
ImplicitPK = 1, //
create a primary key for field called 'Id'
ImplicitIndex = 2, // create an index for fields ending in 'Id'
AllImplicit = 3, // do
both above
AutoIncPK = 4 // force
PK field to be auto inc
}
So to define and register a class, we can now use something
like the following:
public class Valuation
{
public int Id { get; set; }
public int StockId { get; set; }
public DateTime Time { get; set; }
public decimal Price { get; set; }
}
And then
myConnection.CreateTable<Valuation>(CreateFlags.AllImplicit | CreateFlags.AutoIncPK);
This will create the table, make Id into an auto incrementing primary key and add an index for StockId. To explicitly add additional indexes, use the CreateIndex
methods. E.g.
myConnection.CreateIndex<Valuation>(v => v.Time);
The main advantage of this approach is that it lets you separate storage details of the object from it's definition. This is great for cases when you don't want to, or can't, use the sqlite attributes in the main class.
The main disadvantages are that you have now separated storage from definition, and currently there is no way to set the field size.
