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">
  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>
  31. </StackLayout>
  32. </StackLayout>
  33. </StackLayout>
  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 { get; set; }
  8. public string Name { get; set; }
  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;
  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();
  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. }
  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. };
  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. }
  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. }
  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. };
  25. //Update Person
  26. await App.SQLiteDb.SaveItemAsync(person);
  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. }
  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. }
  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");
  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. }
  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. }
  27. //Delete
  28. public Task<int> DeleteItemAsync(Person person)
  29. {
  30. return db.DeleteAsync(person);
  31. }
  33. //Read All Items
  34. public Task<List<Person>> GetItemsAsync()
  35. {
  36. return db.Table<Person>().ToListAsync();
  37. }
  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;
  3. namespace XamarinSQLite
  4. {
  5. public partial class MainPage : ContentPage
  6. {
  7. public MainPage()
  8. {
  9. InitializeComponent();
  10. }
  12. protected async override void OnAppearing()
  13. {
  14. base.OnAppearing();
  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. };
  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. }
  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. }
  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. };
  77. //Update Person
  78. await App.SQLiteDb.SaveItemAsync(person);
  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. }
  90. }
  91. else
  92. {
  93. await DisplayAlert("Required", "Please Enter PersonID", "OK");
  94. }
  95. }
  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");
  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 :)