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.