Click here to Skip to main content
13,770,603 members
Click here to Skip to main content
Add your own
alternative version

Stats

36.9K views
25 bookmarked
Posted 2 Nov 2014
Licenced CPOL

Xamarin + SQlite + Android

, 2 Nov 2014
Rate this:
Please Sign up or sign in to vote.
How to use SQLite with Xamarin C# 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;
      }

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionComplete source code listing Pin
mbhildebrand21-Aug-16 11:37
membermbhildebrand21-Aug-16 11:37 
SuggestionWhy not use .NET Task? Pin
Serge Desmedt3-Nov-14 1:22
professionalSerge Desmedt3-Nov-14 1:22 
GeneralRe: Why not use .NET Task? Pin
Ullas_Krishnan3-Nov-14 4:52
memberUllas_Krishnan3-Nov-14 4:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05-2016 | 2.8.181119.1 | Last Updated 2 Nov 2014
Article Copyright 2014 by Ullas_Krishnan
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid