Deploying a SQLite Database in a Xamarin Project

I use SQLite databases in some of my Xamarin projects and hence there is a requirement to make sure that anyone who has my application has the updated database when I do an update through Google Play or the Apple Store.

So in a situation where you have a pre-populated SQLite database, the first thing to do is to copy the database into the deployed location. This procedure is done when the application starts up by checking if the database already exists or not.

You add the SQLite database into the Assets folder for Android and into the Resources folder for iOS.

(Right click on the project Assets folder or Resources folder, select “add existing” and then browse to the SQLite file and select it).

In your Android project create a new class file FileAccessHelper.cs at the root of the Android project.

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;

namespace SQLiteDeploy.Droid
{
   public class FileAccessHelper
   {

    /// <summary>
    /// This copies the database over if it does not exist on the Andriod device
    /// </summary>
    /// <param name="dbPath">The path of the database on the Andriod device</param>
    /// <param name="SQLiteAsset">The database name in the assets folder</param>
    public static void CopyDatabaseIfNotExists(string dbPath, string SQLiteAsset)
       {
       if (!File.Exists(dbPath))
          {
             using (var br = new BinaryReader(Application.Context.Assets
                                                                    .Open(SQLiteAsset)))
             {
                using (var bw = new BinaryWriter(new FileStream(dbPath, 
                                                                     FileMode.Create)))
                {
                 byte[] buffer = new byte[2048];
                 int length = 0;
                 while ((length = br.Read(buffer, 0, buffer.Length)) > 0)
                    {
                       bw.Write(buffer, 0, length);
                    }
                 }
              }
           }

        }

     }
}

In the MainActivity.cs file:

//Copy over database
var SQLiteAsset = "SQLite.db3"; //database name
//get path of database
var documentsPath = System.Environment.GetFolderPath(System.Environment
                                   .SpecialFolder.MyDocuments);
var path = System.IO.Path.Combine(documentsPath, SQLiteAsset);
FileAccessHelper.CopyDatabaseIfNotExists(path, SQLiteAsset);

In your iOS project create a new class file FileAccessHelper.cs at the root of the iOS project.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

using Foundation;
using UIKit;

namespace SQLiteDeploy.iOS
{
     public class FileAccessHelper
     {

        /// <summary>
        /// This copies the database over if it does not exist on the iOS device
        /// If the file does not exist in dbPath, then copy it from SQLiteAsset location
        /// </summary>
        /// <param name="dbDestination">The path of the database on the iOS device</param>
        /// <param name="SQLiteAsset">The database name in the installation folder</param>
        public static void CopyDatabaseIfNotExists(string dbDestination, string SQLiteAsset)
        {

           //If file does not exist, then copy file to it from the resources folder
           //located at NSBundle.MainBundle.PathForResource
           if (!File.Exists(dbDestination))
           {
              var ext = Path.GetExtension(SQLiteAsset);
              var name = Path.GetFileNameWithoutExtension(SQLiteAsset);
              var dbSource = NSBundle.MainBundle.PathForResource(name, ext);

              //check if database exists at source first.
              if (File.Exists(dbSource))
              {
                  File.Copy(dbSource, dbDestination, true);
              }
            }
          }

     }
}

In the iOS Main.cs file:

     //Copy over database
     var SQLiteAsset = "SQLite.db3"; //database name

     var documentsPath = System.Environment.GetFolderPath(System.Environment
                                     .SpecialFolder.MyDocuments);
     var dbDestinationFolder = System.IO.Path.Combine(documentsPath, SQLiteAsset);

     //path is the full folder and file name, SQLLiteAssest is just the name of the file
     FileAccessHelper.CopyDatabaseIfNotExists(dbDestinationFolder, SQLiteAsset);

How do I test if it works?

Note that you can only test your application by completly deleting the application from the tablet or phone and then re-deploying it because the database may have already been copied over in a previous deployment.

Updated application but with new data in the database

When you want to deploy a new version of a database where the application is already available on the Google Play store and Apple store, then you need to make sure that the original database gets overwritten with the new database on the customer phone or tablet if different data is needed. The only way I can see to do that at the moment is to read the file size and compare it with the deployed value. This can be done in the FileAccessHelper.cs file.

The following is a modified version of FileAccessHelper.cs

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;

namespace KitchenTimer.Droid
{
       public class FileAccessHelper
       {

         /// <summary>
         /// This copies the database over if it does not exist on the Andriod device
         /// </summary>
         /// <param name="dbPath">The path of the database on the Andriod device</param>
         /// <param name="SQLiteAsset">The database name in the assets folder</param>
         public static void CopyDatabaseIfNotExists(string dbPath, string SQLiteAsset)
         {
            if (!File.Exists(dbPath))
            {
               using (var br = new BinaryReader(Application.Context.Assets
                                                                    .Open(SQLiteAsset)))
               {
                  using (var bw = new BinaryWriter(new FileStream(dbPath, 
                                                                     FileMode.Create)))
                  {
                   byte[] buffer = new byte[2048];
                   int length = 0;
                   while ((length = br.Read(buffer, 0, buffer.Length)) > 0)
                     {
                        bw.Write(buffer, 0, length);
                     }
                }
            }
        }
         else
        {

           //if file does exist, compare the file sizes
           long fileDeploySize = 20480; //deployment size of file.

          try
          {

           FileInfo fSQLiteAsset2 = new FileInfo(dbPath);
           long s2 = fSQLiteAsset2.Length;

               if (s2 != fileDeploySize)
               {
                  using (var br = new BinaryReader(Application.Context.Assets
                                                                   .Open(SQLiteAsset)))
                  {
                     using (var bw = new BinaryWriter(new FileStream(dbPath,
                                                                    FileMode.Create)))
                     {
                        byte[] buffer = new byte[2048];
                        int length = 0;
                        while ((length = br.Read(buffer, 0, buffer.Length)) > 0)
                        {
                           bw.Write(buffer, 0, length);
                        }
                     }
                 }
              }

          }
          catch
          {

          }

       }

    }
  }
}

In the above code, if the database already exists, I check the size of the database file and if they are different I copy the file over to the deploy location, overwriting the existing database file. In the above example the new database file size is 20480 bytes. So you do have to enter this value manually which may not be the best method.

How do I create a SQLite database and add my tables and data

I use “DB Browser for SQLite” which you can download from http://sqlitebrowser.org/ for Windows and Mac computers.

Using SQLite in your Xamarin Projects

This briefly describes using SQLite in your Xamarin Project and provides a simple exampple of the code:

** Add sqlite-net-pcl to all projects. This is the Nuget package sqlite-net-pcl by Frank A. Krueger
** Declare interface in PCL. You need to set up a connection Interface in the PCL project
** Implement in each application project. You then need to implement the interface in each of the other projects.

So first in the PCL project, create a folder called Persistence. Then create a new class interface file called
ISQLiteDb.cs as follows:

using SQLite;
using System;
using System.Collections.Generic;
using System.Text;

namespace SQLiteDeploy.Persistence
{
    public interface ISQLiteDb
    { 
      SQLiteAsyncConnection GetConnection();
    }
}

Now in both the Android Project and iOS project create a folder called Persistence. Then create a class file called SQLiteDb.cs with the following code which is the same for the Android file and iOS file:

using System;
using System.Collections.Generic;
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 Xamarin.Forms;
using SQLite;
using SQLiteDeploy.Persistence;
using SQLiteDeploy.Droid.Persistence;
using System.IO;

[assembly: Dependency(typeof(SQLiteDb))]
namespace KitchenTimer.Droid.Persistence
{
    public class SQLiteDb : ISQLiteDb
    {
       public SQLiteAsyncConnection GetConnection()
       {
        var documentsPath = System.Environment.GetFolderPath(System.Environment
                                 .SpecialFolder.MyDocuments);
        var path = System.IO.Path.Combine(documentsPath, "SQLite.db3");

        return new SQLiteAsyncConnection(path);
       }

     }
}

You can download my example of this from here:

https://github.com/paulv200/SQLiteDeploy.git