Xamarin

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
  1. using SQLite;  
  2. namespace SQLiteDB.Resources.Model  
  3. {  
  4.     public class Person  
  5.     {  
  6.         [PrimaryKey, AutoIncrement]  
  7.         public int Id { get; set; }  
  8.         public string Name { get; set; }  
  9.         public string Department { get; set; }  
  10.         public string Email { get; set; }  
  11.     }  
  12. }  
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 
  1. <?xml version="1.0" encoding="utf-8"?>  
  2. <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"  
  3.     android:orientation="vertical"  
  4.     android:layout_width="match_parent"  
  5.     android:layout_height="match_parent">  
  6.     <EditText  
  7.         android:hint="Enter your Name"  
  8.         android:layout_width="match_parent"  
  9.         android:layout_height="wrap_content"  
  10.         android:id="@+id/edtName"/>  
  11.     <EditText  
  12.         android:hint="Enter your Department"  
  13.         android:layout_width="match_parent"  
  14.         android:layout_height="wrap_content"  
  15.         android:id="@+id/edtDepart" />  
  16.     <EditText  
  17.         android:hint="Enter your Email"  
  18.         android:layout_width="match_parent"  
  19.         android:layout_height="wrap_content"  
  20.         android:id="@+id/edtEmail"/>  
  21.     <LinearLayout  
  22.         android:layout_width="match_parent"  
  23.         android:layout_height="wrap_content"  
  24.         android:orientation="horizontal">  
  25.         <Button  
  26.             android:id="@+id/btnAdd"  
  27.             android:layout_width="wrap_content"  
  28.             android:layout_height="wrap_content"  
  29.             android:text="Add"  
  30.             android:layout_weight="1"/>  
  31.         <Button  
  32.             android:id="@+id/btnEdit"  
  33.             android:layout_width="wrap_content"  
  34.             android:layout_height="wrap_content"  
  35.             android:text="Update"  
  36.             android:layout_weight="1"/>  
  37.         <Button  
  38.             android:id="@+id/btnRemove"  
  39.             android:layout_width="wrap_content"  
  40.             android:layout_height="wrap_content"  
  41.             android:text="Remove"  
  42.             android:layout_weight="1"/>  
  43.     </LinearLayout>  
  44.     <ListView  
  45.         android:minWidth="25px"  
  46.         android:minHeight="25px"  
  47.         android:layout_width="match_parent"  
  48.         android:layout_height="match_parent"  
  49.         android:id="@+id/listView"/>  
  50. </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
  1. <?xml version="1.0" encoding="utf-8"?>  
  2. <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"  
  3.     android:orientation="vertical"  
  4.     android:layout_width="match_parent"  
  5.     android:layout_height="match_parent">  
  6.     <TextView  
  7.         android:text="Name"  
  8.         android:textAppearance="?android:attr/textAppearanceLarge"  
  9.         android:layout_width="match_parent"  
  10.         android:layout_height="wrap_content"  
  11.         android:id="@+id/txtView_Name" />  
  12.     <TextView  
  13.         android:text="Department"  
  14.         android:textAppearance="?android:attr/textAppearanceMedium"  
  15.         android:layout_width="match_parent"  
  16.         android:layout_height="wrap_content"  
  17.         android:id="@+id/txtView_Depart" />  
  18.     <TextView  
  19.         android:text="Email"  
  20.         android:textAppearance="?android:attr/textAppearanceMedium"  
  21.         android:layout_width="match_parent"  
  22.         android:layout_height="wrap_content"  
  23.         android:id="@+id/txtView_Email" />  
  24. </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
  1. using Android.Util;  
  2. using SQLite;  
  3. using SQLiteDB.Resources.Model;  
  4. using System.Collections.Generic;  
  5. using System.Linq;  
  6. namespace SQLiteDB.Resources.Helper  
  7. {  
  8.     public class Database  
  9.     {  
  10.         string folder = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);  
  11.         public bool createDatabase()  
  12.         {  
  13.             try  
  14.             {  
  15.                 using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))  
  16.                 {  
  17.                     connection.CreateTable<Person>();  
  18.                     return true;  
  19.                 }  
  20.             }  
  21.             catch(SQLiteException ex)  
  22.             {  
  23.                 Log.Info("SQLiteEx", ex.Message);  
  24.                 return false;  
  25.             }  
  26.         }  
  27.          //Add or Insert Operation  
  28.   
  29.         public bool insertIntoTable(Person person)  
  30.         {  
  31.             try  
  32.             {  
  33.                 using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))  
  34.                 {  
  35.                     connection.Insert(person);  
  36.                     return true;  
  37.                 }  
  38.             }  
  39.             catch (SQLiteException ex)  
  40.             {  
  41.                 Log.Info("SQLiteEx", ex.Message);  
  42.                 return false;  
  43.             }  
  44.         }  
  45.         public List<Person> selectTable()  
  46.         {  
  47.             try  
  48.             {  
  49.                 using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))  
  50.                 {  
  51.                     return connection.Table<Person>().ToList();  
  52.                 }  
  53.             }  
  54.             catch (SQLiteException ex)  
  55.             {  
  56.                 Log.Info("SQLiteEx", ex.Message);  
  57.                 return null;  
  58.             }  
  59.         }  
  60.          //Edit Operation  
  61.   
  62.         public bool updateTable(Person person)  
  63.         {  
  64.             try  
  65.             {  
  66.                 using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))  
  67.                 {  
  68.                     connection.Query<Person>("UPDATE Person set Name=?, Department=?, Email=? Where Id=?",person.Name, person.Department, person.Email, person.Id);  
  69.                     return true;  
  70.                 }  
  71.             }  
  72.             catch (SQLiteException ex)  
  73.             {  
  74.                 Log.Info("SQLiteEx", ex.Message);  
  75.                 return false;  
  76.             }  
  77.         }  
  78.          //Delete Data Operation  
  79.   
  80.         public bool removeTable(Person person)  
  81.         {  
  82.             try  
  83.             {  
  84.                 using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))  
  85.                 {  
  86.                     connection.Delete(person);  
  87.                     return true;  
  88.                 }  
  89.             }  
  90.             catch (SQLiteException ex)  
  91.             {  
  92.                 Log.Info("SQLiteEx", ex.Message);  
  93.                 return false;  
  94.             }  
  95.         }  
  96.          //Select Operation  
  97.   
  98.         public bool selectTable(int Id)  
  99.         {  
  100.             try  
  101.             {  
  102.                 using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))  
  103.                 {  
  104.                     connection.Query<Person>("SELECT * FROM Person Where Id=?",Id);  
  105.                     return true;  
  106.                 }  
  107.             }  
  108.             catch (SQLiteException ex)  
  109.             {  
  110.                 Log.Info("SQLiteEx", ex.Message);  
  111.                 return false;  
  112.             }  
  113.         }  
  114.     }  
  115. }  
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
  1. using Android.App;  
  2. using Android.Views;  
  3. using Android.Widget;  
  4. using System.Collections.Generic;  
  5. namespace SQLiteDB.Resources.Model  
  6. {  
  7.     public class ViewHolder: Java.Lang.Object  
  8.     {  
  9.         public TextView txtName { get; set; }  
  10.         public TextView txtDepartment { get; set; }  
  11.         public TextView txtEmail { get; set; }  
  12.     }  
  13.     public class ListViewAdapter : BaseAdapter  
  14.     {  
  15.         private Activity activity;  
  16.         private List<Person> listPerson;  
  17.         public ListViewAdapter(Activity activity, List<Person> listPerson)  
  18.         {  
  19.             this.activity = activity;  
  20.             this.listPerson = listPerson;  
  21.         }  
  22.         public override int Count  
  23.         {  
  24.             get { return listPerson.Count;}  
  25.         }  
  26.         public override Java.Lang.Object GetItem(int position)  
  27.         {  
  28.             return null;  
  29.         }  
  30.         public override long GetItemId(int position)  
  31.         {  
  32.             return listPerson[position].Id;  
  33.         }  
  34.         public override View GetView(int position, View convertView, ViewGroup parent)  
  35.         {  
  36.             var view = convertView ?? activity.LayoutInflater.Inflate(Resource.Layout.list_view, parent, false);  
  37.             var txtName = view.FindViewById<TextView>(Resource.Id.txtView_Name);  
  38.             var txtDepart = view.FindViewById<TextView>(Resource.Id.txtView_Depart);  
  39.             var txtEmail = view.FindViewById<TextView>(Resource.Id.txtView_Email);  
  40.             txtName.Text = listPerson[position].Name;  
  41.             txtDepart.Text = listPerson[position].Department;  
  42.             txtEmail.Text = listPerson[position].Email;  
  43.             return view;  
  44.         }  
  45.     }  
  46. }  
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
  1. using Android.App;  
  2. using Android.OS;  
  3. using Android.Widget;  
  4. using SQLiteDB.Resources.Helper;  
  5. using SQLiteDB.Resources.Model;  
  6. using System.Collections.Generic;  
  7. namespace SQLiteDB  
  8. {  
  9.     [Activity(Label = "SQLiteDB", MainLauncher = true)]  
  10.     public class MainActivity : Activity  
  11.     {  
  12.         ListView lstViewData;  
  13.         List<Person> listSource = new List<Person>();  
  14.         Database db;  
  15.         protected override void OnCreate(Bundle savedInstanceState)  
  16.         {  
  17.             base.OnCreate(savedInstanceState);  
  18.             // Set our view from the "main" layout resource  
  19.             SetContentView(Resource.Layout.Main);  
  20.             //Create Database  
  21.             db = new Database();  
  22.             db.createDatabase();  
  23.             lstViewData = FindViewById<ListView>(Resource.Id.listView);  
  24.             var edtName = FindViewById<EditText>(Resource.Id.edtName);  
  25.             var edtDepart = FindViewById<EditText>(Resource.Id.edtDepart);  
  26.             var edtEmail = FindViewById<EditText>(Resource.Id.edtEmail);  
  27.             var btnAdd = FindViewById<Button>(Resource.Id.btnAdd);  
  28.             var btnEdit = FindViewById<Button>(Resource.Id.btnEdit);  
  29.             var btnRemove = FindViewById<Button>(Resource.Id.btnRemove);  
  30.             //Load Data  
  31.             LoadData();  
  32.             //Event  
  33.             btnAdd.Click += delegate  
  34.             {  
  35.                 Person person = new Person()  
  36.                 {  
  37.                     Name = edtName.Text,  
  38.                     Department = edtDepart.Text,  
  39.                     Email = edtEmail.Text  
  40.                 };  
  41.                 db.insertIntoTable(person);  
  42.                 LoadData();  
  43.             };  
  44.             btnEdit.Click += delegate  
  45.             {  
  46.                 Person person = new Person()  
  47.                 {  
  48.                     Id = int.Parse(edtName.Tag.ToString()),  
  49.                     Name = edtName.Text,  
  50.                     Department = edtDepart.Text,  
  51.                     Email = edtEmail.Text  
  52.                 };  
  53.                 db.updateTable(person);  
  54.                 LoadData();  
  55.             };  
  56.             btnRemove.Click += delegate  
  57.             {  
  58.                 Person person = new Person()  
  59.                 {  
  60.                     Id = int.Parse(edtName.Tag.ToString()),  
  61.                     Name = edtName.Text,  
  62.                     Department = edtDepart.Text,  
  63.                     Email = edtEmail.Text  
  64.                 };  
  65.                 db.removeTable(person);  
  66.                 LoadData();  
  67.             };  
  68.             lstViewData.ItemClick += (s,e) =>  
  69.             {  
  70.                 //Set Backround for selected item  
  71.                 for (int i = 0; i < lstViewData.Count; i++)  
  72.                 {  
  73.                     if (e.Position == i)  
  74.                         lstViewData.GetChildAt(i).SetBackgroundColor(Android.Graphics.Color.Black);  
  75.                     else  
  76.                         lstViewData.GetChildAt(i).SetBackgroundColor(Android.Graphics.Color.Transparent);  
  77.                 }  
  78.                 //Binding Data  
  79.                 var txtName = e.View.FindViewById<TextView>(Resource.Id.txtView_Name);  
  80.                 var txtDepart = e.View.FindViewById<TextView>(Resource.Id.txtView_Depart);  
  81.                 var txtEmail = e.View.FindViewById<TextView>(Resource.Id.txtView_Email);  
  82.                 edtEmail.Text = txtName.Text;  
  83.                 edtName.Tag = e.Id;  
  84.                 edtDepart.Text = txtDepart.Text;  
  85.                 edtEmail.Text = txtEmail.Text;  
  86.             };  
  87.         }  
  88.         private void LoadData()  
  89.         {  
  90.             listSource = db.selectTable();  
  91.             var adapter = new ListViewAdapter(this, listSource);  
  92.             lstViewData.Adapter = adapter;  
  93.         }  
  94.     }  
  95. }  
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.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs