How to Create a Database Mobile App with SQLite and Xamarin Studio

When we work on mobile app development, it is just a matter of time when we face the need for data storage; information that can be the backbone for the mobile app to just a single data such as the score for a game.

Nowdays every mobile app with minimum data storage needs to use a database. Since the mobile devices do not offer the same memory and processing capacity as a computer, we need to use specially designed systems for those environments.

So I will guide you in this basic tutorial on how to create a database for your mobile app working with the cross-platform Xamarin studio, which is a great tool for this example.

What is SQLite?

SQLite is a database engine, compatible with ACID. Unlike client-server systems, SQLite is linked to the mobile app by becoming part of it. Every operation is performed within the mobile app through calls and methods provided by the SQLite library which is written in C and has a relatively smaller size.

Creating a SQLite database app

Create a new Android mobile application solution in Xamarin Studio

If you don't have Xamarin Studio, don't worry. You can download it here: Xamarin Studio

Database class

1. Right click project BD_Demo --> Add --> New File… --> Android Class (Database)

2. Database class

Database class is for handling SQLiteDatabase object. We are now going to create objects and methods for handling CRUD (Create, Read, Update and Delete) operations in a database table. Here is the code:

//Required assemblies
using Android.Database.Sqlite;
using System.IO;namespace BD_Demo
{
class Database
{
//SQLiteDatabase object for database handling
private SQLiteDatabase sqldb;
//String for Query handling
private string sqldb_query;
//String for Message handling
private string sqldb_message;
//Bool to check for database availability
private bool sqldb_available;
//Zero argument constructor, initializes a new instance of Database class
public Database()
{
sqldb_message = "";
sqldb_available = false;
}
//One argument constructor, initializes a new instance of Database class with database name parameter
public Database(string sqldb_name)
{
try
{
sqldb_message = "";
sqldb_available = false;
CreateDatabase(sqldb_name);
}
catch (SQLiteException ex)
{
sqldb_message = ex.Message;
}
}
//Gets or sets value depending on database availability
public bool DatabaseAvailable
{
get{ return sqldb_available; }
set{ sqldb_available = value; }
}
//Gets or sets the value for message handling
public string Message
{
get{ return sqldb_message; }
set{ sqldb_message = value; }
}
//Creates a new database which name is given by the parameter
public void CreateDatabase(string sqldb_name)
{
try
{
sqldb_message = "";
string sqldb_location = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
string sqldb_path = Path.Combine(sqldb_location, sqldb_name);
bool sqldb_exists = File.Exists(sqldb_path);
if(!sqldb_exists)
{
sqldb = SQLiteDatabase.OpenOrCreateDatabase(sqldb_path,null);
sqldb_query = "CREATE TABLE IF NOT EXISTS MyTable (_id INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR, LastName VARCHAR, Age INT);";
sqldb.ExecSQL(sqldb_query);
sqldb_message = "Database: " + sqldb_name + " created";
}
else
{
sqldb = SQLiteDatabase.OpenDatabase(sqldb_path, null, DatabaseOpenFlags.OpenReadwrite);
sqldb_message = "Database: " + sqldb_name + " opened";
}
sqldb_available=true;
}
catch(SQLiteException ex)
{
sqldb_message = ex.Message;
}
}
//Adds a new record with the given parameters
public void AddRecord(string sName, string sLastName, int iAge)
{
try
{
sqldb_query = "INSERT INTO MyTable (Name, LastName, Age) VALUES ('" + sName + "','" + sLastName + "'," + iAge + ");";
sqldb.ExecSQL(sqldb_query);
sqldb_message = "Record saved";
}
catch(SQLiteException ex)
{
sqldb_message = ex.Message;
}
}
//Updates an existing record with the given parameters depending on id parameter
public void UpdateRecord(int iId, string sName, string sLastName, int iAge)
{
try
{
sqldb_query="UPDATE MyTable SET Name ='" + sName + "', LastName ='" + sLastName + "', Age ='" + iAge + "' WHERE _id ='" + iId + "';";
sqldb.ExecSQL(sqldb_query);
sqldb_message = "Record " + iId + " updated";
}
catch(SQLiteException ex)
{
sqldb_message = ex.Message;
}
}
//Deletes the record associated to id parameter
public void DeleteRecord(int iId)
{
try
{
sqldb_query = "DELETE FROM MyTable WHERE _id ='" + iId + "';";
sqldb.ExecSQL(sqldb_query);
sqldb_message = "Record " + iId + " deleted";
}
catch(SQLiteException ex)
{
sqldb_message = ex.Message;
}
}
//Searches a record and returns an Android.Database.ICursor cursor
//Shows all the records from the table
public Android.Database.ICursor GetRecordCursor()
{
Android.Database.ICursor sqldb_cursor = null;
try
{
sqldb_query = "SELECT*FROM MyTable;";
sqldb_cursor = sqldb.RawQuery(sqldb_query, null);
if(!(sqldb_cursor != null))
{
sqldb_message = "Record not found";
}
}
catch(SQLiteException ex)
{
sqldb_message = ex.Message;
}
return sqldb_cursor;
}
//Searches a record and returns an Android.Database.ICursor cursor
//Shows records according to search criteria
public Android.Database.ICursor GetRecordCursor(string sColumn, string sValue)
{
Android.Database.ICursor sqldb_cursor = null;
try
{
sqldb_query = "SELECT*FROM MyTable WHERE " + sColumn + " LIKE '" + sValue + "%';";
sqldb_cursor = sqldb.RawQuery(sqldb_query, null);
if(!(sqldb_cursor != null))
{
sqldb_message = "Record not found";
}
}
catch(SQLiteException ex)
{
sqldb_message = ex.Message;
}
return sqldb_cursor;
}
}
}

Records Layout

2. Expand Resources Folder on Solution Pad

    a) Right click Layout Folder --> Add --> New File… --> Android Layout (record_view)

We need a layout for each item we are going to add to our database table. We do not need to define a layout for every item and this same layout can be re-used as many times as the items we have.

4. Layout demo resized 600

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="horizontal"
android:layout_width="match_parent"
android:layout_height="wrap_content">
<TextView
android:text="ID"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/Id_row"
android:layout_weight="1"
android:gravity="center"
android:textSize="15dp"
android:textColor="#ffffffff"
android:textStyle="bold" />
<TextView
android:text="Name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/Name_row"
android:layout_weight="1"
android:textSize="15dp"
android:textColor="#ffffffff"
android:textStyle="bold" />
<TextView
android:text="LastName"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/LastName_row"
android:layout_weight="1"
android:textSize="15dp"
android:textStyle="bold"
android:textColor="#ffffffff" />
<TextView
android:text="Age"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/Age_row"
android:layout_weight="1"
android:textSize="15dp"
android:textStyle="bold"
android:textColor="#ffffffff"
android:gravity="center" />
</LinearLayout>

Main Layout

3. Expand Resources folder on Solution Pad --> Expand Layout folder

    a) Double Click Main layout (Main.axml)

Xamarin automatically makes Form Widgets's IDs available by referencing Resorce.Id class.

main widgets

Images

 add  delete  save  search

add.png

delete.png

save.png

search.png

Note: I highly recommended putting images into Drawable folder.

-Expand Resources Folder

~Right Click Drawable folder --> Add --> Add Files…

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="fill_parent"
android:layout_height="fill_parent">
<LinearLayout
android:orientation="horizontal"
android:minWidth="25px"
android:minHeight="25px"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:background="#ff004150">
<TextView
android:text="Name"
android:gravity="center"
android:layout_width="wrap_content"
android:layout_height="fill_parent"
android:layout_weight="1"
android:textSize="20dp"
android:textStyle="bold"
android:textColor="#ffffffff" />
<TextView
android:text="Last Name"
android:gravity="center"
android:layout_width="wrap_content"
android:layout_height="fill_parent"
android:layout_weight="1"
android:textSize="20dp"
android:textColor="#ffffffff"
android:textStyle="bold" />
<TextView
android:text="Age"
android:gravity="center"
android:layout_width="wrap_content"
android:layout_height="fill_parent"
android:layout_weight="1"
android:textSize="20dp"
android:textStyle="bold"
android:textColor="#ffffffff" />
</LinearLayout>
<LinearLayout
android:orientation="horizontal"
android:minWidth="25px"
android:minHeight="25px"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:background="#ff004185">
<EditText
android:inputType="textPersonName"
android:id="@+id/txtName"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1" />
<EditText
android:id="@+id/txtLastName"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1" />
<EditText
android:inputType="number"
android:id="@+id/txtAge"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1" />
</LinearLayout>
<LinearLayout
android:orientation="horizontal"
android:minWidth="25px"
android:minHeight="25px"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:paddingLeft="10dp"
android:background="#ff004185"
android:gravity="center">
<ImageButton
android:layout_width="50dp"
android:paddingLeft="10dp"
android:layout_height="50dp"
android:background="@drawable/add"
android:id="@+id/imgAdd"
android:layout_marginLeft="5dp"
android:layout_marginRight="10dp" />
<ImageButton
android:layout_width="50dp"
android:paddingLeft="10dp"
android:layout_height="50dp"
android:background="@drawable/save"
android:id="@+id/imgEdit"
android:layout_marginLeft="10dp"
android:layout_marginRight="10dp" />
<ImageButton
android:layout_width="50dp"
android:paddingLeft="10dp"
android:layout_height="50dp"
android:background="@drawable/delete"
android:id="@+id/imgDelete"
android:layout_marginLeft="10dp"
android:layout_marginRight="10dp" />
<ImageButton
android:layout_width="50dp"
android:paddingLeft="10dp"
android:layout_height="50dp"
android:background="@drawable/search"
android:id="@+id/imgSearch"
android:layout_marginLeft="10dp"
android:layout_marginRight="10dp" />
</LinearLayout>
<TextView
android:text="Message"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:id="@+id/shMsg"
android:background="#ff004185"
android:textColor="#ffffffff"
android:textStyle="bold"
android:textSize="15dp"
android:gravity="center" />
<LinearLayout
android:orientation="horizontal"
android:minWidth="25px"
android:minHeight="25px"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:paddingLeft="10dp"
android:background="#ff004150"
android:gravity="center">
<TextView
android:text="ID"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:textColor="@android:color/white"
android:textSize="20dp"
android:layout_weight="1"
android:gravity="center"
android:id="@+id/id" />
<TextView
android:text="Name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:textColor="@android:color/white"
android:gravity="center"
android:textSize="20dp"
android:layout_weight="1"
android:id="@+id/name" />
<TextView
android:text="Last Name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:textColor="@android:color/white"
android:layout_weight="1"
android:gravity="center"
android:textSize="20dp"
android:id="@+id/last" />
<TextView
android:text="Age"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:textColor="@android:color/white"
android:layout_weight="1"
android:textSize="20dp"
android:gravity="center"
android:id="@+id/age" />
</LinearLayout>
<ListView
android:minWidth="25px"
android:minHeight="25px"
android:layout_width="fill_parent"
android:layout_height="match_parent"
android:paddingLeft="10dp"
android:id="@+id/listItems" />
</LinearLayout>

Main Activity class

4. Double Click Main Activity (MainActivity.cs)

We have to get object instances from main layout and provide them with an event. Main events will be Add, Edit, Delete and Search for the image buttons we have defined. We have to populate our ListView object with the data stored in the database or create a new one in case it does not exist.

namespace BD_Demo
{
//Main activity for app launching
[Activity (Label = "BD_Demo", MainLauncher = true)]
public class MainActivity : Activity
{
//Database class new object
Database sqldb;
//Name, LastName and Age EditText objects for data input
EditText txtName, txtAge, txtLastName;
//Message TextView object for displaying data
TextView shMsg;
//Add, Edit, Delete and Search ImageButton objects for events handling
ImageButton imgAdd, imgEdit, imgDelete, imgSearch;
//ListView object for displaying data from database
ListView listItems;
//Launches the Create event for app
protected override void OnCreate (Bundle bundle)
{
base.OnCreate (bundle);
//Set our Main layout as default view
SetContentView (Resource.Layout.Main);
//Initializes new Database class object
sqldb = new Database("person_db");
//Gets ImageButton object instances
imgAdd = FindViewById<ImageButton> (Resource.Id.imgAdd);
imgDelete = FindViewById<ImageButton> (Resource.Id.imgDelete);
imgEdit = FindViewById<ImageButton> (Resource.Id.imgEdit);
imgSearch = FindViewById<ImageButton> (Resource.Id.imgSearch);
//Gets EditText object instances
txtAge = FindViewById<EditText> (Resource.Id.txtAge);
txtLastName = FindViewById<EditText> (Resource.Id.txtLastName);
txtName = FindViewById<EditText> (Resource.Id.txtName);
//Gets TextView object instances
shMsg = FindViewById<TextView> (Resource.Id.shMsg);
//Gets ListView object instance
listItems = FindViewById<ListView> (Resource.Id.listItems);
//Sets Database class message property to shMsg TextView instance
shMsg.Text = sqldb.Message;
//Creates ImageButton click event for imgAdd, imgEdit, imgDelete and imgSearch
imgAdd.Click += delegate {
//Calls function AddRecord for adding a new record
sqldb.AddRecord (txtName.Text, txtLastName.Text, int.Parse (txtAge.Text));
shMsg.Text = sqldb.Message;
txtName.Text = txtAge.Text = txtLastName.Text = "";
GetCursorView();
};imgEdit.Click += delegate {
int iId = int.Parse(shMsg.Text);
//Calls UpdateRecord function for updating an existing record
sqldb.UpdateRecord (iId, txtName.Text, txtLastName.Text, int.Parse (txtAge.Text));
shMsg.Text = sqldb.Message;
txtName.Text = txtAge.Text = txtLastName.Text = "";
GetCursorView();
};imgDelete.Click += delegate {
int iId = int.Parse(shMsg.Text);
//Calls DeleteRecord function for deleting the record associated to id parameter
sqldb.DeleteRecord (iId);
shMsg.Text = sqldb.Message;
txtName.Text = txtAge.Text = txtLastName.Text = "";
GetCursorView();
};imgSearch.Click += delegate {
//Calls GetCursorView function for searching all records or single record according to search criteria
string sqldb_column = "";
if (txtName.Text.Trim () != "")
{
sqldb_column = "Name";
GetCursorView (sqldb_column, txtName.Text.Trim ());
} else
if (txtLastName.Text.Trim () != "")
{
sqldb_column = "LastName";
GetCursorView (sqldb_column, txtLastName.Text.Trim ());
} else
if (txtAge.Text.Trim () != "")
{
sqldb_column = "Age";
GetCursorView (sqldb_column, txtAge.Text.Trim ());
} else
{
GetCursorView ();
sqldb_column = "All";
}
shMsg.Text = "Search " + sqldb_column + ".";
};
//Add ItemClick event handler to ListView instance
listItems.ItemClick += new EventHandler<AdapterView.ItemClickEventArgs> (item_Clicked);
}
//Launched when a ListView item is clicked
void item_Clicked (object sender, AdapterView.ItemClickEventArgs e)
{
//Gets TextView object instance from record_view layout
TextView shId = e.View.FindViewById<TextView> (Resource.Id.Id_row);
TextView shName = e.View.FindViewById<TextView> (Resource.Id.Name_row);
TextView shLastName = e.View.FindViewById<TextView> (Resource.Id.LastName_row);
TextView shAge = e.View.FindViewById<TextView> (Resource.Id.Age_row);
//Reads values and sets to EditText object instances
txtName.Text = shName.Text;
txtLastName.Text = shLastName.Text;
txtAge.Text = shAge.Text;
//Displays messages for CRUD operations
shMsg.Text = shId.Text;
}
//Gets the cursor view to show all records
void GetCursorView()
{
Android.Database.ICursor sqldb_cursor = sqldb.GetRecordCursor ();
if (sqldb_cursor != null)
{
sqldb_cursor.MoveToFirst ();
string[] from = new string[] {"_id","Name","LastName","Age" };
int[] to = new int[] {
Resource.Id.Id_row,
Resource.Id.Name_row,
Resource.Id.LastName_row,
Resource.Id.Age_row
};
//Creates a SimplecursorAdapter for ListView object
SimpleCursorAdapter sqldb_adapter = new SimpleCursorAdapter (this, Resource.Layout.record_view, sqldb_cursor, from, to);
listItems.Adapter = sqldb_adapter;
}
else
{
shMsg.Text = sqldb.Message;
}
}
//Gets the cursor view to show records according to search criteria
void GetCursorView (string sqldb_column, string sqldb_value)
{
Android.Database.ICursor sqldb_cursor = sqldb.GetRecordCursor (sqldb_column, sqldb_value);if (sqldb_cursor != null)
{
sqldb_cursor.MoveToFirst ();
string[] from = new string[] {"_id","Name","LastName","Age" };
int[] to = new int[]
{
Resource.Id.Id_row,
Resource.Id.Name_row,
Resource.Id.LastName_row,
Resource.Id.Age_row
};
SimpleCursorAdapter sqldb_adapter = new SimpleCursorAdapter (this, Resource.Layout.record_view, sqldb_cursor, from, to);
listItems.Adapter = sqldb_adapter;
}
else
{
shMsg.Text = sqldb.Message;
}
}
}
}

5. Build solution and run

Solution and run

Ok, so that's it for today! I hope that this tutorial has been useful. Feel free to make any questions, complaints, suggestions or comments.

Happy coding!

About the Author

Francisco Nieves is a current Computer Systems Engineering student with more than 2 years of experience in .NET development. He currently works at iTexico as a Xamarin Mobile Developer for mobile app development projects.

Oscar Salas

Written by Oscar Salas

Oscar Salas is a B2B Digital Marketing Specialist with 5 years of experience, who has helped organizations to grow and expand through strategic brand development and marketing programs. Analytical thinker, cat lover, he enjoys to play the piano and listening to Led Zeppelin He's currently leading the iTexico Demand Gen strategies.

Explore iTexico, The Nearshore + Company 

Explore The Nearshore Services

Read More

Mobile Competency Center

Recent Posts