Xamarin + SQlite + Android

Introduction

This tip explains how to connect SQLite in Xamarin C# Android.

Background

The SQLite database can be added to the Asset folder and then later, it can be accessed by the IO class and copy the database file into an external storage device.

It's better to move the SQLite database file to the external storage for working Android database application, if there is any issue or error in program or need to update the program will not delete the database file. The data files stored along with the Android apk file will be deleted when uninstalling application from the Settings-> Apps section. The database file is stored into the data folder under Linux Android file system.

So when developing an application, it's better to move the database working SQLite file to the external storage device.

Code Example

Below is the source code explained.

The below code was used when the database from Asset folder is moved to the external storage folder "Sample". Inside "Sample" folder, we create a sub folder named "WorkingDB" where we copy the current database file. This can be done when the application first loads after installation.

// Global variable for storing Database file name and file path,
// so it can be accessed from any Android Activity

AppGlobal.DatabaseFileName = "Sample.db";

AppGlobal.DatabasebFilePath = System.IO.Path.Combine
  ("/mnt/sdcard/Sample/WorkingDB",AppGlobal.DatabaseFileName);

Code for creating a working folder in external storage device and moving database file from Asset folder to the newly created folder.

try
{
   // function call used fro creating a working directory
   CreateDirectory ();

    // checking is there a directory available in the same external storage, if not create one

   if (!File.Exists(AppGlobal.DatabasebFilePath))
       {
         // creating Database folder and file
        createWorkingDB_File();

        }
}
catch (Exception ex)
{
      Toast.MakeText(this, ex.Message,ToastLength.Short).Show();
}

Below is the code for creating folder in external storage and moving the database file from the Asset folder.

public void createDirectory()
{
  bool isExists=false;
  string folderExternal;

  try
    {
       // folder path

       folderExternal = "/mnt/sdcard/Sample";

       // checking folder available or not
       isExists= System.IO.Directory.Exists(folderExternal);

       // if not create the folder
       if(!isExists)
       System.IO.Directory.CreateDirectory(folderExternal);

       folderExternal = "/mnt/sdcard/Sample/WorkingDB;;
       isExists= System.IO.Directory.Exists(folderExternal);

       if(!isExists)
       System.IO.Directory.CreateDirectory(folderExternal);
}

The below code is for copying database file from the Asset folder to external storage. Asset folder should have a database attached with name "Sample.db".

public void createWorkingDB_File()
      {

          try{
              //checking file exist in location or not

              if (!File.Exists (AppGlobal.DatabasebFilePath))

              {    _progressDialog.SetTitle("Creating Sample Database");
                  _progressDialog.SetMessage("Please wait...");
                  _progressDialog.Show();

                 // Java thread is used for creating or copying
                 // database file because this will not make the program non responsive

                  new Java.Lang.Thread(() =>
                      {
                       // calling data from Asset folder

                          using (var asset =      Assets.Open ("Empty_DB.db"))
                          using (var dest = File.Create ( AppGlobal.DatabasebFilePath))
                          {
                            // copying database from Asset folder to external storage device
                              asset.CopyTo (dest);
                          }

                          RunOnUiThread(() => onSuccessfulLogin());
                      }).Start();
              }

          } catch (System.Exception ex) {

              Toast.MakeText (this, ex.ToString(), ToastLength.Long ).Show ();
          }
      }

Below is the code which explains how to connect and pull data from SQLite (Clients) table.

public List<client> GetAllClients()
      {
          List<client> _lstClientName = new List<client>();

           SqliteConnection _connectin = null;
              try
              {
               _connectin = new SqliteConnection(Utility.GetDbConnectionString());
                  _connectin.Open();
               SqliteCommand _selectCommand = null;
                  _selectCommand = new SqliteCommand("Select * From Clients", _connectin);
               SqliteDataReader _dataReader = _selectCommand.ExecuteReader();

                  while (_dataReader.Read())
                  {
                   byte[] _photo = null;

                      if (_dataReader["LogoPic"] != null
                      && _dataReader["LogoPic"] != DBNull.Value)
                      {
                       _photo = (byte[]) _dataReader["LogoPic"] ;

                      }
                   _lstClientName.Add(new Client(_dataReader["Name"],
                   _dataReader["ClientID"], _photo, _dataReader["JobID"]));
                  }
                  _dataReader.Close();
              }
              catch (Exception ex)
              {
                  throw ex;
              }
              finally
              {
                  if (_connectin != null)
                      _connectin.Close();
              }

          return _lstClientName;
      }