Friday, September 20, 2013

Slides - Cross platform programming with .net, xamarin and MvvmCross

The slides from yesterdays presentation "Hot tuna - Cross platform development with .net, Xamarin and MvvmCross" are available from here.

Useful links from the presentation

MvvmCross
Stuart Lodge - MvvmCross Author

Twitter          
@slodge

Blog           
http://slodge.blogspot.co.uk/

Videos           
http://www.youtube.com/user/MrHollywoof?feature=watch
Video index   
http://mvvmcross.wordpress.com/

Repository        
https://github.com/slodge/MvvmCross

Xamarin
Website
http://www.xamarin.com/
Samples
https://github.com/xamarin
Mobile library
https://github.com/xamarin/Xamarin.Mobile 

Ninja Coder
Adrian Sudbury – Ninja Coder Author
Twitter       
@asudbury

Blog        
http://adriansudbury.blogspot.co.uk/

Ninja Coder    
http://visualstudiogallery.msdn.microsoft.com/618b51f0-6de8-4f85-95ce-a50c658c7767



Portable Class Libraries
http://www.hanselman.com/blog/CrossPlatformPortableClassLibrariesWithNETAreHappening.aspx

Wednesday, March 27, 2013

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.