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.
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.
Next, select the blank template and platform depending on your requirement.
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
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:
<!--?xml version="1.0" encoding="utf-8”?-->
<contentpage x:class="CrudSqliteDatabase.MainPage" xmlns="http://xamarin.com/schemas/2014/forms" xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml">
<stacklayout>
<stacklayout>
<stacklayout horizontaloptions="Center" verticaloptions="Start">
<entry placeholder="Enter STudent ID" x:name="txtStudentId"></entry>
<entry placeholder="Enter Student Name" x:name="txtStuName"></entry>
<stacklayout horizontaloptions="CenterAndExpand" orientation="Horizontal"><button clicked="btnStudentAdd_Clicked" text="Insert" widthrequest="200" x:name="btnStudentAdd"></button><button clicked="btnStudentData_Clicked" text="Details" widthrequest="200" x:name="btnStudentData">
<stacklayout horizontaloptions="CenterAndExpand" orientation="Horizontal">
</stacklayout></button><button clicked="btnStudentUpdate_Clicked" text="Update" widthrequest="200" x:name="btnStudentUpdate"></button><button clicked="btnStudentDelete_Clicked" text="Delete" widthrequest="200" x:name="btnStudentDelete">
<listview x:name="lstStudentData">
<listview.itemtemplate>
<datatemplate>
<viewcell>
<stacklayout horizontaloptions="CenterAndExpand" verticaloptions="CenterAndExpand">
<grid>
<grid.columndefinitions>
<columndefinition>
<columndefinition>
</columndefinition></columndefinition></grid.columndefinitions>
</grid>
</stacklayout>
</viewcell>
</datatemplate>
</listview.itemtemplate>
</listview></button></stacklayout></stacklayout></stacklayout></stacklayout></contentpage>
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<student>().Wait();
}
//Insert and Update new record
public Task<int> SaveItemAsync(Student student)
{
if (student.StudentId != 0)
{
return database.UpdateAsync(student);
}
else
{
return database.InsertAsync(student);
}
}
//Delete
public Task<int> DeleteItemAsync(Student student)
{
return database.DeleteAsync(student);
}
//Read All the students name
public Task<list<student>> GetItemsAsync()
{
return database.Table<student>().ToListAsync();
}
//Read Student data
public Task<student> GetItemAsync(int StudentId)
{
return database.Table<student>().Where(i => i.StudentId == StudentId).FirstOrDefaultAsync();
}
}
}
</student></student></student></list<student></int></int></student>
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.
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.
Top comments (0)