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.
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; }
}
}
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();
}
}
}
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
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.
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();
}
}
}
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.
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.
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.
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
https://github.com/oysteinkrog/SQLite.Net-PCL
SQLite-net-wp project site
https://github.com/peterhuene/sqlite-net-wp8
SQLite-net-wp project site
https://github.com/peterhuene/sqlite-net-wp8
