Setting up a Local Database

A project in any language or framework is nothing without a good database, likewise in Xamarin.Forms, we require a repository to keep all the data, though there are applications that do not possess a requirement for having data locally as they are completely dependent on APIs.

We traditionally have two ways to setup a local database in Xamarin, with ADO.NET and with SQLite. We are leaving ADO.NET for some other day and move along with SQLite.

What is SQLite?

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is the most widely deployed database in the world with more applications than we can count, including several high-profile projects.

Getting Started-

Step-1 Create an empty project, Like "DatabaseSetupDemo" here.

Step 2-Add the SQLite-Net NuGet package by Frank Kreuger


Step 3-Create a file called Teacher.cs. This is your table. The code snippet below illustrates set up for the table.

public class Teacher
{
     [PrimaryKey, AutoIncrement]
     public int ID { get; set; }

     public string FirstName { get; set; }

     public string LastName { get; set; }

     public override string ToString()
     {
        return string.Format("[Person: ID={0}, FirstName={1}, LastName={2}]", ID, FirstName, LastName);
     }
}

The first step in the creation of the database is to create a connection. The following snippet shows how to connect using the createDatabase method:

private string createDatabase(string path)
{
    try
    {
        var connection = new SQLiteAsyncConnection(path);{
             connection.CreateTableAsync<Person>();
             return "Database created";
    }
    catch (SQLiteException ex)
    {
        return ex.Message;
    }
}

SQLite has Insert, Update, InsertAll and UpdateAll for insertion or updating of data respectively.
Insert and InsertAll returns 0 if the data is non-exist in the table. If the value is non-zero, then the data exists and the Update or UpdateAll is used. The following snippet shows how to perform this operation.

private string insertUpdateData(Person data, string path)
{
    try
    {
        var db = new SQLiteAsyncConnection(path);
            if (db.InsertAsync(data) != 0)
                db.UpdateAsync(data);
            return "Single data file inserted or updated";
    }
    catch (SQLiteException ex)
    {
        return ex.Message;
    }
}

As to find the number of records in the database, Count(*) is used as part of the SQLite query as demonstrated in this code.

private int findNumberRecords(string path)
{
    try
    {
        var db = new SQLiteAsyncConnection(path);
        // this counts all records in the database, it can be slow depending on the size of the database
        var count = db.ExecuteScalar<int>("SELECT Count(*) FROM Person");

        // for a non-parameterless query
        // var count = db.ExecuteScalar<int>("SELECT Count(*) FROM Person WHERE FirstName="Amy");

        return count;
      }
      catch (SQLiteException ex)
      {
          return -1;
      }
  }