Serving Industries Worldwide

Innovative Ways - Satisfied Clientele

Stored data in local SQLite.NET Database


Listening is fun too.

Straighten your back and cherish with coffee - PLAY !

 
 

ai-Hiring-banner

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.

ai-Hiring-banner

Fig 1: Create a New project
 

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

ai-Hiring-banner

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

ai-Hiring-banner

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 ASP.Net Software 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.

ai-Hiring-banner

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.

ai-Hiring-banner

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.

ai-Hiring-banner

Fig 1: Create a New project
 

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

ai-Hiring-banner

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

ai-Hiring-banner

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 ASP.Net Software 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.

ai-Hiring-banner

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.