SQLite.NET as a Local Database in Xamarin.how it works?

DotNek Software Development 14 minute(s) read

Last updated: Feb 23, 2021

Why SQLite?

SQLite is the most used and preferred database software for web and mobile developers. This is because it is an open and free source, and it provides quite simple and server less set up.

What exactly is SQLite?

SQLite is a transactional, zero-configuration, public domain SQLite database software, or engine. It means SQLite is a complete mechanism available for web or app developers where they can store their data in a structured way, and along with this, they also get access to the source code as it is free and open source.

SQLite as a local database in xamarin .Forms are perfectly fit for a simple reason as it is readily available on both Android and iOS. This all means that you can use SQLite technology whenever you need to write a xamarin.Forms app.
SQLite is a zero-configured trading software or engine owned by SQLite. This means that SQLite is a complete mechanism available to web or application developers in which they can store their data structurally, as well as access free and open source code.
So, with SQLite, you will have the ability to put database features or functionality to your Xamarin.Forms app to retrieve and store any type of data easily.

One of the most basic needs is the ability to store and retrieve information. This can be implemented and set up using different tools on different platforms. SQLite is another data storage and retrieval tool known for its unique features. These features include power, free and support for all operating systems such as Windows, Linux, Mac, Android and iOS. SQLite data storage and retrieval tool is programmed in C language, and it is also a software that has a Public Domain license, which means that this tool is not owned by any organization or individual. It is not limited and everyone can use it without any restrictions.

Save information

Every application developer cares about data storage, whether it is a game application or any other type of application. This data can be statistics from the user or any other type of data that your user or you will need at some point in using the application.

Do you know that you have decided to use xamarin? Forms the path, then you must save your user data in your application. The solution to this problem is SQLite.

SQLite database

Steps to use SQLite in Xamarin.Forms

1. In the first step, what you do is to create a file of the SQLite database right directly in the Xamarin.Forms or if you already have a pre-created SQLite database file, then use it and insert it in your app.
2. Next, you link your
SQLite database file to both iOS and Android platforms specific project in Xamarin.Forms.
3. Connect SQLite.Net-PCL to every project in a solution of your xamarin.Forms.
4. From the app bundle, copy the database's file and put it on the writable place on the file system of your mobile device. And then open this file for writing and reading and to implement it on the Android and iOS platforms in order to do this step.
5. Create a model that matches the table definitions of SQLite Database in the Xamarin shared, common code base specific project
6. In the last step, you will have to create an SQLite connection and Database Access class in the Xamarin common code base project to perform CRUD operations on the database. Use results to bind the list of your XAML views.

Use SQLite.NET in Xamarin.Forms

To use Xamarin . Forms, you need to know some concepts in advance so that you can easily use the SQLite.NET database in Xamarin.Forms.
Depending on the format you choose for your project, different approaches are used to integrate SQLite.NET:

PCL Solution:

PCL solutions can easily be referred to SQLite classes. This reference can be easily done using
SQLite.NET PCL NuGet packages and can be combined with common code in the database. Xamarin.Forms Dependency Service can be used to connect to the SQLite database.

Collaborative projects:

Collaborative projects projects are integrated directly with the Net resource via GitHub and are referred to SQLite database classes through the combination with these resources.

Compile commands are used to use code for specific platforms, meaning that in order to be able to use the code on a different and specific platform, compiler commands must be used. Most code in SQLite.NET can be shared on other platforms and can be shared on other platforms, all you have to do is set up the database connection with each platform and then the location. Specify the SQLite database file. After this, you can easily share and use all the code in the SQLite database on all platforms.

How to build a new project and add SQLite support?

1- Create a new project for example called XamarinSqliteSample
2. Add a new package called SQLite.Net to the created project (this package will act as a cover for SQLite that allows access to all native SQLite features through Xamarin.Forms PCL or Shared Project and makes it possible.) 3. To add SQLite.Net support, right-click on the project name and select Manage NuGet Packages.
4. Select and install SQLite.Net PCL.
5. To separate the performance of each platform, you must write a separate interface for each.

Create an interface called I SQLite, for example, and write the following code inside:

using SQLite.Net;
namespace XamarinSqliteSample
{ public interface ISQLite
{
SQLite Connection Get Connection ();
} }


All we have to do is locate the database file and then make the connection between them. By implementing this interface in native projects through Dependency Service, communication between them can be established.

Build a database

After creating an interface for each platform, separating their functions and creating a connection, we can define our database.
In this example, we define and create a database that stores student information. The name of the class that will hold the student information in the database is Student.

using System;
using SQLite.Net.Attributes;

namespace XamarinSqliteSample
{ public class Student
{
[Primary Key, Auto Increment] public int Id {get; set;} public string Name {get; set;} public string Address {get; set;}
public string Phone {get; set;}

public Student ()
{
}
} }


Using this code, we designed and created four columns, id is defined as Primary Key because its values will be unique and non-duplicate. In this example, using this code, the id value is defined as Auto Increment.

Next, for the convenience of work, we create a class as a database and keep the logic and how to access the database and its tables inside the same class. The name of this class, which is a database, is called Student DB.

using System. LINQ;
using SQLite.Net;
using Xamarin.Forms;

namespace XamarinSqliteSample
{
public class Student DB
{
private SQLite Connection _sqlconnection;

public Student DB ()
{
//Getting connection and Creating table
_sqlconnection = DependencyService.Get(). Get Connection ();
_sqlconnection. Create Table ();
}

//Get all students public IEnumerable Get Students ()
{
return (from t in _sqlconnection. Table () select t). To List ();
}

//Get specific student
public Student Get Student (int id)
{
return _sqlconnection. Table (). FirstOrDefault (t => t. Id == id);
}

//Delete specific student
public void Delete Student (int id)
{
_sqlconnection. Delete(id);
}

//Add new student to DB
public void AddStusent (Student student)
{
_sqlconnection. Insert(student);
}
}
}


Two very important points in these codes:

1- In order to be able to access the classes registered in the Student DB interface, we must use the Get Connection () method, which is located in the Dependency Service class.
2- Using the Create Table method, a table called Student is created in the SQLite Connection class . This table is created when it does not already exist.

IOS implementation

The main problem with using SQLite Database is when we want to store real files in the database. To solve or fix this problem, we must take steps.
Both SQLite.Net PCL and SQLite.NET PCL - XamarinIOS Platform packages must be added to the project before adding any feature from the SQLite database to the iOS project. It is possible to add these packages through Nuget.
The main obstacle that needs to be solved when using SQLite is when we want to store the data in the actual database file. This barrier varies from platform to platform.
To implement the ISQLite interface, you need to create a class called SQLite iOS.

using System;
using Xamarin.Forms;
using XamarinSqliteSample. IOS;
using System.IO;

[assembly: Dependency (type of (SQLite iOS))]
namespace XamarinSqliteSample. IOS 6- {
public class SQLite iOS: ISQLite
{
public SQLite.Net.SQLiteConnection Get Connection ()
{
var filename = "Student.db3";
var documents Path = Environment.GetFolderPath(Environment.SpecialFolder. Personal);
var library Path = Path.Combine (documents Path, "..", "Library"); 14- var path = Path.Combine (library Path, filename);

var platform = new SQLite.Net.Platform. XamarinIOS.SQLitePlatformIOS(); var connection = new SQLite.Net.SQLiteConnection(platform, path);

return connection;
}
}
}


The assembly attribute at the top of this code is used to retrieve the Dependency Service class via the Get method, because we want to define this class as Dependency.
Once we have access to the database file storage, we add an object called SQLite Connection and return it to the original PCL project.

Android implementation

The Android implementation is quite similar to the iOS implementation, but the only difference between the Android and iOS implementations is that the database file paths are different. Be sure to install SQLite.Net PCL and SQLite.NET PCL - XamarinAndroid packages through Nuget.
To implement the ISQLite interface, you need to create a class called SQLite Android.

using System;
using System.IO;
using Xamarin.Forms;
using XamarinSqliteSample. Droid;

[assembly: Dependency (type of (SQLite Android))]
namespace XamarinSqliteSample. Droid
{
public class SQLite Android: ISQLite
{
public SQLite.Net.SQLiteConnection Get Connection ()
{
var filename = "Student.db3";
var documents path = Environment.GetFolderPath(Environment.SpecialFolder. Personal);
var path = Path. Combine (documents path, filename);

var platform = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid();
var connection = new SQLite.Net.SQLiteConnection(platform, path); 16. return connection;
}
}
}


Windows Phone implementation

You need to spend a little more time and work to implement Windows Phone, because database engines are available on Android and iOS operating systems and are easier to set up.
After you have installed the SQLite database , you need to add the SQLite.NetPCL and SQLite.Net PCL - Windows Phone 8 Platform packages to your project.
To implement the ISQLite interface, you need to create a class called Windows Phone.

The steps for adding support to the Windows Phone project are as follows:

using Xamarin.Forms;
using XamarinSqliteSample.WinPhone;
using System.IO;
using Windows. Storage;

[assembly: Dependency (type of(SQLite_WinPhone))] namespace XamarinSqliteSample.WinPhone
{ public class SQLite_WinPhone: ISQLite
{
public SQLite_WinPhone ()
{
}
public SQLite.Net.SQLiteConnection Get Connection ()
{
var filename = "Student.db3";
var path = Path.Combine (ApplicationData.Current.LocalFolder.Path, filename);

var platform = new SQLite.Net.Platform.WindowsPhone8.SQLitePlatformWP8();
var connection = new SQLite.Net.SQLiteConnection(platform, path);
return connection;
}
} }



Read more on:
https://www.c-sharpcorner.com/UploadFile/f8fa6c/use-sqlite-net-in-xamarin-forms/