Install an SQLite database in your Xamarin app

Introduction

When we are developing a Xamarin application, one of the important points may be the initialization of the data for our application. To carry this out, there are different options:

1. Download the data when opening the application for the first time
2. Save the data in JSON or XML files
3. Preload a SQLite database

In one of my personal projects I had this situation. In the beginning, we had implemented the first option, load the data when organizing the application. The problem with this approach, the data loading can be quite time consuming and the user experience the first time the application is opened can be quite negative. In the measurements it took at the last hour.

With respect to the second option, load the data of a JSON, it solves the issue of the issue of the heavy load time, and that the JSON is installed as a resource next to the application. The downside of this is that it is not a highly optimized format when making queries and also the size of our APK or IPA will create a lot. In my case, the data I needed saved came to occupy between 3-4 MB.

Finally, I came up with the possibility of pre-generating a DB in SQLite that will carry the static data for the application. In this case, the database only occupies a size of 276 KB.

In this article we are going to see how an SQLite database is made in your Xamarin Forms application.

Creating the database

To generate the database in sqlite 3, there are several options. One of them would be to export the DB in a file inside the mobile and export it. Or you can use a tool like sqlite studio (https://sqlitestudio.pl), which will allow you to work with the database as with any other manager (create tables, modify data, make queries, etc).

Installing the Database in Android

Once we have created the database, we will add it to our android project, in the "Assets" folder. In addition, it is important that in the properties select the Build Action as AndroidAsset, to then be able to access the resource.



Next, we go to the main activity (MainActivity.cs) and in the OnCreate method we call the CopyDatabaseIfNotExists ("app.db3") method that is defined as follows:

private void CopyDatabaseIfNotExists(string dataBaseName)
{
 var dbPath = System.IO.Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), dataBaseName);

 if (!System.IO.File.Exists(dbPath))
 {
  var dbAssetStream = Assets.Open(dataBaseName);
  var dbFileStream = new System.IO.FileStream(dbPath, System.IO.FileMode.OpenOrCreate);
  var buffer = new byte[1024];

  int b = buffer.Length;
  int length;

  while ((length = dbAssetStream.Read(buffer, 0, b)) > 0)
  {
   dbFileStream.Write(buffer, 0, length);
  }

  dbFileStream.Flush();
  dbFileStream.Close();
  dbAssetStream.Close();
 }
}

As we can see the method takes the file from the database of the assets and if it did not exist previously, it copies it into the personal storage of the device.

Installing the Database in iOS

Similarly, in the iOS project we copied the database file into the Resources folder. In this case, in the properties of the file we select BundleResource:




Now in the Main.cs class, we can similarly call the CopyDatabaseIfNotExists ("app.db3") method, which would be defined as:

public void CopyDatabaseIfNotExists(string filename)
{
 string docFolder = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
 string libFolder = Path.Combine(docFolder, "..", "Library", "Databases");

 if (!Directory.Exists(libFolder))
 {
  Directory.CreateDirectory(libFolder);
 }

 string dbPath = Path.Combine(libFolder, filename);

 if (!File.Exists(dbPath))
 {
  var existingDb = Path.Combine(NSBundle.MainBundle.BundlePath, filename);

  File.Copy(existingDb, dbPath);
 }
}

And with this we will already have installed the sqlite database in our Xamarin Froms application, efficiently, both in loading speed and in saving data.

I hope you find it useful.

Happy coding!!