В предыдущей части мы рассматривали как получить доступ к файловому хранилищу приложения. Так как есть прямой доступ к файловому хранилищу, мы можем воспользоваться практически любой встраиваемой БД. На сегодняшний день 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.
К сожалению при выкладывании приложения придется компилировать проект отдельно для каждой платформы, но пока не появится порт 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…
Теперь у нас все готово что бы работать с базой данных.
Для подключения к БД можно использоваться следующий код:
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. Таким образом вы можете воспользоваться единой кросплатформенной БД во всех версиях вашего приложения. В ноябрьской статье я рассмотрю как создавать кросплатформенное приложение с использованием этой библиотеки.
Автор: Atreides07
