Mobile Local Databases In Xamarin.Forms Using SQLite

Introduction

The most frequent feature requirement in mobile applications is the online and offline feature. In online mode, mobile apps exchange data over networks, using cloud storage.

In offline mode, mobile apps only need to store data in mobile storage locally. With simple unstructured data, such as - user settings, options, and cloud download data, applications can store information inside local files or local database.

This article will show a step by step guide on how to use an SQLite database with a Xamarin.Forms application in iOS, Android, and Windows Universal application. I have seen most of the articles on SQLite implementation but everywhere they use Dependency and creating database in platform specific code.

The sample, shown below, is about Cross Mobile local database in Xamarin.Forms using SQLite with 100% code re-use to all the platforms.


Step 1 Create New Xamarin.Forms Application

Create new Xamarin.Form application using Visual Studio 2015 or Xamarin Studio. You can refer to my previous article for creating new Xamarin.Forms  Application. 



Step 2 Add SQLite NuGet Package

After creating Xamarin.Forms application, you need a managed way to access SQLite database. You need to add a reference to portable SQLite library from our project.

SQLite-net-pcl is on open source and lightweight library for .NET, Mono, and Xamarin applications. It’s available as a NuGet package with the name sqlite-net-pcl .

Right click on Solution - Manage NuGet Package for Solution - Search “Sqlite-net-pcl” - select all platform project - Click on "Install".



Step 3 Add PCLStorage Package

PCL Storage provides a consistent, portable set of local files I/O APIs for .NET, Windows Phone, Windows Store, Xamarin.iOS, Xamarin.Android, and Silverlight. This makes it easier to create cross-platform .NET libraries and apps.

Here, we need to save SQLite database to all the platforms and local storage, so for getting local storage path we are using PCLStorage .

Add PCLStorage Nuget package to all the projects. Right click on Project Solution - Click “Manage NuGet package for Solution “ - Search and select “PCLStorage” - Select all the Projects - Click Install.



Step 4 Create Entity For table

You have created one Data Model Entity with below table column.

using SQLite;
namespace DevEnvExe_LocalStorage
{
    public class RegEntity
    {
        public RegEntity()
        {
        }

        [PrimaryKey, AutoIncrement]
        public int ID { get; set; }
        public string Name { get; set; }
        public string Username { get; set; }
        public string Password { get; set; }
    }
}

Step 4 Create SQLHelper Class

Create "New class" and add method for SQL connection and Get, Update, Insert,  and Delete data.

Right Click Portable Class Library - Add New Item - select Class and name it as “SQLHelper”.

Using directives

You need to add the following using reference from SQLHelper class and what you need in this class are two fields that store the connection string and an object that will be used to implement locks on data operations, to avoid database collisions.

using SQLite;
using System.Collections.Generic;
using System.Linq;
using PCLStorage;

namespace DevEnvExe_LocalStorage
{
    public class SqlHelper
    {
        static object locker = new object();
        SQLiteConnection database;

SQLite Connection

Sqlite Connection Common for all the platforms. The below code will be used for getting  local storage path and creating the Sqlite connection .

public SQLite.SQLiteConnection GetConnection()
        {
            SQLiteConnection sqlitConnection;
            var sqliteFilename = "Employee.db3";
            IFolder folder = FileSystem.Current.LocalStorage;
            string path = PortablePath.Combine(folder.Path.ToString(), sqliteFilename);
            sqlitConnection = new SQLite.SQLiteConnection(path);
            return sqlitConnection;
        }

Create Table

Now it’s time to implement the class constructor; the above method will return DBConnection using that creates table in the Sqlite Database

public SqlHelper()
        {
            database = GetConnection();
            // create the tables
            database.CreateTable<RegEntity>();
        }

Get Data

The below code is to get  all the registered user's details.

public IEnumerable<RegEntity> GetItems()
        {
            lock (locker)
            {
                return (from i in database.Table<RegEntity>() select i).ToList();
            }
        }

The  below code is get the all the user details and filter by user parameter .

public RegEntity GetItem(string userName)
      {
          lock (locker)
          {
              return database.Table<RegEntity>().FirstOrDefault(x => x.Username == userName);
          }
      }

Insert and Update Data

The SaveItem method will act as insert and update. if its ID value is 0 it means it will insert the value to sqlite otherwise it will be updated on an item based ID .

public int SaveItem(RegEntity item)
        {
            lock (locker)
            {
                if (item.ID != 0)
                {
                    //Update Item
                    database.Update(item);
                    return item.ID;
                }
                else
                {
                    //Insert item
                    return database.Insert(item);
                }
            }
        }

Delete Data

The below code deletes the item based on ID

public int DeleteItem(int id)
        {
            lock (locker)
            {
                return database.Delete<RegEntity>(id);
            }
        }

SQLHelper Class

I have included all the helper methods for your reference

using SQLite;
using System.Collections.Generic;
using System.Linq;
using PCLStorage;

namespace DevEnvExe_LocalStorage
{
    public class SqlHelper
    {
        static object locker = new object();
        SQLiteConnection database;
        public SqlHelper()
        {
            database = GetConnection();
            // create the tables
            database.CreateTable<RegEntity>();
        }
        public SQLite.SQLiteConnection GetConnection()
        {
            SQLiteConnection sqlitConnection;
            var sqliteFilename = "Employee.db3";
            IFolder folder = FileSystem.Current.LocalStorage;
            string path = PortablePath.Combine(folder.Path.ToString(), sqliteFilename);
            sqlitConnection = new SQLite.SQLiteConnection(path);
            return sqlitConnection;
        }

        public IEnumerable<RegEntity> GetItems()
        {
            lock (locker)
            {
                return (from i in database.Table<RegEntity>() select i).ToList();
            }
        }

        public RegEntity GetItem(string userName)
        {
            lock (locker)
            {
                return database.Table<RegEntity>().FirstOrDefault(x => x.Username == userName);
            }
        }
        public RegEntity GetItem(string userName ,string passWord)
        {
            lock (locker)
            {
                return database.Table<RegEntity>().FirstOrDefault(x => x.Username == userName && x.Password ==passWord);
            }
        }
        public int SaveItem(RegEntity item)
        {
            lock (locker)
            {
                if (item.ID != 0)
                {
                    //Update Item
                    database.Update(item);
                    return item.ID;
                }
                else
                {
                    //Insert item
                    return database.Insert(item);
                }
            }
        }

        public int DeleteItem(int id)
        {
            lock (locker)
            {
                return database.Delete<RegEntity>(id);
            }
        }
    }
}

Step 4 UI Design

Login page

You can create Login Page as per the below design with two entry boxes and buttons



Xaml Design

You can reference the  below xaml code and add it in your login page

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             xmlns:local="clr-namespace:DevEnvExe_LocalStorage"
             x:Class="DevEnvExe_LocalStorage.MainPage"
     Padding="0, 20, 0, 0">

  <Grid>
    <Grid.RowDefinitions>
      <RowDefinition Height="Auto" />
      <RowDefinition Height="Auto" />
      <RowDefinition Height="Auto" />
      <RowDefinition Height="Auto" />
    </Grid.RowDefinitions>

    <Grid.ColumnDefinitions>
      <ColumnDefinition Width="Auto" />
      <ColumnDefinition Width="Auto" />
      <ColumnDefinition Width="300" />
    </Grid.ColumnDefinitions>
    <Label Text="Login" Grid.Row="0" Grid.Column="2" FontSize="50" ></Label>

    <Entry Placeholder="UserID" x:Name="txtuserid" Grid.Row="1" Grid.Column="1" Grid.ColumnSpan="2"  ></Entry>

    <Entry IsPassword="True" x:Name="txtpassword" Placeholder="Password" Grid.Row="2" Grid.Column="1" Grid.ColumnSpan="2" ></Entry>
    <StackLayout Grid.Row="3" Grid.Column="1" Grid.ColumnSpan="3">
      <Button Text="Login" Clicked="Click_Login" ></Button>
      <Button Text="Registrtion" Clicked="Click_Reg" ></Button>
    </StackLayout>

  </Grid>

</ContentPage>

C# Code

The below code is Login check to see if userid and password already available or not. If it’s not available navigate to Register page otherwise  navigate to Edit profile page (Home Page)

RegEntity userDetail = App.Database.GetItem(txtuserid.Text, txtpassword.Text);

Above Get method will return user details.

using System;
using Xamarin.Forms;

namespace DevEnvExe_LocalStorage
{
    public partial class MainPage : ContentPage
    {
        public MainPage()
        {
            InitializeComponent();
        }
        async void Click_Reg(object sender, EventArgs e)
        {
            await Navigation.PushModalAsync(new Registration());
        }

        async void Click_Login(object sender, EventArgs e)
        {
            RegEntity userDetail = App.Database.GetItem(txtuserid.Text, txtpassword.Text);

            if (userDetail != null)
            {
                if (txtuserid.Text != userDetail.Username && txtpassword.Text != userDetail.Password)
                {
                    await DisplayAlert("Login", "Login failed .. Please try again ", "OK");
                }
                else
                {
                    await DisplayAlert("Registrtion", "Login Success ... Now Edit your profile ", "OK");
                    await Navigation.PushModalAsync(new Home(txtuserid.Text));
                }
            }
            else
            {
                await DisplayAlert("Login", "Login failed .. Please try again ", "OK");
            }


        }

    }
}

Registration page

You can create Registration Page as per the below design with 3 entry boxes and buttons



Xaml Design

You can refer to the below xaml code for registration page design

<?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="DevEnvExe_LocalStorage.Registration"
             Padding="0, 20, 0, 0">

  <Grid>
    <Grid.RowDefinitions>
      <RowDefinition Height="Auto" />
      <RowDefinition Height="Auto" />
      <RowDefinition Height="Auto" />
      <RowDefinition Height="Auto" />
      <RowDefinition Height="Auto" />
    </Grid.RowDefinitions>

    <Grid.ColumnDefinitions>
      <ColumnDefinition Width="Auto" />
      <ColumnDefinition Width="Auto" />
      <ColumnDefinition Width="300" />
    </Grid.ColumnDefinitions>
    <Label Text="Registartion" Grid.Row="0" Grid.Column="2" FontSize="50" ></Label>

    <Entry Placeholder="Name" x:Name="txtname" Grid.Row="1" Grid.Column="1" Grid.ColumnSpan="2"  ></Entry>
    <Entry Placeholder="UserID"  x:Name="txtuserid" Grid.Row="2" Grid.Column="1" Grid.ColumnSpan="2"  ></Entry>

    <Entry IsPassword="True"  x:Name="txtpassword" Placeholder="Password" Grid.Row="3" Grid.Column="1" Grid.ColumnSpan="2" ></Entry>
    <StackLayout Grid.Row="4" Grid.Column="1" Grid.ColumnSpan="3">
      <Button Text="Registrtion" Clicked="Click_Reg" ></Button>
      <Button Text="Already Register ... Login " Clicked="Click_Login"></Button>
    </StackLayout>

  </Grid>

</ContentPage>

C# Code

The below method will save the user details into SQLite database.

RegEntity OReg = new RegEntity();
OReg.Name = txtname.Text;
OReg.Username = txtuserid.Text;
OReg.Password = txtpassword.Text;
int i = App.Database.SaveItem(OReg);

Here, save method will save the user details while sending parameter ID; value is by default 0

using System;

using Xamarin.Forms;

namespace DevEnvExe_LocalStorage
{
    public partial class Registration : ContentPage
    {
        public Registration()
        {
            InitializeComponent();
        }
        async void Click_Reg(object sender, EventArgs e)
        {

          if (txtuserid.Text != "")
            {
                RegEntity fileexist = App.Database.GetItem(txtuserid.Text);
                if (fileexist == null)
                {
                    if (txtname.Text != "" && txtpassword.Text != "" && txtuserid.Text != "")
                    {
                        RegEntity OReg = new RegEntity();
                        OReg.Name = txtname.Text;
                        OReg.Username = txtuserid.Text;
                        OReg.Password = txtpassword.Text;
                        int i = App.Database.SaveItem(OReg);
                        if (i > 0)
                        {
                            await DisplayAlert("Registrtion", "Registrtion Success ... Login and Edit profile ", "OK");
                            await Navigation.PushModalAsync(new MainPage());
                        }
                        else
                        {
                            await DisplayAlert("Registrtion", "Registrtion Fail .. Please try again ", "OK");
                        }
                    }
                }
                else
                {
                    await DisplayAlert("Registrtion Failed", "username already exist .. Please try differnt user name ", "OK");
                    txtuserid.Text = "";
                    txtuserid.Focus();

                }
            }

        }
        async void Click_Login(object sender, EventArgs e)
        {
            await Navigation.PushModalAsync(new MainPage());
        }
    }
}

Home page

You can create edit profile page like below



Xaml Design

You can refer to the  below code for creating updated profile page

<?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="DevEnvExe_LocalStorage.Home">
 <Grid>
    <Grid.RowDefinitions>
      <RowDefinition Height="Auto" />
      <RowDefinition Height="Auto" />
      <RowDefinition Height="Auto" />
      <RowDefinition Height="Auto" />
      <RowDefinition Height="Auto" />
    </Grid.RowDefinitions>

    <Grid.ColumnDefinitions>
      <ColumnDefinition Width="Auto" />
      <ColumnDefinition Width="Auto" />
      <ColumnDefinition Width="300" />
    </Grid.ColumnDefinitions>
    <Label Text="Registartion" Grid.Row="0" Grid.Column="2" FontSize="50" ></Label>

    <Entry Placeholder="Name" x:Name="txtname" Grid.Row="1" Grid.Column="1" Grid.ColumnSpan="2"  ></Entry>
    <Entry Placeholder="UserID"  x:Name="txtuserid" IsEnabled="false" Grid.Row="2" Grid.Column="1" Grid.ColumnSpan="2"  ></Entry>

    <Entry IsPassword="True"  x:Name="txtpassword" Placeholder="Password" Grid.Row="3" Grid.Column="1" Grid.ColumnSpan="2" ></Entry>
    <StackLayout Grid.Row="4" Grid.Column="1" Grid.ColumnSpan="3">
      <Button Text="Update Profile" Clicked="Click_UpdateProfile" ></Button>
      <Button Text="LogOut" Clicked="Click_Login"></Button>
    </StackLayout>

  </Grid>
</ContentPage>

C# Code

The below code gets and updates user details in sqlite database

using System;

using Xamarin.Forms;

namespace DevEnvExe_LocalStorage
{
    public partial class Home : ContentPage
    {
        public Home(string userId)
        {
            InitializeComponent();
            GetUserDetail(userId);


        }
        RegEntity userDetail;
        public void GetUserDetail(string userId)
        {
            userDetail = App.Database.GetItem(userId);
            txtname.Text = userDetail.Name;
            txtuserid.Text = userDetail.Username;
            txtpassword.Text = userDetail.Password;
        }
        async void Click_UpdateProfile(object sender, EventArgs e)
        {
            int i = -1;
            if (txtname.Text != "" && txtpassword.Text != "" && txtuserid.Text != "")
            {
                userDetail.Name = txtname.Text;
                userDetail.Username = txtuserid.Text;
                userDetail.Password = txtpassword.Text;
                 i = App.Database.SaveItem(userDetail);
            }


            if (i < 0)
            {
                await DisplayAlert("Update Profile", "Update Fail .. Please try again ", "OK");
            }
            else
            {
                await DisplayAlert("Update Profile", "Profile update Success . ", "OK");
            }
        }

        async void Click_Login(object sender, EventArgs e)
        {
            await Navigation.PushModalAsync(new MainPage());
        }
    }
}

I believe this article will help you to create local SQLite database with 100% code re-use in Xamarin.Forms . If you have any question or feedback, please share in the comment box.