Post
Ask Question

Xamarin Android - SQLite Database Joins (Using SQLiteOpenHelper)

Introduction

In this blog, we will see how to create an SQLite database in Xamarin Android application. We will also see how to add records to the different tables in the same database and read and display that data in an application.

Step1

Create 2 table classes and join and retrieve a separate class linking them,

  1. public class Person  
  2.     {  
  3.         public int Id { get; set; }  
  4.         public string Name { get; set; }  
  5.         public DateTime Dob { get; set; }  
  6.     }  
  7.     public class Work  
  8.     {  
  9.         public int PersonId { get; set; }  
  10.         public string WorkName { get; set; }          
  11.     }  
  12.     public class joining  
  13.     {  
  14.         public List<Person> Personss{ get; set; }  
  15.         public List<Work> Works { get; set; }  
  16.     }  

Step 2

To retrieve a join, use the joining class like below.

  1. public static List<joining> GetPW(Context context)  
  2.             {  
  3.                 List<Person> people = new List<Person>();  
  4.                 List<Work> peoplew = new List<Work>();  
  5.                 SQLiteDatabase db = new DataStore(context).ReadableDatabase;  
  6.                 string[] column = new string[] { ColumnID, ColumnName, ColumnDob };  
  7.                 string[] columns = new string[] { ColumnID, ColumnNamew};  
  8.                 using (ICursor cursor = db.Query(TableName, column, null, null, null, null, null))  
  9.                 {  
  10.                     while (cursor.MoveToNext())  
  11.                     {  
  12.                         people.Add(new Person  
  13.                         {  
  14.                             Id = cursor.GetInt(cursor.GetColumnIndexOrThrow(ColumnID)),  
  15.                             Name = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnName)),  
  16.                             Dob = DateTime.Parse(cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnDob)))  
  17.                         });  
  18.                     }  
  19.                 }  
  20.                 using (ICursor cursor = db.Query(TableNamew, columns, null, null, null, null,null))  
  21.                 {  
  22.                     while (cursor.MoveToNext())  
  23.                     {  
  24.                         peoplew.Add(new Work  
  25.                         {  
  26.                             PersonId = cursor.GetInt(cursor.GetColumnIndexOrThrow(ColumnID)),  
  27.                             WorkName = cursor.GetString(cursor.GetColumnIndexOrThrow(ColumnNamew))                          
  28.                         });  
  29.                     }  
  30.                 }  
  31.                 List<joining> lstJoin = new List<joining>() { new joining { Personss = people, Works = peoplew } };  
  32.                 return lstJoin;  
  33.             }  

Step3

To retrieve in MainActivity, use the below code.

  1. var peoplew = GetPW(this);   
  2.             Toast.MakeText(this, $"{peoplew[0].Works[0].WorkName} \n {peoplew[0].Personss[0].Name} people found.", ToastLength.Short).Show();  

I have attached the sample application. If any one knows how to use joins in SQLITE-net PCL, please let me know.