Xamarin Android - Working with Existing DB

Introduction

Android provides several ways to store and retrieve user data from database. SQLite is a very lightweight database which comes with Android OS. In this tutorial I’ll be discussing how to write class to retrieve user data from existing database.

Prerequisites

Visual Studio 2017
SQLite DB Broswer
SQLite DB file
SQLite.Net-PCL NuGet Package

The steps given below are required to be followed in order to create a SQLite app in Xamarin.Android, using Visual Studio.

Step 1 - Create SQLite Database

We need to use DB Broswer to create new SQLite DB and insert some data in to it. Open DB Broswer and Go File ->New Database then, A pop window will show up. Give it a name like MyDB and location where you want to save.

Step 2

Now, add a new table with name Account and add 3 fields - Name, Email, Mobile.

Step 3

Insert some data into Account table fields like shown blow and click on writes the changes button.

It writes the changes. We have created an SQLite database for Android application.

Android Application

Step 4

Open Visual Studio and go to New Project-> Templates-> Visual C#-> Android-> Blank app. Give it a name, like ExistingDB.

Step 5

Go to Solution Explorer-> Project Name-> References. Then, right-click to "Manage NuGet Packages" and search for SQLite.Net-PCL. Install the sqlite.net-PCL package.

Step 6

Open Solution Explorer-> Project Name -> Resources Right click add a new folder give it a name like Raw and paste your database file into it.

(FolderName: Raw)

(FileName: MyDB)

Step 7 - Writing Account Class

Before you go further you need to write your Account class with all 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: Account.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 SQLite.Net.Attributes;
namespace ExistingDB.Helper
{
public class Account
{
[PrimaryKey]
public string Name { get; set; }
public string Email { get; set; }
public string Mobile { get; set; }
}
}

Step 8 - 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">
<Button
android:text="Load Data"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/btnLoadData" />
<LinearLayout
android:orientation="vertical"
android:minWidth="25px"
android:minHeight="25px"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/container" />
</LinearLayout>

Step 9

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 row.axml. Open this layout file and add the following code.

(File Name: row.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">
<TextView
android:text="Large Text"
android:textAppearance="?android:attr/textAppearanceLarge"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/txtName" />
<TextView
android:text="Large Text"
android:textAppearance="?android:attr/textAppearanceLarge"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/txtEmail" />
<TextView
android:text="Large Text"
android:textAppearance="?android:attr/textAppearanceLarge"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/txtMobile" />
</LinearLayout>

Step 10 - Writing SQLite Database Handler Class

Go to Solution Explorer-> Project Name and right-click. Select Add -> New Item-> Class. Give it a name as DBHelper.cs and write the following code.

(File Name: DBHelper.cs)

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Android.App;
using Android.Content;
using Android.Database.Sqlite;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
namespace ExistingDB.Helper
{
public class DBHelper : SQLiteOpenHelper
{
private static string DB_PATH = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
private static string DB_NAME = "MyDB.db";
private static int VERSION = 1;
private Context context;
public DBHelper(Context context) : base(context, DB_NAME, null, VERSION)
{
this.context = context;
}
private string GetSQLiteDBPath()
{
return Path.Combine(DB_PATH, DB_NAME);
}
public override SQLiteDatabase WritableDatabase
{
get
{
return CreateSQLiteDB();
}
}
private SQLiteDatabase CreateSQLiteDB()
{
SQLiteDatabase sqliteDB = null;
string path = GetSQLiteDBPath();
Stream streamSQLite = null;
FileStream streamWriter = null;
Boolean isSQLiteInit = false;
try
{
if (File.Exists(path))
isSQLiteInit = true;
else
{
streamSQLite = context.Resources.OpenRawResource(Resource.Raw.MyDB);
streamWriter = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);
if(streamSQLite != null && streamWriter != null)
{
if (CopySQLiteDB(streamSQLite, streamWriter))
isSQLiteInit = true;
}
}
if (isSQLiteInit)
sqliteDB = SQLiteDatabase.OpenDatabase(path, null, DatabaseOpenFlags.OpenReadonly);
}
catch { }
return sqliteDB;
}
private bool CopySQLiteDB(Stream streamSQLite, FileStream streamWriter)
{
bool isSuccess = false;
int lenght = 256;
Byte[] buffer = new Byte[lenght];
try
{
int bytesRead = streamSQLite.Read(buffer, 0, lenght);
while(bytesRead > 0)
{
streamWriter.Write(buffer, 0, bytesRead);
bytesRead = streamSQLite.Read(buffer, 0, lenght);
}
isSuccess = true;
}
catch { }
finally
{
streamSQLite.Close();
streamWriter.Close();
}
return isSuccess;
}
public override void OnCreate(SQLiteDatabase db)
{
}
public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
}
}
}

Step 11

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

(FileName: MainActivity)

C# Code

using Android.App;
using Android.Widget;
using Android.OS;
using ExistingDB.Helper;
using Android.Database.Sqlite;
using System;
using Android.Database;
using System.Collections.Generic;
using Android.Views;
using Android.Content;
namespace ExistingDB
{
[Activity(Label = "ExistingDB", MainLauncher = true)]
public class MainActivity : Activity
{
DBHelper db;
SQLiteDatabase sqliteDB;
LinearLayout container;
Button btnGetData;
List<Account> lstUser = new List<Account>();
protected override void OnCreate(Bundle savedInstanceState)
{
base.OnCreate(savedInstanceState);
// Set our view from the "main" layout resource
SetContentView(Resource.Layout.Main);
db = new DBHelper(this);
sqliteDB = db.WritableDatabase;
container = FindViewById<LinearLayout>(Resource.Id.container);
btnGetData = FindViewById<Button>(Resource.Id.btnLoadData);
btnGetData.Click += delegate
{
Data();
};
}
private void Data()
{
ICursor selectData = sqliteDB.RawQuery("select * from Account", new string[] { });
if(selectData.Count > 0)
{
selectData.MoveToFirst();
do
{
Account user = new Account();
user.Name = selectData.GetString(selectData.GetColumnIndex("Name"));
user.Email = selectData.GetString(selectData.GetColumnIndex("Email"));
user.Mobile = selectData.GetString(selectData.GetColumnIndex("Mobile"));
lstUser.Add(user);
}
while (selectData.MoveToNext());
selectData.Close();
}
foreach(var item in lstUser)
{
LayoutInflater layoutInflater = (LayoutInflater)BaseContext.GetSystemService(Context.LayoutInflaterService);
View addView = layoutInflater.Inflate(Resource.Layout.row, null);
TextView txtName = addView.FindViewById<TextView>(Resource.Id.txtName);
TextView txtEmail = addView.FindViewById<TextView>(Resource.Id.txtEmail);
TextView txtMobile = addView.FindViewById<TextView>(Resource.Id.txtMobile);
txtName.Text = item.Name;
txtEmail.Text = item.Email;
txtMobile.Text = item.Mobile;
container.AddView(addView);
}
}
}
}

Output

Summary

This was the process of working with existing SQLiteDB in Xamarin.Android, using Visual Studio. Please share your comments and feedback.

6 Comments

Join the conversation! Your thoughts help the community grow.

Nwenar Ismail5y ago
Resource.Raw.Db is not Found solution?
Dylan Métral7y ago
Hello, i got a question about how you write on your database when use a UPDATE or something like that. I use your method but i can only read the database and not edited.
Jose Carlos Macoratti8y ago
Hi, Can you explain what exactly the DBHelper class does ? I guess that it copies the created database from raw folder to System.Environment.SpecialFolder.Personal android's folder. But because this class must inherit from SQLiteOpenHelper ?
- Ahsan Siddique8y ago
  Yes your understanding is true.
Sagar Pandurang Kap8y ago
Good article.Keep Posting..
- Ahsan Siddique8y ago
  Thanks and you keep visiting....