Работа с базой данных SQLite с помощью обертки SQLitePCL
SQLite это кроссплатформенный (Windows, iOS, Android, Python, Mono и др.) движок базы данных с открытым исходным кодом. Он поддерживает множественные таблицы, индексы, триггеры и представления.
Поддерживаются ACID транзакции (Atomicity/Атомарность, Consistency/Согласованность, Isolation/Изолированность, Durability/Надежность).
SQLitePCL — это библиотека Portable Class Library с открытым исходным кодом, доступным по адресу https://sqlitepcl.codeplex.com/, которая позволяет работать с базами SQLite единым образом и в .Net приложениях и в WP, Windows Store, UAP, а также Android/iOS (с помощью Xamarin). Другими словами, это обертка/wrapper библиотеки C, которая упрощает разработку и экономит время. Обертка довольно новая. Ранее для приложений .Net и Windows Store можно было использовать обертку sqlite-net.
Статьи о том, как использовать SQLite-net:
Работа с данными в WinRT. Часть 2. Работа с БД на примере SQLite
Использование SQLite в C#
Есть на хабре и статья с описанием чуть менее известной обертки:
SQLite. Готовимся к Windows 10 (Universal App Platform)
А ранее была такая статья про обертку с названием SQLite-net PCL:
SQLite теперь для мобильных приложений на С# под любую платформу
Казалось бы, это и есть SQLitePCL, тем более что URL у проекта тот же самый, но на деле это оказалась обертка с совершенно другим синтаксисом.
Мы все-таки рассмотрим именно текущую версию SQLitePCL.
Сначала нам нужно добавить SQLite в проект UAP. Сделать это можно таким образом:
После установки для того, чтобы изменения вступили в силу, требуется перезапустить Visual Studio.
Как вариант, расширение .vsix можно скачать по этой ссылке:
SQLite for Universal App Platform
или с сайта SQLite, найдя .vsix файл для Universal App Platform
SQLite Download Page
После чего установить.
Теперь в проекте нужно задать ссылку на SQLite:
Tim Heuer в своей статье о SQLite и приложениях Windows 8 рекомендует также добавить ссылку на Microsoft Visual C++ Runtime Package, мотивируя тем, что вряд ли на клиентской машине будет отсутствовать C++ Runtime, но если эту ссылку не добавить, то приложение провалит сертификационные тесты перед публикацией в Windows Store.
Саму библиотеку SQLite и ссылку на нее мы добавили, теперь нам нужно добавить обертку SQLitePCL. Делаем это зайдя в меню Сервис — Диспетчер пакетов NuGet — Управление пакетами NuGet для решения… Находим по поиску SQLitePCL и устанавливаем:
Либо можно установить PCL с помощью консоли диспетчера пакетов (Сервис — Диспетчер пакетов NuGet — Консоль диспетчера пакетов) с помощью команды:
Install-Package SQLitePCL
Теперь все готово к работе. Если вдруг ваш проект при развертывании выдаст сообщение о том, что развертывание пропущено:
Значит необходимо, чтобы в диспетчере конфигураций:
конфигурация была предназначена для развертывания (а если нужно то и для сборки):
Немного вводной информации о SQLite, которая будет полезна при первом знакомстве с этой базой:
Каждая строка таблиц SQLite (за исключением таблиц WITHOUT ROWID) имеет 64-битный signed integer ключ который называется ROWID. Получить значение этого столбца можно по одному из следующих регистр независимых псевдонимов: «rowid», «oid» или »_rowid_».
По информации с официального сайта SQLite Datatypes In SQLite Version 3 данные хранятся в одном из следующих классов:
NULL Значением является NULL
INTEGER Значение знакового целого числа может хранится в 1,2,3,4,6 или 8 байтах в зависимости от величины числа.
REAL Значение числа с плавающей точкой хранится в 8-байтном IEEE числе с плавающей точкой.
TEXT Значением является текстовая строка, которая хранится с использованием кодирования базы данных (UTF-8, UTF-16BE or UTF-16LE).
BLOB Значением являются blob-данные, которые заносятся так, как они и были введены.
Класс данных содержит в себе более широкий смысл, чем тип данных. Скажем, класс INTEGER содержит в себе 6 типов данных различной длины.
В базах SQLite нет типа BOOLEAN. Вместо этого типа используется тип INTEGER со значениями 1 и 0.
Время и дата может сохраняться в типах TEXT, REAL или INTEGER:
1. В виде текста как строка ISO8601 («YYYY-MM-DD HH: MM: SS.SSS»).
2. В виде REAL числа с плавающей точкой как количество дней Юлианского календаря, начиная считать с полдня по Гринвичу 24 ноября 4714 года до нашей эры в соответствии с пролептическим григорианским календарем.
3. В виде целого числа INTEGER как время Unix, — число секунд прошедших начиная с 1970–01–01 00:00:00 UTC.
Для того чтобы добиться максимальной совместимости с другими базами данных SQLite поддерживает так называемый концепт сходства типов — type affinity. Имеется порядок правил, сопоставляющий типы данных других баз и SQLite. Скажем, первым правилом идет, что если в строке типа данных чужой базы содержится текст «INT», то этот тип будет сопоставлен типу SQLite INTEGER. Если это правило не выполняется, то проверяется второе правило — содержит ли строка типа данных текст «CHAR», «CLOB» или «TEXT». Если содержит, то тип данных сопоставляется с типом SQLite TEXT. И так далее…
И вот после такого относительно большого введения, большая часть информации которого в той или иной части уже встречалась в рунете, наконец-то перейдем к коду примеров работы с SQLitePCL
Для работы нам нужно добавить ссылку на пространство имен:
using SQLitePCL;
Создать таблицу в базе можно так:
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();
}
}
Столбец первичного ключа задается с помощью выражения PRIMARY KEY и может быть только один у таблицы.
UNIQUE — ограничение, которое требует, чтобы в столбце содержались только уникальные значения. Таких столбцов в таблице может быть несколько.
NOT NULL — это constraint, который запрещает наличие в колонке значений NULL.
CHECK — задает условие, которое будет проверено. Пример:
CREATE TABLE IF NOT EXISTS People ( ID INTEGER NOT NULL PRIMARY KEY, FirstName TEXT CHECK (FirstName NOT IN ('Леша','Алешка')), LastName TEXT NOT NULL UNIQUE)
Если у таблицы есть внешний ключ, то для него необходимо создать индекс. Также индексы желательно создавать для оптимизации скорости поиска по большим таблицам. Для столбцов первичного ключа индекс создается автоматически. Создать индекс можно так:
using (var statement = conn.Prepare(@"CREATE INDEX IF NOT EXISTS lastname_indx ON People (LastName)"))
{
statement.Step();
}
Более подробная информация о SQL синтаксисе создания таблиц доступна по ссылке: SQLite Query Language: CREATE TABLE
Из минусов SQLitePCL можно отметить то, что она не поддерживает создание внешних ключей (SQLite Foreign Key Support).
Если вам необходимо добавить связи между таблицами создавая их «на лету», то вы можете воспользоваться какой-либо другой оберткой. Кстати, внешние ключи в SQLite по умолчанию отключены.
Добавить запись в таблицу можно простым запросом:
using (var statement = conn.Prepare("INSERT INTO People (FirstName, LastName) VALUES ('Конек', 'Горбунок')"))
{
statement.Step();
}
А можно использовать параметры:
using (var statement = conn.Prepare("INSERT INTO People (FirstName, LastName) VALUES (?, ?)"))
{
statement.Bind(1, "Кощей");
statement.Bind(2, "Бессмертный");
statement.Step();
}
Следующего пользователя мы можем добавить в таблицу, используя точно такую же конструкцию, или же можем внутри using данной конструкции после Step () совершить следующие действия:
// обнулить statement и его связи
statement.Reset();
statement.ClearBindings();
// сделать привязку с другими именами
statement.Bind(1, "Елена");
statement.Bind(2, "Прекрасная");
// Добавить данные
statement.Step();
Также есть возможность вместо указания номера параметра использовать псевдоним:
using (var statement = conn.Prepare("INSERT INTO People (FirstName, LastName) VALUES (@fName, @lName)"))
{
statement.Bind("@fName", "Иванушка");
statement.Bind("@lName", "Дурачок");
statement.Step();
}
Удаление происходит аналогично добавлению или обновлению записей:
string fname ="Елена";
using (var statement = conn.Prepare("DELETE FROM People WHERE FirstName=?"))
{
statement.Bind(1, fname);
statement.Step();
}
Процесс чтения записей немного отличается:
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;
}
}
Транзакции могут быть запущены вручную, используя «COMMIT TRANSACTION» или «ROLLBACK TRANSACTION». Для этого перед списком запросов выполняем:
using (var statement = conn.Prepare("BEGIN TRANSACTION"))
{
statement.Step();
}
А уже после запросов подтверждаем с помощью:
using (var statement = conn.Prepare("COMMIT TRANSACTION"))
{
statement.Step();
}
Сама база данных приложения на десктопе находится по адресу
%USERPROFILE%\AppData\Local\Packages\{PackageId}
Для просмотра базы на мобильном устройстве необходима утилита.
Например, вы можете воспользоваться Power Tools for WP8.1
Мне удалось с помощью этой утилиты получить доступ к базам SQLite и на телефоне с Windows 10:
На официальном сайте можно найти инструменты для работы с SQLite
Мне по вкусу пришлась польская программа SQLite Studio, которая имеет поддержку русского языка и не требует установки под Windows (.zip архив)
Кроме нее можно выделить бесплатную SQLite2009 Pro Enterprise Manager
Персональная версия SQLite Expert бесплатна для личного и коммерческого использования.
Материалы, которые мне помогли:
Курс на MVA «A Developer’s Guide to Windows 10»
Статья «The new Portable Class Library for SQLite»
