Xamarin.Android - Develop SQLite Database

 Introduction

 

Today, I am very excited to share some features of the SQLite database that we can also use in Xamarin Android. In this article, I will demonstrate how we can develop an SQLite database using Android class SQLiteOpenHelper.cs. For the sake of simplicity, I will create a user login and registration system in this demo.

 

The Purpose of This Article

 

This week, I need to create a feature for login and registration in my application. However, I added the SQLite.Net NuGet package that was published by Frank Krueger on Wednesday, February 26, 2014, and I created all the things that I needed for login and registration. When I ran the app then, it showed me a warning message.

 

"Detected Problems with app native libraries (Please consult log for detail): libmonosgen-64bit-2.0.so: unauthorized access to "/system/lib64/libsqlite.so"

 

This happened because I was using Android Oreo 8.1 and the SQLite.Net package is not compatible with these advanced versions of Android. However, SQLite.Net library was working perfectly with the older version of Android, like 5.1.1 lollipop or 6.0 marshmallow. A few months before I had made an app using SQLite.Net library. In addition, I wrote an article on this topic.

 

SQLite.Net Nuget Package

 

SQLite-net is an open-source, minimal library to allow .NET and Mono applications to store data in SQLite databases. It is written in C# 3.0 and is meant to be simply compiled in with your projects. It was first designed to work with MonoTouch on the iPhone, but should work in any other CLI environment.

 

Many developers use a sqlite.net library for creating a database in Xamarin.Android. In developer.xamarin websites, the Xamarin community also refers to an SQLite.net library for creating a database in Xamarin.Android. However, this time SQLite.Net library is not able to solve the user's database problem. Therefore, I have learned about a native library of SQLite for creating a database in Xamarin.Android.

 

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

 

Step 2 - Writing Admin Class

 

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

 

(File Name: Admin.cs)

1. public class Admin    
2. {    
3.     public string ID { get; set; }    
4.     public string Username { get; set; }    
5.     public string FullName { get; set; }    
6.     public string Email { get; set; }    
7.     public string Password { get; set; }    
8.     public string Mobile { get; set; }    
9.     public Admin() { }    
10.     public Admin(string Id, string username, string fullName, string email, string password, string mobile) //Constructor with all parameters    
11.     {    
12.         ID = Id;    
13.         Username = username;    
14.         FullName = fullName;    
15.         Email = email;    
16.         Password = password;    
17.         Mobile = mobile;    
18.     }    
19.     public Admin(string Password) //Constructor with one parameter    
20.     {    
21.         this.Password = Password;    
22.     }    
23. }    

 

Step 3 - Writing SQLite Helper Class

 

We need to write our own helper class to create a new database with operations. Go to Solution Explorer-> Project Name and right-click. Select Add -> New Item-> Class. Give it a name as Helper.cs. Inherit with SQLiteOpenHelper class and write the following code with appropriate namespaces.

(File Name: Helper.cs)

1. public class Helper : SQLiteOpenHelper  
2.     {  
3.         private static string _DatabaseName = "clientDatabase";  
4.   
5.         public Helper(Context context) : base(context, _DatabaseName, null, 1) { }  
6.         public override void OnCreate(SQLiteDatabase db)  
7.         {  
8.             db.ExecSQL(Helper.CreateQuery);  
9.         }  
10.   
11.         public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)  
12.         {  
13.             db.ExecSQL(Helper.DeleteQuery);  
14.             OnCreate(db);  
15.         }  
16.           
17.         private const string TableName = "adminTable";  
18.         private const string ColumnID = "id";  
19.         private const string ColumnUsername = "username";  
20.         private const string ColumnFullName = "fullname";  
21.         private const string ColumnPassword = "password";  
22.         private const string ColumnEmail = "email";  
23.         private const string ColumnMobile = "mobile";  
24.   
25.         public const string CreateQuery = "CREATE TABLE " + TableName +  
26.             " ( "  
27.             + ColumnID + " INTEGER PRIMARY KEY,"  
28.             + ColumnUsername + " TEXT,"  
29.             + ColumnFullName + " TEXT,"  
30.             + ColumnPassword + " TEXT,"  
31.             + ColumnEmail + " TEXT,"  
32.             + ColumnMobile + " TEXT)";  
33.   
34.         public const string DeleteQuery = "DROP TABLE IF EXISTS " + TableName;  
35.   
36.         public void Register(Context context, Admin admin)  
37.         {  
38.             SQLiteDatabase db = new Helper(context).WritableDatabase;  
39.             ContentValues Values = new ContentValues();  
40.             Values.Put(ColumnUsername, admin.Username);  
41.             Values.Put(ColumnFullName, admin.FullName);  
42.             Values.Put(ColumnPassword, admin.Password);  
43.             Values.Put(ColumnEmail, admin.Email);  
44.             Values.Put(ColumnMobile, admin.Mobile);  
45.             db.Insert(TableName, null, Values);  
46.             db.Close();  
47.         }  
48.         public Admin Authenticate(Context context, Admin admin)  
49.         {  
50.             SQLiteDatabase db = new Helper(context).ReadableDatabase;  
51.             ICursor cursor = db.Query(TableName, new string[]   
52.             { ColumnID, ColumnFullName, ColumnUsername, ColumnPassword, ColumnEmail, ColumnMobile },  
53.             ColumnUsername + "=?", new string[] { admin.Username }, null, null, null);  
54.             if(cursor != null && cursor.MoveToFirst() && cursor.Count > 0)  
55.             {  
56.                 Admin admin1 = new Admin(cursor.GetString(3));  
57.                 if (admin.Password.Equals(admin1.Password))  
58.                 return admin1;  
59.             }  
60.             return null;  
61.         }  
62.   
63.         public List<Admin> GetAdmin(Context context)  
64.         {  
65.             List<Admin> admins = new List<Admin>();  
66.             SQLiteDatabase db = new Helper(context).ReadableDatabase;  
67.             string[] columns = new string[] {ColumnID,ColumnUsername,ColumnFullName,ColumnPassword,ColumnEmail,ColumnMobile };  
68.             using(ICursor cursor = db.Query(TableName, columns, null, null, null, null, null))  
69.             {  
70.                 while (cursor.MoveToNext())  
71.                 {  
72.                     admins.Add(new Admin {  
73.                         ID = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnID)),  
74.                         Username = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnUsername)),  
75.                         FullName = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnFullName)),  
76.                         Password = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnPassword)),  
77.                         Email = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnEmail)),  
78.                         Mobile = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnMobile))  
79.   
80.                     });  
81.                 }  
82.                 db.Close();  
83.                 return admins;  
84.             }  
85.         }  
86.     }  

Step 4 - Add SignUp Layout

 

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

 

(Folder Name: Layout , File Name: SignUp.axml)

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.     android:minWidth="25px"  
7.     android:minHeight="25px">  
8.     <EditText  
9.         android:hint="Full Name"  
10.         android:layout_width="match_parent"  
11.         android:layout_height="wrap_content"  
12.         android:layout_marginLeft="20dp"  
13.         android:layout_marginRight="20dp"  
14.         android:id="@+id/edtfullname"/>  
15.     <EditText  
16.         android:hint="Username"  
17.         android:layout_width="match_parent"  
18.         android:layout_height="wrap_content"  
19.         android:layout_marginLeft="20dp"  
20.         android:layout_marginRight="20dp"  
21.         android:id="@+id/edtusername"/>  
22.     <EditText  
23.         android:hint="Email"  
24.         android:layout_width="match_parent"  
25.         android:layout_height="wrap_content"  
26.         android:layout_marginLeft="20dp"  
27.         android:layout_marginRight="20dp"  
28.         android:id="@+id/edtEmail"  
29.          />  
30.     <EditText  
31.         android:hint="Mobile"  
32.         android:layout_width="match_parent"  
33.         android:layout_height="wrap_content"  
34.         android:layout_marginLeft="20dp"  
35.         android:layout_marginRight="20dp"  
36.         android:id="@+id/edtMobile"  
37.         android:inputType="phone" />  
38.     <EditText  
39.         android:hint="Password"  
40.         android:layout_width="match_parent"  
41.         android:layout_height="wrap_content"  
42.         android:layout_marginLeft="20dp"  
43.         android:layout_marginRight="20dp"  
44.         android:id="@+id/edtpassword"  
45.         android:inputType="textPassword" />  
46.     <Button  
47.         android:text="Create"  
48.         android:layout_width="match_parent"  
49.         android:layout_height="wrap_content"  
50.         android:layout_marginLeft="20dp"  
51.         android:layout_marginRight="20dp"  
52.         android:id="@+id/btnCreate" />  
53.     <Button  
54.         android:text="Back to Home"  
55.         android:layout_width="match_parent"  
56.         android:layout_height="wrap_content"  
57.         android:layout_marginLeft="20dp"  
58.         android:layout_marginRight="20dp"  
59.         android:id="@+id/btnBack" />  
60. </LinearLayout>  

Step 5 - Create SignUp Activity

 

Add a new Activity. For this, open Solution Explorer-> Project Name-> right click to add a new item and select Activity. Give it a name like SignUp.cs and add the following code using the appropriate namespaces.

 

(FileName: SignUp)

1. public class SignUp : Activity  
2.     {  
3.         private EditText edtFullname, edtUsername, edtEmail, edtPassword, edtMobile;  
4.         private Button btnCreate, btnBack;  
5.         Helper helper;  
6.         protected override void OnCreate(Bundle savedInstanceState)  
7.         {  
8.             base.OnCreate(savedInstanceState);  
9.   
10.             // Create your application here  
11.             SetContentView(Resource.Layout.SignUp);  
12.               
13.             edtFullname = FindViewById<EditText>(Resource.Id.edtfullname);  
14.             edtUsername = FindViewById<EditText>(Resource.Id.edtusername);  
15.             edtPassword = FindViewById<EditText>(Resource.Id.edtpassword);  
16.             edtEmail = FindViewById<EditText>(Resource.Id.edtEmail);  
17.             edtMobile = FindViewById<EditText>(Resource.Id.edtMobile);  
18.             btnCreate = FindViewById<Button>(Resource.Id.btnCreate);  
19.             btnBack = FindViewById<Button>(Resource.Id.btnBack);  
20.             helper = new Helper(this);  
21.   
22.             btnBack.Click += delegate { StartActivity(typeof(MainActivity)); };  
23.   
24.             btnCreate.Click += delegate   
25.             {  
26.                 Admin admin = new Admin()  
27.                 {  
28.                     FullName = edtFullname.Text,  
29.                     Username = edtUsername.Text,  
30.                     Password = edtPassword.Text,  
31.                     Email = edtEmail.Text,  
32.                     Mobile = edtMobile.Text  
33.                 };  
34.                 string username = edtUsername.Text;  
35.                 string password = edtPassword.Text;  
36.                 if(string.IsNullOrEmpty(username) || string.IsNullOrEmpty(password))  
37.                 {  
38.                     Toast.MakeText(this, "Username and Password should not be empty.", ToastLength.Short).Show();  
39.                 }  
40.                 else  
41.                 {  
42.                     helper.Register(this,admin);  
43.                     var data = helper.GetAdmin(this);  
44.                     admin = data[data.Count - 1];  
45.                     Toast.MakeText(this, $"User {admin.FullName} registration successful!", ToastLength.Short).Show();  
46.                     Clear();  
47.                     Toast.MakeText(this, $"Total {data.Count} Admin founds.", ToastLength.Short).Show();  
48.                 }  
49.             };  
50.              
51.         }  
52.         void Clear()  
53.         {  
54.             edtFullname.Text = "";  
55.             edtUsername.Text = "";  
56.             edtPassword.Text = "";  
57.             edtMobile.Text = "";  
58.             edtEmail.Text = "";  
59.         }  
60.     }  

Step 6 - Main 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)

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.     android:minWidth="25px"  
7.     android:minHeight="25px">  
8.     <EditText  
9.         android:hint="Username"  
10.         android:layout_width="match_parent"  
11.         android:layout_height="wrap_content"  
12.         android:layout_marginLeft="20dp"  
13.         android:layout_marginRight="20dp"  
14.         android:id="@+id/txtusername"/>  
15.     <EditText  
16.         android:hint="Password"  
17.         android:layout_width="match_parent"  
18.         android:layout_height="wrap_content"  
19.         android:layout_marginLeft="20dp"  
20.         android:layout_marginRight="20dp"  
21.         android:id="@+id/txtpassword"  
22.         android:inputType="textPassword" />  
23.     <Button  
24.         android:text="Sign In"  
25.         android:layout_width="match_parent"  
26.         android:layout_height="wrap_content"  
27.         android:layout_marginLeft="20dp"  
28.         android:layout_marginRight="20dp"  
29.         android:id="@+id/btnSign" />  
30.     <TextView  
31.         android:text="Or"  
32.         android:textAppearance="?android:attr/textAppearanceMedium"  
33.         android:layout_width="match_parent"  
34.         android:layout_height="wrap_content"  
35.         android:textColor="#ffffff"  
36.         android:gravity="center"  
37.         android:id="@+id/txtOr" />  
38.     <Button  
39.         android:text="Create an Account"  
40.         android:layout_width="match_parent"  
41.         android:layout_height="wrap_content"  
42.         android:layout_marginLeft="20dp"  
43.         android:layout_marginRight="20dp"  
44.         android:id="@+id/btnSignUp" />  
45. </LinearLayout>  

Step 7 - Main Activity Class

 

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

 

(FileName: MainActivity)

1. public class MainActivity : Activity  
2.     {  
3.         private EditText txtUsername, txtPassword;  
4.         private Button btnSignIn, btnCreate;  
5.         Helper helper;  
6.         protected override void OnCreate(Bundle savedInstanceState)  
7.         {  
8.             base.OnCreate(savedInstanceState);  
9.   
10.             // Set our view from the "main" layout resource  
11.             SetContentView(Resource.Layout.Main);  
12.               
13.             txtUsername = FindViewById<EditText>(Resource.Id.txtusername);  
14.             txtPassword = FindViewById<EditText>(Resource.Id.txtpassword);  
15.             btnCreate = FindViewById<Button>(Resource.Id.btnSignUp);  
16.             btnSignIn = FindViewById<Button>(Resource.Id.btnSign);  
17.             helper = new Helper(this);  
18.   
19.             btnCreate.Click += delegate { StartActivity(typeof(SignUp)); };  
20.   
21.             btnSignIn.Click += delegate  
22.             {  
23.                 try  
24.                 {  
25.                     string Username = txtUsername.Text.ToString();  
26.                     string Password = txtPassword.Text.ToString();  
27.                     var user = helper.Authenticate(this,new Admin(null,Username,null,null,Password,null));  
28.                     if (user != null)  
29.                     {  
30.                         Toast.MakeText(this, "Login Successful", ToastLength.Short).Show();  
31.                           
32.                     }  
33.                     else  
34.                     {  
35.                         Toast.MakeText(this, "Login Unsuccessful! Please verify your Username and Password", ToastLength.Short).Show();  
36.                     }  
37.                 }  
38.                 catch (SQLiteException ex)  
39.                 {  
40.                     Toast.MakeText(this, ""+ex, ToastLength.Short).Show();  
41.                 }  
42.                   
43.             };  
44.         }  
45.     }  

Summary

 

This was the process of creating an app for Login and Registration with SQLite database in Xamarin.Android, using Visual Studio. Please share your comments and feedback.