SQLite .NET, OneDrive & Uno Platform (Part 1)

 

References, Tools, Frameworks & Operating System used.
Items	Links
Windows 10 or 11	Download Windows 10 (microsoft.com)
Visual Studio 2019 or 2022	Visual Studio: IDE and Code Editor for Software Developers and Teams (microsoft.com)
Visual Studio Code	Visual Studio: IDE and Code Editor for Software Developers and Teams (microsoft.com)
Uno Platform Project Template	Uno Platform Solution Templates – Visual Studio Marketplace
SQLite .NET NuGet Package	NuGet Gallery | SQLiteNetExtensions 2.1.0
Bogus .NET NuGet Package	NuGet Gallery | Bogus 33.1.1
SQLite Explorer extension for Visual Studio Code	SQLite – Visual Studio Marketplace

Data persistence is a critical part of any software system (except when you’re trying not to be found 😉). it’s storage locations and types are of critical importance primarily because these factors into the speed of accessing transforming and visualizing of data. In the space of mobile and desktop applications, local store data is of prime importance because this delivers the feature of data persistence however, a problem arises when you want to share your data across several devices think accessing the number of due invoices from your phone when the data is stored on your laptop. This gives rise to a need for portability and syncing of data. With this feature you can ensure end users have access to the most recent data across their system their system or devices. With a good data persistence portability and syncing subsystem, you have the makings of a robust and reliable back end for your apps to take it a step further, you need to have a polished and well thought out and executed UX.

In this first of three-part series, I will be walking you through the basics an intermediary building blocks of creating a data persistence subsystem which is SQLite, Data portability and syncing subsystem with the personal cloud service OneDrive and visualizing data with a collection of Uno Platform’s UI controls layouts and theming features. For this series, we will be using SQLite dot net library for the creation and CRUD operations, Bogus dot net library for populating fake data into the database and our data will be focused on freelancing invoicing and client tracking.

SQLite: What Why and When

1.What?

SQLite Is a relational database management system typically used in software solutions that require the database to be embedded in the solution itself (think mobile, desktop apps, web browsers etc.)

2.Why?

Whilst Client/Server database engines (MySQL etc.) scalability, concurrency, centralization and control for enterprise data, SQLite strive to provides a local data store for individual applications and devices and as such exposes economy, efficiency, reliability, independence and simplicity ask its core features.

3.When?

a. Internet of things and embedded devices.
b. Websites with low to medium traffic.
c. Data analysis.
d. Caching systems.
e. Data transfer format (Sinking between devices).
f. File archiving.
g. Internal or temporary databases.

Data Model & Relationships

For our invoicing app, we will be declaring our entities of data in individual tables and defining their relationships by the following means: many to one relationship, many to many relationships and blobs. The entities we will be creating itemize below

1. Communication: houses contact related data.
2. Address: houses location related data.
3. Account: houses financial related data.
4. Client: houses client related data.
5. Invoice: houses invoice related data.
6. Item Blob: defines invoice item related data.

S/No	Entity	Relationship Type	Entity
1.	Communication	One-To-One	Client
2.	Address	One-To-One	Account
			Client
3.	Account	One-To-One	Address
			Client
4.	Client	One-To-One	Communication
			Address
			Account
5.	Invoice	Many-To-One	Address
			Account
			Client
		Text Blob	Item Blob

 

To implement a many to one relationship, the attribute [ManyToOne] Is used to decorate the intended table property that is attached to the Table, the many parts. In the invoice example, the invoice table representation has three many to one relationship implying several invoices can be linked to an address on account or a client entity record.
If we declared a many to many relationships, this would imply several records of the type: entity A can be linked to several records of entity B. This might seem like an ideal scenario for our invoice item relationship: several invoices can be linked to a specific item and vice versa. However there exists a special case of invoice and item linking, you would need to catalogue all services and products you offer a new granular level, and you will need to either link existing items to an invoice or create items as you create the invoice. This is where blob comes into play, in its most basic term a BLOB allows you to save a collection of data in its serializable format in a single column. In the case of items, we will be using an ItemBlob class to provide structure to the data and using the [TextBlob] attribute in the Invoice entity table representation to decorate the collection of items.

In One-To-One relationships, there can only be a one-to-one mapping between an instance of an entity type A and another of type B for our case, a client entity record is linked to a communication entity record, 1 Address entity record is linked to 1 Account entity record etc.

In all the above scenarios using SQLite .NET, tables are represented by C# classes and decorated with the TABLE attribute, its constituent columns decorated with the COLUMN attribute, primary and foreign keys I decorated with the PRIMARYKEY and FOREIGNKEY attributes its relationships are decorated as previously stated. The class is primarily composed of public C# properties. the code base for SQLite is in the database folder of the Demo app. Here you have the Table implementations, Enum types, customized error exceptions, implementation of CRUD operations for each entity and a mock data class for populating the database with fake data which is powered by Bogus.NET.

Bogus.NET is a robust library useful providing fake data in different categories (finance, country, personal, health care etc.) 4 UX and back in software development. Read more about this library through this link: Bogus.NET

Continue reading part II of this series, Syncing Local Database with OneDrive Personal

As always, we would like to express our appreciation to guest blogger and Software Engineer at Microsoft, Paula Aliu, for her valuable and insightful article.