×

iFour Logo

Stored data in local SQLite.NET Database

Kapil Panchal - April 07, 2021

Listening is fun too.

Straighten your back and cherish with coffee - PLAY !

  • play
  • pause
  • pause
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

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.

Create a New project 

Fig 1: Create a New project

 

Next, select the blank template and platform depending on your requirement.

Select the template and platform. 

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

Install the SQLite-Net-PCL package 

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.


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.

 

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.

Output 

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.

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.

Build Your Agile Team

Enter your e-mail address Please enter valid e-mail

Categories

Ensure your sustainable growth with our team

Talk to our experts
Sustainable
Sustainable
 

Blog Our insights

Next-Gen Programming Languages: Shaping the Future of Software Development in 2024
Next-Gen Programming Languages: Shaping the Future of Software Development in 2024

Introduction Imagine standing in line at the grocery store, waiting to pay for groceries. You pull out your phone and scan each item’s barcode with a single tap. This seemingly...

MySQL vs Azure SQL Database: Understanding Needs, Factors, and Performance Metrics
MySQL vs Azure SQL Database: Understanding Needs, Factors, and Performance Metrics

The world of technology is constantly changing, and databases are at the forefront of this evolution. We have explored different types of databases, both physical and cloud-based, and realized how each of them provides unique features to improve data accessibility and inclusive performance. Leading the pack are MySQL and Azure SQL database services , helping business elevate their processes to new heights.

Streamlining E-commerce Operations with Microsoft PowerApps
Streamlining E-commerce Operations with Microsoft PowerApps

In today's rapidly changing digital world, eCommerce is a dynamic industry. Every day, millions of transactions take place online, so companies are always looking for new and creative methods to improve consumer satisfaction and optimize operations.