Post
Ask Question

Working With SQLite In Xamarin.Forms Application

Introduction

Xamarin is a Microsoft-owned San Francisco, California based software company founded in May 2011. Xamarin helps in creating cross-platform mobile applications. Here, in this tutorial, we will focus on the following things.
  1. Creating a simple registration form.
  2. Connecting to the SQLite database.
  3. Using SQLite to save the data from UI.
  4. Retrieving and showing data using a ListView in the UI.
To work with Xamarin, you need to install Xamarin Components on your machine. There are mainly two editors to work with Xamarin (Visual Studio and Xamrin Studio). For this demo, I have chosen Visual Studio 2017.

Let us get started with designing a form like this.
 

We have used 4 types of controls to design the form. Let's learn each of them.
 
Entry

The Xamarin.Forms Entry is used for single-line text input. It exposes a text property. This property can be used to set and read the text presented by the entry.
  1. <Entry x:Name="FirstName" Placeholder="First Name"></Entry>  
The placeholder is mainly for displaying the watermark text on the control. To make the entry as a password, set the IsPassword property to True.
  1. <Entry x:Name="Password" Placeholder="Password" IsPassword="True"></Entry>  
DatePicker

To input a date into an Android application, the Xamrin provides the DatePicker widget and the DatePickerDialog. The DatePicker allows users to select the year, month, and day in a consistent interface across devices and applications.
  1. <DatePicker x:Name="DOB"></DatePicker>  
 
Now, here is the full code for designing the Registration View.
  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.              xmlns:local="clr-namespace:SqliteReg"  
  5.              x:Class="SqliteReg.MainPage" BackgroundColor="#1A73D7" Title="Registration Page">  
  6.   
  7.     <StackLayout VerticalOptions="CenterAndExpand" Padding="5">  
  8.          
  9.             <Entry x:Name="FirstName" Placeholder="First Name" ></Entry>  
  10.           
  11.             <Entry x:Name="LastName" Placeholder="Last Name"></Entry>  
  12.         <Entry Keyboard="Chat" />  
  13.         <Entry x:Name="Email" Placeholder="Email"></Entry>  
  14.         <Entry x:Name="Password" Placeholder="Password" IsPassword="True"></Entry>  
  15.         <Entry Placeholder="Confirm Password" IsPassword="True"></Entry>  
  16.         <Label  Text="Date Of Birth"></Label>  
  17.         <DatePicker x:Name="DOB"></DatePicker>  
  18.         <Label Text="Address"></Label>  
  19.         <Editor x:Name="Address"></Editor>  
  20.   
  21.           
  22.          
  23.         <StackLayout Orientation="Horizontal">  
  24.             <Button Text="Sign Up" Clicked="Signed_Clicked"></Button>  
  25.             <Button Text="show" Clicked="Show_Clicked"></Button>  
  26.         </StackLayout>  
  27.         <Label Text="Already have account? Sign In" TextColor="Blue"></Label>  
  28.          
  29.     </StackLayout>  
  30.   
  31. </ContentPage>  

Now, let's start learning how to work with the SQLite Database.

SQLite is an open source and relational database management system contained in a C programming library.

In contrast to many other database management systems, SQLite is not a client server database engine this is mainly used as a local storage program and the data will be saved and retrieved within the mobile device. SQLite is a popular choice for embedded database software for local/client storage in application software such as web browsers. This mainly uses C/C++ API to communicate with other 3rd party devices.

 

To use in Xamrin, we need to use the following references in our project.

 
This SQL.Net-PCL.dll does the following things.
  • Gives support to define the data entity.
  • Helps in interacting with SQLite Engine synchronously or asynchronously. 
For connecting with SQLite, we need the following steps.
 
Add the following statement to the C# files where data access is required.
  1. using SQLite;  
The first step is to create an interface for ISQLite in your PCL with an abstract method returning SQLiteConnection.
  1. using SQLite;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Text;  
  5.   
  6. namespace App7  
  7. {  
  8.     public interface Isqlite  
  9.     {  
  10.         SQLiteConnection GetConnection();  
  11.     }  
  12. }  
 Now, create a new SQliteDroid class which will be inherited from the interface Isqlite.
  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 SQLite;  
  13. using SQLitePCL;  
  14. using System.IO;  
  15. using Xamarin.Forms;  
  16. using App7.Droid;  
  17.   
  18. [assembly:Dependency(typeof(SQliteDroid))]  
  19. namespace App7.Droid  
  20. {  
  21.     public class SQliteDroid : Isqlite  
  22.     {  
  23.         public SQLiteConnection GetConnection()  
  24.         {  
  25.             var dbase = "Mydatabase";  
  26.             var dbpath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.ApplicationData);  
  27.             var path = Path.Combine(dbpath, dbase);  
  28.             var connection = new SQLiteConnection(path);  
  29.             return connection;  
  30.               
  31.         }  
  32.     }  
  33. }  
 Now, create the following Model for the CRUD opperation.
  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 SQLite;  
  13.   
  14. namespace App7.Droid.Model  
  15. {  
  16.     public class Registration  
  17.     {  
  18.         [PrimaryKey,AutoIncrement]  
  19.         public int id { get; set; }  
  20.         public string FirstName { get; set; }  
  21.         public string LastName { get; set; }  
  22.         public string Dob { get; set; }  
  23.         public string Email{ get; set; }  
  24.         public string Password { get; set; }  
  25.         public string Address { get; set; }  
  26.   
  27.     }  
  28. }  
Here is the MainPage.cs where you have to call GetConnection via DependencyService.

Once you define the connection, just create the Database as the Model Name.
  1. public partial class MainPage : ContentPage  
  2.    {  
  3.        public SQLiteConnection conn;  
  4.        public Registration regmodel;  
  5.        public MainPage()  
  6.        {  
  7.            InitializeComponent();  
  8.            conn = DependencyService.Get<Isqlite>().GetConnection();  
  9.            conn.CreateTable<Registration>();  
  10.        }  
Now, the connection is established. You can start doing your task. Here is the complete code for registration process. 
  1. private void Signed_Clicked(object sender, EventArgs e)  
  2.        {  
  3.            Registration reg = new Registration();  
  4.            reg.FirstName = FirstName.Text;  
  5.            reg.LastName = LastName.Text;  
  6.            reg.Dob = DOB.Date.ToString();  
  7.            reg.Email = Email.Text;  
  8.            reg.Password = Password.Text;  
  9.            reg.Address = Address.Text;  
  10.            int x=0;  
  11.            try  
  12.            {  
  13.                x = conn.Insert(reg);  
  14.            }  
  15.            catch(Exception ex)  
  16.            {  
  17.                throw ex;  
  18.            }  
  19.              
  20.            if (x == 1)  
  21.            {  
  22.                DisplayAlert("Registration", "Thanks for Registration", "Cancel");  
  23.            }  
  24.            else  
  25.            {  
  26.                DisplayAlert("Registration Failled!!!", "Please try again", "ERROR");  
  27.            }  
  28.   
  29.        }  
So, this will save the data in SQLite Database.

Now, to implement the retrieval functionality, here is the code. We need to redirect to a specific page and perform the retrieval operation.

For this, add a new page and name it as mentioned below.

 
 
Here is the Show Button Click which will redirect to the Display page.
  1. private void Show_Clicked(object sender, EventArgs e)  
  2.         {  
  3.             try  
  4.             {  
  5.                 Navigation.PushAsync(new Display());  
  6.                   
  7.                  
  8.             }  
  9.             catch(Exception ex)  
  10.             {  
  11.                 throw ex;  
  12.             }  
  13.               
  14.   
  15.   
  16.   
  17.         }  
Here is the code of the Display page to retrieve the data and show in the ListView.
  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="SqliteReg.Display">  
  5.     <ContentPage.Content>  
  6.         <StackLayout>  
  7.             <StackLayout Orientation="Horizontal">  
  8.                 <ListView x:Name="myListView" HasUnevenRows="True" >  
  9.                      
  10.                     <ListView.ItemTemplate>  
  11.                         <DataTemplate>  
  12.                             <TextCell Text="{Binding FirstName}"        Detail="{Binding Address}"/> 
  13.                         </DataTemplate>      
  14.                     </ListView.ItemTemplate>  
  15.                 </ListView>  
  16.                   
  17.             </StackLayout>  
  18.         </StackLayout>  
  19.     </ContentPage.Content>  
  20. </ContentPage>  
Here is the Display.cs file code.
  1. using App7.Droid.Model;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Text;  
  6. using System.Threading.Tasks;  
  7.   
  8. using Xamarin.Forms;  
  9. using Xamarin.Forms.Xaml;  
  10. using SQLite;  
  11. using App7;  
  12.   
  13. namespace SqliteReg  
  14. {  
  15.     [XamlCompilation(XamlCompilationOptions.Compile)]  
  16.     public partial class Display : ContentPage  
  17.     {  
  18.         public SQLiteConnection conn;  
  19.         public Registration regmodel;  
  20.         public Display()  
  21.         {  
  22.             InitializeComponent();  
  23.             conn = DependencyService.Get<Isqlite>().GetConnection();  
  24.             conn.CreateTable<Registration>();  
  25.             DisplayDetails();  
  26.   
  27.         }  
  28.   
  29.         public void DisplayDetails()  
  30.         {  
  31.   
  32.             var details = (from x in conn.Table<Registration>() select x).ToList();  
  33.             myListView.ItemsSource = details;  
  34.         }  
  35.   
  36.          
  37.     }  
  38. }  
 
 
So, in this way, we can save and retrieve the data from the SQLite DB using Xamarin.Forms.

Conclusion

Hope this article will help all the new members who are learning Xamarin. If there is any doubt or any issue in understanding, please feel free to comment or write to me.


Similar Articles