Stored data in local SQLite.NET Database

namespace CrudSqliteDatabase
{
public partial class App : Application
{
static SQLiteHelper Database;
public static SQLiteHelper SQLiteDb
{
get
{
if (Database == null)
{
Database = new 
//Define the destination for store the data   
SQLiteHelper(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "XamarinStudentSQLite.db3"));
}
return Database;
}
}
public App()
{
InitializeComponent();
MainPage = new MainPage();
}
protected override void OnStart()
{
}
protected override void OnSleep()
{
}
protected override void OnResume()
{
}
}
}
			

namespace CrudSqliteDatabase
{
public partial class MainPage : ContentPage
{
public MainPage()
{
InitializeComponent();
}
protected async override void OnAppearing()
{
base.OnAppearing();
//Get All Student Data 
var StudentList = await App.SQLiteDb.GetItemsAsync();
if (StudentList != null)
{
lstStudentData.ItemsSource = StudentList;
}
}
private async void btnStudentData_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtStudentId.Text))
{
//Get Student Details
var student = await App.SQLiteDb.GetItemAsync(Convert.ToInt32(txtStudentId.Text));
 if (student != null)
{
txtStuName.Text = student.StudentName;
//Display all The Student name in alert box
}
}
else
{
//Display the alert for Please enter the Student ID.
}
}
private async void btnStudentUpdate_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtStudentId.Text))
{
Student student = new Student()
{
StudentId = Convert.ToInt32(txtStudentId.Text),
StudentName = txtStuName.Text
};
//Update Student  
await App.SQLiteDb.SaveItemAsync(student);
txtStudentId.Text = string.Empty;
txtStuName.Text = string.Empty;
//Data updated Successfully to show the message in alert
//Get All Students  
var StudentList = await App.SQLiteDb.GetItemsAsync();
 if (StudentList != null)
{
lstStudentData.ItemsSource =StudentList;
}
}
else
{
//Display the alert for Please enter the Student ID.
}
}
private async void btnStudentDelete_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtStudentId.Text))
{
//Get Student  
var student = await App.SQLiteDb.GetItemAsync(Convert.ToInt32(txtStudentId.Text));
if (student != null)
{
//Delete Student  
await App.SQLiteDb.DeleteItemAsync(student);
txtStudentId.Text = string.Empty;
//Data Deleted Successfully to show the message in alert
//Get All Student  
var StudentList = await App.SQLiteDb.GetItemsAsync();
if (StudentList != null)
{
lstStudentData.ItemsSource = StudentList;
}
}
}
else
{
//Display the alert for Please enter the Student ID.
}
}
private async void btnStudentAdd_Clicked(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtStuName.Text))
{
Student student = new Student()
{
StudentName = txtStuName.Text
};
//Add New Student  
await App.SQLiteDb.SaveItemAsync(student);
txtStuName.Text = string.Empty;
//Display the message for Data Inserted Successfully. 
//Get All Student  
var StudentList = await App.SQLiteDb.GetItemsAsync();
if (StudentList != null)
{
lstStudentData.ItemsSource =StudentList;
}
}
  else
{
//Display the alert for Please enter the Student ID.            }
}
}
			

namespace CrudSqliteDatabase
{
public class SQLiteHelper
{
SQLiteAsyncConnection database;
public SQLiteHelper(string dbPath)
{
database = new SQLiteAsyncConnection(dbPath);
database.CreateTableAsync().Wait();
}
//Insert and Update new record  
public Task SaveItemAsync(Student student)
{
if (student.StudentId != 0)
{
return database.UpdateAsync(student);
}
else
{
return database.InsertAsync(student);
}
}
//Delete  
public Task DeleteItemAsync(Student student)
{
return database.DeleteAsync(student);
}
//Read All the students name
public Task> GetItemsAsync()
{
return database.Table().ToListAsync();
}
//Read Student data
public Task GetItemAsync(int StudentId)
{
return database.Table().Where(i => i.StudentId == StudentId).FirstOrDefaultAsync();
}
}
}

		 

namespace CrudSqliteDatabase.Model
{
public class Student
{
[PrimaryKey, AutoIncrement]
public int StudentId { get; set; }
public string StudentName { get; set; }
}
}
			

Stored data in local SQLite.NET Database In this blog, we will be going to talk about how to use the SQLite local Database in Xamarin.Forms with crud operation. This article describes how to use SQLite.Net as shared code to store and extract information into a local database.   Table of Content 1. SQLite Means 2. How to use SQLite Database in Xamarin forms with Crud Operation? 3. Conclusion SQLite Means SQLite is a popular choice of local database storage in application software. It is probably the most widely used database engine today and is used by many browsers, operating systems and embedded systems, among others. The SQLite database engine enables Xamarin.Forms applications to load and back up data objects into shared code. How to use SQLite Database in Xamarin forms with Crud Operation? Follow these steps about using SQLite Database in Xamarin forms with Crud Operation. Step 1: Create a Xamarin Project. Create a new project by clicking on the Xamarin forms project.   Fig 1: Create a New project   Next, select the blank template and platform depending on your requirement.   Fig 2: Select the template and platform. Step 2: After creating the project, click the project name in the solution explorer then right-click and select the Manage NuGet packages to install the package. There are several NuGet packages with the same names. The following attributes apply to the correct package: ID: sqlite-net-pcl Authors: SQLite-net Owners: praeclarum   Fig 3: Install the SQLite-Net-PCL package.   Step 3: After installing the package you have to create three folders in the project and names as Model, View, and ViewModel. Read More: Planning For Database Backup Strategy - 7 Must-haves You Cannot Ignore Step 4: Specify the database file destination in the system. This specification must be declared in App.xaml.cs file. File Name: App.xaml.cs Code: namespace CrudSqliteDatabase { public partial class App : Application { static SQLiteHelper Database; public static SQLiteHelper SQLiteDb { get { if (Database == null) { Database = new //Define the destination for store the data SQLiteHelper(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "XamarinStudentSQLite.db3")); } return Database; } } public App() { InitializeComponent(); MainPage = new MainPage(); } protected override void OnStart() { } protected override void OnSleep() { } protected override void OnResume() { } } } Step 5: Then create a view page in the view folder and assign the name like MainPage.xaml. File Name: MainPage.xaml Code: Step 6: All the methods are implemented in the Mainpage.xaml.cs file for binding and getting the data on the user side. File Name: MainPage.xaml.cs Code: namespace CrudSqliteDatabase { public partial class MainPage : ContentPage { public MainPage() { InitializeComponent(); } protected async override void OnAppearing() { base.OnAppearing(); //Get All Student Data var StudentList = await App.SQLiteDb.GetItemsAsync(); if (StudentList != null) { lstStudentData.ItemsSource = StudentList; } } private async void btnStudentData_Clicked(object sender, EventArgs e) { if (!string.IsNullOrEmpty(txtStudentId.Text)) { //Get Student Details var student = await App.SQLiteDb.GetItemAsync(Convert.ToInt32(txtStudentId.Text)); if (student != null) { txtStuName.Text = student.StudentName; //Display all The Student name in alert box } } else { //Display the alert for Please enter the Student ID. } } private async void btnStudentUpdate_Clicked(object sender, EventArgs e) { if (!string.IsNullOrEmpty(txtStudentId.Text)) { Student student = new Student() { StudentId = Convert.ToInt32(txtStudentId.Text), StudentName = txtStuName.Text }; //Update Student await App.SQLiteDb.SaveItemAsync(student); txtStudentId.Text = string.Empty; txtStuName.Text = string.Empty; //Data updated Successfully to show the message in alert //Get All Students var StudentList = await App.SQLiteDb.GetItemsAsync(); if (StudentList != null) { lstStudentData.ItemsSource =StudentList; } } else { //Display the alert for Please enter the Student ID. } } private async void btnStudentDelete_Clicked(object sender, EventArgs e) { if (!string.IsNullOrEmpty(txtStudentId.Text)) { //Get Student var student = await App.SQLiteDb.GetItemAsync(Convert.ToInt32(txtStudentId.Text)); if (student != null) { //Delete Student await App.SQLiteDb.DeleteItemAsync(student); txtStudentId.Text = string.Empty; //Data Deleted Successfully to show the message in alert //Get All Student var StudentList = await App.SQLiteDb.GetItemsAsync(); if (StudentList != null) { lstStudentData.ItemsSource = StudentList; } } } else { //Display the alert for Please enter the Student ID. } } private async void btnStudentAdd_Clicked(object sender, EventArgs e) { if (!string.IsNullOrEmpty(txtStuName.Text)) { Student student = new Student() { StudentName = txtStuName.Text }; //Add New Student await App.SQLiteDb.SaveItemAsync(student); txtStuName.Text = string.Empty; //Display the message for Data Inserted Successfully. //Get All Student var StudentList = await App.SQLiteDb.GetItemsAsync(); if (StudentList != null) { lstStudentData.ItemsSource =StudentList; } } else { //Display the alert for Please enter the Student ID. } } } Step 7: Create a SqliteHelper class for insert, update, details, and delete method implement in this class to store the data in local database. This class store in the ViewModel folder. Planning to Hire Dot Net Development Company ? Your Search ends here. See here   File Name: SqliteHelper.cs Code: namespace CrudSqliteDatabase { public class SQLiteHelper { SQLiteAsyncConnection database; public SQLiteHelper(string dbPath) { database = new SQLiteAsyncConnection(dbPath); database.CreateTableAsync().Wait(); } //Insert and Update new record public Task SaveItemAsync(Student student) { if (student.StudentId != 0) { return database.UpdateAsync(student); } else { return database.InsertAsync(student); } } //Delete public Task DeleteItemAsync(Student student) { return database.DeleteAsync(student); } //Read All the students name public Task> GetItemsAsync() { return database.Table().ToListAsync(); } //Read Student data public Task GetItemAsync(int StudentId) { return database.Table().Where(i => i.StudentId == StudentId).FirstOrDefaultAsync(); } } } Step 8: Create a model class for Student data in the Model folder and assign the name as Student.cs. File Name: Student.cs Code: namespace CrudSqliteDatabase.Model { public class Student { [PrimaryKey, AutoIncrement] public int StudentId { get; set; } public string StudentName { get; set; } } } Step 9: Run the project and see the output below.   Fig 4: Output Conclusion In this blog, we discussed the crud operation using the SQLite database in xamarin.forms. SQLite is a popular choice of local database storage in application software. This software is more useful because many times network traffic issues and other issues like no signals, etc. This all are the issues solved using this software. We explained all of the information regarding the SQLite database using the MVVM pattern with an example.

Kapil Panchal

A passionate Technical writer and an SEO freak working as a Technical Content Manager at iFour Technolab, USA. With extensive experience in IT, Services, and Product sectors, I relish writing about technology and love sharing exceptional insights on various platforms. I believe in constant learning and am passionate about being better every day.