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 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 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 SQLite.Net package is not compatible with this 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 a SQLite.net library for creating a database in Xamarin.Android. However, this time SQLite.Net library is not able to solve the user 'sdatabase 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 a 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 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)
public class Admin
{
public string ID { get; set; }
public string Username { get; set; }
public string FullName { get; set; }
public string Email { get; set; }
public string Password { get; set; }
public string Mobile { get; set; }
public Admin() { }
public Admin(string Id, string username, string fullName, string email, string password, string mobile) //Constructor with all parameters
{
ID = Id;
Username = username;
FullName = fullName;
Email = email;
Password = password;
Mobile = mobile;
}
public Admin(string Password) //Constructor with one parameter
{
this.Password = Password;
}
}
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)
public class Helper : SQLiteOpenHelper
{
private static string _DatabaseName = "clientDatabase";
public Helper(Context context) : base(context, _DatabaseName, null, 1) { }
public override void OnCreate(SQLiteDatabase db)
{
db.ExecSQL(Helper.CreateQuery);
}
public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
db.ExecSQL(Helper.DeleteQuery);
OnCreate(db);
}
private const string TableName = "adminTable";
private const string ColumnID = "id";
private const string ColumnUsername = "username";
private const string ColumnFullName = "fullname";
private const string ColumnPassword = "password";
private const string ColumnEmail = "email";
private const string ColumnMobile = "mobile";
public const string CreateQuery = "CREATE TABLE " + TableName +
" ( "
+ ColumnID + " INTEGER PRIMARY KEY,"
+ ColumnUsername + " TEXT,"
+ ColumnFullName + " TEXT,"
+ ColumnPassword + " TEXT,"
+ ColumnEmail + " TEXT,"
+ ColumnMobile + " TEXT)";
public const string DeleteQuery = "DROP TABLE IF EXISTS " + TableName;
public void Register(Context context, Admin admin)
{
SQLiteDatabase db = new Helper(context).WritableDatabase;
ContentValues Values = new ContentValues();
Values.Put(ColumnUsername, admin.Username);
Values.Put(ColumnFullName, admin.FullName);
Values.Put(ColumnPassword, admin.Password);
Values.Put(ColumnEmail, admin.Email);
Values.Put(ColumnMobile, admin.Mobile);
db.Insert(TableName, null, Values);
db.Close();
}
public Admin Authenticate(Context context, Admin admin)
{
SQLiteDatabase db = new Helper(context).ReadableDatabase;
ICursor cursor = db.Query(TableName, new string[]
{ ColumnID, ColumnFullName, ColumnUsername, ColumnPassword, ColumnEmail, ColumnMobile },
ColumnUsername + "=?", new string[] { admin.Username }, null, null, null);
if(cursor != null && cursor.MoveToFirst() && cursor.Count > 0)
{
Admin admin1 = new Admin(cursor.GetString(3));
if (admin.Password.Equals(admin1.Password))
return admin1;
}
return null;
}
public List<Admin> GetAdmin(Context context)
{
List<Admin> admins = new List<Admin>();
SQLiteDatabase db = new Helper(context).ReadableDatabase;
string[] columns = new string[] {ColumnID,ColumnUsername,ColumnFullName,ColumnPassword,ColumnEmail,ColumnMobile };
using(ICursor cursor = db.Query(TableName, columns, null, null, null, null, null))
{
while (cursor.MoveToNext())
{
admins.Add(new Admin {
ID = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnID)),
Username = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnUsername)),
FullName = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnFullName)),
Password = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnPassword)),
Email = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnEmail)),
Mobile = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnMobile))
});
}
db.Close();
return admins;
}
}
}
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)
<?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"
android:minWidth="25px"
android:minHeight="25px">
<EditText
android:hint="Full Name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="20dp"
android:layout_marginRight="20dp"
android:id="@+id/edtfullname"/>
<EditText
android:hint="Username"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="20dp"
android:layout_marginRight="20dp"
android:id="@+id/edtusername"/>
<EditText
android:hint="Email"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="20dp"
android:layout_marginRight="20dp"
android:id="@+id/edtEmail"
/>
<EditText
android:hint="Mobile"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="20dp"
android:layout_marginRight="20dp"
android:id="@+id/edtMobile"
android:inputType="phone" />
<EditText
android:hint="Password"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="20dp"
android:layout_marginRight="20dp"
android:id="@+id/edtpassword"
android:inputType="textPassword" />
<Button
android:text="Create"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="20dp"
android:layout_marginRight="20dp"
android:id="@+id/btnCreate" />
<Button
android:text="Back to Home"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="20dp"
android:layout_marginRight="20dp"
android:id="@+id/btnBack" />
</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)
public class SignUp : Activity
{
private EditText edtFullname, edtUsername, edtEmail, edtPassword, edtMobile;
private Button btnCreate, btnBack;
Helper helper;
protected override void OnCreate(Bundle savedInstanceState)
{
base.OnCreate(savedInstanceState);
// Create your application here
SetContentView(Resource.Layout.SignUp);
edtFullname = FindViewById<EditText>(Resource.Id.edtfullname);
edtUsername = FindViewById<EditText>(Resource.Id.edtusername);
edtPassword = FindViewById<EditText>(Resource.Id.edtpassword);
edtEmail = FindViewById<EditText>(Resource.Id.edtEmail);
edtMobile = FindViewById<EditText>(Resource.Id.edtMobile);
btnCreate = FindViewById<Button>(Resource.Id.btnCreate);
btnBack = FindViewById<Button>(Resource.Id.btnBack);
helper = new Helper(this);
btnBack.Click += delegate { StartActivity(typeof(MainActivity)); };
btnCreate.Click += delegate
{
Admin admin = new Admin()
{
FullName = edtFullname.Text,
Username = edtUsername.Text,
Password = edtPassword.Text,
Email = edtEmail.Text,
Mobile = edtMobile.Text
};
string username = edtUsername.Text;
string password = edtPassword.Text;
if(string.IsNullOrEmpty(username) || string.IsNullOrEmpty(password))
{
Toast.MakeText(this, "Username and Password should not be empty.", ToastLength.Short).Show();
}
else
{
helper.Register(this,admin);
var data = helper.GetAdmin(this);
admin = data[data.Count - 1];
Toast.MakeText(this, $"User {admin.FullName} registration successful!", ToastLength.Short).Show();
Clear();
Toast.MakeText(this, $"Total {data.Count} Admin founds.", ToastLength.Short).Show();
}
};
}
void Clear()
{
edtFullname.Text = "";
edtUsername.Text = "";
edtPassword.Text = "";
edtMobile.Text = "";
edtEmail.Text = "";
}
}
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)
<?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"
android:minWidth="25px"
android:minHeight="25px">
<EditText
android:hint="Username"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="20dp"
android:layout_marginRight="20dp"
android:id="@+id/txtusername"/>
<EditText
android:hint="Password"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="20dp"
android:layout_marginRight="20dp"
android:id="@+id/txtpassword"
android:inputType="textPassword" />
<Button
android:text="Sign In"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="20dp"
android:layout_marginRight="20dp"
android:id="@+id/btnSign" />
<TextView
android:text="Or"
android:textAppearance="?android:attr/textAppearanceMedium"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:textColor="#ffffff"
android:gravity="center"
android:id="@+id/txtOr" />
<Button
android:text="Create an Account"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="20dp"
android:layout_marginRight="20dp"
android:id="@+id/btnSignUp" />
</LinearLayout>
Step 7 - Main Activity Class
Now, go to Solution Explorer-> Project Name-> MainActivity and add the following code with appropriate namespaces.
(FileName: MainActivity)
public class MainActivity : Activity
{
private EditText txtUsername, txtPassword;
private Button btnSignIn, btnCreate;
Helper helper;
protected override void OnCreate(Bundle savedInstanceState)
{
base.OnCreate(savedInstanceState);
// Set our view from the "main" layout resource
SetContentView(Resource.Layout.Main);
txtUsername = FindViewById<EditText>(Resource.Id.txtusername);
txtPassword = FindViewById<EditText>(Resource.Id.txtpassword);
btnCreate = FindViewById<Button>(Resource.Id.btnSignUp);
btnSignIn = FindViewById<Button>(Resource.Id.btnSign);
helper = new Helper(this);
btnCreate.Click += delegate { StartActivity(typeof(SignUp)); };
btnSignIn.Click += delegate
{
try
{
string Username = txtUsername.Text.ToString();
string Password = txtPassword.Text.ToString();
var user = helper.Authenticate(this,new Admin(null,Username,null,null,Password,null));
if (user != null)
{
Toast.MakeText(this, "Login Successful", ToastLength.Short).Show();
}
else
{
Toast.MakeText(this, "Login Unsuccessful! Please verify your Username and Password", ToastLength.Short).Show();
}
}
catch (SQLiteException ex)
{
Toast.MakeText(this, ""+ex, ToastLength.Short).Show();
}
};
}
}
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.
![]()