Setting Up SQLite Connection In Xamarin App

Introduction

In this article, we are going to learn how to set up a connection with SQLite in our application. We will connect each of the iOS, Android, and UWP projects with SQLite.

Targeted Audience

People with the basic knowledge of C# and Xamarin.

Tools

Visual Studio with Xamarin installed.

SQLite is the most preferred embedded database for mobile developers. The reason behind this is that it provides a very simple, serverless, zero-configuration setup and also, it is free and open source. Setting up a connection with SQLite is easy. Let’s see how to do this in the following simple steps.

1. Install the NuGet package to your solution “SQLite-net-PCL”.
2. Create a model matching the SQLite Database table definition in the Xamarin common base project.
3. Create an interface in it.
4. Implement the interface in each iOS, Android, and UWP project.
5. Register each of them with the SQL Dependency Services.

First of all, you have to go to your solution and right-click on it to manage the NuGet packages. Type SQLite-net-pcl to add the package for the database as done below in the picture.

Click the "Install" button to install the package and don’t forget to choose the latest stable version.

Next, you have to add a folder in your base project with a class. Here, I am adding a “Connections” folder with the class name “ISqlConnection.cs”. The naming convention is not necessary; you can use your own as you like.

Create an interface in this class with a public access modifier and type the following syntax in it.

SQLiteAsyncConnection Connection();

It will come up with an error but don’t get worried, this is just because you did not add the namespace. So, add the namespace on the top “using SQLite” and it will fix the issues.

C# Code

using System;
using System.Collections.Generic;
using System.Text;
using SQLite;
namespace PracApp1.Connections
{
    public interface ISqlConnection
    {
        SQLiteAsyncConnection Connection();
    }
}

Now, you just have to implement the interface in all three native projects. And register with the dependency services. So, add a class to your Android project like in the picture below.

After that, you have to implement the interface in it. And register it with the SQL dependency. As you can see in the code below, the SqlConnection class is implemented with the interface ISqlConnection. The function “Connection” returns the SQLiteAsyncConnection with the path.

C# Code for Android

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using PracApp1.Connections;
using PracApp1.Droid;
using SQLite;
using Xamarin.Forms;
[assembly: Dependency(typeof(SqlConnection))]
namespace PracApp1.Droid
{
    class SqlConnection : ISqlConnection
    {
        public SQLiteAsyncConnection Connection()
        {
            var documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments);
            var path = Path.Combine(documentsPath, "MySQLite.sqldb");

            return new SQLiteAsyncConnection(path);
        }
    }
}

For iOS, you have to do the same. Add a new class to the iOS project and the C# code is almost the same as the Android.

C# Code

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Foundation;
using PracApp1.Connections;
using PracApp1.iOS;
using SQLite;
using UIKit;
using Xamarin.Forms;
[assembly: Dependency(typeof(SqlConnection))]
namespace PracApp1.iOS
{
    class SqlConnection : ISqlConnection
    {
        public SQLiteAsyncConnection Connection()
        {
            var documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            var path = Path.Combine(documentsPath, "MySQLite.sqldb");

            return new SQLiteAsyncConnection(path);
        }
    }
}

For UWP also, do the same. Add a new class but the code is a bit different from the Android and iOS. You can use the following code.

C# Code

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using PracApp1.Connections;
using PracApp1.UWP;
using SQLite;
using Windows.Storage;
using Xamarin.Forms;
[assembly: Dependency(typeof(SqlConnection))]
namespace PracApp1.UWP
{
    class SqlConnection : ISqlConnection
    {
        public SQLiteAsyncConnection Connection()
        {
            string documentPath = ApplicationData.Current.LocalFolder.Path;
            string path = Path.Combine(documentPath, "MySQLite.sqldb");
            return new SQLiteAsyncConnection(path);
        }
    }
}

Summary

This article focused on accessing the SQLite database using Xamarin.Forms. So, the connection with the SQLite is successfully built. Now, you can operate with its functionalities, like CRUD functions and many others. Xamarin.Forms support database-driven applications using the SQLite database engine, which makes it possible to load and save the objects in a shared code.