Xamarin.Android - SQLite Database

Introduction

 

In this article, I shall show you how to handle all database CRUD operations using SQLite. SQLite is an open-source relational database used to perform database operations on Android devices such as storing, manipulating, or retrieving persistent data from the database.

 

Prerequisites

• Visual Studio 2017
• SQLite.Net NuGet Package

The steps given below are required to be followed in order to create an SQLite operations app in Xamarin.Android, using Visual Studio.

 

Step 1 - Create a Project

 

Open Visual Studio and go to New Project-> Templates-> Visual C#-> Android-> Blank app. Give it a name, like SQLiteDB.

 

 

 

Step 2 - Install Sqlite.net Packages

 

Go to Solution Explorer-> Project Name-> References. Then, right-click to "Manage NuGet Packages" and search for SQLite.Net. Install the sqlite.net package.

 

 

 

Step 3 - Writing Person Class

 

Before you go further, you need to write your Person class with all the getter and setter methods to maintain single person as an object. Go to Solution Explorer-> Project Name and right-click. Select Add -> New Item-> Class. Give it a name as Person.cs and write the following code.

 

(File Name: Person.cs)

 

C# Code

using SQLite;
namespace SQLiteDB.Resources.Model
{
    public class Person
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Department { get; set; }
        public string Email { get; set; }
    }
}

Step 4 - Design Layout 

 

Open Solution Explorer-> Project Name-> Resources-> Layout-> Main.axml. Open this main layout file and add the following code.

 

(File Name: Main.axml)

(Folder Name: Layout)

 

XML Code 

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="match_parent"
    android:layout_height="match_parent">
    <EditText
        android:hint="Enter your Name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/edtName"/>
    <EditText
        android:hint="Enter your Department"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/edtDepart" />
    <EditText
        android:hint="Enter your Email"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/edtEmail"/>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">
        <Button
            android:id="@+id/btnAdd"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Add"
            android:layout_weight="1"/>
        <Button
            android:id="@+id/btnEdit"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Update"
            android:layout_weight="1"/>
        <Button
            android:id="@+id/btnRemove"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Remove"
            android:layout_weight="1"/>
    </LinearLayout>
    <ListView
        android:minWidth="25px"
        android:minHeight="25px"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:id="@+id/listView"/>
</LinearLayout>

Step 5

 

Next, add a new Layout, go to Solution Explorer-> Project Name-> Resources-> Layout-> Right click to add a new item, select Layout, give it a name as list_view.axml. Open this layout file and add the following code.

 

(File Name: list_view.axml)

(Folder Name: Layout)

 

XML Code

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="match_parent"
    android:layout_height="match_parent">
    <TextView
        android:text="Name"
        android:textAppearance="?android:attr/textAppearanceLarge"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/txtView_Name" />
    <TextView
        android:text="Department"
        android:textAppearance="?android:attr/textAppearanceMedium"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/txtView_Depart" />
    <TextView
        android:text="Email"
        android:textAppearance="?android:attr/textAppearanceMedium"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/txtView_Email" />
</LinearLayout>

Step 6 - Writing SQLite Database Handler Class

 

We need to write our own class to handle all database CRUD (Create, Read, Update, and Delete) operations. Go to Solution Explorer-> Project Name and right-click. Select Add -> New Item-> Class. Give it a name as Database.cs and write the following code.

 

(File Name: Database.cs)

 

C# Code

using Android.Util;
using SQLite;
using SQLiteDB.Resources.Model;
using System.Collections.Generic;
using System.Linq;
namespace SQLiteDB.Resources.Helper
{
    public class Database
    {
        string folder = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
        public bool createDatabase()
        {
            try
            {
                using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))
                {
                    connection.CreateTable<Person>();
                    return true;
                }
            }
            catch(SQLiteException ex)
            {
                Log.Info("SQLiteEx", ex.Message);
                return false;
            }
        }
         //Add or Insert Operation

        public bool insertIntoTable(Person person)
        {
            try
            {
                using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))
                {
                    connection.Insert(person);
                    return true;
                }
            }
            catch (SQLiteException ex)
            {
                Log.Info("SQLiteEx", ex.Message);
                return false;
            }
        }
        public List<Person> selectTable()
        {
            try
            {
                using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))
                {
                    return connection.Table<Person>().ToList();
                }
            }
            catch (SQLiteException ex)
            {
                Log.Info("SQLiteEx", ex.Message);
                return null;
            }
        }
         //Edit Operation

        public bool updateTable(Person person)
        {
            try
            {
                using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))
                {
                    connection.Query<Person>("UPDATE Person set Name=?, Department=?, Email=? Where Id=?",person.Name, person.Department, person.Email, person.Id);
                    return true;
                }
            }
            catch (SQLiteException ex)
            {
                Log.Info("SQLiteEx", ex.Message);
                return false;
            }
        }
         //Delete Data Operation

        public bool removeTable(Person person)
        {
            try
            {
                using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))
                {
                    connection.Delete(person);
                    return true;
                }
            }
            catch (SQLiteException ex)
            {
                Log.Info("SQLiteEx", ex.Message);
                return false;
            }
        }
         //Select Operation

        public bool selectTable(int Id)
        {
            try
            {
                using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))
                {
                    connection.Query<Person>("SELECT * FROM Person Where Id=?",Id);
                    return true;
                }
            }
            catch (SQLiteException ex)
            {
                Log.Info("SQLiteEx", ex.Message);
                return false;
            }
        }
    }
}

Step 7 - List View Adapter

 

Next, add a new class. Go to Solution Explorer-> Project Name and right-click Add -> New Item-> Class. Give it a name like ListViewAdapter.cs, and write the following code.

 

(FileName: ListViewAdapter)

 

C# Code

using Android.App;
using Android.Views;
using Android.Widget;
using System.Collections.Generic;
namespace SQLiteDB.Resources.Model
{
    public class ViewHolder: Java.Lang.Object
    {
        public TextView txtName { get; set; }
        public TextView txtDepartment { get; set; }
        public TextView txtEmail { get; set; }
    }
    public class ListViewAdapter : BaseAdapter
    {
        private Activity activity;
        private List<Person> listPerson;
        public ListViewAdapter(Activity activity, List<Person> listPerson)
        {
            this.activity = activity;
            this.listPerson = listPerson;
        }
        public override int Count
        {
            get { return listPerson.Count;}
        }
        public override Java.Lang.Object GetItem(int position)
        {
            return null;
        }
        public override long GetItemId(int position)
        {
            return listPerson[position].Id;
        }
        public override View GetView(int position, View convertView, ViewGroup parent)
        {
            var view = convertView ?? activity.LayoutInflater.Inflate(Resource.Layout.list_view, parent, false);
            var txtName = view.FindViewById<TextView>(Resource.Id.txtView_Name);
            var txtDepart = view.FindViewById<TextView>(Resource.Id.txtView_Depart);
            var txtEmail = view.FindViewById<TextView>(Resource.Id.txtView_Email);
            txtName.Text = listPerson[position].Name;
            txtDepart.Text = listPerson[position].Department;
            txtEmail.Text = listPerson[position].Email;
            return view;
        }
    }
}

Step 8 - Main Activity

 

Lastly, go to Solution Explorer-> Project Name-> MainActivity and add the following code to main activity with appropriate namespaces.

(FileName: MainActivity)

 

C# Code

using Android.App;
using Android.OS;
using Android.Widget;
using SQLiteDB.Resources.Helper;
using SQLiteDB.Resources.Model;
using System.Collections.Generic;
namespace SQLiteDB
{
    [Activity(Label = "SQLiteDB", MainLauncher = true)]
    public class MainActivity : Activity
    {
        ListView lstViewData;
        List<Person> listSource = new List<Person>();
        Database db;
        protected override void OnCreate(Bundle savedInstanceState)
        {
            base.OnCreate(savedInstanceState);
            // Set our view from the "main" layout resource
            SetContentView(Resource.Layout.Main);
            //Create Database
            db = new Database();
            db.createDatabase();
            lstViewData = FindViewById<ListView>(Resource.Id.listView);
            var edtName = FindViewById<EditText>(Resource.Id.edtName);
            var edtDepart = FindViewById<EditText>(Resource.Id.edtDepart);
            var edtEmail = FindViewById<EditText>(Resource.Id.edtEmail);
            var btnAdd = FindViewById<Button>(Resource.Id.btnAdd);
            var btnEdit = FindViewById<Button>(Resource.Id.btnEdit);
            var btnRemove = FindViewById<Button>(Resource.Id.btnRemove);
            //Load Data
            LoadData();
            //Event
            btnAdd.Click += delegate
            {
                Person person = new Person()
                {
                    Name = edtName.Text,
                    Department = edtDepart.Text,
                    Email = edtEmail.Text
                };
                db.insertIntoTable(person);
                LoadData();
            };
            btnEdit.Click += delegate
            {
                Person person = new Person()
                {
                    Id = int.Parse(edtName.Tag.ToString()),
                    Name = edtName.Text,
                    Department = edtDepart.Text,
                    Email = edtEmail.Text
                };
                db.updateTable(person);
                LoadData();
            };
            btnRemove.Click += delegate
            {
                Person person = new Person()
                {
                    Id = int.Parse(edtName.Tag.ToString()),
                    Name = edtName.Text,
                    Department = edtDepart.Text,
                    Email = edtEmail.Text
                };
                db.removeTable(person);
                LoadData();
            };
            lstViewData.ItemClick += (s,e) =>
            {
                //Set Backround for selected item
                for (int i = 0; i < lstViewData.Count; i++)
                {
                    if (e.Position == i)
                        lstViewData.GetChildAt(i).SetBackgroundColor(Android.Graphics.Color.Black);
                    else
                        lstViewData.GetChildAt(i).SetBackgroundColor(Android.Graphics.Color.Transparent);
                }
                //Binding Data
                var txtName = e.View.FindViewById<TextView>(Resource.Id.txtView_Name);
                var txtDepart = e.View.FindViewById<TextView>(Resource.Id.txtView_Depart);
                var txtEmail = e.View.FindViewById<TextView>(Resource.Id.txtView_Email);
                edtEmail.Text = txtName.Text;
                edtName.Tag = e.Id;
                edtDepart.Text = txtDepart.Text;
                edtEmail.Text = txtEmail.Text;
            };
        }
        private void LoadData()
        {
            listSource = db.selectTable();
            var adapter = new ListViewAdapter(this, listSource);
            lstViewData.Adapter = adapter;
        }
    }
}

Output

 

 

Summary

 

This was the process of creating an app for storing and removing Personnel Info in SQLite database in Xamarin.Android, using Visual Studio. Please share your comments and feedback.