SQLite with Xamarin Forms Step by Step guide

In many mobile applications it’s a requirement to store data locally and for that purpose we use databases with the applications. The type of database to use mainly depends upon the requirement of the application, but in most of MIS (Management Information Systems) based application relational databases are used for this purpose.
The most commonly used relational database with Xamarin Forms is SQLite. It is the best suited relational database for mobile applications as it has very small footprint. This article will be an step by step guide on how to use a SQLite database with a Xamarin Forms application.We will be using the database to persist employee data. The structure of the database will be like following Diagram:

Step-1: create a new project in Xamarin/Visual Studio (see this article for steps). Name it as ”. There is no out of box tool available in Xamarin Forms, So we will be using a third party plugin for this purpose and the most popular plugin is ‘SQLite.Net-PCL’ created by oysteinkrog.
Step-2: In order to use ‘SQLite.Net-PCL’ plugin, add the nuget package of the plugin to each project of the solution. In Visual Studio this can be done by right click on the solution and selecting ‘Manage NuGet Packages for Solution‘ option. Which will give following screen:

In case of Xamarin Studio, you will have to individually add the NuGet package by right clicking on the ‘Packages’ folder and selecting ‘Add Packages’ option on each project.
Even tough the ‘SQLite.Net-PCL‘ package will provide us the functionality of manipulating the SQLite database, it can’t automatically initialize the database connection object as the location of the database file varies on different platforms. So in order to solve this issue we will use dependency service to load the database file in connection object.
Step-3: Create a blank interface with one method signature like following code

using SQLite.Net;

namespace SQLiteEx
{
    public interface ISQLite
    {
        SQLiteConnection GetConnection();
    }
}

Step-4: Create Class file named ‘SQLiteService‘ implementing ‘ISQLite‘ interface and Write the implementation of this ‘GetConnection‘ in every platform specific code like following given codes:
For new database: When we are creating a database in the application itself for the first time then following code will be used.
Android:

using System;
using Xamarin.Forms;
using SQLiteEx.Droid;
using System.IO;

[assembly: Dependency(typeof(SqliteService))]
namespace SQLiteEx.Droid
{
    public class SqliteService : ISQLite
    {
        public SqliteService() { }

        #region ISQLite implementation
        public SQLite.Net.SQLiteConnection GetConnection()
        {
            var sqliteFilename = "SQLiteEx.db3";
            string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder
            var path = Path.Combine(documentsPath, sqliteFilename);
            Console.WriteLine(path);
            if (!File.Exists(path)) File.Create(path);
            var plat = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid();
            var conn = new SQLite.Net.SQLiteConnection(plat, path);
            // Return the database connection 
            return conn;
        }

        #endregion


    }
}

iOS:

using SQLiteEx.iOS;
using System;
using System.IO;
using Xamarin.Forms;

[assembly: Dependency(typeof(SqliteService))]
namespace SQLiteEx.iOS
{
    public class SqliteService : ISQLite
    {
        public SqliteService()
        {
        }
        #region ISQLite implementation
        public SQLite.Net.SQLiteConnection GetConnection()
        {
            var sqliteFilename = "SQLiteEx.db3";
            string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder
            string libraryPath = Path.Combine(documentsPath, "..", "Library"); // Library folder
            var path = Path.Combine(libraryPath, sqliteFilename);

            // This is where we copy in the prepopulated database
            Console.WriteLine(path);
            if (!File.Exists(path))
            {
                File.Create(path);
            }

            var plat = new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS();
            var conn = new SQLite.Net.SQLiteConnection(plat, path);

            // Return the database connection 
            return conn;
        }
        #endregion
    }
}

For Pre-Created Database: In some application scenarios there may be a requirement that the database is pre-populated with some data and we are going to use the same in the application. In such scenarios first copy the database file at following mentioned locations in platform specific project and then use following code in ‘SQLiteService’ Class
Android:
Copy the database file in ‘Resources\Raw’ folder of Android platform project.

using System;
using Xamarin.Forms;
using SQLiteEx.Droid;
using System.IO;

[assembly: Dependency(typeof(SqliteService))]
namespace SQLiteEx.Droid
{
	
	public class SqliteService : ISQLite {

		public SqliteService () {}

		#region ISQLite implementation	
		public SQLite.Net.SQLiteConnection GetConnection ()
		{
			var sqliteFilename = "SQLiteEx.db3";
			string documentsPath = System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal); // Documents folder
			var path = Path.Combine(documentsPath, sqliteFilename);

			// This is where we copy in the prepopulated database
			Console.WriteLine (path);
			if (!File.Exists(path))
			{
				var s = Forms.Context.Resources.OpenRawResource(Resource.Raw.APGameDb);  // RESOURCE NAME ###

				// create a write stream
				FileStream writeStream = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);
				// write to the stream
				ReadWriteStream(s, writeStream);
			}

			var plat = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid();
			var conn = new SQLite.Net.SQLiteConnection(plat, path);

			// Return the database connection 
			return conn;
		}
		#endregion

		///
<summary>
		/// helper method to get the database out of /raw/ and into the user filesystem
		/// </summary>

		void ReadWriteStream(Stream readStream, Stream writeStream)
		{
			int Length = 256;
			Byte[] buffer = new Byte[Length];
			int bytesRead = readStream.Read(buffer, 0, Length);
			// write the required bytes
			while (bytesRead > 0)
			{
				writeStream.Write(buffer, 0, bytesRead);
				bytesRead = readStream.Read(buffer, 0, Length);
			}
			readStream.Close();
			writeStream.Close();
		}


	}
}

iOS:
Copy the database file in ‘Resources’ folder of Android platform project.

using SQLiteEx.iOS;
using System;
using System.IO;
using Xamarin.Forms;

[assembly: Dependency(typeof(SqliteService))]
namespace SQLiteEx.iOS
{
    public class SqliteService : ISQLite
    {
        public SqliteService()
        {
        }
        #region ISQLite implementation
        public SQLite.Net.SQLiteConnection GetConnection()
        {
            var sqliteFilename = "SQLiteEx.db3";
            string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder
            string libraryPath = Path.Combine(documentsPath, "..", "Library"); // Library folder
            var path = Path.Combine(libraryPath, sqliteFilename);

            // This is where we copy in the prepopulated database
            Console.WriteLine(path);
            if (!File.Exists(path))
            {
                File.Copy(sqliteFilename, path);
            }

            var plat = new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS();
            var conn = new SQLite.Net.SQLiteConnection(plat, path);

            // Return the database connection 
            return conn;
        }
        #endregion
    }
}

Step-5: Create a class in PCL project named as ‘DataAccess‘. This class will contain all the data access codes for the application. The code of the class is as follows:

using SQLite.Net;
using Xamarin.Forms;

namespace SQLiteEx
{
    public class DataAccess
    {
        SQLiteConnection dbConn;
        public DataAccess()
        {
            dbConn = DependencyService.Get<ISQLite>().GetConnection();
            // create the table(s)
            dbConn.CreateTable<Employee>();
        }
        public List<Employee> GetAllEmployees()
        {
            return dbConn.Query<Employee>("Select * From [Employee]");
        }
        public int SaveEmployee(Employee aEmployee)
        {
            return dbConn.Insert(aEmployee);            
        }
        public int DeleteEmployee(Employee aEmployee)
        {
            return dbConn.Delete(aEmployee);
        }
        public int EditEmployee(Employee aEmployee)
        {
            return dbConn.Update(aEmployee);
        }
    }
}

As it can be seen from the code above that the application is using dependency service to fetch the database and create the connection object in the constructor of the class which will be used in order to manipulate the data. Secondly the code to create tables are there in constructor also, this we will have to give even if we are using a pre-created database as the method automatically checks for the table in database and create it if not present.
Step-6: Create the Plain Old CLR Object (POCO) classes for the table(s) present in the database. For Example ‘Employee‘ class for the above given tables in ‘DataAccess‘ class.
Employee:

using SQLite.Net.Attributes;
using System;

namespace SQLiteEx
{
    public class Employee
    {
        [PrimaryKey, AutoIncrement]
        public long EmpId
        { get; set; }
        [NotNull]
        public string EmpName
        { get; set; }
        public string Designation
        { get; set; }
        public string Department
        { get; set; }
        public string Qualification
        { get; set; }
    }
}

Step-7: Create the static object property of ‘DataAccess‘ in ‘App‘ class like following code. This will enable us to use manipulate the data from any screen of the application.

using Xamarin.Forms;
using Xamarin.Forms.Xaml;

[assembly: XamlCompilation(XamlCompilationOptions.Compile)]
namespace SQLiteEx
{
    public class App : Application
    {
        static DataAccess dbUtils;
        public App()
        {
            // The root page of your application
            MainPage = new NavigationPage(new ManageEmployee());
        }
        public static DataAccess DAUtil
        {
            get
            {
                if (dbUtils == null)
                {
                    dbUtils = new DataAccess();
                }
                return dbUtils;
            }
        }
        protected override void OnStart()
        {
            // Handle when your app starts
        }

        protected override void OnSleep()
        {
            // Handle when your app sleeps
        }

        protected override void OnResume()
        {
            // Handle when your app resumes
        }
    }
}

This completes the basic structure of application using a SQLite database to persist data of the application.
Now let’s create the screens of our sample application to see the CRUD operation code.The Application will contain following screens:

1. Manage Employees
2. Add Employee
3. Show Employee Details
4. Edit Employee

Manage Employees: This screen is the home screen of the application, it will show the current list of Employees and give an option to add new employee, view the details of the Employee and manage departments. The XAML code of this page will be like :

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             x:Class="SQLiteEx.ManageEmployee" Title="Manage Employees" >
  <ContentPage.Padding>
    <OnPlatform x:TypeArguments="Thickness" iOS="0, 20, 0, 0" />
  </ContentPage.Padding>
  <ContentPage.Content>
    <ListView x:Name="lstData" HasUnevenRows="false" Header="Header Value" Footer="Footer"  ItemSelected="OnSelection" >
      <ListView.HeaderTemplate>
        <DataTemplate>
          <StackLayout Orientation="Horizontal" BackgroundColor="Blue" Padding="5,5,5,5">
            <Label Text="Name" FontSize="Medium" FontAttributes="Bold" TextColor="White"/>
            <Label Text="Designation" FontSize="Medium" FontAttributes="Bold" TextColor="White"/>
            <Label Text="Department" FontSize="Medium" FontAttributes="Bold" TextColor="White"/>
          </StackLayout>       
        </DataTemplate>
      </ListView.HeaderTemplate>
      <ListView.ItemTemplate>
        <DataTemplate>
          <ViewCell>
            <StackLayout Orientation="Horizontal" Padding="5,5,5,5">
              <Label Text="{Binding EmpName}" FontSize="Medium" />
              <Label Text="{Binding Designation}" FontSize="Medium" />
              <Label Text="{Binding Department}" FontSize="Medium" />
            </StackLayout>
          </ViewCell>
        </DataTemplate>
      </ListView.ItemTemplate>
      <ListView.FooterTemplate>
        <DataTemplate>
          <StackLayout Orientation="Horizontal" Padding="5,5,5,5">
            <Button Text="Add New Employee" Clicked="OnNewClicked" />
          </StackLayout>
        </DataTemplate>
      </ListView.FooterTemplate>
    </ListView>
  </ContentPage.Content>
</ContentPage>

As per the above XAML, the application executes ‘OnSelection‘ method on ‘ItemSelected‘ event of list to show the detailed employee information, ‘OnNewClicked‘ method on ‘Clicked‘ event of ‘Add New Employee‘ button. The code behind of this page will contain following code.

using System;
using Xamarin.Forms;

namespace SQLiteEx
{
    public partial class ManageEmployee : ContentPage
    {
        public ManageEmployee()
        {
            InitializeComponent();
            var vList = App.DAUtil.GetAllEmployees();
            lstData.ItemsSource = vList;
        }

        void OnSelection(object sender, SelectedItemChangedEventArgs e)
        {
            if (e.SelectedItem == null)
            {
                return; 
                //ItemSelected is called on deselection, 
                //which results in SelectedItem being set to null
            }
            var vSelUser = (Employee)e.SelectedItem;
            Navigation.PushAsync(new ShowEmplyee(vSelUser));
        }
        public void OnNewClicked(object sender, EventArgs args)
        {
            Navigation.PushAsync(new AddEmployee());
        }
    }
}

Add Employee: As the name suggests this page will be used for adding a new employee and will be called upon click of ‘Add New Employee’ button on ‘Manage Employee’ page. The XAML code of this page will be like:

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms" xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml" x:Class="SQLiteEx.AddEmployee" Title="Add New Employee">
      <ContentView.Content>
			  <TableView Intent="Settings" BackgroundColor="White">
				  <TableRoot>
					  <TableSection Title="Add New Employee">
						  <EntryCell x:Name="txtEmpName" Label="Employee Name" Keyboard="Text" />
						  <EntryCell x:Name="txtDesign" Label="Designation" Keyboard="Text" />
						  <EntryCell x:Name="txtDepartment" Label="Department" Keyboard="Text" />
						  <EntryCell x:Name="txtQualification" Label="Qualification" Keyboard="Text" />             
						  <ViewCell>
							  <ContentView Padding="0,0">
                  <ContentView.Padding>
                    <OnPlatform x:TypeArguments="Thickness" iOS="10,0" WinPhone="0,15,0,0" />
                  </ContentView.Padding>
                  <ContentView.Content>
                    <Button BackgroundColor="#fd6d6d" Text="Save" TextColor="White" Clicked="OnSaveClicked" />
                  </ContentView.Content>
                </ContentView>
						  </ViewCell>
					  </TableSection>
				  </TableRoot>
			  </TableView>
      </ContentView.Content>
</ContentPage>

The code behind of this page will contain following code:

using System;
using Xamarin.Forms;

namespace SQLiteEx
{
    public partial class AddEmployee : ContentPage
    {
        public AddEmployee()
        {
            InitializeComponent();
        }

        public void OnSaveClicked(object sender, EventArgs args)
        {
            var vEmployee = new Employee()
            {
                EmpName = txtEmpName.Text,
                Department = txtDepartment.Text,
                Designation = txtDesign.Text,
                Qualification = txtQualification.Text
            };
            App.DAUtil.SaveEmployee(vEmployee);
            Navigation.PushAsync(new ManageEmployee());
        }
    }
}

Show Employee Details: As the name suggests, this page will be used to show the complete details of the employee and also will have the option to invoke ‘Edit Employee’ page and ‘Delete Employee’ to delete the details of employee from database. The XAML code of this page is like:

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms" xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml" x:Class="SQLiteEx.ShowEmplyee" Title="View Employee">
  <ContentView.Content>
    <Grid>
      <Grid.RowDefinitions>
        <RowDefinition Height="*"/>
        <RowDefinition Height="*"/>
        <RowDefinition Height="*"/>
        <RowDefinition Height="*"/>
        <RowDefinition Height="*"/>
        <RowDefinition Height="*"/>
        <RowDefinition Height="*"/>
      </Grid.RowDefinitions>

      <Grid.ColumnDefinitions>
        <ColumnDefinition Width="10"/>
        <ColumnDefinition Width="*"/>
        <ColumnDefinition Width="*"/>
        <ColumnDefinition Width="10"/>
      </Grid.ColumnDefinitions>

      <Label Grid.Row ="0" Grid.Column="0" Grid.ColumnSpan="2" Text="Employee Details" />
      <Label Grid.Row ="1" Grid.Column="1" Text="Name" />
      <Label Grid.Row="1" Grid.Column="2" Text ="{Binding EmpName}" />
      <Label Grid.Row ="2" Grid.Column="1" Text="Designation" />
      <Label Grid.Row="2" Grid.Column="2" Text ="{Binding Designation}"/>
      <Label Grid.Row ="3" Grid.Column="1" Text="Department" />
      <Label Grid.Row="3" Grid.Column="2" Text ="{Binding Department}"/>
      <Label Grid.Row ="4" Grid.Column="1" Text="Qualification" />
      <Label Grid.Row="4" Grid.Column="2" Text ="{Binding Qualification}" />
      <Button Grid.Row ="5" Grid.Column="1" Text="Edit Details" Clicked="OnEditClicked" />
      <Button Grid.Row="5" Grid.Column="2" Text="Delete" Clicked="OnDeleteClicked" />
    </Grid>
  </ContentView.Content>
</ContentPage>

And the code behind of this page will contain following code:

using System;
using Xamarin.Forms;

namespace SQLiteEx
{
    public partial class ShowEmplyee : ContentPage
    {
        Employee mSelEmployee;
        public ShowEmplyee(Employee aSelectedEmp)
        {
            InitializeComponent();
            mSelEmployee = aSelectedEmp;
            BindingContext = mSelEmployee;
        }

        public void OnEditClicked(object sender, EventArgs args)
        {
            Navigation.PushAsync(new EditEmployee(mSelEmployee));
        }
        public async void  OnDeleteClicked(object sender, EventArgs args)
        {
            bool accepted = await DisplayAlert("Confirm", "Are you Sure ?", "Yes", "No");
            if (accepted)
            {
                App.DAUtil.DeleteEmployee(mSelEmployee);
            }
            await Navigation.PushAsync(new ManageEmployee());
        }
    }
}

Edit Employee: As the name suggests, this page will be used to edit the details of the employee. The XAML code of this page is like:

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms" xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml" x:Class="SQLiteEx.EditEmployee" Title="Edit Employee">
  <ContentView.Content>
    <TableView Intent="Settings" BackgroundColor="White">
      <TableRoot>
        <TableSection Title="Edit Employee">
          <EntryCell x:Name="txtEmpName" Label="Employee Name" Text ="{Binding EmpName}" Keyboard="Text" />
          <EntryCell x:Name="txtDesign" Label="Designation" Text ="{Binding Designation}" Keyboard="Text" />
          <EntryCell x:Name="txtDepartment" Label="Department" Text ="{Binding Department}" Keyboard="Text" />
          <EntryCell x:Name="txtQualification" Label="Qualification" Text ="{Binding Qualification}" Keyboard="Text" />
          <ViewCell>
            <ContentView Padding="0,0">
              <ContentView.Padding>
                <OnPlatform x:TypeArguments="Thickness" iOS="10,0" WinPhone="0,15,0,0" />
              </ContentView.Padding>
              <ContentView.Content>
                <Button BackgroundColor="#fd6d6d" Text="Save" TextColor="White" Clicked="OnSaveClicked" />
              </ContentView.Content>
            </ContentView>
          </ViewCell>
        </TableSection>
      </TableRoot>
    </TableView>
  </ContentView.Content>
</ContentPage>

And the code behind of this page will contain following code:

using System;
using Xamarin.Forms;

namespace SQLiteEx
{
    public partial class EditEmployee : ContentPage
    {
        Employee mSelEmployee;
        public EditEmployee(Employee aSelectedEmp)
        {
            InitializeComponent();
            mSelEmployee = aSelectedEmp;
            BindingContext = mSelEmployee;
        }

        public void OnSaveClicked(object sender, EventArgs args)
        {
            mSelEmployee.EmpName = txtEmpName.Text;
            mSelEmployee.Department = txtDepartment.Text;
            mSelEmployee.Designation = txtDesign.Text;
            mSelEmployee.Qualification = txtQualification.Text;
            App.DAUtil.EditEmployee(mSelEmployee);
            Navigation.PushAsync(new ManageEmployee());
        }
    }
}

As mentioned earlier in the article, this article contains step by step guide to use SQLite database in Xamarin Forms mobile application. The example code of this article can be found at GitHub. Let me know if I has missed anything or have any suggestions.

:):) Happy Coding :):)

Reference : Xamarin Forms Documentation.