How to store data in a local SQLite.NET database in Xamarin?
In this tutorial you will learn:
- How to store data in the local Xamarin database ?
- How to use a NuGet Package Manager to add a NuGet Package?
Note: In order to be able to do this project, you must have already successfully completed the project of building multi-page applications .
How to use local storage in Xamarin Forms using Visual Studio?
1- Open Visual Studio
2- Open the Notes solution project.
3- In the Solution Explorer section, select the Notes project and right-click on it.
4- After right-clicking on the project, select Manage NuGet Packages.
5- In the NuGet Package Manager section, select the Browser tab to search.
6- Search for the SQLite-net-plc. package in NuGet , and after finding it, click Install to add it to the project.
Note:
There are packages with similar names, the package you should choose has the following specifications:
- Author (s): Frank A. Krueger
- Id: SQLite-net-plc.
- NuGet link: SQLite-net-plc.
- Open the Models folder in the Solution Explorer section of the Note.cs project and delete the existing code and enter the following code there.
using System;
using SQLite;
namespace Notes. Models
{
public class Note
{
[PrimaryKey, AutoIncrement]
public int ID {get; set;}
public string Text {get; set;}
public DateTime Date {get; set;}
}
}
These codes define the Note model, which is used to store note data in applications .
Each note in the SQLite.NET database has its own unique id, in fact the ID, PrimeryKey, and AutoIncrement attributes make each note have its own id.
1- Record the changes made and exit that window.
2- Create a folder called Data in the Solution Explorer section and add it to the Notes project.
3- In the folder created in the Notes project and in the Solution Explorer section, create a class called NoteDatabase.
4- In the created NoteDatabase.cs class, delete all existing codes and replace the following codes.
using System.Collections. Generic;
using System.Threading. Tasks;
using SQLite;
using Notes.Models;
namespace Notes.Data
{
public class NoteDatabase
{
readonly SQLiteAsyncConnection _database;
public NoteDatabase (dbPath string)
{
_database = new SQLiteAsyncConnection (dbPath);
_database. CreateTableAsync <note> (). Wait ();
}
public Task <list <note>> GetNotesAsync ()
{
return _database. Table <note> (). ToListAsync ();
}
public Task <note> GetNoteAsync (int id)
{
return _database. Table <note> ()
. Where (i => i.ID == id)
. FirstOrDefaultAsync ();
}
public Task <int> SaveNoteAsync (Note note)
{
if (note.ID! = 0)
{
return _database. UpdateAsync (note);
}
else
{
return _database. InsertAsync (note);
}
}
public Task <int> DeleteNoteAsync (Note note)
{
return _database. DeleteAsync (note);
}
}
}
The code written in this class is used to create a database, read data, write and add data and delete it.
Constructor This class receives the address of the file in which the database was created and stored as an argument. The address of the file in which the database was created and stored is provided by the App class. Asynchronous SQLite.NET APIs in these classes are used to transfer database calculations to the background thread.
- Click on the App.xaml.cs section in the Solution Explorer of the Notes project and replace the code in the file with the code below.
using System;
using System.IO;
using Xamarin.Forms;
using Notes.Data;
namespace Notes
{
public partial class App: Application
{
static NoteDatabase database;
public static NoteDatabase Database
{
get
{
if (database == null)
{
database = new NoteDatabase (Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder. LocalApplicationData), "Notes.db3"));
}
return database;
}
}
public App ()
{
InitializeComponent ();
MainPage = new NavigationPage (new NotesPage ());
}
protected override void OnStart ()
{
// Handle when your app starts
}
protected override void OnSleep ()
{
// Handle when your app sleeps
}
protected override void OnResume ()
{
// Handle when your app resumes
}
}
}
By entering this code, you create a database that creates a NoteDatabase instance as a singleton and also receives its filename as an argument and sends it to the NoteDatabase constructor. Creating a database as a singleton has the advantage that only one connection is created and used to communicate with the database with the same connection.
- Save the changes made and exit that window.
- Click on the NotePage.xaml.cs section in the Solution Explorer of the Notes project and replace the code listed below with the OnAppearing method with the code inside the file.
protected override async void OnAppearing ()
{
base. OnAppearing ();
listView.ItemsSource = await App.Database. GetNotesAsync ();
}
By entering this code, you will collect the notes stored in the database. In other words, this code collects all the notes stored in the database.
- Save the changes you made and exit that window.
- Click on NoteEntryPage.xaml.cs in the Solution Explorer section and enter the following code inside it:
async void OnSaveButtonClicked (object sender, EventArgs e)
{
var note = (Note) BindingContext;
note. Date = DateTime.UtcNow;
await App.Database. SaveNoteAsync (note);
await Navigation.PopAsync ();
}
async void OnDeleteButtonClicked (object sender, EventArgs e)
{
var note = (Note) BindingContext;
await App.Database. DeleteNoteAsync (note);
await Navigation.PopAsync ();
}
NoteEntryPage maintains an instance of a Note.
When the OnSaveButtonClicked method is executed, the Note instance is stored in the database and the application returns to the previous page.
When the OnDeleteButtonClicked method is executed, the Note instance is removed from the database and the application returns to the previous page.
How to use sqllite.net as a local storage in Xamarin Form using Visual Studio for Mac?
1- Open Visual Studio
2- Open the Notes solution project.
3- In the Solution Pad section, select the Notes project and right-click on it.
4- After right-clicking on the project, select Add> Add NuGet Packages.
5- In the NuGet Package Manager section, select the Browser tab to search.
6- And search for NuGet SQLite-net-plc. and after finding it, click Add Package to add to the project.
Note:
There are packages with similar names, the package you should choose has the following specifications:
- Author (s): Frank A. Krueger
- Id: SQLite-net-plc.
- NuGet link: SQLite-net-plc.
- Open the Models folder in the Solution Pad section of the Note.cs project and delete the existing code and enter the following code there.
using System;
using SQLite;
namespace Notes.Models
{
public class Note
{
[PrimaryKey, AutoIncrement]
public int ID {get; set;}
public string Text {get; set; }
public DateTime Date {get; set;}
}
}
These codes define the Note model, which is used to store note data in applications.
Each note in the SQLite.NET database has its own unique id, in fact the ID, PrimeryKey, and AutoIncrement attributes make each note have its own id.
- Record the changes made and exit that window.
- Create a folder called Data in the Solution Pad and add it to the Notes project.
- In the folder created in the Notes project and in the Solution Pad section, create a class called NoteDatabase.
- In the created NoteDatabase.cs class, delete all existing codes and replace the following codes.
using System.Collections. Generic;
using System.Threading. Tasks;
using SQLite;
using Notes.Models;
namespace Notes.Data
{
public class NoteDatabase
{
readonly SQLiteAsyncConnection _database;
public NoteDatabase (dbPath string)
{
_database = new SQLiteAsyncConnection (dbPath);
_database. CreateTableAsync <note> (). Wait ();
}
public Task <list <note>> GetNotesAsync ()
{
return _database. Table <note> (). ToListAsync (async (Note note)
{
return _database. DeleteAsync (note);
}
}
}
The code written in this class is used to create a database, read data, write and add data and delete it.
Constructor This class receives the address of the file in which the database was created and stored as an argument. The address of the file in which the database was created and stored is provided by the App class. Asynchronous SQLite.NET APIs in these classes are used to transfer database calculations to the background thread.
- Click on the App.xaml.cs section in the Solution Pad of the Notes project and replace the code inserted in the file below with the code inside it.
using System;
using System.IO;
using Xamarin.Forms;
using Notes.Data;
namespace Notes
{
public partial class App: Application
{
static NoteDatabase database;
public static NoteDatabase Database
{
get
{
if (database == null)
{
database = new NoteDatabase (Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder. LocalApplicationData), "Notes.db3"));
}
return database;
}
}
public App ()
{
InitializeComponent ();
MainPage = new NavigationPage (new NotesPage ());
}
protected override void OnStart ()
{
// Handle when your app starts
}
protected override void OnSleep ()
{
// Handle when your app sleeps
}
protected override void OnResume ()
{
// Handle when your app resumes
}
}
}
By entering this code, you create a database that creates a NoteDatabase instance as a singleton and also receives its filename as an argument and sends it to the NoteDatabase constructor. Creating a database as a singleton has the advantage that only one connection is created and used to communicate with the database with the same connection.
- Save the changes made and exit that window.
- Click on the NotePage.xaml.cs section in the Solution Pad of the Notes project and replace the code listed below with the OnAppearing method with the code inside the file.
protected override async void OnAppearing ()
{
base. OnAppearing ();
listView.ItemsSource = await App.Database. GetNotesAsync ();
}
By inserting this piece of code, you will collect the notes stored in the database. In other words, this code collects all the notes stored in the database.
- Save the changes you made and exit that window.
- Click on NoteEntryPage.xaml.cs in the Solution Pad and enter the following code inside:
async void OnSaveButtonClicked (object sender, EventArgs e)
{
var note = (Note) BindingContext;
note. Date = DateTime.UtcNow;
await App.Database. SaveNoteAsync (note);
await Navigation.PopAsync ();
}
async void OnDeleteButtonClicked (object sender, EventArgs e)
{
var note = (Note) BindingContext;
await App.Database. DeleteNoteAsync (note);
await Navigation.PopAsync ();
}
NoteEntryPage maintains an instance of a Note.
When the OnSaveButtonClicked method is executed, the Note instance is stored in the database and the application returns to the previous page.
Source: https://www.dotnek.com/Blog/Apps/how-to-store-data-in-a-local-sqlitenet-databa