Using sqlite.net with POCO classes

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.

Sample applications demonstrating both approaches can be found on github.  The sqlite.dll binaries can be downloaded from here.