Post
Ask Question

Xamarin.Forms: Working With SQLite (Android, iOS and Windows)

Step 1: Before going through this article, please go through my previous articles:

Step 2: First, create a Xamarin.Forms project with the Portable Class Library (PCL) as discussed in my previous article,

create
Step 3: Now, we will install sqlite-net-pcl from Nuget Package Manager.

Under PCL project, right click on References and from the Nuget Package Manager, search sqlite-net-pcl and install that within Portable Class Library (PCL) project.

sqlite-net-pcl

Step 4: Install the same SQLite library for each platform (Android, iOS and Windows) under each project. So, let’s add sqlite-net-pcl for Android first.

Right click on References under Android project and install sqlite-net-pcl in a similar way as above.

Step 5: Add an XAML Page with the name FormsPage.xaml. For this, right click on PCL project and add Forms Xaml Page.

Page

Step 6: In the FormsPage.xaml, we add two labels, two input fields and two buttons like this:
  1. <?xml version="1.0" encoding="utf-8" ?>  
  2. <ContentPage xmlns="http://xamarin.com/schemas/2014/forms"  
  3.              xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"  
  4.              x:Class="SQLiteTutorial.FormsPage">  
  5.   
  6.   <ContentPage.Content>  
  7.     <StackLayout Padding="20">  
  8.       <Label Text="Name" FontSize="25"/>  
  9.       <Entry x:Name="memberName" Placeholder="Enter Name"></Entry>  
  10.       <Label Text="Age" FontSize="25"/>  
  11.       <Entry x:Name="memberAge" Placeholder="Enter Age"></Entry>  
  12.         
  13.       <StackLayout Orientation="Horizontal">  
  14.         <Button Text="Insert Members" BackgroundColor="Gray" TextColor="White" Clicked="InsertMember"/>  
  15.         <Button Text="Show Memebrs" BackgroundColor="Maroon" TextColor="White" Clicked="ShowMembers"/>  
  16.       </StackLayout>  
  17.     </StackLayout>  
  18.   </ContentPage.Content>  
  19. </ContentPage>  
It will create a UI like this,

UI

Step 7: We need to create an interface class to define platform specific database file, save, and create a database connection.

So, in your PCL project, add an Interface class with the name ISQLite.

Interface

Complete code for ISQLite.cs
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6.   
  7. namespace SQLiteTutorial  
  8. {  
  9.     public interface ISQLite  
  10.     {  
  11.         SQLite.SQLiteConnection GetConnection();  
  12.     }  
  13. }  
Step 8: Next, we create a Model class with name “Member”.

Right Click on PCL Project > Add > Class > Name it Member and Click Add.

Here, Name and  Age are used to set and get the values with the ID which has PrimaryKey and AutoIncrement properties derived from SQLite.

Complete Code snippet for Member class is,
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using SQLite;  
  7.   
  8. namespace SQLiteTutorial  
  9. {  
  10.    public  class Member  
  11.     {  
  12.         [PrimaryKey, AutoIncrement]  
  13.         public int ID { get; set; }  
  14.         public string Name { get; set; }  
  15.         public string Age { get; set; }  
  16.   
  17.         public Member()  
  18.         {  
  19.         }  
  20.     }  
  21. }  
Step 9: Now, we add another class that represents database which includes all the logic for database operations, like Create, Read, Write, Delete, Update, etc.

Let’s add another class with name MemberDatabase for DB logics.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using SQLite;  
  7. using Xamarin.Forms;  
  8. using System.Collections;  
  9.   
  10. namespace SQLiteTutorial  
  11. {  
  12.     public class MemberDatabase  
  13.     {  
  14.         private SQLiteConnection conn;  
  15.   
  16.         //CREATE  
  17.         public MemberDatabase()  
  18.         {  
  19.             conn = DependencyService.Get<ISQLite>().GetConnection();  
  20.             conn.CreateTable<Member>();  
  21.         }  
  22.   
  23.         //READ  
  24.         public IEnumerable<Member> GetMembers()  
  25.         {  
  26.             var members = (from mem in conn.Table<Member>() select mem);  
  27.             return members.ToList();  
  28.         }  
  29.         //INSERT  
  30.         public string AddMember(Member member)  
  31.         {  
  32.             conn.Insert(member);  
  33.             return "success";  
  34.         }  
  35.         //DELETE  
  36.         public string DeleteMember(int id)  
  37.         {  
  38.             conn.Delete<Member>(id);  
  39.             return "success";  
  40.         }  
  41.     }  
  42. }  
Step 10: Now, we need to add platform specific database file creation code to save the database file and to create a database connection, since each platform ha a different folder environment.

FOR ANDROID

Add a class with name Andorid_SQLite and implement ISQLite interface. Under Android Project > Right Click > Add > New Class.

ANDROID

In Andorid_SQLite class, update your code with the following code snippet,
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5.   
  6. using Android.App;  
  7. using Android.Content;  
  8. using Android.OS;  
  9. using Android.Runtime;  
  10. using Android.Views;  
  11. using Android.Widget;  
  12. using SQLiteTutorial.Droid;  
  13. using Xamarin.Forms;  
  14.   
  15. [assembly: Dependency(typeof(Android_SQLite))]  
  16. namespace SQLiteTutorial.Droid  
  17. {  
  18.     public class Android_SQLite : ISQLite  
  19.     {  
  20.         public SQLite.SQLiteConnection GetConnection()  
  21.         {  
  22.             var dbName = "Members.sqlite";  
  23.             var dbPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.ApplicationData);  
  24.             var path = System.IO.Path.Combine(dbPath, dbName);  
  25.             var conn = new SQLite.SQLiteConnection(path);  
  26.             return conn;  
  27.         }  
  28.     }  
  29. }  
FOR WINDOWS

Add a similar class like above inside Windows Phone/Windows Project. Name it Windows_SQLite.

In Windows_SQLite class, update your code with the following code snippet,
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using Windows.Storage;  
  7. using SQLite;  
  8. using System.IO;  
  9. using Xamarin.Forms;  
  10. using SQLiteTutorial.WinPhone;  
  11.   
  12. [assembly: Dependency(typeof(Windows_SQLite))]  
  13. namespace SQLiteTutorial.WinPhone  
  14. {  
  15.     public class Windows_SQLite : ISQLite  
  16.     {  
  17.         public SQLiteConnection GetConnection()  
  18.         {  
  19.             var sqliteFilename = "Member.sqlite";  
  20.             string path = Path.Combine(ApplicationData.Current.LocalFolder.Path, sqliteFilename);  
  21.             var conn = new SQLite.SQLiteConnection(path);  
  22.             return conn;  
  23.         }  
  24.     }  
  25. }  
FOR iOS

Add a similar class like above inside iOS Project. Name it IOS_SQLite.

In IOS_SQLite class, update your code with the following code snippet,
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.IO;  
  4. using System.Text;  
  5. using Xamarin.Forms;  
  6. using SQLite;  
  7. using SQLiteTutorial.iOS;  
  8.   
  9. [assembly: Dependency(typeof(IOS_SQLite))]  
  10. namespace SQLiteTutorial.iOS  
  11. {  
  12.     public class IOS_SQLite : ISQLite  
  13.     {  
  14.         public SQLiteConnection GetConnection()  
  15.         {  
  16.             var dbName = "Member.sqlite";  
  17.             string dbPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder  
  18.             string libraryPath = Path.Combine(dbPath, "..", "Library"); // Library folder  
  19.             var path = Path.Combine(libraryPath, dbName);  
  20.             var conn = new SQLite.SQLiteConnection(path);  
  21.             return conn;  
  22.         }  
  23.     }  
  24. }  
Step 11: In the code behind of FormsPage.xaml.cs, we will initialize MemberDatabase and Member class and call the AddMember function.

Click events. InsertMember and ShowMembers are defined here. And we navigate to MemberList page inside the ShowMember function.

Complete code snippet for FormsPage.xaml.cs
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6.   
  7. using Xamarin.Forms;  
  8.   
  9. namespace SQLiteTutorial  
  10. {  
  11.     public partial class FormsPage : ContentPage  
  12.     {  
  13.         public MemberDatabase memberDatabase;  
  14.         public Member member;  
  15.   
  16.         public FormsPage()  
  17.         {  
  18.             InitializeComponent();  
  19.         }  
  20.   
  21.         public void InsertMember(object o, EventArgs e)  
  22.         {  
  23.             member = new Member();  
  24.             memberDatabase = new MemberDatabase();  
  25.             member.Name = memberName.Text;  
  26.             member.Age = memberAge.Text;  
  27.             memberDatabase.AddMember(member);  
  28.         }  
  29.   
  30.         public async void ShowMembers(object o, EventArgs e)  
  31.         {  
  32.             await Navigation.PushModalAsync(new MemberList());  
  33.         }  
  34.     }  
  35. }  
Step 12: In the App.cs, update App constructor with,
  1. public App()  
  2. {  
  3.     // The root page of your application  
  4.     MainPage = new FormsPage();  
  5. }  
Step 13: Let’s add another xaml page that shows the list of members from database.

Inside PCL, Add a Forms Xaml Page with name MembersList.xaml,

MembersList

In the MemberList.xaml, add a ListView inside ContentPage.Content. Inside the ListView, we have ItemTemplate and DataTemplate. We use ViewCell to display our content.

Complete XAML code will be
  1. <?xml version="1.0" encoding="utf-8" ?>  
  2. <ContentPage xmlns="http://xamarin.com/schemas/2014/forms"  
  3.              xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"  
  4.              x:Class="SQLiteTutorial.MemberList">  
  5.     
  6.   <ContentPage.Content>  
  7.     <ListView x:Name="listMembers" ItemTapped="OnSelected">  
  8.       <ListView.ItemTemplate>  
  9.         <DataTemplate>  
  10.           <ViewCell>  
  11.             <StackLayout Spacing="2" HorizontalOptions="Center">  
  12.               <StackLayout Orientation="Horizontal">  
  13.                 <Label Text="Name: "  FontSize="16"/>  
  14.                 <Label x:Name="firstName"  
  15.                        Text="{Binding Name}"  
  16.                        FontSize="16"  
  17.                        TextColor="Red"/>  
  18.               </StackLayout>  
  19.   
  20.               <StackLayout Orientation="Horizontal">  
  21.                 <Label Text="Age: "  FontSize="16"/>  
  22.                 <Label x:Name="middleName"  
  23.                        Text="{Binding Age}"  
  24.                        FontSize="16"  
  25.                        TextColor="Red"/>  
  26.               </StackLayout>  
  27.             </StackLayout>  
  28.           </ViewCell>  
  29.         </DataTemplate>  
  30.       </ListView.ItemTemplate>  
  31.     </ListView>  
  32.   </ContentPage.Content>  
  33. </ContentPage>  
Step 14: In the code behind MemberList.xaml.cs, update your code with this,
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6.   
  7. using Xamarin.Forms;  
  8.   
  9. namespace SQLiteTutorial  
  10. {  
  11.     public partial class MemberList : ContentPage  
  12.     {  
  13.         public MemberDatabase memberDatabase;  
  14.         public MemberList()  
  15.         {  
  16.             InitializeComponent();  
  17.   
  18.             memberDatabase = new MemberDatabase();  
  19.             var members = memberDatabase.GetMembers();  
  20.             listMembers.ItemsSource = members;  
  21.            // BindingContext = this;  
  22.         }  
  23.   
  24.         public async void OnSelected(object obj, ItemTappedEventArgs args)  
  25.         {  
  26.             var member = args.Item as Member;  
  27.             await DisplayAlert("You selected", member.Name + " " + member.Age, "OK");  
  28.         }  
  29.     }  
  30. }  
Step 15: Run the application in your Android or Windows devices/emulators. When you insert members and then view them, you will be navigated to the Member List Page and see the following output.

Android Screenshots
Screenshots

Screenshots

Windows Screenshots

Windows Screenshots

Windows Screenshots

Note: Since the whole project has very large size, download the Portable Class Library (PCL) only from GitHub.


Similar Articles