Xamarin.Forms - SQLite Database CRUD Operations

In this article, you will learn how to use SQLite Database with CRUD Operations in Xamarin.Forms.

Introduction

 
Xamarin.Forms code runs on multiple platforms - each of which has its own filesystem. This means that reading and writing files is most easily done using the native file APIs on each platform. Alternatively, embedded resources are a simpler solution to distribute data files with an app.
Xamarin.Forms - SQLite Database CRUD Operations  
 
SQLite
Xamarin.Forms - SQLite Database CRUD Operations 
SQLite is a lightweight database that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.
SQLite is the most used database in the world. It is built into all mobile phones.
 
Prerequisites
  • Visual Studio 2017 or Later(Windows or Mac)

Setting up a Xamarin.Forms Project

 
Start by creating a new Xamarin.Forms project. You’ll learn more by going through the steps yourself or download the source from here.
 
Visual Studio 2019 has more options in the opening window. Clone or check out the code from any repository or open a project or solution for your computer.
 
Now, you need to click "Create a new project".
 
Xamarin.Forms - SQLite Database CRUD Operations 
 
Now, filter by Project Type: Mobile
 
Choose the Mobile App (Xamarin. forms) project under C# and Mobile.
 
Name your app. You probably want your project and solution to use the same name as your app. Put it on your preferred location for projects and click "Create".
 
Select the blank app and target platforms - Android, iOS and Windows (UWP).
 
Subsequently, go to the solution. In there, you get all the files and sources of your project (.NET Standard). Now, select XAML page and
double-click to open the MainPage.Xaml page.
 
You now have a basic Xamarin.Forms app. Click the "Play" button to try it out.
 

Setting up the User Interface

 
Go to MainPage.Xaml and write the following code.
 
MainPage.xaml
  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:XamarinSQLite"  
  5.              x:Class="XamarinSQLite.MainPage">  
  6.   
  7.     <StackLayout>  
  8.         <StackLayout>  
  9.             <StackLayout HorizontalOptions="Center" VerticalOptions="Start">  
  10.                 <Image x:Name="imgBanner" Source="banner.png" ></Image>  
  11.                 <Image Margin="0,0,0,10" HeightRequest="100" Source="SQLite.png" ></Image>  
  12.                 <Label Margin="0,0,0,10" Text="SQLite" FontAttributes="Bold" FontSize="Large" TextColor="Gray" HorizontalTextAlignment="Center" ></Label>  
  13.                 <Entry x:Name="txtPersonId" Placeholder="PersonId Update and Delete"></Entry>  
  14.                 <Entry x:Name="txtName" Placeholder="Enter Person Name"></Entry>  
  15.                 <StackLayout  HorizontalOptions="CenterAndExpand" Orientation="Horizontal">  
  16.                     <Button x:Name="btnAdd" WidthRequest="200" Text="Add" Clicked="BtnAdd_Clicked" />  
  17.                     <Button x:Name="btnRead" WidthRequest="200" Text="Read" Clicked="BtnRead_Clicked" />  
  18.                 </StackLayout>  
  19.                 <StackLayout HorizontalOptions="CenterAndExpand" Orientation="Horizontal">  
  20.                     <Button x:Name="btnUpdate" WidthRequest="200" Text="Update" Clicked="BtnUpdate_Clicked"/>  
  21.                     <Button x:Name="btnDelete" WidthRequest="200" Text="Delete" Clicked="BtnDelete_Clicked" />  
  22.                 </StackLayout>  
  23.                 <ListView x:Name="lstPersons">  
  24.                     <ListView.ItemTemplate>  
  25.                         <DataTemplate>  
  26.                             <TextCell Text="{Binding Name}" Detail="{Binding PersonID}"></TextCell>  
  27.                         </DataTemplate>  
  28.                     </ListView.ItemTemplate>  
  29.                 </ListView>  
  30.   
  31.             </StackLayout>  
  32.         </StackLayout>  
  33.     </StackLayout>  
  34.   
  35. </ContentPage>  
Click the "Play" button to try it out.
 
Xamarin.Forms - SQLite Database CRUD Operations 
 

NuGet Packages

 
Now, add the following NuGet Packages.
  • sqlite-net-pc
Add sqlite-net-pc NuGet
 
Go to Solution Explorer and select your solution. Right-click and select "Manage NuGet Packages for Solution". Search "sqlite-net-pc" and add Package. Remember to install it for each project (.NET Standard, Android, iO, and UWP).
 
Xamarin.Forms - SQLite Database CRUD Operations 
 
Create a Model
 
In this step, you can create a model for deserializing your response.
 
Person.cs
  1. using SQLite;  
  2. namespace XamarinSQLite  
  3. {  
  4.     public class Person  
  5.     {  
  6.         [PrimaryKey, AutoIncrement]  
  7.         public int PersonID { getset; }  
  8.         public string Name { getset; }  
  9.     }  
  10. }  
Get Local File Path
 
Write the following code to get local file path for storing the database in App.xaml.cs
 
App.xaml.cs
  1. static SQLiteHelper db;  
  2.   
  3. public static SQLiteHelper SQLiteDb  
  4.         {  
  5.             get  
  6.             {  
  7.                 if (db == null)  
  8.                 {  
  9.                     db = new SQLiteHelper(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "XamarinSQLite.db3"));  
  10.                 }  
  11.                 return db;  
  12.             }  
  13.         }  
Create a Table
 
In this step, write the following code to create an SQLite Connection and create the table in SQLiteHelper.cs constructor.
 
SQLiteHelper.cs
  1. SQLiteAsyncConnection db;  
  2.         public SQLiteHelper(string dbPath)  
  3.         {  
  4.             db = new SQLiteAsyncConnection(dbPath);  
  5.             db.CreateTableAsync<Person>().Wait();  
  6.         }  
Read All
 
Now, write the code to read all data from the SQLite Database.
 
SQLiteHelper.cs
  1. //Read All Items  
  2.         public Task<List<Person>> GetItemsAsync()  
  3.         {  
  4.             return db.Table<Person>().ToListAsync();  
  5.         }  
MainPage.Xaml.cs
  1. protected async override void OnAppearing()  
  2.         {  
  3.             base.OnAppearing();  
  4.               
  5.             //Get All Persons  
  6.             var personList = await App.SQLiteDb.GetItemsAsync();  
  7.             if(personList!=null)  
  8.             {  
  9.                 lstPersons.ItemsSource = personList;  
  10.             }  
  11.         }  
Click the "Play" button to try it out.
 
Xamarin.Forms - SQLite Database CRUD Operations 
 
Insert
 
Now, write the following code to insert data into SQLite Database.
  1.  //Insert and Update new record  
  2.         public Task<int> SaveItemAsync(Person person)  
  3.         {  
  4.             if (person.PersonID != 0)  
  5.             {  
  6.                 return db.UpdateAsync(person);  
  7.             }  
  8.             else  
  9.             {  
  10.                 return db.InsertAsync(person);  
  11.             }  
  12.         }  
  13.   
  14.   
  15. private async void BtnAdd_Clicked(object sender, EventArgs e)  
  16.         {  
  17.             if (!string.IsNullOrEmpty(txtName.Text))  
  18.             {  
  19.                 Person person = new Person()  
  20.                 {  
  21.                     Name = txtName.Text  
  22.                 };  
  23.   
  24.                 //Add New Person  
  25.                 await App.SQLiteDb.SaveItemAsync(person);  
  26.                 txtName.Text = string.Empty;  
  27.                 await DisplayAlert("Success""Person added Successfully""OK");  
  28.                 //Get All Persons  
  29.                 var personList = await App.SQLiteDb.GetItemsAsync();  
  30.                 if (personList != null)  
  31.                 {  
  32.                     lstPersons.ItemsSource = personList;  
  33.                 }  
  34.             }  
  35.             else  
  36.             {  
  37.                 await DisplayAlert("Required""Please Enter name!""OK");  
  38.             }  
  39.         }  
Click the "Play" button to try it out.
 
Xamarin.Forms - SQLite Database CRUD Operations 
 
Read
 
Now, write the following code to read data from the SQLite Database.
  1. //Read Item  
  2.        public Task<Person> GetItemAsync(int personId)  
  3.        {  
  4.            return db.Table<Person>().Where(i => i.PersonID == personId).FirstOrDefaultAsync();  
  5.        }  
  6.   
  7. private async void BtnRead_Clicked(object sender, EventArgs e)  
  8.        {  
  9.            if (!string.IsNullOrEmpty(txtPersonId.Text))  
  10.            {  
  11.                //Get Person  
  12.                var person = await App.SQLiteDb.GetItemAsync(Convert.ToInt32(txtPersonId.Text));  
  13.                if(person!=null)  
  14.                {  
  15.                    txtName.Text = person.Name;  
  16.                    await DisplayAlert("Success","Person Name: "+ person.Name, "OK");  
  17.                }  
  18.            }  
  19.            else  
  20.            {  
  21.                await DisplayAlert("Required""Please Enter PersonID""OK");  
  22.            }  
  23.        }   
Click the "Play" button to try it out.
 
Xamarin.Forms - SQLite Database CRUD Operations 

Update
 
Now, write the following code to update the data in the SQLite Database.
  1. //Insert and Update new record  
  2.         public Task<int> SaveItemAsync(Person person)  
  3.         {  
  4.             if (person.PersonID != 0)  
  5.             {  
  6.                 return db.UpdateAsync(person);  
  7.             }  
  8.             else  
  9.             {  
  10.                 return db.InsertAsync(person);  
  11.             }  
  12.         }  
  13.   
  14.   
  15. private async void BtnUpdate_Clicked(object sender, EventArgs e)  
  16.         {  
  17.             if (!string.IsNullOrEmpty(txtPersonId.Text))  
  18.             {  
  19.                 Person person = new Person()  
  20.                 {  
  21.                     PersonID=Convert.ToInt32(txtPersonId.Text),  
  22.                     Name = txtName.Text  
  23.                 };  
  24.   
  25.                 //Update Person  
  26.                 await App.SQLiteDb.SaveItemAsync(person);  
  27.   
  28.                 txtPersonId.Text = string.Empty;  
  29.                 txtName.Text = string.Empty;  
  30.                 await DisplayAlert("Success""Person Updated Successfully""OK");  
  31.                 //Get All Persons  
  32.                 var personList = await App.SQLiteDb.GetItemsAsync();  
  33.                 if (personList != null)  
  34.                 {  
  35.                     lstPersons.ItemsSource = personList;  
  36.                 }  
  37.   
  38.             }  
  39.             else  
  40.             {  
  41.                 await DisplayAlert("Required""Please Enter PersonID""OK");  
  42.             }  
  43.         }  
Click the "Play" button to try it out.
 
Xamarin.Forms - SQLite Database CRUD Operations
 
Delete
 
Now, write the following code to delete data from SQLite Database.
  1. //Delete  
  2.         public Task<int> DeleteItemAsync(Person person)  
  3.         {  
  4.             return db.DeleteAsync(person);  
  5.         }  
  6.   
  7.   
  8.   
  9. private async void BtnDelete_Clicked(object sender, EventArgs e)  
  10.         {  
  11.             if (!string.IsNullOrEmpty(txtPersonId.Text))  
  12.             {  
  13.                 //Get Person  
  14.                 var person = await App.SQLiteDb.GetItemAsync(Convert.ToInt32(txtPersonId.Text));  
  15.                 if (person != null)  
  16.                 {  
  17.                     //Delete Person  
  18.                     await App.SQLiteDb.DeleteItemAsync(person);  
  19.                     txtPersonId.Text = string.Empty;  
  20.                     await DisplayAlert("Success""Person Deleted""OK");  
  21.                       
  22.                     //Get All Persons  
  23.                     var personList = await App.SQLiteDb.GetItemsAsync();  
  24.                     if (personList != null)  
  25.                     {  
  26.                         lstPersons.ItemsSource = personList;  
  27.                     }  
  28.                 }  
  29.             }  
  30.             else  
  31.             {  
  32.                 await DisplayAlert("Required""Please Enter PersonID""OK");  
  33.             }  
  34.         }  
Click the "Play" button to try it out.

Xamarin.Forms - SQLite Database CRUD Operations 
 
Full code

SQLiteHelper.cs
  1. using System.Threading.Tasks;  
  2. using SQLite;  
  3. namespace XamarinSQLite  
  4. {  
  5.     public class SQLiteHelper  
  6.     {  
  7.         SQLiteAsyncConnection db;  
  8.         public SQLiteHelper(string dbPath)  
  9.         {  
  10.             db = new SQLiteAsyncConnection(dbPath);  
  11.             db.CreateTableAsync<Person>().Wait();  
  12.         }  
  13.   
  14.         //Insert and Update new record  
  15.         public Task<int> SaveItemAsync(Person person)  
  16.         {  
  17.             if (person.PersonID != 0)  
  18.             {  
  19.                 return db.UpdateAsync(person);  
  20.             }  
  21.             else  
  22.             {  
  23.                 return db.InsertAsync(person);  
  24.             }  
  25.         }  
  26.   
  27.         //Delete  
  28.         public Task<int> DeleteItemAsync(Person person)  
  29.         {  
  30.             return db.DeleteAsync(person);  
  31.         }  
  32.   
  33.         //Read All Items  
  34.         public Task<List<Person>> GetItemsAsync()  
  35.         {  
  36.             return db.Table<Person>().ToListAsync();  
  37.         }  
  38.   
  39.          
  40.         //Read Item  
  41.         public Task<Person> GetItemAsync(int personId)  
  42.         {  
  43.             return db.Table<Person>().Where(i => i.PersonID == personId).FirstOrDefaultAsync();  
  44.         }  
  45.     }  
  46. }  
MainPage.Xaml.cs
  1. using Xamarin.Forms;  
  2.   
  3. namespace XamarinSQLite  
  4. {  
  5.     public partial class MainPage : ContentPage  
  6.     {  
  7.         public MainPage()  
  8.         {  
  9.             InitializeComponent();  
  10.         }  
  11.   
  12.         protected async override void OnAppearing()  
  13.         {  
  14.             base.OnAppearing();  
  15.               
  16.             //Get All Persons  
  17.             var personList = await App.SQLiteDb.GetItemsAsync();  
  18.             if(personList!=null)  
  19.             {  
  20.                 lstPersons.ItemsSource = personList;  
  21.             }  
  22.         }  
  23.         private async void BtnAdd_Clicked(object sender, EventArgs e)  
  24.         {  
  25.             if (!string.IsNullOrEmpty(txtName.Text))  
  26.             {  
  27.                 Person person = new Person()  
  28.                 {  
  29.                     Name = txtName.Text  
  30.                 };  
  31.   
  32.                 //Add New Person  
  33.                 await App.SQLiteDb.SaveItemAsync(person);  
  34.                 txtName.Text = string.Empty;  
  35.                 await DisplayAlert("Success""Person added Successfully""OK");  
  36.                 //Get All Persons  
  37.                 var personList = await App.SQLiteDb.GetItemsAsync();  
  38.                 if (personList != null)  
  39.                 {  
  40.                     lstPersons.ItemsSource = personList;  
  41.                 }  
  42.             }  
  43.             else  
  44.             {  
  45.                 await DisplayAlert("Required""Please Enter name!""OK");  
  46.             }  
  47.         }  
  48.   
  49.         private async void BtnRead_Clicked(object sender, EventArgs e)  
  50.         {  
  51.             if (!string.IsNullOrEmpty(txtPersonId.Text))  
  52.             {  
  53.                 //Get Person  
  54.                 var person = await App.SQLiteDb.GetItemAsync(Convert.ToInt32(txtPersonId.Text));  
  55.                 if(person!=null)  
  56.                 {  
  57.                     txtName.Text = person.Name;  
  58.                     await DisplayAlert("Success","Person Name: "+ person.Name, "OK");  
  59.                 }  
  60.             }  
  61.             else  
  62.             {  
  63.                 await DisplayAlert("Required""Please Enter PersonID""OK");  
  64.             }  
  65.         }  
  66.   
  67.         private async void BtnUpdate_Clicked(object sender, EventArgs e)  
  68.         {  
  69.             if (!string.IsNullOrEmpty(txtPersonId.Text))  
  70.             {  
  71.                 Person person = new Person()  
  72.                 {  
  73.                     PersonID=Convert.ToInt32(txtPersonId.Text),  
  74.                     Name = txtName.Text  
  75.                 };  
  76.   
  77.                 //Update Person  
  78.                 await App.SQLiteDb.SaveItemAsync(person);  
  79.   
  80.                 txtPersonId.Text = string.Empty;  
  81.                 txtName.Text = string.Empty;  
  82.                 await DisplayAlert("Success""Person Updated Successfully""OK");  
  83.                 //Get All Persons  
  84.                 var personList = await App.SQLiteDb.GetItemsAsync();  
  85.                 if (personList != null)  
  86.                 {  
  87.                     lstPersons.ItemsSource = personList;  
  88.                 }  
  89.   
  90.             }  
  91.             else  
  92.             {  
  93.                 await DisplayAlert("Required""Please Enter PersonID""OK");  
  94.             }  
  95.         }  
  96.   
  97.         private async void BtnDelete_Clicked(object sender, EventArgs e)  
  98.         {  
  99.             if (!string.IsNullOrEmpty(txtPersonId.Text))  
  100.             {  
  101.                 //Get Person  
  102.                 var person = await App.SQLiteDb.GetItemAsync(Convert.ToInt32(txtPersonId.Text));  
  103.                 if (person != null)  
  104.                 {  
  105.                     //Delete Person  
  106.                     await App.SQLiteDb.DeleteItemAsync(person);  
  107.                     txtPersonId.Text = string.Empty;  
  108.                     await DisplayAlert("Success""Person Deleted""OK");  
  109.                       
  110.                     //Get All Persons  
  111.                     var personList = await App.SQLiteDb.GetItemsAsync();  
  112.                     if (personList != null)  
  113.                     {  
  114.                         lstPersons.ItemsSource = personList;  
  115.                     }  
  116.                 }  
  117.             }  
  118.             else  
  119.             {  
  120.                 await DisplayAlert("Required""Please Enter PersonID""OK");  
  121.             }  
  122.         }  
  123.     }  
  124. }  
I hope you have understood, how to use the SQLite Database with CRUD operations in Xamarin.Forms. Thanks for reading. Please share your comments and feedback.
 
Happy Coding :)