Привет, дорогой читатель, в этой статье я хотел бы поделиться опытом работы с базой данных посредством ORM Dapper на .NET Core, а также рассказать полезные лайфхаки, которые нам помогают удобно использовать его при разработке приложений и рассмотрим как мог бы выглядеть сервис по созданию ведьмаков с использованием Dapper. В данной статье будет много примеров кода и комментариев к нему, а также граблей, по которым мы прошли. Статья рассчитана на тех, кто хочет начать использовать dapper в своих проектах либо тех, кто его уже использует. Итак начнем.
Введение
Dapper - это что-то вроде mini ORM не такая монструозная и более быстрая по сравнению с популярной на .NET Entity Framework. Dapper позволяет писать SQL-запросы к БД и маппить их на C# классы, в общем позволяет связать .Net код и SQL. Из очевидных минусов даппер не автогенерирует код и многие шаблонные запросы, такие как SELECT, INSERT, DELETE, приходится писать вручную, но зато он позволяет писать сложные запросы практически не жертвуя скоростью работы программы и без всякой черной магии и тонны кода.
Как настоящие ведьмаки сразу ринемся в бой с монстрами и посмотрим практические примеры. Будем считать, что БД у нас уже есть и в ней находятся все необходимые таблицы, так что сосредоточимся только на C# составляющей.
Select запрос
Ну и начнем мы, пожалуй, с самых популярных SELECT запросов. В первую очередь нам необходим класс, который мы хотим наполнять данными.
public class Witcher
{
public string WitcherNickname { get; set; }
public string SwordName { get; set; }
public string WitchersSchool { get; set; }
}Затем напишем SQL запрос к базе данных, чтобы загрузить все данные по ведьмакам.
private static readonly string _selectQuery = $@"select
witcher_nickname as WitcherNickname,
silver_sword_name as SilverSwordName,
witchers_school as WitcherSchool
from witchers";Далее выполним данный запрос к БД.
private static readonly IDbConnection _dbConnection;
public static void Main()
{
var witchers = _dbConnection.Query<Witcher>(_selectQuery);
}Dapper позволяет связать запрос с классом благодаря алиасам “as” внутри запроса, но требует, чтобы алиас соответствовал названию свойства класса. В нашем примере “witcher_nickname as WitcherNickname” внутри запроса позволяет связать поле “witcher_nickname” из БД со свойством “WitcherNickname” в нашем классе. И все бы хорошо, но что, если мы захотим переименовать свойство класса? Нам придется править все SQL запросы, где используется этот класс, а таких мест может быть много. Чтобы избежать этой проблемы поправим немного наш запрос.
private static readonly string _selectQuery = $@"select
witcher_nickname as {nameof(Witcher.WitcherNickname)},
sword_name as {nameof(Witcher.SwordName)},
witchers_school as {nameof(Witcher.WitchersSchool)}
from witchers";Заменим алиасы на конструкцию nameof(), которая позволит нам связать запрос со свойствами класса, но при этом, в случае рефакторинга класса Witcher в любимой IDE, у нас автоматически подменяется названия свойств в запросах или, в случае удаления свойства, приложение просто не сбилдится, с ошибкой, что в запросе используется имя, которое удалено. В общем, использование данной конструкции значительно уменьшает количество ошибок, головной боли и повышает качество кода.
Запросы с параметрами
Скорее всего, рано или поздно придет аналитик с продактом и скажут, что делать выборку по всем ведьмакам уже неактуально, и надо строить рейтинги по ведьмакам из каждой школы. Мы, конечно, знаем, что Геральт и школа волка даст всем прикурить, но запрос все равно напишем.
private static readonly string _selectQuery = $@"select
witcher_nickname as {nameof(Witcher.WitcherNickname)},
sword_name as {nameof(Witcher.SwordName)},
witchers_school as {nameof(Witcher.WitchersSchool)}
from witchers
where witchers_school = @Name";
private static readonly IDbConnection _dbConnection;
public static void Main()
{
var wolfWitchers = _dbConnection.Query<Witcher>(
_selectQuery,
new {Name = "Школа волка"});
}В Dapper любой параметр, который необходимо передать в запрос помечается @{Name}, где Name должен совпадать с названием параметра в коде. Затем в функцию Query передаются значения этих параметров. В данном примере мы использовали анонимный класс для передачи параметров.
new {Name = "Школа волка"}Но, как не трудно догадаться, тут можно использовать и обычный класс, давайте создадим класс ведьмачьих школ и передадим его в запрос.
public class WitcherSchool
{
public string Name { get; set; }
public string Location { get; set; }
}
private static readonly string _selectQuery = $@"select
witcher_nickname as {nameof(Witcher.WitcherNickname)},
sword_name as {nameof(Witcher.SwordName)},
witchers_school as {nameof(Witcher.WitchersSchool)}
from witchers
where witchers_school = @Name";
private static readonly IDbConnection _dbConnection;
public static void Main()
{
var wolfSchool = new WitcherSchool
{
Name = "Школа волка",
Location = "Каер Морхен"
};
var wolfWitchers = _dbConnection.Query<Witcher>(_selectQuery, wolfSchool);
}Теперь наш запрос принимает на вход wolfSchool и в SQL подставляется значение свойства Name. В данном примере мы создали экземпляр класса WitcherSchool вручную, но, как можно догадаться, он мог бы быть загружен из базы или еще откуда. Использовать класс вместо анонимного типа полезно, когда у нас есть связанные классы и таблицы, и для написания запроса можно не создавать анонимный класс, а использовать уже готовый.
Ну и соответственно, раз мы теперь передаем в качества параметра не анонимный класс, то мы можем использовать оператор nameof().
private static readonly string _selectQuery = $@"select
witcher_nickname as {nameof(Witcher.WitcherNickname)},
sword_name as {nameof(Witcher.SwordName)},
witchers_school as {nameof(Witcher.WitchersSchool)}
from witchers
where witchers_school = @{nameof(WitcherSchool.Name)}";Теперь при рефакторинге и изменении класса WolfSchool мы получаем стабильно работающий код и можем не боятся за наши запросы в БД.
Insert запрос
Без лишних отступлений создадим несколько ведьмаков и сохраним их в БД.
private static readonly string _insertQuery = $@"insert into witchers
(witcher_nickname, sword_name, witchers_school)
values
@{nameof(Witcher.WitcherNickname)},
@{nameof(Witcher.SwordName)},
@{nameof(Witcher.WitchersSchool)}";
private static readonly IDbConnection _dbConnection;
public static void Main()
{
var geralt = new Witcher
{
WitcherNickname = "Гервант из Рыблии",
SwordName = "Махакамский рунный сигиль",
WitchersSchool = "Школа волка"
};
var lambert = new Witcher
{
WitcherNickname = "Ламберт",
SwordName = "Новиградский меч",
WitchersSchool = "Школа волка"
};
var witchers = new List<Witcher>
{
geralt,
lambert
};
_dbConnection.Execute(_insertQuery, witchers);
}Тут мы написали Insert запрос, замапили его на класс ведьмаков, затем создали двух ведьмаков: Геральта и Ламберта и собрали список ведьмаков, которых необходимо добавить в БД. Для выполнения запросов, которые не возвращают результата, используем метод Execute и передаем ему коллекцию ведьмаков для вставки.
Скорее всего, в таблице ведьмаков имеется автоинкрементарное поле id, и было бы неплохо при вставке получить его значения. Перепишем запрос, чтобы получить id добавленных записей.
private static readonly string _insertQuery = $@"insert into witchers
(witcher_nickname, sword_name, witchers_school)
values
@{nameof(Witcher.WitcherNickname)},
@{nameof(Witcher.SwordName)},
@{nameof(Witcher.WitchersSchool)}
returning id";Ну и соответственно изменим вызов к БД.
var ids = _dbConnection.Query<int>(_insertQuery, witchers);Теперь при вставке ведьмаков в БД будут возвращаться id вставленных записей в соответствующем порядке, их можно использовать, например, чтобы потом проставить ссылки на ведьмаков в другие таблицы.
Delete запрос
Предположим, что на школу грифона напала дикая охота и уничтожила всех ведьмаков.
private static readonly string _deleteQuery = $@"delete from witchers
where witchers_school = @SchoolName";
private static readonly IDbConnection _dbConnection;
public static void Main()
{
_dbConnection.Execute(_deleteQuery, new { SchoolName = "Школа грифона" });
}Да, удалять данные в приложениях равно самоубийству, потому что потом придет менеджер и скажет, что хочет сделать корзину или дать возможность пользователю восстанавливать удаленные данные, но иногда есть ситуации, когда и такие запросы пригодятся.
Copy запрос
Часто встречающаяся задача — это копирование данных в таблицах, мы обычно реализуем следующим образом.
private static readonly string _copyQuery = $@"insert into witchers
(witcher_nickname, sword_name, witchers_school)
select
@NewName, sword_name, witchers_school
from witchers
where witcher_nickname = @Name";
private static readonly IDbConnection _dbConnection;
public static void Main()
{
_dbConnection.Execute(
_copyQuery,
new { Name = "Геральт",
NewName = "Магический клон Геральта" });
}В запрос передаем имя ведьмака, чей клон при помощи астральной магии мы хотим создать, и новое название для клона. В БД получаем новую запись с таким же мечом и школой, как у Геральта, но новым именем.
Транзакции
Часто приходится выполнять несколько запросов друг за другом, но в рамках одной транзакции. Например, мы хотим удалить все данные по школе волка и сразу занести в нее двух новых ведьмаков. Вот как это выглядит с использованием Dapper.
public static void Main()
{
var geralt = new Witcher
{
WitcherNickname = "Гервант из Рыблии",
SwordName = "Махакамский рунный сигиль",
WitchersSchool = "Школа волка"
};
var lambert = new Witcher
{
WitcherNickname = "Ламберт",
SwordName = "Новиградский меч",
WitchersSchool = "Школа волка"
};
var witchers = new List<Witcher>
{
geralt,
lambert
};
using var transaction = _dbConnection.BeginTransaction();
_dbConnection.Execute(_deleteQuery,
new { SchoolName = "Школа волка" },
transaction);
var ids = _dbConnection.Query<int>(_insertQuery, witchers, transaction);
transaction.Commit();
}Создаем транзакцию методом BeginTransaction(). И во все запросы передаем ее в качестве аргумента функции. В конце не забываем закоммитить транзакцию. Таким образом мы можем выполнить 2 и более запроса за 1 транзакцию. В случае если во время выполнения запроса происходит ошибка, то транзакция автоматически откатывается и вываливается ексепшн.
В заключение
В данной статье отражены основные операции, с которыми сталкиваются наши команды разработки при работе с БД и то, как их можно реализовать при помощи библиотеки Dapper. Сложные SQL запросы пишутся по аналогии. Для получения результатов мапим возвращаемые значения на классы или просто получаем dynamic в ответ, для передачи параметров используем @.
Мы стараемся всегда использовать классы и маппинг на основе конструкции nameof(), чтобы случайный рефакторинг или удаление классов или их свойств не уронили приложение, и все ошибки выявились на этапе билда приложения. Когда лень создавать класс под запрос, то позволяем себе пользоваться анонимными классами, но всегда строго прописываем имя передаваемого параметра, чтобы избежать проблем с рефакторингом.
Для тех, кто заинтересовался в использовании Dapper, ссылка на официальный туториал.
