Post
Ask Question

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)
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using Android.App;  
  6. using Android.Content;  
  7. using Android.OS;  
  8. using Android.Runtime;  
  9. using Android.Views;  
  10. using Android.Widget;  
  11. using SQLite.Net.Attributes;  
  12. namespace ExistingDB.Helper  
  13. {  
  14.     public class Account  
  15.     {  
  16.         [PrimaryKey]  
  17.         public string Name { get; set; }  
  18.         public string Email { get; set; }  
  19.         public string Mobile { get; set; }  
  20.     }  
  21. }  
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)
  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.     <Button  
  7.         android:text="Load Data"  
  8.         android:layout_width="match_parent"  
  9.         android:layout_height="wrap_content"  
  10.         android:id="@+id/btnLoadData" />  
  11.     <LinearLayout  
  12.         android:orientation="vertical"  
  13.         android:minWidth="25px"  
  14.         android:minHeight="25px"  
  15.         android:layout_width="match_parent"  
  16.         android:layout_height="wrap_content"  
  17.         android:id="@+id/container" />  
  18. </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)
  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.     <TextView  
  7.         android:text="Large Text"  
  8.         android:textAppearance="?android:attr/textAppearanceLarge"  
  9.         android:layout_width="match_parent"  
  10.         android:layout_height="wrap_content"  
  11.         android:id="@+id/txtName" />  
  12.     <TextView  
  13.         android:text="Large Text"  
  14.         android:textAppearance="?android:attr/textAppearanceLarge"  
  15.         android:layout_width="match_parent"  
  16.         android:layout_height="wrap_content"  
  17.         android:id="@+id/txtEmail" />  
  18.     <TextView  
  19.         android:text="Large Text"  
  20.         android:textAppearance="?android:attr/textAppearanceLarge"  
  21.         android:layout_width="match_parent"  
  22.         android:layout_height="wrap_content"  
  23.         android:id="@+id/txtMobile" />  
  24. </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)
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.IO;  
  4. using System.Linq;  
  5. using System.Text;  
  6. using Android.App;  
  7. using Android.Content;  
  8. using Android.Database.Sqlite;  
  9. using Android.OS;  
  10. using Android.Runtime;  
  11. using Android.Views;  
  12. using Android.Widget;  
  13. namespace ExistingDB.Helper  
  14. {  
  15.     public class DBHelper : SQLiteOpenHelper  
  16.     {  
  17.         private static string DB_PATH = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);  
  18.         private static string DB_NAME = "MyDB.db";  
  19.         private static int VERSION = 1;  
  20.         private Context context;  
  21.         public DBHelper(Context context) : base(context, DB_NAME, null, VERSION)  
  22.         {  
  23.             this.context = context;  
  24.         }  
  25.         private string GetSQLiteDBPath()  
  26.         {  
  27.             return Path.Combine(DB_PATH, DB_NAME);  
  28.         }  
  29.         public override SQLiteDatabase WritableDatabase  
  30.         {  
  31.             get  
  32.             {  
  33.                 return CreateSQLiteDB();  
  34.             }  
  35.         }  
  36.         private SQLiteDatabase CreateSQLiteDB()  
  37.         {  
  38.             SQLiteDatabase sqliteDB = null;  
  39.             string path = GetSQLiteDBPath();  
  40.             Stream streamSQLite = null;  
  41.             FileStream streamWriter = null;  
  42.             Boolean isSQLiteInit = false;  
  43.             try  
  44.             {  
  45.                 if (File.Exists(path))  
  46.                     isSQLiteInit = true;  
  47.                 else  
  48.                 {  
  49.                     streamSQLite = context.Resources.OpenRawResource(Resource.Raw.MyDB);  
  50.                     streamWriter = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);  
  51.                     if(streamSQLite != null && streamWriter != null)  
  52.                     {  
  53.                         if (CopySQLiteDB(streamSQLite, streamWriter))  
  54.                             isSQLiteInit = true;  
  55.                     }  
  56.                 }  
  57.                 if (isSQLiteInit)  
  58.                     sqliteDB = SQLiteDatabase.OpenDatabase(path, null, DatabaseOpenFlags.OpenReadonly);  
  59.             }  
  60.             catch { }  
  61.             return sqliteDB;  
  62.         }  
  63.         private bool CopySQLiteDB(Stream streamSQLite, FileStream streamWriter)  
  64.         {  
  65.             bool isSuccess = false;  
  66.             int lenght = 256;  
  67.             Byte[] buffer = new Byte[lenght];  
  68.             try  
  69.             {  
  70.                 int bytesRead = streamSQLite.Read(buffer, 0, lenght);  
  71.                 while(bytesRead > 0)  
  72.                 {  
  73.                     streamWriter.Write(buffer, 0, bytesRead);  
  74.                     bytesRead = streamSQLite.Read(buffer, 0, lenght);  
  75.                 }  
  76.                 isSuccess = true;  
  77.             }  
  78.             catch { }  
  79.             finally  
  80.             {  
  81.                 streamSQLite.Close();  
  82.                 streamWriter.Close();  
  83.             }  
  84.             return isSuccess;  
  85.         }  
  86.         public override void OnCreate(SQLiteDatabase db)  
  87.         {  
  88.         }  
  89.         public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)  
  90.         {  
  91.         }  
  92.     }  
  93. }  
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
  1. using Android.App;  
  2. using Android.Widget;  
  3. using Android.OS;  
  4. using ExistingDB.Helper;  
  5. using Android.Database.Sqlite;  
  6. using System;  
  7. using Android.Database;  
  8. using System.Collections.Generic;  
  9. using Android.Views;  
  10. using Android.Content;  
  11. namespace ExistingDB  
  12. {  
  13.     [Activity(Label = "ExistingDB", MainLauncher = true)]  
  14.     public class MainActivity : Activity  
  15.     {  
  16.         DBHelper db;  
  17.         SQLiteDatabase sqliteDB;  
  18.         LinearLayout container;  
  19.         Button btnGetData;  
  20.         List<Account> lstUser = new List<Account>();  
  21.         protected override void OnCreate(Bundle savedInstanceState)  
  22.         {  
  23.             base.OnCreate(savedInstanceState);  
  24.             // Set our view from the "main" layout resource  
  25.             SetContentView(Resource.Layout.Main);  
  26.             db = new DBHelper(this);  
  27.             sqliteDB = db.WritableDatabase;  
  28.             container = FindViewById<LinearLayout>(Resource.Id.container);  
  29.             btnGetData = FindViewById<Button>(Resource.Id.btnLoadData);  
  30.             btnGetData.Click += delegate   
  31.             {  
  32.                 Data();  
  33.             };  
  34.         }  
  35.         private void Data()  
  36.         {  
  37.             ICursor selectData = sqliteDB.RawQuery("select * from Account", new string[] { });  
  38.             if(selectData.Count > 0)  
  39.             {  
  40.                 selectData.MoveToFirst();  
  41.                 do  
  42.                 {  
  43.                     Account user = new Account();  
  44.                     user.Name = selectData.GetString(selectData.GetColumnIndex("Name"));  
  45.                     user.Email = selectData.GetString(selectData.GetColumnIndex("Email"));  
  46.                     user.Mobile = selectData.GetString(selectData.GetColumnIndex("Mobile"));  
  47.                     lstUser.Add(user);  
  48.                 }  
  49.                 while (selectData.MoveToNext());  
  50.                 selectData.Close();  
  51.             }  
  52.             foreach(var item in lstUser)  
  53.             {  
  54.                 LayoutInflater layoutInflater = (LayoutInflater)BaseContext.GetSystemService(Context.LayoutInflaterService);  
  55.                 View addView = layoutInflater.Inflate(Resource.Layout.row, null);  
  56.                 TextView txtName = addView.FindViewById<TextView>(Resource.Id.txtName);  
  57.                 TextView txtEmail = addView.FindViewById<TextView>(Resource.Id.txtEmail);  
  58.                 TextView txtMobile = addView.FindViewById<TextView>(Resource.Id.txtMobile);  
  59.                 txtName.Text = item.Name;  
  60.                 txtEmail.Text = item.Email;  
  61.                 txtMobile.Text = item.Mobile;  
  62.                 container.AddView(addView);  
  63.             }  
  64.         }  
  65.     }  
  66. }  

Output

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


Similar Articles