Download Code

Xamarin SQLite Database Example Android

Android platform provides different ways to store user data.In this tutorial you will learn how to store user data using Xamarin SQLite Database Example Android Application.You will use ListView , TextView , EditText , Toast components.

  • Insert
  • Display
  • Update
  • Delete

I have visit alot of website’s for SQLite database like valokafor.com ,  codeproject.com but they provide database with simple example.That’s why i decided to work on professional app like example. Let me give you some basic information about SQLite and Xamarin SQLite Database Exampe Android.

SQLite

  • SQLite is an in-process library
  • implements a self-contained, server less, zero-configuration, transactional SQL database engine.
  • The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private.

For more detail visit SQLite official website.

Xamarin SQLite Database

Exposes methods to manage a SQLitedatabase.

SQLite Database has methods to create, delete, execute SQL commands, and perform other common database management tasks.

SQLiteOpenHelper

SQLiteOpenHelper helper class to manage database creation and version management.

You create a subclass implementing onCreate(SQLiteDatabase), onUpgrade(SQLiteDatabase, int, int) and optionally onOpen(SQLite Database), and SQLiteOpenHelper class takes care of opening the database if it exists, creating it if it does not, and upgrading it as necessary. Transactions are used to make sure the database is always in a sensible state.

Let’s start create new Xamarin SQLite Database Example android application project.Create layouts with some components.

Main.axml

xamarin sqlite database,xamarin sqlite,xamarin sqlite database example,xamarin sqlite database example android,sqlite database example android,

insert.axml

xamarin sqlite database,xamarin sqlite,xamarin sqlite database example,xamarin sqlite database example android,sqlite database example android,

complete_data.axml

xamarin sqlite database,xamarin sqlite,xamarin sqlite database example,xamarin sqlite database example android,sqlite database example android,

update.axml

xamarin sqlite database,xamarin sqlite,xamarin sqlite database example,xamarin sqlite database example android,sqlite database example android,

delete.axml

xamarin sqlite database,xamarin sqlite,xamarin sqlite database example,xamarin sqlite database example android,sqlite database example android,

You are done with layouts.Now first create SQLite Helper class and extend with SQLiteOpenHelper class.

SQLiteHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.Database;
using Android.Database.Sqlite;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using Java.Util;

namespace LocalDatabase
{
	[Activity(Label = "SQLiteHelper")]
	public class SQLiteHelper : SQLiteOpenHelper
	{
		private static String DATABASE_NAME = "EmployeeManagement.db";

		public String EMPLOYEE_ID = "id";
		public String EMPLOYEE_NAME = "name";
		public String EMPLOYEE_EMAIL = "email";
		public String EMPLOYEE_PHONE = "phone";
		public String EMPLOYEE_DESIGNATION = "designation";
		//constructor to create database


		public SQLiteHelper(Context context) 
			: base(context, DATABASE_NAME, null, 2)
		{
			
		}

		public override void OnCreate(SQLiteDatabase db)
		{
			db.ExecSQL(
				"create table Employees " +
				"(id integer primary key, name text,email text,phone text,designation text)"
				);
		}

		public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
		{
			db.ExecSQL("DROP TABLE IF EXISTS Employees");
			OnCreate(db);
		}

		// insert data

		public bool insertEmployee(String name, String email, String phone, String designation)
		{
			SQLiteDatabase db = this.WritableDatabase;
			ContentValues contentValues = new ContentValues();
			contentValues.Put("name", name);
			contentValues.Put("phone", phone);
			contentValues.Put("email", email);
			contentValues.Put("designation", designation);

			db.Insert("Employees", null, contentValues);
			return true;
		}


		// get complete data/info

		public System.Collections.ArrayList getAllEmployeesData()
		{
			System.Collections.ArrayList array_list = new System.Collections.ArrayList();

			//hp = new HashMap();
			SQLiteDatabase db = this.ReadableDatabase;
			ICursor res = db.RawQuery("select * from Employees", null);
			res.MoveToFirst();

			while (res.IsAfterLast == false)
			{
				array_list.Add(res.GetString(res.GetColumnIndex(EMPLOYEE_NAME)));
				res.MoveToNext();
			}
			return array_list;
		}

		// get single entry
		public ICursor getSingleEntry(int id)
		{
			SQLiteDatabase db = this.ReadableDatabase;
			ICursor res = db.RawQuery("select * from Employees where id=" + id + "", null);
			return res;
		}
		// delete entry
		public int deleteEmployee(int id)
		{
			SQLiteDatabase db = this.WritableDatabase;
			return db.Delete("Employees",
			"id = ? ",new String[] { Convert.ToString(id) });
		}
		// update entry
		public bool updateEmployeeInfo(int id, String name, String email, String phone, String designation)
		{
			SQLiteDatabase db = this.WritableDatabase;
			ContentValues contentValues = new ContentValues();
			contentValues.Put("name", name);
			contentValues.Put("email", phone);
			contentValues.Put("phone", email);
			contentValues.Put("designation", designation);

			db.Update("Employees", contentValues, "id = ? ", new String[] { Convert.ToString(id) });
			return true;
		}
	}
}

MainActivity.cs

using Android.App;
using Android.Widget;
using Android.OS;
using Android.Views;
using System;
using Android.Content;

namespace LocalDatabase
{
	[Activity(Label = "LocalDatabase", MainLauncher = true, Icon = "@mipmap/icon")]
	public class MainActivity : Activity, Android.Views.View.IOnClickListener
	{
		Button iBtn, vBtn, uBtn, dBtn;

		protected override void OnCreate(Bundle savedInstanceState)
		{
			base.OnCreate(savedInstanceState);

			// Set our view from the "main" layout resource
			SetContentView(Resource.Layout.Main);
			initialize();
		}
		public void initialize()
		{
			iBtn = (Button)FindViewById(Resource.Id.insertBtn);
			vBtn = (Button)FindViewById(Resource.Id.viewBtn);
			uBtn = (Button)FindViewById(Resource.Id.updateBtn);
			dBtn = (Button)FindViewById(Resource.Id.deleteBtn);

			iBtn.SetOnClickListener(this);
			vBtn.SetOnClickListener(this);
			uBtn.SetOnClickListener(this);
			dBtn.SetOnClickListener(this);

		}

		public void OnClick(View v)
		{
			switch (v.Id)
			{
				case Resource.Id.insertBtn:
					var i = new Intent(this, typeof(insertEmployee));
					StartActivity(i);
					break;
				case Resource.Id.viewBtn:
					var vw = new Intent(this, typeof(showCompleteData));
					StartActivity(vw);
					break;
				case Resource.Id.updateBtn:
					var up = new Intent(this, typeof(updateEmployee));
					StartActivity(up);
					break;
				case Resource.Id.deleteBtn:
					var d = new Intent(this, typeof(deleteEmployee));
					StartActivity(d);
					break;

			}
		}
	}
}



insertEmployee.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;

namespace LocalDatabase
{
	[Activity(Label = "insertEmployee")]
	public class insertEmployee : Activity
	{
		EditText name, email, phone, designation;
		Button submitBtn;
		private SQLiteHelper dbHelper;

		protected override void OnCreate(Bundle savedInstanceState)
		{
			base.OnCreate(savedInstanceState);

			// Create your application here
			SetContentView(Resource.Layout.insert);
			initialize();
			dbHelper = new SQLiteHelper(this);
			submitBtn.Click += SubmitBtn_Click;
		}

		void SubmitBtn_Click(object sender, EventArgs e)
		{
			if (name.Text.ToString().Equals("") || email.Text.ToString().Equals("") || phone.Text.ToString().Equals("") || designation.Text.ToString().Equals(""))
			{
				Toast.MakeText(this, "Fields Empty Found", ToastLength.Short).Show();
			}
			else {
				dbHelper.insertEmployee(name.Text.ToString(), email.Text.ToString(), phone.Text.ToString(), designation.Text.ToString());
				Toast.MakeText(this, "Data Successfully Stored", ToastLength.Short).Show();
			}
		}

		public void initialize()
		{
			name = (EditText)FindViewById(Resource.Id.empName);
			email = (EditText)FindViewById(Resource.Id.empEmail);
			phone = (EditText)FindViewById(Resource.Id.empPhone);
			designation = (EditText)FindViewById(Resource.Id.empDesignation);
			submitBtn = (Button)FindViewById(Resource.Id.insertEmpBtn);

		}


	}
}


showCompleteData.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.Database;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using Java.Util;

namespace LocalDatabase
{
	[Activity(Label = "showCompleteData")]
	public class showCompleteData : Activity
	{
		ListView list;
		TextView dName, dEmail, dPhone, dDesignation;
		String name, email, phone, designation;
		SQLiteHelper dbHelper;

		protected override void OnCreate(Bundle savedInstanceState)
		{
			base.OnCreate(savedInstanceState);

			// Create your application here
			SetContentView(Resource.Layout.complete_data);
			initialize();
			dbHelper = new SQLiteHelper(this);
			System.Collections.ArrayList dataList = dbHelper.getAllEmployeesData();
			String[] myArr = (String[])dataList.ToArray(typeof(string));
			list.Adapter = new ArrayAdapter(this, Android.Resource.Layout.SimpleExpandableListItem1, myArr);

			list.ItemClick += List_ItemClick;
		}
		public void initialize()
		{
			list = (ListView)FindViewById(Resource.Id.listView1);
			dName = (TextView)FindViewById(Resource.Id.eName);
			dEmail = (TextView)FindViewById(Resource.Id.eEmail);
			dPhone = (TextView)FindViewById(Resource.Id.ePhone);
			dDesignation = (TextView)FindViewById(Resource.Id.eDesignation);
		}

		void List_ItemClick(object sender, AdapterView.ItemClickEventArgs e)
		{
			
			int click_Employee = e.Position + 1;
			Toast.MakeText(this, e.Position.ToString(), ToastLength.Short).Show();
			ICursor c = dbHelper.getSingleEntry(click_Employee);
			c.MoveToFirst();
			name = c.GetString(c.GetColumnIndex(dbHelper.EMPLOYEE_NAME));
			email = c.GetString(c.GetColumnIndex(dbHelper.EMPLOYEE_EMAIL));
			phone = c.GetString(c.GetColumnIndex(dbHelper.EMPLOYEE_PHONE));
			designation = c.GetString(c.GetColumnIndex(dbHelper.EMPLOYEE_DESIGNATION));
			dName.Text = name;
			dEmail.Text = email;
			dPhone.Text = phone;
			dDesignation.Text = designation;
			c.Close();
		}
	}
}

updateEmployee.cs

using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.Database;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using Java.Util;

namespace LocalDatabase
{
	[Activity(Label = "updateEmployee")]
	public class updateEmployee : Activity
	{
		ListView list;
		EditText dName, dEmail, dPhone, dDesignation;
		String name, email, phone, designation;
		SQLiteHelper dbHelper;
		int click_Employee;
		Button upBtn;

		protected override void OnCreate(Bundle savedInstanceState)
		{
			base.OnCreate(savedInstanceState);

			// Create your application here
			SetContentView(Resource.Layout.update);
			initialize();
			dbHelper = new SQLiteHelper(this);
			System.Collections.ArrayList dataList = dbHelper.getAllEmployeesData();
			String[] myArr = (String[])dataList.ToArray(typeof(string));
			list.Adapter = new ArrayAdapter(this, Android.Resource.Layout.SimpleExpandableListItem1, myArr);

			list.ItemClick += List_ItemClick;

			upBtn.Click += delegate {
				dbHelper.updateEmployeeInfo(click_Employee, dName.Text.ToString(), dEmail.Text.ToString(), dPhone.Text.ToString(), dDesignation.Text.ToString());
			};

		}

		void List_ItemClick(object sender, AdapterView.ItemClickEventArgs e)
		{
			click_Employee = e.Position + 1;
			ICursor c = dbHelper.getSingleEntry(click_Employee);
			c.MoveToFirst();
			name = c.GetString(c.GetColumnIndex(dbHelper.EMPLOYEE_NAME));
			email = c.GetString(c.GetColumnIndex(dbHelper.EMPLOYEE_EMAIL));
			phone = c.GetString(c.GetColumnIndex(dbHelper.EMPLOYEE_PHONE));
			designation = c.GetString(c.GetColumnIndex(dbHelper.EMPLOYEE_DESIGNATION));
			dName.Text = name;
			dEmail.Text=email;
			dPhone.Text=phone;
			dDesignation.Text=designation;
		}

		public void initialize()
		{
			list = (ListView)FindViewById(Resource.Id.listView1);
			dName = (EditText)FindViewById(Resource.Id.upName);
			dEmail = (EditText)FindViewById(Resource.Id.upEmail);
			dPhone = (EditText)FindViewById(Resource.Id.upPhone);
			dDesignation = (EditText)FindViewById(Resource.Id.upDesignation);
			upBtn = (Button)FindViewById(Resource.Id.updateEmploy);
		}
	}
}

deleteEmployee.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using Java.Util;

namespace LocalDatabase
{
	[Activity(Label = "deleteEmployee")]
	public class deleteEmployee : Activity
	{
		ListView list;
		SQLiteHelper dbHelper;
		protected override void OnCreate(Bundle savedInstanceState)
		{
			base.OnCreate(savedInstanceState);

			// Create your application here
			SetContentView(Resource.Layout.delete);
			initialize();
			dbHelper = new SQLiteHelper(this);
			System.Collections.ArrayList dataList = dbHelper.getAllEmployeesData();
			String[] myArr = (String[])dataList.ToArray(typeof(string));
			list.Adapter = new ArrayAdapter(this, Android.Resource.Layout.SimpleExpandableListItem1,myArr);

			list.ItemClick += List_ItemClick;

		}
		public void initialize()
		{
			list = (ListView)FindViewById(Resource.Id.listView1);

		}

		void List_ItemClick(object sender, AdapterView.ItemClickEventArgs e)
		{
			int click_Employee = e.Position + 1;
			dbHelper.deleteEmployee(click_Employee);
			Toast.MakeText(this, "Employee Successfully Deleted", ToastLength.Short).Show();
			var n = new Intent(this, typeof(MainActivity));
			StartActivity(n);
		}
	}
}


Now run your Xamarin SQLite Database example android application project.

xamarin sqlite database,xamarin sqlite,xamarin sqlite database example,xamarin sqlite database example android,sqlite database example android,

xamarin sqlite database,xamarin sqlite,xamarin sqlite database example,xamarin sqlite database example android,sqlite database example android,

That’s it for this Xamarin SQLite Database example android tutorial.

For Xamarin SQLite Database Example Android like example in Java visit Android Codec.

Thanks.