:::: MENU ::::

Using SQLite.Net with Xamarin.Forms

SQLite in Xamarin.Forms is awesome. In this blog post let’s see how to use SQLite in Xamarin.Forms.

First let’s talk about few things about implementation of SQLite.

The approach for incorporating SQLite.Net differs depending on the project template you select:

  • PCL Solutions: PCL solutions can take advantage of the SQLite.NET PCL NuGet packages to easily incorporate database operations into the shared code by referencing the SQLite classes that ship in the NuGet. The Xamarin.Forms DependencyService is used to create the platform-specific SQLite connection that is required in this implementation.
  • Shared Projects: Shared Projects can directly incorporate the Net source from GitHub and reference those SQLite classes. Compiler directives are used when platform-specific code is required (such as determining the location of the SQLite data file).

Most SQLite.Net code is shareable across all platforms; only configuring the database connection and location of the SQLite database file requires platform-specific functionality. This is explained below for each solution type.

In this post I will use SQLite with Xamarin.Forms PCL template.

-Create new project and add SQLite support

Let’s create new project and I name it as “XamarinSqliteSample”.

Now we need to install a new package into our project named SQLite.Net. This is a .NET wrapper around SQLite that will allow us to access the native SQLite functionality from a Xamarin.Forms PCL or Shared project. To add SQLite support to a Xamarin.Forms PCL template solution, start with the shared PCL library. Right-click and choose Manage NuGet Packages. to add SQLite.Net support.

Search SQLite.Net PCL and install the package as shown:

Once the reference has been added, write an Interface to abstract any platform-specific functionality. For SQLite.Net the only platform-specific work required is to determine the location of the database file and create a connection. Within your PCL project, create a new interface named ISQLite and replace the definition with the following:

This is the interface that we will implement and get access connection via DependencyService from the native projects.

-Let’s create a Database

We now have access to the SQLite functionality let’s define our database. In this simple application we are just keeping student records. Let’s call this class Student.

As you see above we got four columns where Id is set as PrimaryKey and AutoIncrement.

Now for the simplicity let’s create a class that represents my database and keep all the logic to access the database and its tables within this class. I will call it StudentDB.

In StudentDB() constructor we are doing two things. First we are using the DependencyService class to get a registered class that implements the ISQLite interface and call its GetConnection method.

Second, we use the CreateTable method on the SQLiteConnection class to create a table called Student. This method will create the table, if it doesn’t already exist, and exit if it already exists.

-iOS implementation

The main obstacle that we need to work around on the native side when using SQLite is where we are going to store the actual database file. This differs from platform to platform.

Before we can actually add any sort of SQLite functionality to the iOS project, we need to add the SQLite.Net PCL as well as the SQLite.NET PCL – XamarinIOS Platform packages to this project from Nuget as like earlier. Once you have added these packages, you can start to write some SQLite code within the iOS project.

Let’s create class SQLite_iOS class and implement ISQLite interface.

The assembly attribute at the top of the file is used to identify this class as a Dependency that can be retrieved via the Get method on the DependencyService class. Now we get access to the correct location to store the database file, create a new SQLiteConnection object, and pass it back to our PCL project.

-Android implementation

Android implementation is also very similar to the iOS implementation only difference will be that the location of the database file will be different. Here also you need to add packages (SQLite.Net PCL and SQLite.NET PCL – XamarinAndroid) from Nuget. Now let’s create SQLite_Android class and implement ISQLite interface.

You now have a working implementation of the ISQLite interface for your android app.

-Windows Phone Implementation

For Windows phone you should have to do a little more work. The SQLite database engine is built-in to the iOS and Android operating systems. To add SQLite support to Windows Phone projects follow this instruction.

After installing SQLite, Lets add the packages from Nuget to our project SQLite.Net PCL and SQLite.Net PCL – WindowsPhone 8 Platform. With these packages installed, you can create the Windows Phone implementation of the ISQLite interface.

Ok that’s it for SQLite implementation part. Now it’s time for creating UI.

-Adding User Interface

For this sample application I will make our interface very simple. I have created two pages where in first page (Register.Xaml) user can register student detail and in second page (StudentList.Xaml) viewing the list of added students.

Here is code view of Register.xaml and Register.xaml.cs

And here is code view of  StudentList.xaml and StudentList.xaml.cs

In my case I run this app in Windows Phone and works fine. Here are screen shots:

 

That’s it. Now you can add database functionality on your Xamarin.Forms app.

You can download complete code from here.

Happy Coding!!! 🙂