A sqlite example (vNext portable)

I thought I'd try some sqlite-net - especially following on from the discussion on the Xamarin forum, and from ConceptDev's link to his portable port of Tasky - https://github.com/conceptdev/xamarin-samples

My initial attempt was to try to remove the #if statements from sqlite-net by using inheritance - see https://gist.github.com/3857333

However.... Craig (ConceptDev) pointed out to me that heading this way would mean that it would be harder to keep up to date with changes, fixes, etc in sqlite-net

So instead of inheriting out of sqlite-net, I adapted sqlite-net so it used an interface:

    public interface ISQLiteConnection : IDisposable
    {
        string DatabasePath { get; }
        bool TimeExecution { get; set; }
        bool Trace { get; set; }
        int CreateTable<T>();
        ISQLiteCommand CreateCommand(string cmdText, params object[] ps);
        int Execute(string query, params object[] args);
        List<T> Query<T>(string query, params object[] args) where T : new();
        IEnumerable<T> DeferredQuery<T>(string query, params object[] args) where T : new();
        List<object> Query(ITableMapping map, string query, params object[] args);
        IEnumerable<object> DeferredQuery(ITableMapping map, string query, params object[] args);
        ITableQuery<T> Table<T>() where T : new();
        T Get<T>(object pk) where T : new();
        bool IsInTransaction { get; }
        void BeginTransaction();
        void Rollback();
        void Commit();
        void RunInTransaction(Action action);
        int InsertAll(System.Collections.IEnumerable objects);
        int Insert(object obj);
        int Insert(object obj, Type objType);
        int Insert(object obj, string extra);
        int Insert(object obj, string extra, Type objType);
        int Update(object obj);
        int Update(object obj, Type objType);
        int Delete<T>(T obj);
        void Close();
    }

This then made the changes to Sqlite-net really simple - just maybe 4 or 5 classes to add inheritance to, and 4 or 5 methods to add `where T : new()` constraints too.

-

The result is this portable plugin: vnext/Cirrious/Plugins/Sqlite
With a MonoDroid implementation in vnext/Cirrious/Plugins/Sqlite/Cirrious.MvvmCross.Plugins.Sqlite.Droid/SQLiteNet.cs

Note that I've only done the Droid implementation of this plugin so far, but I think all of the rest - WP7, MonoTouch, and WinRT (and beyond) should be straight-forward - and that the same SQLiteNet.cs file (#if's and all) will be link-shared between those plugins.

-

As a first example of use I created a 'simple' style project
- SimpleDroidSql.Core
- SimpleDroidSql

This simple example isn't a good architecture (all the logic is in the ViewModel), but it does make it easy(ish) to see the SQLite connection at work - e.g. you can see how it is created in the ViewModel :

        public ListViewModel()
        {
            Cirrious.MvvmCross.Plugins.Sqlite.PluginLoader.Instance.EnsureLoaded();
            var factory = this.GetService<ISQLiteConnectionFactory>();
            var connection = factory.Create("SimpleList");
            connection.CreateTable<ListItem>();
            _items = new DatabaseBackedObservableCollection<ListItem, int>(connection, listItem => -listItem.Id);
        }
And you can see how it is queried in my DatabaseBackedObservableCollection<T> :

        public void Add(T item)
        {
            _connection.Insert(item);
            RaiseCollectionChanged();
        }

        public int Count { get { return _connection.Table<T>().Count(); }}

        public bool Remove(T item)
        {
            var result = _connection.Delete(item);
            return true;
        }

        public T this[int index]
        {
            get { return _connection.Table<T>().OrderBy(_sortOrder).Skip(index).FirstOrDefault(); }
            set { throw new NotImplementedException(); }
        }
Note - DatabaseBackedObservableCollection<T> is definitely not optimal code -but it works well for a demo :)

And the MonoDroid specific code.... it is, of course, just XML with bindings:

[Activity(Label = "SimpleDroidSql", MainLauncher = true, Icon = "@drawable/icon")]
public sealed class MainActivity : MvxSimpleBindingActivity<ListViewModel>
{
   public MainActivity()  
   {
   }

   protected override void OnCreate(Bundle bundle)  
   {
      base.OnCreate(bundle);
      if (ViewModel == null)
        ViewModel = new ListViewModel();

      // Set our view from the "main" layout resource         
      SetContentView(Resource.Layout.Main);
   }
}

Loading an xml list binding of:

    local:MvxBind="{'ItemsSource':{'Path':'Items'}}"  
And list item templates that include:

 local:MvxBind="{'Text':{'Path':'Name'}}"

 local:MvxBind="{'Text':{'Path':'WhenCreated'}}"

 

Really glad this conversation happened - adding a proper SQLite layer has been on the TODO list for MvvmCross for a long time.

I'll update this further with the other platforms at some point soon - and I may also add Vici plugins at some point too - I've used their sqlite wrapper before - it worked really well - http://viciproject.com/wiki/projects/coolstorage/home