Wednesday, April 30, 2014

Setting up Sqlite on iOS, Android and Windows Phone using Xamarin

This post is about setting up SQLite.NET-PCL to achieve cross-platform usage of the database code. SQLite is a very competent little database that we access using SQLite.NET that provides you with a small OR-mapper. No more sql for you... Unless you want to of course.

This is NOT an article about how to use SQLite.

We'll take a few architectural short cuts to make the core points clearer. We'll not use any IoC or MVVM patterns. This is not a tutorial, it simply highlights the important points, gives some examples and shows of the bare bone set up.

Short version - Use the SQLite.NET PCL and it's helper packages (one for each platform) to setup and use SQLite.

You can download the solution here

Prequisits

I'm using Visual Studio 2013 with Xamarins great stuff on Windows 8 and Xamarin Studio on Mac. You need to know about Nuget.

Step 1 - Set up your projects

I started with four projects:
  • A Portable Library core called Demo.Core (profile 78)
  • A windows phone project called Demo.WP
  • A iOS project called Demo.iOS
  • An Android project called Demo.Droid (don't name it Android since it will cause namespace issues for you).

Step 2 - "Install" Sqlite

Sqlite is already installed on Android and iOS. All you need there is a wrapper called Sqlite.net. On Windows Phone you need to install the underlying Sqlite database as well.

Each platform has a different nuget package that needs to be added to each solution. The core will only reference the sqlite.net-pcl package whilst each platform will need some platform specific bits. This collection of nuget packages makes this very easy.

Core

Add the SQLite.Net PCL library from nuget (sqlite.net-pcl).

iOS

Add the SQLite.NET PCL library for iOS (SQLite.Net.Platform.XamarinIOS)

Android

Add the SQLite.NET PCL library for Android (SQLite.Net.Platform.XamarinAndroid)

Windows Phone

Add the SQLite.NET PCL library for Windows Phone 8 (SQLite.Net.Platform.WindowsPhone8)

Step 3 - Define the entities

Keeping it simple, we only create one entity called Car in the core-project. Depending on requirements you could put the entity in another assembly, but this is good for now.

using System;
using SQLite.Net.Attributes;

namespace Demo.Core
{
    public class Car
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        public string Brand { get; set; }
        public int Milage { get; set; }
        public int NumberOfWheels { get; set; }
    }
}


Step 4 - Create the repository

The repository is going to wrap our data access to keep it isolated from the rest of the application. In a real world you would add an ICarRepository interface to enable testing and Inversion of Control.

using System;
using System.Collections.Generic;
using System.Linq;
using SQLite.Net;

namespace Demo.Core
{
    public class CarRepository
    {
        private SQLiteConnection _connection;

        public CarRepository(SQLiteConnection connection)
        {
            _connection = connection;
            _connection.CreateTable<Car>();
        }

        public void Create(Car car)
        {
            _connection.Insert(car);
            _connection.Commit();
        }

        public void Delete(Car car)
        {
            _connection.Delete(car);
            _connection.Commit();
        }

        public Car Get(int carId)
        {
            var query = from c in _connection.Table<Car>()
                        where c.Id == carId
                        select c;

            return query.FirstOrDefault ();
        }

        public List<Car> GetAll()
        {
            var query = from c in _connection.Table<Car>()
                        select c;
            return query.ToList();
        }

        public void Update(Car car)
        {
            _connection.Update(car);
            _connection.Commit();
        }
    }
}

The repository takes a SQLiteConnection in its constructor. This is the key part of getting your code platform independent. The core does not know about your clients. It only cares about a database connection to use when communicating with SQLite.

We use standard linq to interact with the database. You can also drop down to SQL-level if you're more comfortable with that.

One more point is that we need to create the table at first run. Otherwise there will be nothing to interact with. The _connection.CreateTable(); does that for you. It will check each time you create a repository to make sure that we have that table. If it already exists, nothing will happen. So don't worry that it will destroy your table.

Step 5 - Consume the stuff in the clients

Each of the platforms provide a specific connection to the repository in the core. The demo application is bare bone. We supply a button that adds another car to the repository. We then display the total car count for the user. It's pretty much just the new project template, added a label and the platform specific SQLite code to initialize.

Each project must reference the Core-project as well.

iOS

This is a view controller that holds on to a connection. The connection is created in ViewDidLoad() by passing the platform specific SQLitePlatformIOS() object to the constructor of SQLiteConnection. We also need to pass the path to the database.

using System;
using System.Collections.Generic;
using System.Drawing;
using Demo.Core;
using MonoTouch.Foundation;
using MonoTouch.UIKit;
using SQLite.Net;
using SQLite.Net.Platform.XamarinIOS;

namespace Demo.iOS
{
    public partial class Demo_iOSViewController : UIViewController
    {
        private SQLiteConnection _connection;

        public Demo_iOSViewController () : base ("Demo_iOSViewController", null)
        {
        }
            
        public override void ViewDidLoad ()
        {
            base.ViewDidLoad ();
            
            // Setup the database connection
            _connection = new SQLiteConnection (new SQLitePlatformIOS(), "test.sqlite");

            // Wire events
            AddCarButton.TouchUpInside += HandleTouchUpInside;

            // Update UI
            UpdateCarCount ();
        }

        void HandleTouchUpInside (object sender, EventArgs e)
        {
            var repository = new CarRepository (_connection);

            var c = new Car ();
            repository.Create (c);

            UpdateCarCount ();
        }

        void UpdateCarCount ()
        {
            var repository = new CarRepository (_connection);

            var count = repository.GetAll ().Count;
            CarCountLabel.Text = count.ToString();
        }
    }
}

That's it... It's not pretty but it's only supposed to get you started.

Android

We follow the same pattern on android by creating a connection in OnCreate. We then hook up events and

using System;
using System.IO;
using System.Linq;
using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using Demo.Core;
using SQLite.Net;
using SQLite.Net.Platform.XamarinAndroid;

namespace Demo.Droid
{
    [Activity (Label = "Demo.Droid", MainLauncher = true)]
    public class MainActivity : Activity
    {
        private SQLiteConnection _connection;

        protected override void OnCreate (Bundle bundle)
        {
            base.OnCreate (bundle);

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

            // Setup the database connection
            var path = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "test.sqlite");
            _connection = new SQLiteConnection (new SQLitePlatformAndroid(), path);

            // Wire up events
            Button button = FindViewById<Button> (Resource.Id.myButton);
            button.Click += AddCarButton_Click;

            // Update display
            UpdateCarCount ();
        }

        private void AddCarButton_Click(object sender, System.EventArgs e)
        {
            var repository = new CarRepository (_connection);

            var c = new Car ();
            repository.Create (c);

            UpdateCarCount ();
        }

        private void UpdateCarCount()
        {
            var repository = new CarRepository (_connection);

            var count = repository.GetAll ().Count;
            var textview = FindViewById<TextView> (Resource.Id.countTv);
            textview.Text = count.ToString ();
        }
    }
}


Android is a little more fancy pants when it comes to the location of the database file. But other than that the code is pretty much the same, except that we pass in a SQLitePlatformAndroid() object instead of the iOS version. Starting to see a pattern here?

Windows Phone

WP doesn't come with SQLite installed. This is not rocket science anymore however.

1. In Visual Studio, go to Tools -> Extensions and update and search and install SQLite for Windows Phone. When found, install.

2. Add a reference to the SQLite for Windows Phone extension under your Windows Phone projects references.


When the underlying SQLite database is installed, it's almost the same code again. We added a label and a button to the page created by the default project template.

The code for the page would look something like this.

using Demo.Core;
using Microsoft.Phone.Controls;
using SQLite.Net;
using SQLite.Net.Platform.WindowsPhone8;
using System.IO;
using System.Windows;
using Windows.Storage;

namespace Demo.WP
{
    public partial class MainPage : PhoneApplicationPage
    {
        private SQLiteConnection _connection;


        public MainPage()
        {
            InitializeComponent();

            // Setup database
            var path = Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "test.sqlite"));
            _connection = new SQLiteConnection(new SQLitePlatformWP8(), path);

            // Wire events
            AddCarButton.Click += AddCarButton_Click;
        }

        void AddCarButton_Click(object sender, RoutedEventArgs e)
        {
            var repository = new CarRepository(_connection);

            var c = new Car();
            repository.Create(c);

            UpdateCarCount();
        }

        void UpdateCarCount()
        {
            var repository = new CarRepository(_connection);
            var count = repository.GetAll().Count;
            CarCountLabel.Text = count.ToString();
        }
    }
}

And it's all the same code again.

Summary

We've created a core PCL containing a repository and a car entity. The entity should perhaps be moved into an assembly of its own depending on business rules. In case you need to share entities with backend or something like that.

We then created separate projects for three different platforms, initializing the SQLite database and references the repository in the core PCL.

Other highlights

There is also a asynchronous support. Check out the project site for examples.

As you might have noticed, even the client code kinda looks identical. This should get you thinking about moving that code to a common layer instead. And that's probably what I'm going to do in the next article by using MVVM Cross.

Resources

The project site


6 comments:

  1. Really useful thanks. I am trying to get this to work on my own project. I have followed your steps to add the correct references/packages. I get a runtime error when creating a new database:

    An exception of type 'System.TypeLoadException' occurred in SQLite.Net.DLL but was not handled in user code

    Additional information: Could not find Windows Runtime type 'Sqlite.Sqlite3'.

    ---

    Looking in the bin folder there is no sqlite3.dll.

    Any ideas? I am using VS2013 and targetting Windows Phone 8.0

    Thanks
    Russell

    ReplyDelete
    Replies
    1. Did you get the extension to install? Otherwise, use the manual installation from this link. http://visualstudiogallery.msdn.microsoft.com/cd120b42-30f4-446e-8287-45387a4f40b7
      It sounds like SQLite wasn't installed properly.

      Delete
    2. Thanks. It was already installed (from the sqlite website) but I uninstalled and used your link. Installation went fine. Restarted VS and loaded my solution, removed and re-added the reference for SQLite for Windows Phone, and rebuilt. Same, no sqlite3.dll in the bin folder :(

      Delete
    3. Okay, creating a new solution and building up again from the start WORKS :)

      Delete
    4. That's weird but I'm glad it worked out for you!

      Delete
    5. I am using VS2013 RC2 so I probably was using the wrong profile originally. Thanks

      Delete