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
  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
  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
  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
  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.