Работа с данными в WinRT. Часть 2. Работа с БД на примере SQLite

В предыдущей части мы рассматривали как получить доступ к файловому хранилищу приложения. Так как есть прямой доступ к файловому хранилищу, мы можем воспользоваться практически любой встраиваемой БД. На сегодняшний день SQLite, пожалуй, является самой популярной кроссплатформенной встраиваемой базой данных. В связи с этим при портировании приложений скорее всего придется работать с SQLite, работу с которым будем рассматривать далее.

В целом статья получилось больше похожей на инструкцию по установке и работе SQL Lite.

В статье будет описано
Установка расширения для сутдии.
Добавление SQLite в проект
Добавление провайдера sqlite-net
Работа с БД через linq провайдер (в стиле LinqToSql, EF)
Работа с БД через SQL запросы (в стиле ADO.NET)


В демонстрационном проекте к статье приложен очень простой пример работы со списком книг который будет рассматриваться в статье.

В блоге Tim Heuer есть прекрасная статья о том как добавить поддержку SQLite. Рассмотрим здесь инструкцию по добавлению SQLite.

Подготовка студии. Установка расширения.

Самым простым способом предоставления возможности работы с SQLite является установка специального расширения. Для этого открываем Extensions and Updates (В меню Tools).


После установки расширения потребуется перезапуск студии и теперь мы сможем в наших проектах добавлять ссылку на библиотеку SQLite в наши проекты

Добавление SQLite в проект

В первую очередь необходимо добавить ссылку на библиотеки SQLite (Правой кнопкой на проекте – Add reference):


Обратите внимание на ссылку Microsoft Visual C++ Runtime Package. Эта компонента используется библиотекой SQLite. В том случае если мы его не добавим — наш проект будет запускать и работать, но его необходимо добавить, так как в том случае если мы не укажем ссылку, проект не пройдет валидацию Windows App Certification Toolkit.

По умолчанию проект не будет компилироваться.

Для того что бы проект компилировался необходимо в Configuration Manager (Последний пункт в меню Build) выбрать платформу x86, x64 или Arm



К сожалению при выкладывании приложения придется компилировать проект отдельно для каждой платформы, но пока не появится порт SQL Lite на чистом C# не будет возможности компилировать с опцией Any CPU.

При этом в свойствах проекта можно обратно вернуть Any CPU для опций Platform и Platform Target.

Добавляем Linq провайдер

Мы можем значительно упростить работу с SQLite добавив в наш .NET проект обертку sqlite-net от Tim Heuer что бы работать с SQLite аналогично LinqToSql провайдеру. Эта обертка будет полезна тем кто предпочитает linq вместо SQL и так же упростит портирование приложений с Windows Phone.

Для тех кто знакомы с пакетным менеджером, установить обертку можно командой:

Install-Package sqlite-net

Для тех кто предпочитает работать с визуальным пакетным менеджером, пакет можно установить через меню Manage NuGet packages (доступно в контекстном меню проекта (правой кнопкой мыши по проекту)) или через меню студии Tools –> Library Package Manager – > Manage NuGet Packages for Solution…

Решение проблемы с киррилическими именами. (Именами пользователя на русском языке)

Так как имя пользователя входит в часть пути изолированного хранилища приложения, и из за небольшой баги, sqlite-net не может работать с путями с кириллицей. Ваше приложение может оказаться не работоспособным для тех пользователей, у которых имя указано кирилицей.

После установки Sqlite-net в наш проект добавляется два файла

SQLite.cs
SQLiteAsync.cs

для исправления ошибки, в файле sqlite.net в конструкторе класса public SQLiteConnection (string databasePath, bool storeDateTimeAsTicks = false) необходимо поменять строку

var r = SQLite3.Open(DatabasePath, out handle);

на

var r = SQLite3.Open16(DatabasePath, out handle);

Теперь у нас все готово что бы работать с базой данных.

Для подключения к БД можно использоваться следующий код:

using (var db = new SQLiteConnection(dbPath))
{
   //db code
}

где dbPath полный путь к базе в файловой системе:

dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "databasename.db");

sqlite-net позволяет работать как с помощью Linq провайдера, так и в стиле ADO.NET. Рассмотрим оба пункта чуть подробнее.

Добавление провайдера sqlite-net

К примеру нам нужно сохранять в базе список продуктов. В первую очередь необходимо подготовить соответствующие модели.

К примеру, создадим простую модель продукта, в котором будет указан первичный ключ с автоинкрементом и текстовое поле в 250 символов:

public class Product
{
    [PrimaryKey, AutoIncrement]
    public int ProductId { get; set; }
    [MaxLength(250)]
    public string Name { get; set; }
}

Допустим у нас есть некий отдельный класс DataLayer для работы с базой данных. В конструкторе класса создим строку подключения к базе данных:

private readonly string dbPath;
public DataLayer()
{
    dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "products.db");
}

Добавим в класс метод Init который будет создавать базу данных и таблицу Product при выполнении этого метода:

public void Init()
{
    using (var db = new SQLiteConnection(dbPath))
    {
        db.CreateTable<Product>();
    }
}

При выполнении этого метода в локальной папке будет создана база products.db. Это будет обычная sqlite БД, которую можно будет использовать на других платформах. Так же мы можем использовать любые утилиты для просмотра базы. К примеру, если возмем бесплатную SQLite Expert Personal и откроем в нем эту БД мы можем посмотреть схему нашей таблицы:


Рассмотрим несколько примеров работы для типовых сценариев. Например код добавления нового продукта:

public Product AddProduct(string name)
{
    var product = new Product() {Name = name};
    using (var db = new SQLiteConnection(dbPath))
    {
        db.Insert(product);
    }
    return product;
}

При выполнении этого метода, объекту Product свойству ProductId будет автоматически присвоено идентификатор из БД.

Следующий участок кода получает продукт по выбранному идентификатору:

public Product GetProductById(int productId)
{
    using (var db = new SQLiteConnection(dbPath))
    {
        return db.Table<Product>().FirstOrDefault(i => i.ProductId == productId);
    }   
}

так же можно использовать прямой sql запрос для извлечения записей:

public Product GetProductById(int productId)
{
    using (var db = new SQLiteConnection(dbPath))
    {
        return db.Query<Product>("select * from product where ProductId=?", productId).FirstOrDefault();
    }
}

Соответственно все записи мы можем получить аналогично:

public List<Product>  GetAllProducts()
{
    using (var db = new SQLiteConnection(dbPath))
    {
        return db.Table<Product>().ToList();
    }   
}

Еще одной из приятных особенностей обертки является возможность удаления продукта по идентификатору (чего не хватает в LinqToSQL):

public void DeleteProduct(int id)
{
    using (var db = new SQLiteConnection(dbPath))
    {
        db.Delete<Product>(id);
    }   
}

Полный пример демонстрирующую очень простой пример работы со списком книг можно скачать здесь.

Работа с БД через SQL запросы (в стиле ADO.NET)

Несмотря на удобства Linq провайдера, в некоторых случаях возможно будет удобнее работать с базой данных напрямую с помощью SQL запросов, к примеру, при портировании существующего кода. sqlite-net позволяет полноценно работать с базой данных как для выполнения простых запросов не возвращающих никаких данных, так и с данными возвращающими сложные типы данных.

В следующих примерах рассмотрим как выполнять простые запросы, запросы с параметрами и запросы возвращающий простой тип данных и таблицу.

Для примеров мы рассмотрим аналогичный рассмотренному в 6.3. класс DataLayer для работы с данными, со строкой подключения в конструкторе:

private readonly string dbPath;
public DataLayer()
{
    dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "products.db");
}

Рассмотрим аналогичный метод создания таблицы:

public void Init()
{
    using (var db = new SQLiteConnection(dbPath))
    {
        db.CreateCommand(
@"CREATE TABLE ""Product""( ""ProductId"" integer primary key autoincrement not null , ""Name"" varchar(250) );"
            ).ExecuteNonQuery();
    }
}

В некоторых случаях требуется выполнить запрос с входными аргументами. В таком случае можно добавлять неограниченное количество параметров используя символ “?” в sql запросе для каждого параметра. К примеру рассмотрим реализацию метода добавления продукта по имени:

public void AddProduct(string name)
{
    using(var db=new SQLiteConnection(dbPath))
    {
        db.CreateCommand("INSERT INTO Product (Name) VALUES (?)", name).ExecuteNonQuery();
    }
}

В некоторых случаях нам может потребоваться возвращать простой тип данных в результате выполнения запроса. Так, запрос возвращающий количество строк в БД будет выглядеть следующим образом:

public int GetCount()
{
    using (var db = new SQLiteConnection(dbPath))
    {
        return db.CreateCommand("SELECT COUNT(ProductId) FROM Product").ExecuteScalar<int>();
    }
}

В тех случаях когда нам нужно возвращать таблицы, у нас должна быть соответствующая модель для извлечения записи. Для нашей таблицы модель будет выглядеть следующим образом:

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
}

теперь мы можем получить через sql запрос все записи из нашей базы:

public List<Product> GetAllProducts()
{
    using(var db=new SQLiteConnection(dbPath))
    {
        return db.CreateCommand("select * from product").ExecuteQuery<Product>();
    }
}

Итоги

Как видим, использование SQL Lite не сложнее работы с SQL CE. Таким образом вы можете воспользоваться единой кросплатформенной БД во всех версиях вашего приложения. В ноябрьской статье я рассмотрю как создавать кросплатформенное приложение с использованием этой библиотеки.

P.S. Большое спасибо VoldemarRinger и Stasus за помощь, консультацию и решение проблемы при изучении проблемы с кирилицей.