We’re building an iOS app for a client that will use Xamarin.Forms and Xamarin.iOS. This is a walkthrough for my own purposes and for anyone else who may find it helpful on how to get started with SQLite in the app. I’ve done similar work on a project with iOS and Android, so this app will use the techniques I’ve learned there such as dependency services, but I’m not doing any actual Android coding for this project.
Create a PCL Project
To get started, I created a Xamarin.Forms PCL Project. In this case I only needed to target iOS so I unchecked Android.
Add NuGet Packages
SQLite
Next I added the SQLite packages to both the Xamarin.Forms project and the Xamarin.iOS project. There are a lot of SQLite packages out there, and I’ve found many posts and books say the same thing… this is the one you need:
- Name: SQLite-net PCL
- ID: sqlite-net-pcl
- Author: Frank A. Krueger
- Project Page: https://github.com/praeclarum/sqlite-net
Add this to both the Xamarin.Forms and the Xamarin.iOS Projects
JSON.NET
I also routinely add JSON.NET as you’re going to need it if you’re passing data back and forth to a web server / API. I added this to both my Xamarin.Forms and my Xamarin.iOS Projects:
- Name: Json.NET
- ID: Newtonsoft.Json
- Author: James Newton-King
- Project Page: http://www.newtonsoft.com/json
Microsoft.Net.HTTP
Finally, for my case, I know I’m going to be sending a lot of data back and forth to a Web API, so I’ll go ahead and add the Microsoft.Net.HTTP NuGet package that allows for easy communication with my API. Here’s the package:
- Name: Microsoft HTTP Client Libraries
- ID: Microsoft.Net.Http
- Author: Microsoft
- Project Page: http://blogs.msdn.microsoft.com/bclteam/p/httpclient/
So that’s it for adding the NuGet packages you’ll need. For getting started, especially if you’re just prototyping or starting a new app, you may only need SQLite.
Creating Your Data Model
The SQLite package I installed makes it VERY easy to create data tables and connect them with my data model. You simply create classes (I put them in a _DATA folder to keep them organized) and specify the public properties you need (these will become fields). Here’s an example:
using System;
using SQLite;
using System.Collections.Generic;
using System.Linq;
namespace NorthernLights { public class NLUser { [PrimaryKey] public int UserId { get; set; }
public string UserName { get; set; } public string UserEmail { get; set; } public string UserPhone { get; set; } public bool UserIsActive { get; set; }
public NLUser () { }
} }
SQLite Attributes
There are a number of attributes you can assign to a property to specify how it should be used. In the example above, I only used the [PrimaryKey] indicator, which will make a field in the database unique and indexed. Other attributes you can use are:
- [PrimaryKey] – This attribute can be applied to an integer property to force it to be the underlying table’s primary key. Composite primary keys are not supported.
- [AutoIncrement] – This attribute will cause an integer property’s value to be auto-increment for each new object inserted into the database
- [Column(name)] – Supplying the optional name parameter will override the default value of the underlying database column’s name (which is the same as the property).
- [Table(name)] – Marks the class as being able to be stored in an underlying SQLite table. Specifying the optional name parameter will override the default value of the underlying database table’s name (which is the same as the class name).
- [MaxLength(value)] – Restrict the length of a text property, when a database insert is attempted. Consuming code should validate this prior to inserting the object as this attribute is only ‘checked’ when a database insert or update operation is attempted.
- [Ignore] – Causes SQLite.NET to ignore this property. This is particularly useful for properties that have a type that cannot be stored in the database, or properties that model collections that cannot be resolved automatically be SQLite.
- [Unique] – Ensures that the values in the underlying database column are unique.
Creating Additional Models
I continue on and create data models for all my tables. At this point I’m not concerned about creating relationships between the tables… I’ll get to that a little bit down the road.
Creating a Database
Ok, now for the fun part… creating a database and all the tables that go with it. There is a lot of magic that goes in behind the scenes in the SQLite package that I’m not going to deal with, so here’s what needs to be done to get everything started.
Reset The Database Flag
One thing I do while I’m developing an app is change my database structure from time to time. Ever done that? Of course you have. The thing I’ve found is that it’s not all that easy to “add a field” to a SQLite table after it’s been created. Maybe I’ll learn how to do that down the road, but right now I don’t know how. So, during development, I create a shared class that allows me to reset the database at any time. It starts off looking like this (in the Xamarin.Forms project):
using System;
namespace NorthernLights
{
public class NLData
{
public static readonly bool ResetDatabaseOnStart = false;
}
}
Wow… complicated stuff, huh? It comes in very handy down the road, though…
Creating an Interface
The next thing we have to do is create an interface so the iOS app can do it’s platform-specific work in establishing the connection. I create a new class called ISQLite and add the following code in my Xamarin.Forms app:
using System;
using SQLite;
namespace NorthernLights
{
public interface ISQLite
{
SQLiteConnection GetConnection(bool ResetDatabaseOnStart);
}
}
Rocket science? Hardly. But it creates the wiring so you can connect the Xamarin.Forms app with the Xamarin.iOS app when it comes to creating a physical database file on the device.
Creating the Connection in Xamarin.Forms
The next step is to begin establishing the database connection in the Xamarin.Forms project. I add some code to my NLData class so it winds up looking this this:
using System;
using SQLite;
using System.Collections.Generic;
using System.Linq;
using Xamarin.Forms;
namespace NorthernLights
{
public class NLData
{
public static readonly bool ResetDatabaseOnStart = false;
static SQLiteConnection _database;
static bool _databaseInitiated = false;
public static SQLiteConnection database {
get {
if (!_databaseInitiated) {
initDatabase();
}
return _database;
}
}
static void initDatabase() {
_database = DependencyService.Get<ISQLite> ().GetConnection (ResetDatabaseOnStart);
// create the tables
_database.CreateTable<NLUser>();
}
}
}
This added code does several things:
- Creates a static connection to the database that can be used throughout my app.
- Initiates the database the first time through.
- Creates a dependency service that must be implemented in my iOS App
- Creates my database tables (repeat the CreateTable line for every model you’ve made)
We’re just about there, believe it or not! Time to switch over to the iOS app and wire up the database connection.
Connecting to the Database in iOS
The final piece of the puzzle is to create a class to handle the ISQLite interface we created above. iOS (and Android) have different ways of organizing and accessing files you so you need to write platform specific code for this. In the iOS app, I create a new class called SQLite_iOS that looks like this:
using System;
using NorthernLights;
using Xamarin.Forms;
using NorthernLights.iOS;
using System.IO;
[assembly: Dependency (typeof (SQLite_iOS))]
namespace NorthernLights.iOS
{
public class SQLite_iOS : ISQLite
{
public SQLite_iOS ()
{
}
private static bool _initiated=false;
public SQLite.SQLiteConnection GetConnection (bool ResetDatabaseOnStart)
{
//Build the path for storing the iOS database
var sqliteFilename = "database.db3";
string documentsPath = Environment.GetFolderPath (Environment.SpecialFolder.Personal); // Documents folder
string libraryPath = Path.Combine (documentsPath, "..", "Library"); // Library folder
var path = Path.Combine(libraryPath, sqliteFilename);
if (!_initiated) {
//do things just once while the app is running
_initiated = true;
//Reset the file if requested
if (ResetDatabaseOnStart) {
if (File.Exists (path)) {
File.Delete (path);
}
}
}
// Create the connection
var conn = new SQLite.SQLiteConnection(path);
// Return the database connection
return conn;
}
}
}
Here’s what’s going on here:
- We tell the assembly that this class is going to be responsible for handling the Dependency we created earlier.
- We create a class called SQLite_iOS that inherits from the interface we created earlier.
- We handle the GetConnection method we created earlier that needs to be implemented on the iOS platform.
- Within this method, we delete the database if ResetDatabaseOnStart is specified, and then we create the database file and return the connection to control of the Xamarin.Forms app
Wrapping It Up
And that, my friend, is it. You’ve added the packages you’ll need, created your data models, and created a cross-platform database object you can now use in your Xamarin.Forms project. What we haven’t done yet is create any methods to access the data IN your database, or to add data to it. I’ve written a separate post for that, which you can find right here.
Recent Comments