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.
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
{
CreateDirectory ();
if (!File.Exists(AppGlobal.DatabasebFilePath))
{
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
{
folderExternal = "/mnt/sdcard/Sample";
isExists= System.IO.Directory.Exists(folderExternal);
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{
if (!File.Exists (AppGlobal.DatabasebFilePath))
{ _progressDialog.SetTitle("Creating Sample Database");
_progressDialog.SetMessage("Please wait...");
_progressDialog.Show();
new Java.Lang.Thread(() =>
{
using (var asset = Assets.Open ("Empty_DB.db"))
using (var dest = File.Create ( AppGlobal.DatabasebFilePath))
{
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;
}