A blog about mobile development and related technologies. Nothin' fancy! Mostly using Xamarins stuff.

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.

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.

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.

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.

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.

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.

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

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 :(

Trend-setters move us forward in every area from medicine to fashion to space. Which is why one long-standing, burning question recently received an answer: if they can put a man on the moon, why can't they come up with a decent-looking cell phone case?wholesale personalized iphone cases