Work with a SQLite database using SQLitePCL wrapper

SQLite is a cross-platform (Windows, iOS, Android, Python, Mono, etc.) open source database engine. It supports multiple tables, indexes, triggers, and views.
ACID transactions are supported (Atomicity / Atomicity, Consistency / Consistency, Isolation / Isolation, Durability / Reliability).
SQLitePCL is an open source Portable Class Library available at https://sqlitepcl.codeplex.com/ , which allows you to work with SQLite databases in the same way in .Net applications and in WP, Windows Store, UAP, and Android / iOS (using Xamarin). In other words, it is the C wrapper / wrapper that simplifies development and saves time. The wrapper is pretty new. Previously, sqlite-net wrapper could be used for .Net and Windows Store applications.

Articles on how to use SQLite-net:
Work with data in WinRT. Part 2. Working with the database on the example of SQLite
Using SQLite in C #

There is an article on Habré describing a slightly less well-known wrapper:
SQLite. Getting ready for Windows 10 (Universal App Platform)

And earlier there was such an article about a wrapper called SQLite-net PCL:
SQLite is now for mobile C # applications for any platform
It would seem that this is SQLitePCL, especially since the project’s URL is the same, but in reality it turned out to be a wrapper with a completely different syntax.
')
We still consider exactly the current version of SQLitePCL.
First we need to add SQLite to the UAP project. You can do it this way:





After installation, you must restart Visual Studio for the changes to take effect.

Alternatively, the .vsix extension can be downloaded from this link:
SQLite for Universal App Platform
or from the SQLite website by finding the .vsix file for the Universal App Platform
SQLite Download Page
Then install.

Now in the project you need to set a link to SQLite:





Tim Heuer in his article on SQLite and applications, Windows 8 also recommends adding a link to the Microsoft Visual C ++ Runtime Package, arguing that the C ++ Runtime is unlikely to be missing on the client machine, but if this link is not added, the application will fail certification tests before publishing in the Windows Store.

The SQLite library itself and the link to it we added, now we need to add SQLitePCL wrapper. We do this by going to the menu Tools - Package Manager NuGet - Package Management NuGet to solve ... Find SQLitePCL search and install:



Or, you can install PCL using the package manager console (Tools - NuGet Package Manager - Package Manager Console) using the command:
Install-Package SQLitePCL

Now everything is ready for work. If suddenly your project when deploying will give a message that the deployment is skipped:



So it is necessary that in the configuration manager:



The configuration was intended for deployment (and, if necessary, for the assembly):

Some introductory information about SQLite, which will be useful when you first met this database:

Each row of SQLite tables (with the exception of the WITHOUT ROWID tables) has a 64-bit signed integer key called ROWID. You can get the value of this column by one of the following register independent aliases: “rowid”, “oid” or “_rowid_”.
According to the official SQLite Datatypes In SQLite Version 3 website, data is stored in one of the following classes:
NULL Value is NULL
INTEGER The value of the signed integer can be stored in 1,2,3,4,6 or 8 bytes, depending on the value of the number.
REAL The value of a floating-point number is stored in an 8-byte IEEE floating-point number.
TEXT Value is a text string that is stored using database encoding (UTF-8, UTF-16BE or UTF-16LE).
BLOB Values ​​are blob data that is entered as it was entered.

A data class has a broader meaning than a data type. Let's say the class INTEGER contains 6 data types of different lengths.

In SQLite databases there is no type BOOLEAN. Instead of this type, the INTEGER type is used with values ​​of 1 and 0.

Time and date can be saved in types TEXT, REAL or INTEGER:
1. As text as ISO8601 string (“YYYY-MM-DD HH: MM: SS.SSS”).
2. In the form of REAL, floating-point numbers as the number of days in the Julian calendar, starting from half a day according to GMT on November 24, 4714 BC in accordance with the pro-septic Gregorian calendar.
3. As an integer INTEGER as Unix time, is the number of seconds elapsed since 1970-01-01 00:00:00 UTC.

In order to achieve maximum compatibility with other databases, SQLite supports the so-called type affinity concept. There is a rule order that matches the data types of other databases and SQLite. Let's say the first rule goes that if the string “INT” is in the string of the foreign database's data type, this type will be mapped to the SQLite INTEGER type. If this rule is not executed, then the second rule is checked - does the string of the data type contain the text “CHAR”, “CLOB” or “TEXT”. If it does, then the data type is mapped to the SQLite TEXT type. And so on…

And after such a relatively large introduction, most of the information which in one way or another has already been encountered in runet, we will finally move on to the code of examples of working with SQLitePCL

To work, we need to add a link to the namespace:

using SQLitePCL; 

You can create a table in the database as follows:

  using (var conn = new SQLiteConnection("Storage.db")) { string sql = @"CREATE TABLE IF NOT EXISTS People ( ID INTEGER NOT NULL PRIMARY KEY, FirstName NVARCHAR(50), LastName NVARCHUAR(50));"; using (var statement = conn.Prepare(sql)) { statement.Step(); } } 

The primary key column is specified using the PRIMARY KEY expression and there can only be one in the table.
UNIQUE is a constraint that requires that a column contain only unique values. There can be several such columns in the table.
NOT NULL is a constraint that forbids the presence of null values ​​in a column.
CHECK - sets the condition to be checked. Example:

 CREATE TABLE IF NOT EXISTS People ( ID INTEGER NOT NULL PRIMARY KEY, FirstName TEXT CHECK (FirstName NOT IN ('','')), LastName TEXT NOT NULL UNIQUE) 

If the table has a foreign key, then it is necessary to create an index for it. Also, indexes are desirable to create to optimize the search speed on large tables. For primary key columns, an index is created automatically. You can create an index like this:

 using (var statement = conn.Prepare(@"CREATE INDEX IF NOT EXISTS lastname_indx ON People (LastName)")) { statement.Step(); } 

More information about the SQL syntax for creating tables is available at: SQLite Query Language: CREATE TABLE

Among the minuses of SQLitePCL, it can be noted that it does not support the creation of foreign keys ( SQLite Foreign Key Support ).
If you need to add relationships between the tables creating them on the fly, then you can use some other wrapper. By the way, foreign keys are disabled by default in SQLite.

You can add an entry to the table by a simple query:

 using (var statement = conn.Prepare("INSERT INTO People (FirstName, LastName) VALUES ('', '')")) { statement.Step(); } 

And you can use the parameters:

 using (var statement = conn.Prepare("INSERT INTO People (FirstName, LastName) VALUES (?, ?)")) { statement.Bind(1, ""); statement.Bind(2, ""); statement.Step(); } 

We can add the following user to the table using the exact same structure, or we can perform the following actions inside using this structure after Step () :

 //  statement    statement.Reset(); statement.ClearBindings(); //      statement.Bind(1, ""); statement.Bind(2, ""); //   statement.Step(); 

It is also possible, instead of specifying the parameter number, to use an alias:

 using (var statement = conn.Prepare("INSERT INTO People (FirstName, LastName) VALUES (@fName, @lName)")) { statement.Bind("@fName", ""); statement.Bind("@lName", ""); statement.Step(); } 

Deletion is similar to adding or updating records:

  string fname =""; using (var statement = conn.Prepare("DELETE FROM People WHERE FirstName=?")) { statement.Bind(1, fname); statement.Step(); } 

The process of reading entries is slightly different:

 using (var statement = conn.Prepare("SELECT LastName, FirstName FROM People WHERE FirstName=''")) { while (statement.Step() == SQLiteResult.ROW) { txtInfo.Text = txtInfo.Text + (string)statement[0] + Environment.NewLine; } } 

Transactions can be started manually using “COMMIT TRANSACTION” or “ROLLBACK TRANSACTION”. To do this, before the list of requests we perform:

  using (var statement = conn.Prepare("BEGIN TRANSACTION")) { statement.Step(); } 

And after the requests we confirm with:

  using (var statement = conn.Prepare("COMMIT TRANSACTION")) { statement.Step(); } 

The application database itself is on the desktop at
% USERPROFILE% \ AppData \ Local \ Packages \ {PackageId}

To view the base on your mobile device, you need a utility.
For example, you can use Power Tools for WP8.1
I managed with this utility to access SQLite databases and on a Windows 10 phone:



On the official website, you can find tools for working with SQLite.
I liked the Polish program SQLite Studio , which has support for the Russian language and does not require installation under Windows (.zip archive)
In addition, you can highlight the free SQLite2009 Pro Enterprise Manager
The personal version of SQLite Expert is free for personal and commercial use.

Materials that helped me:
Course on MVA "A Developer's Guide to Windows 10"
Article "The new Portable Class Library for SQLite"