Привет, дорогой читатель, в этой статье я хотел бы поделиться опытом работы с базой данных посредством 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, ссылка на официальный туториал.