Android with SQLite Database Handling in MonoDevelop (Mono for Android, C#)

• Download source code - 222 KB

About SQLite

SQLite is a data engine running in Android and is the native database on Android. SQLite does not run on a separate machine; it runs on the same machine, Android, and runs in the same process as the application. SQLite is embedded in the application and linked to the app during the compilation process. Calls made to SQLite are not made over a network, but stay on the physical device. SQLite, a free application, uses SQL (Structured Query Language) to interact with it.

Creating a SQLite Database Application

Create a New Android Application solution in the MonoDevelop development tool

• Download Mono for Android

1. Right click on project--->Add new file-->Android Class (MyDatabase.cs)

The MyDatabaseDemo class is for handling the SQLiteDatabase object. Next we create other objects, and a method to handle the database table. I have created a constructor of MyDatabase() with zero and one argument. The one argument constructor MyDatabse(string sDatabaseName) gets a parameter database name, and calls the CreateDatabase (string sDatabaseName) method. The CreateDatabase (string sDatabaseName) method also checks if the database is available or not; if the database is not found then create and open for read/write operation.

namespace MyDatabaseDemo
{
    public class MyDatabase
    {
        /// <summary>
        /// SQLiteDatabase object sqldTemp to handle SQLiteDatabase.
        /// </summary>
        private SQLiteDatabase sqldTemp;
        /// <summary>
        /// The sSQLquery for query handling.
        /// </summary>
        private string sSQLQuery;
        /// <summary>
        /// The sMessage to hold message.
        /// </summary>
        private string sMessage;
        /// <summary>
        /// The bDBIsAvailable for database is available or not.
        /// </summary>
        private bool bDBIsAvailable;
        /// <summary>
        /// Initializes a new instance of the <see cref="MyDatabaseDemo.MyDatabase"/> class.
        /// </summary>
        public MyDatabase ()
        {
            sMessage = "";
            bDBIsAvailable = false;
        }
        /// <summary>
        /// Initializes a new instance of the <see cref="MyDatabaseDemo.MyDatabase"/> class.
        /// </summary>
        /// <param name='sDatabaseName'>
        /// Pass your database name.
        /// </param>
        public MyDatabase (string sDatabaseName)
        {
            try {
                sMessage = "";
                bDBIsAvailable = false;
                CreateDatabase (sDatabaseName);
            } catch (SQLiteException ex) {
                sMessage = ex.Message;
            }
        }
        /// <summary>
        /// Gets or sets a value indicating whether this
        ///    <see cref="MyDatabaseDemo.MyDatabase"/> database available.
        /// </summary>
        /// <value>
        /// <c>true</c> if database available; otherwise, <c>false</c>.
        /// </value>
        public bool DatabaseAvailable {
            get{ return bDBIsAvailable;}
            set{ bDBIsAvailable = value;}
        }
        /// <summary>
        /// Gets or sets the message.
        /// </summary>
        /// <value>
        /// The message.
        /// </value>
        public string Message {
            get{ return sMessage;}
            set{ sMessage = value;}
        }
        /// <summary>
        /// Creates the database.
        /// </summary>
        /// <param name='sDatabaseName'>
        /// Pass database name.
        /// </param>
        public void CreateDatabase (string sDatabaseName)
        {
            try {
                sMessage = "";
                string sLocation = 
                  System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal);
                string sDB = Path.Combine (sLocation, sDatabaseName);
                bool bIsExists = File.Exists (sDB);        
                if (!bIsExists) {
                    sqldTemp = SQLiteDatabase.OpenOrCreateDatabase (sDB, null);
                    sSQLQuery = "CREATE TABLE IF NOT EXISTS " +
                        "MyTable " +
                        "(_id INTEGER PRIMARY KEY AUTOINCREMENT,Name VARCHAR,Age INT,Country VARCHAR);";
                    sqldTemp.ExecSQL (sSQLQuery);
                    sMessage = "New database is created.";
                } else {
                    sqldTemp = SQLiteDatabase.OpenDatabase (sDB, null, DatabaseOpenFlags.OpenReadwrite);
                    sMessage = "Database is opened.";
                }
                bDBIsAvailable = true;
            } catch (SQLiteException ex) {
                sMessage = ex.Message;
            }
        }

Next I have created the methods AddRecord(), UpdateRecord(), DeleteRecord() to handle Table operations of the database. The methods allow to add, update, and delete records of a table. Simply pass the query for insert, update, and delete with parameters and call the ExecSQL() method of the SQLiteDatabase object to run the query.

/// <summary>
/// Adds the record.
/// </summary>
/// <param name='sName'>
/// Pass name.
/// </param>
/// <param name='iAge'>
/// Pass age.
/// </param>
/// <param name='sCountry'>
/// Pass country.
/// </param>
public void AddRecord (string sName, int iAge, string sCountry)
{
    try {
        sSQLQuery = "INSERT INTO " +
            "MyTable " +
            "(Name,Age,Country)" +
            "VALUES('" + sName + "'," + iAge + ",'" + sCountry + "');";
        sqldTemp.ExecSQL (sSQLQuery);
        sMessage = "Record is saved.";
    } catch (SQLiteException ex) {
        sMessage = ex.Message;
    }
}
/// <summary>
/// Updates the record.
/// </summary>
/// <param name='iId'>
/// Pass record ID.
/// </param>
/// <param name='sName'>
/// Pass name.
/// </param>
/// <param name='iAge'>
/// Pass age.
/// </param>
/// <param name='sCountry'>
/// Pass country.
/// </param>
public void UpdateRecord (int iId, string sName, int iAge, string sCountry)
{
    try {
        sSQLQuery = "UPDATE MyTable " +
            "SET Name='" + sName + "',Age='" + iAge + "',Country='" + sCountry + "' " +
            "WHERE _id='" + iId + "';";
        sqldTemp.ExecSQL (sSQLQuery);
        sMessage = "Record is updated: " + iId;
    } catch (SQLiteException ex) {
        sMessage = ex.Message;
    }
}
/// <summary>
/// Deletes the record.
/// </summary>
/// <param name='iId'>
/// Pass ID.
/// </param>
public void DeleteRecord (int iId)
{
    try {
        sSQLQuery = "DELETE FROM MyTable " +
            "WHERE _id='" + iId + "';";
        sqldTemp.ExecSQL (sSQLQuery);
        sMessage = "Record is deleted: " + iId;
    } catch (SQLiteException ex) {
        sMessage = ex.Message;
    }
}

Next we create methods GetRecordCursor() and GetRecordCursor(string sColumn, string sValue) for searching a record and return a cursor of Android.Database.ICursor. The method GetRecordCursor() allows to show all records in a table, and the next method GetRecordCursor(string sColumn, string sValue) allows to show records according to search criteria (Name, Age, Country).

/// <summary>
/// Gets the record cursor.
/// </summary>
/// <returns>
/// The record cursor.
/// </returns>
public Android.Database.ICursor GetRecordCursor ()
{
    Android.Database.ICursor icTemp = null;
    try {
        sSQLQuery = "SELECT * FROM MyTable;";
        icTemp = sqldTemp.RawQuery (sSQLQuery, null);
        if (!(icTemp != null)) {
            sMessage = "Record not found.";
        }
    } catch (SQLiteException ex) {
        sMessage = ex.Message;
    }
    return icTemp;
}
/// <summary>
/// Gets the record cursor by search criteria.
/// </summary>
/// <returns>
/// The record cursor.
/// </returns>
/// <param name='sColumn'>
/// column filed of MyTable is Name,Age,Country.
/// </param>
/// <param name='sValue'>
/// Value as user input.
/// </param>
public Android.Database.ICursor GetRecordCursor (string sColumn, string sValue)
{
    Android.Database.ICursor icTemp = null;
    try {
        sSQLQuery = "SELECT * FROM MyTable WHERE " + sColumn + " LIKE '" + sValue + "%';";
        icTemp = sqldTemp.RawQuery (sSQLQuery, null);
        if (!(icTemp != null)) {
            sMessage = "Record not found.";
        }
    } catch (SQLiteException ex) {
        sMessage = ex.Message;
    }
    return icTemp;
}
/// <summary>
/// Releases unmanaged resources and performs other cleanup operations before the
/// <see cref="MyDatabaseDemo.MyDatabase"/> is reclaimed by garbage collection.
/// </summary>
~MyDatabase ()
{
    try {
        sMessage = "";
        bDBIsAvailable = false;        
        sqldTemp.Close ();        
    } catch (SQLiteException ex) {
        sMessage = ex.Message;
    }
}

ListView layout to show record

2. Right click on project--->Add new file-->Android Layout (record_view.xaml)

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="horizontal"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent">
    <TextView
        android:text="ID"
        android:layout_width="50px"
        android:layout_height="wrap_content"
        android:id="@+id/tvIdShow" />
    <TextView
        android:text="Person"
        android:layout_width="125px"
        android:layout_height="wrap_content"
        android:id="@+id/tvPersonShow" />
    <TextView
        android:text="Age"
        android:layout_width="50px"
        android:layout_height="wrap_content"
        android:id="@+id/tvAgeShow" />
    <TextView
        android:text="Country"
        android:layout_width="125px"
        android:layout_height="wrap_content"
        android:id="@+id/tvCountryShow" />
</LinearLayout>

Next, a layout is needed for each ListView item. A ListView item usually displays information for a single object from your data source. A single ListView item is responsible for displaying information for a single record (id, name, age, country).

Update Main layout

3. Open-->Resources--->Layout-->Main Layout (Main.xaml)

Mono for Android automatically makes these IDs available to you for referencing in the Resource.Id class.

<?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">
    <TableLayout
        android:minWidth="25px"
        android:minHeight="25px"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_gravity="center_horizontal"
        android:id="@+id/tableLayout1">
        <TableRow
            android:id="@+id/tableRow1"
            android:layout_width="wrap_content"
            android:background="@drawable/bgh">
            <TextView
                android:text="Name"
                android:textAppearance="?android:attr/textAppearanceSmall"
                android:layout_width="115px"
                android:layout_height="20px"
                android:id="@+id/tvName" />
            <TextView
                android:text="Age"
                android:textAppearance="?android:attr/textAppearanceSmall"
                android:layout_width="70px"
                android:layout_height="20px"
                android:id="@+id/tvAge" />
            <TextView
                android:text="Country"
                android:textAppearance="?android:attr/textAppearanceSmall"
                android:layout_width="115px"
                android:layout_height="20px"
                android:id="@+id/tvCountry" />
        </TableRow>
        <TableRow
            android:background="@android:drawable/bottom_bar"
            android:id="@+id/tableRow2"
            android:layout_height="40dp">
            <EditText
                android:inputType="textPersonName"
                android:layout_width="115px"
                android:layout_height="wrap_content"
                android:id="@+id/txtName"
                android:layout_gravity="center_vertical" />
            <EditText
                android:inputType="number"
                android:layout_width="75px"
                android:layout_height="wrap_content"
                android:id="@+id/txtAge"
                android:layout_gravity="center_vertical" />
            <EditText
                android:layout_width="115px"
                android:layout_height="wrap_content"
                android:id="@+id/txtCountry"
                android:layout_gravity="center_vertical" />
        </TableRow>
        <TableRow
            android:background="@drawable/errorbg"
            android:id="@+id/tableRow3">
            <TextView
                android:layout_width="fill_parent"
                android:layout_height="wrap_content"
                android:id="@+id/tvMsg" />
        </TableRow>
    </TableLayout>
    <LinearLayout
        android:orientation="horizontal"
        android:minWidth="25px"
        android:minHeight="25px"
        android:layout_width="fill_parent"
        android:layout_height="35px"
        android:paddingLeft="10dp"
        android:id="@+id/llButtons"
        android:background="@android:drawable/bottom_bar">
        <ImageButton
            android:src="@android:drawable/ic_menu_gallery"
            android:layout_width="75px"
            android:paddingLeft="10dp"
            android:layout_height="wrap_content"
            android:background="@android:color/transparent"
            android:id="@+id/imgAdd" />
        <ImageButton
            android:src="@android:drawable/ic_menu_gallery"
            android:layout_width="75px"
            android:paddingLeft="10dp"
            android:layout_height="wrap_content"
            android:background="@android:color/transparent"
            android:id="@+id/imgEdit" />
        <ImageButton
            android:src="@android:drawable/ic_menu_gallery"
            android:layout_width="75px"
            android:paddingLeft="10dp"
            android:layout_height="wrap_content"
            android:background="@android:color/transparent"
            android:id="@+id/imgDelete" />
        <ImageButton
            android:src="@android:drawable/ic_menu_gallery"
            android:layout_width="75px"
            android:paddingLeft="10dp"
            android:layout_height="wrap_content"
            android:background="@android:color/transparent"
            android:id="@+id/imgFind" />
    </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:id="@+id/llHeader"
        android:background="@drawable/bgh">
        <TextView
            android:text="ID"
            android:layout_width="50px"
            android:layout_height="wrap_content"
            android:id="@+id/tvIdShowR"
            android:textColor="@android:color/white" />
        <TextView
            android:text="Name"
            android:layout_width="125px"
            android:layout_height="wrap_content"
            android:textColor="@android:color/white"
            android:id="@+id/tvPersonShowR" />
        <TextView
            android:text="Age"
            android:layout_width="50px"
            android:layout_height="wrap_content"
            android:textColor="@android:color/white"
            android:id="@+id/tvAgeShowR" />
        <TextView
            android:text="Country"
            android:layout_width="125px"
            android:layout_height="wrap_content"
            android:textColor="@android:color/white"
            android:id="@+id/tvCountryShowR" />
    </LinearLayout>
    <ListView
        android:minWidth="25px"
        android:minHeight="25px"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:paddingLeft="10dp"
        android:id="@+id/lvTemp" />
</LinearLayout>

Update Main Activity Class

4. Now open the file Activity1.cs and paste the below code.

Finally get the object instance from the layout and provide an event. Four image buttons have events to add, update, delete, and find record. ImageButtons get images from the resource folder. In the OnCreate method, I have created a ListView item click event handler ListView_ItemClick.

namespace MyDatabaseDemo
{
    /// <summary>
    /// Main Activity1 to start applicaton.
    /// </summary>
    [Activity (Label = "My Database Demo", MainLauncher = true)]
    public class Activity1 : Activity
    {
        /// <summary>
        /// The mdtemp is object of MyDatabase class.
        /// </summary>
        MyDatabase mdTemp;
        /// <summary>
        /// The txtName, txtAge, txtCountry are object of EditText.
        /// </summary>
        EditText txtName, txtAge, txtCountry;
        /// <summary>
        /// The tvMsg is object of TextView.
        /// </summary>
        TextView tvMsg;
        /// <summary>
        /// The imgAdd, imgEdit, imgDelete, imgFind are object of ImageButton
        /// </summary>
        ImageButton imgAdd, imgEdit, imgDelete, imgFind;
        /// <summary>
        /// Raises the create event for application.
        /// </summary>
        /// <param name='bundle'>
        /// Bundle.
        /// </param>
        protected override void OnCreate (Bundle bundle)
        {
            base.OnCreate (bundle);

            // Set our view from the "main" layout resource
            SetContentView (Resource.Layout.Main);

            // Get our button from the layout resource,
            // and attach an event to it

            // initialization of database class object.
            mdTemp = new MyDatabase ("person_db");
            // get ImageButton object instance from resource.
            imgAdd = FindViewById<ImageButton> (Resource.Id.imgAdd);
            imgEdit = FindViewById<ImageButton> (Resource.Id.imgEdit);
            imgDelete = FindViewById<ImageButton> (Resource.Id.imgDelete);
            imgFind = FindViewById<ImageButton> (Resource.Id.imgFind);
            // set images on image button from resource.
            imgAdd.SetImageResource (Resource.Drawable.add);
            imgEdit.SetImageResource (Resource.Drawable.save);
            imgDelete.SetImageResource (Resource.Drawable.delete);
            imgFind.SetImageResource (Resource.Drawable.find);
            // get EditText object instance from resource.
            txtName = FindViewById<EditText> (Resource.Id.txtName);
            txtAge = FindViewById<EditText> (Resource.Id.txtAge);
            txtCountry = FindViewById<EditText> (Resource.Id.txtCountry);
            tvMsg = FindViewById<TextView> (Resource.Id.tvMsg);

            tvMsg.Text = mdTemp.Message;
            // create ImageButton click event for imgAdd, imgEdit, imgDelete, imgFind. 
            imgAdd.Click += delegate {
                //database call add record function AddRecord().
                mdTemp.AddRecord (txtName.Text, int.Parse (txtAge.Text), txtCountry.Text);
                tvMsg.Text = mdTemp.Message;
                txtName.Text = txtAge.Text = txtCountry.Text = "";
            };

            imgEdit.Click += delegate {
                int iId = -1;
                int.TryParse (tvMsg.Text, out iId);
                //database call update record function UpdateRecord().
                mdTemp.UpdateRecord (iId, txtName.Text, int.Parse (txtAge.Text), txtCountry.Text);
                tvMsg.Text = mdTemp.Message;
                txtName.Text = txtAge.Text = txtCountry.Text = "";
            };
            imgDelete.Click += delegate {
                int iId = -1;
                int.TryParse (tvMsg.Text, out iId);
                //database call delete record function DeleteRecord().
                mdTemp.DeleteRecord (iId);
                tvMsg.Text = mdTemp.Message;
                txtName.Text = txtAge.Text = txtCountry.Text = "";
            };
            imgFind.Click += delegate {

                //database call search record function GetCursorView().
                string sColumnName = "";
                if (txtName.Text.Trim () != "") {
                    sColumnName = "by Name";
                    GetCursorView (sColumnName, txtName.Text.Trim ());
                } else
                if (txtAge.Text.Trim () != "") {
                    sColumnName = "by Age";
                    GetCursorView (sColumnName, txtAge.Text.Trim ());
                } else
                if (txtCountry.Text.Trim () != "") {
                    sColumnName = "by Country";
                    GetCursorView (sColumnName, txtCountry.Text.Trim ());
                } else {
                    GetCursorView ();
                    sColumnName = "All";
                }
                tvMsg.Text = "Search " + sColumnName + ".";
            };

            // get ListView object instance from resource and add ItemClick, EventHandler.
            ListView lvTemp = FindViewById<ListView> (Resource.Id.lvTemp);        
            lvTemp.ItemClick += new EventHandler<AdapterView.ItemClickEventArgs> (ListView_ItemClick);
        }

The ListView_ItemClick takes two arguments: object and AdapterView.ItemClickEventArgs. The second argument provides a public property View to get the list view layout item. For this we use a template method e.View.FindViewById and pass the class type as argument of the object and the function takes the resource id.

/// <summary>
/// Lists the view_ item click.
/// </summary>
/// <param name='sender'>
/// object sender.
/// </param>
/// <param name='e'>
/// ItemClickEventArgs e.
/// </param>
void ListView_ItemClick (object sender, AdapterView.ItemClickEventArgs e)
{
    // get TextView object instance from resource layout record_view.axml.
    TextView tvIdShow = e.View.FindViewById<TextView> (Resource.Id.tvIdShow);
    TextView tvPersonShow = e.View.FindViewById<TextView> (Resource.Id.tvPersonShow);
    TextView tvAgeShow = e.View.FindViewById<TextView> (Resource.Id.tvAgeShow);
    TextView tvCountryShow = e.View.FindViewById<TextView> (Resource.Id.tvCountryShow);
    // read value and wirte in EditText object.
    txtName.Text = tvPersonShow.Text;
    txtAge.Text = tvAgeShow.Text;
    txtCountry.Text = tvCountryShow.Text;
    //record id is write in TextView object to update or delete record.
    tvMsg.Text = tvIdShow.Text;
}

Next, in Activity1.cs, we have created two methods for filling records in ListView as output. The methods are GetCursorView() and GetCursorView(string sColumn, string sValue). The first method provides all records and the second method provides records according to the search parameter. To fill ListView, I have created a SimpleCursorAdapter object. A SimpleCursorAdapter can be used to quickly map cursor columns to different view types, such as TextViews or even ImageViews. These adapters require fewer steps to bind to an adapter view but do not have the same amount of flexibility.

/// <summary>
/// Gets the cursor view to show all record.
/// </summary>
protected void GetCursorView ()
{
    Android.Database.ICursor icTemp = mdTemp.GetRecordCursor ();
    if (icTemp != null) {
        icTemp.MoveToFirst ();
        ListView lvTemp = FindViewById<ListView> (Resource.Id.lvTemp);
        string[] from = new string[] {"_id","Name","Age","Country" };
        int[] to = new int[] {
            Resource.Id.tvIdShow,
            Resource.Id.tvPersonShow,
            Resource.Id.tvAgeShow,
            Resource.Id.tvCountryShow
        };
        // creating a SimpleCursorAdapter to fill ListView object.
        SimpleCursorAdapter scaTemp = new SimpleCursorAdapter (this, Resource.Layout.record_view, icTemp, from, to);
        lvTemp.Adapter = scaTemp;
    } else {
        tvMsg.Text = mdTemp.Message;
    }
}
/// <summary>
/// Gets the cursor view.
/// </summary>
/// <param name='sColumn'>
/// column filed of MyTable is Name,Age,Country.
/// </param>
/// <param name='sValue'>
/// Value as user input.
/// </param>
protected void GetCursorView (string sColumn, string sValue)
{
    Android.Database.ICursor icTemp = mdTemp.GetRecordCursor (sColumn, sValue);
    if (icTemp != null) {
        icTemp.MoveToFirst ();
        ListView lvTemp = FindViewById<ListView> (Resource.Id.lvTemp);
        string[] from = new string[] {"_id","Name","Age","Country" };
        int[] to = new int[] {
            Resource.Id.tvIdShow,
            Resource.Id.tvPersonShow,
            Resource.Id.tvAgeShow,
            Resource.Id.tvCountryShow
        };
        // creating a SimpleCursorAdapter to fill ListView object.
        SimpleCursorAdapter scaTemp = new SimpleCursorAdapter (this, Resource.Layout.record_view, icTemp, from, to);
        lvTemp.Adapter = scaTemp;
    } else {
        tvMsg.Text = mdTemp.Message;
    }
}

5. Rebuild solutions and run.