Використання параметрів з табличним значенням з Dapper в .NET

Якщо ви працюєте з SQL Server або Azure SQL, ви напевно чули про параметри з табличним значенням. Якщо ж ви – бекенд-розробник, ви, безумовно, будете раді дізнатися, як легко ми можемо використовувати їх у нашому додатку за допомогою Dapper.

Параметри з табличним значенням

TVP – одна з найкорисніших функцій SQL Server. Вона дозволяє просто передавати декілька рядків даних з клієнтської програми в SQL Server. Це означає, що ми можемо передати всю таблицю в збережену процедуру або функцію одним параметром. Для того, щоб виконати будь-яку дію, нам потрібно почати зі створення типу в базі даних.

CREATE TYPE [dbo].[udtt_Project] AS TABLE
(
	[Id] int NULL,
	[Name] nvarchar(4000) NOT NULL,
	[ProjectStartDate] DateTimeOffset NULL,
	[Active] bit NOT NULL, 
	[Draft] bit NOT NULL
)

Після цього кроку ми можемо створити Збережену процедуру, яка прийме наш Визначений користувачем тип таблиці як параметр.

CREATE PROCEDURE [dbo].[ProcedureWhichAcceptsProjectsAsTVP]
	@projects [dbo].[udtt_Project] readonly
AS
BEGIN
	SELECT count(*)
	from @projects;
END

Це все, що нам потрібно зробити в базі даних. Але перш ніж ми зможемо використовувати нашу Збережену процедуру, нам потрібно навчитися передавати Параметр з табличним значенням за допомогою Dapper…

Передача параметру з табличним значенням за допомогою Dapper

Щоб створити параметр з табличним значенням, потрібно викликати метод AsTableValuedParameter. Цей метод перетворює DataTable або IEnumerable<T>, де T : IDataRecord, в ICustomQueryParameter. Ми зосередимося на DataTable, тому що я вважаю, що його буде легше зрозуміти.

Ми як розробники зазвичай використовуємо не ці типи, а скоріше списки певних моделей. Щоб створити TVP, нам потрібно перетворити наш список в DataTable і викликати AsTableValuedParameter. Припустимо, що у нас є модель C#, яка точно представляє наш Визначений користувачем тип таблиці SQL, що виглядає наступним чином:

public class Project
{
    public int? Id { get; set; }
    public string Name { get; set; } = String.Empty;
    public DateTimeOffset? ProjectStartDate { get; set; }
    public bool Active { get; set; }
    public bool Draft { get; set; }
}

Перше, що спадає нам на думку, це просто створити DataTable, вручну додати потрібні стовпці та заповнити їх даними наступним чином:

using var conn = new SqlConnection(connectionString);
conn.Open();

// some example data
List<Project> projects = new()
{
    new Project { Id = 1, Name = "Name1", ProjectStartDate = DateTimeOffset.Parse("2022-11-01"), Active = true, Draft = false },
    new Project { Id = 2, Name = "Name2", ProjectStartDate = DateTimeOffset.Parse("2022-12-01"), Active = false, Draft = true }
};

// create DataTable
DataTable projectsDT = new();
projectsDT.Columns.Add(nameof(Project.Id), typeof(int));
projectsDT.Columns.Add(nameof(Project.Name), typeof(string));
projectsDT.Columns.Add(nameof(Project.ProjectStartDate), typeof(DateTimeOffset));
projectsDT.Columns.Add(nameof(Project.Active), typeof(bool));
projectsDT.Columns.Add(nameof(Project.Draft), typeof(bool));

// add rows to DataTable
foreach (var project in projects)
{
    var row = projectsDT.NewRow();
    row[nameof(Project.Id)] = project.Id ?? (object)DBNull.Value;
    row[nameof(Project.Name)] = project.Name;
    row[nameof(Project.ProjectStartDate)] = project.ProjectStartDate ?? (object)DBNull.Value;
    row[nameof(Project.Active)] = project.Active;
    row[nameof(Project.Draft)] = project.Draft;
    projectsDT.Rows.Add(row);
}

// create parameters
var parameters = new
{
    projects = projectsDT.AsTableValuedParameter("[dbo].[udtt_Project]")
};

// execute Stored Procedure
return await conn.ExecuteScalarAsync<int>(
    "[dbo].[ProcedureWhichAcceptsProjectsAsTVP]",
    param: parameters,
    commandType: CommandType.StoredProcedure);

Це чудове рішення. Крім того, ви можете включити фрагмент коду, що відповідає за створення DataTable, як статичний метод у клас Project, щоб використовувати його повторно в інших місцях.

Згенерований SQL-скрипт

Тепер погляньмо, що згенерував і передав додаток Dapper до бази даних. В інструменті Profiler ми бачимо, що було виконано наступний скрипт:

declare @p1 dbo.udtt_Project
insert into @p1 values(1,N'Name1','2022-11-01 00:00:00 +01:00',1,0)
insert into @p1 values(2,N'Name2','2022-12-01 00:00:00 +01:00',0,1)

exec [dbo].[ProcedureWhichAcceptsProjectsAsTVP] @projects=@p1

Чи все гаразд? Ну, всі дані, які вставляються в змінну @p1, відображаються у вигляді порядкових чисел. Оператор Insert не вказує жодних стовпців, тому вся операція залежить від узгодженості порядку стовпців в C# DataTable і Визначеного користувачем типу таблиці в SQL. Завжди слід пам’ятати, що відображення за назвою стовпця не здійснюється, тож під час створення DataTable завжди слід дбати вручну про:

правильний порядок стовпців,
додавання стовпців, які існують в UDTT, але відсутні в моделі C#,
пропуск реквізитів, які існують в моделі C#, але відсутні в UDTT.

Автоматизуйте створення TVP

Загалом, ми вже навчилися передавати TVP за допомогою Dapper. Що ще можна зробити? Уявіть, що ми використовуємо в додатку багато різних UDTT або в процесі розробки часто змінюємо їх структуру. Тоді ми змушені вручну визначати структуру DataTable для всіх UDTT і в разі внесення якихось змін виправляти їх. Звичайно, ми не хочемо витрачати час на такі дії, тому спробуємо автоматизувати процес побудови DataTable.

Автоматизоване створення TVP – 1-й підхід

Перше, що спадає на думку – автоматичне створення DataTable на основі властивостей моделі. Нам не доведеться робити це кожного разу, коли з’являється новий UDTT. Просто створіть модель на C# з точно такими ж властивостями і запустіть якийсь метод, що генерує DataTable. Виглядає добре? Супер!

Ми можемо визначити метод розширення, який перетворює IEnumerable<T> в DataTable на основі властивостей, визначених у T,

public static DataTable ToDataTable<T>(this IEnumerable<T> enumerable)
{
    var dataTable = new DataTable();
    var propertyDescriptorCollection = TypeDe-scriptor.GetProperties(typeof(T));
    for (int i = 0; i < propertyDescriptorCollection.Count; i++)
    {
        var propertyDescriptor = propertyDescriptorCollection[i];
        var type = propertyDescriptor.PropertyType;

        if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
            type = Nullable.GetUnderlyingType(type)!;

        dataTable.Columns.Add(propertyDescriptor.Name, type);
    }
    var values = new object[propertyDescriptorCollection.Count];
    foreach (T iListItem in enumerable)
    {
        for (int i = 0; i < values.Length; i++)
        {
            values[i] = propertyDescriptorCollec-tion[i].GetValue(iListItem)!;
        }
        dataTable.Rows.Add(values);
    }
    return dataTable;
}

і використовувати цей метод під час створення TVP:

using var conn = new SqlConnection(connectionString);
conn.Open();

// some example data
List<Project> projects = new()
{
    new Project { Id = 1, Name = "Name1", ProjectStartDate = DateTimeOff-set.Parse("2022-11-01"), Active = true, Draft = false },
    new Project { Id = 2, Name = "Name2", ProjectStartDate = DateTimeOff-set.Parse("2022-12-01"), Active = false, Draft = true }
};

// create DataTable
var projectsDT = projects.ToDataTable();

// create parameters
var parameters = new
{
    projects = projectsDT.AsTableValuedParameter("[dbo].[udtt_Project]")
};

// execute Stored Procedure
return await conn.ExecuteScalarAsync<int>(
    "[dbo].[ProcedureWhichAcceptsProjectsAsTVP]",
    param: parameters,
    commandType: CommandType.StoredProcedure);

Такий підхід виглядає доволі перспективним. Нам більше не потрібно визначати DataTable вручну. Нам просто потрібно викликати метод ToDataTable для будь-якого IEnumerable<T>. На жаль, цей підхід має і свої недоліки. Пам’ятаєте, яким чином додаток Dapper створює TVP? Він відображає стовпці в певному порядку, а не за назвами стовпців. Відтак існує кілька ситуацій, у яких такий підхід не буде працювати:

Якщо порядок реквізитів моделі C# не збігається з порядком стовпців UDTT (можливо, в моделях є спадковість, яка змінює порядок, або щось інше?).
Якщо в моделі C# є якась додаткова властивість, якої немає в UDTT (ми спробуємо передати занадто багато стовпців і отримаємо помилку).
Якщо в UDTT є стовпець, якого не існує в моделі C# (наприклад, менша кількість стовпців, ніж потрібно, порушує порядок і може спричинити помилку сумісності типів у стовпцях).

З огляду на вищесказане, цей підхід буде працювати тільки тоді, коли кількість і порядок стовпців точно збігаються між моделлю UDTT і C#. Очевидною перевагою є автоматичне створення DataTable, але враховуючи недоліки цього підходу, можна сказати, що він не настільки універсальний, як хотілося б.

Зрештою, якщо у вашому проєкті всі моделі C# точно відповідають UDTT, і ви будете стежити за тим, щоб так і залишалося, ви можете застосувати цей підхід. Якщо ж ви хоч трохи стурбовані невідповідністю, я запрошую вас до наступного розділу, у якому ми спробуємо розробити краще рішення.

Автоматизоване створення TVP – 2-й підхід

Наш перший підхід мав багато недоліків, які нам потрібно врахувати та усунути. Однак для того, щоб не допустити плутанини в стовпцях або заповнити відсутні стовпці, нам потрібно знати як визначення моделі на C#, так і UDTT в базі даних. Отже, спробуймо зчитати визначення UDTT з бази даних і зіставити кожну властивість з конкретним стовпцем UDTT за назвою. Так ми уникнемо плутанини з порядком стовпців або помилок через те, що кількість стовпців не збігається.

Погляньмо на реалізацію:

using Dapper;
using System.Data;

namespace DapperTVPOrderedExample.DapperExtensions
{
    internal static class DapperExtensions
    {
        /// <summary>
        /// This extension converts enumerable of objects to a Dapper TVP with columns ordered by T-SQL UDTT columns.
        /// Class properties names must correspond to T-SQL UDTT column names.
        /// </summary>
        /// <typeparam name="T">Type to convert from</typeparam>
        /// <param name="enumerable"></param>
        /// <param name="typeName">database type name; if schema is not defi-ned then 'dbo' schema is used by default</param>
        /// <param name="connection">database connection needed to determine column order</param>
        /// <param name="transaction">optional transaction if conversion is done inside any</param>
        /// <returns>Table-Valued Parameter</returns>
        public static async Task<SqlMapper.ICustomQueryParameter> AsTableValu-edParameter<T>(this IEnumerable<T> enumerable, string typeName, IDbConnection connection, IDbTransaction? transaction = null)
        {
            // set type schema and name
            var schema = "dbo";
            var isolatedTypeName = "";
            var typeNameParts = typeName.Replace("[", "").Replace("]", "").Split(".");
            if (typeNameParts.Length == 1)
            {
                isolatedTypeName = typeNameParts[0];
            }
            else if (typeNameParts.Length == 2)
            {
                schema = typeNameParts[0];
                isolatedTypeName = typeNameParts[1];
            }
            else
            {
                throw new ArgumentException($"Argument typeName = {typeName} can have only one dot dividing schema from type name.");
            }

            // query for column names in order
            var typeColumnsQuery = $@"SELECT c.name
            FROM sys.columns c
            join sys.table_types t on t.type_table_object_id = c.object_id
            join sys.schemas s on s.schema_id = t.schema_id
            where s.name = '{schema}'
            and t.[name] = '{isolatedTypeName}'
            order by column_id;";

            var orderedColumnNames = await connec-tion.QueryAsync<string>(typeColumnsQuery, transaction: transaction);
            if (orderedColumnNames == null || !orderedColumnNames.Any())
                throw new Exception($"Table type {typeName} returned no co-lumns");

            // create DataTable
            using var dt = new DataTable();
            var props = typeof(T).GetProperties();

            // add columns in specific order
            foreach (string colName in orderedColumnNames)
            {
                var prop = props.SingleOrDefault(p => p.Name == colName);
                if (prop == null)
                    dt.Columns.Add(colName);
                else
                    dt.Columns.Add(prop.Name, Nulla-ble.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            }

            // add row to DataTable for every item
            foreach (var item in enumerable)
            {
                var row = dt.NewRow();
                // find corresponding prop for every column and add its value or null
                foreach (string colName in orderedColumnNames)
                {
                    try
                    {
                        var prop = props.SingleOrDefault(p => p.Name == colNa-me);
                        if (prop == null)
                            row[colName] = (object)DBNull.Value;
                        else
                        {
                            var value = prop.GetValue(item, null);
                            row[prop.Name] = value ?? (object)DBNull.Value;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new Exception($"Exception when assigning value to DataTable column when colName = '{colName}'", ex);
                    }

                }
                dt.Rows.Add(row);
            }
            // return TVP
            return dt.AsTableValuedParameter(typeName);
        }

        /// <summary>
        /// This extension converts an object to a 1-row Dapper TVP with co-lumns ordered by T-SQL UDTT columns.
        /// Class properties names must coorrestopond to T-SQL UDTT column names.
        /// </summary>
        /// <typeparam name="T">Type to convert from</typeparam>
        /// <param name="item"></param>
        /// <param name="typeName">database type name; if schema is not defi-ned then 'dbo' schema is used by default</param>
        /// <param name="connection">database connection needed to determine column order</param>
        /// <param name="transaction">optional transaction if conversion is done inside any</param>
        /// <returns>Table-Valued Parameter</returns>
        public static async Task<SqlMapper.ICustomQueryParameter> AsTableValu-edParameter<T>(this T item, string typeName, IDbConnection connection, IDb-Transaction? transaction = null) where T : class
        {
            return await (new List<T> { item }).AsEnumerable().AsTableValuedParameter(typeName, connection, transaction);
        }

        /// <summary>
        /// This extension converts list of objects to a Dapper TVP with co-lumns ordered by T-SQL UDTT columns.
        /// Class properties names must coorrestopond to T-SQL UDTT column names.
        /// </summary>
        /// <typeparam name="T">Type to convert from</typeparam>
        /// <param name="list"></param>
        /// <param name="typeName">database type name; if schema is not defi-ned then 'dbo' schema is used by default</param>
        /// <param name="connection">database connection needed to determine column order</param>
        /// <param name="transaction">optional transaction if conversion is done inside any</param>
        /// <returns>Table-Valued Parameter</returns>
        public static async Task<SqlMapper.ICustomQueryParameter> AsTableValu-edParameter<T>(this List<T> list, string typeName, IDbConnection connection, IDbTransaction? transaction = null)
        {
            return await list.AsEnumerable().AsTableValuedParameter(typeName, connection, transaction);
        }


    }
}

У наведеному вище коді ми використовуємо лише Dapper та System.Data. Такі параметри, як простір імен, назви класів або методів можна змінювати, тому ви, за бажанням, можете вільно це робити це. Я назвав метод AsTableValuedParameter (так само, як і оригінальний метод Dapper), щоб чітко показати, що він дає той самий результат, що і Dapper.

Варто зазначити, що у нас є 3 статичних методи, причому 2 з них лише передають дані далі згідно з відповідним типом. Ми ж зосередимося на 1-му методі, який містить всю логіку.

1-й параметр є Пронумерованим переліком (Enumerable) об’єктів, які містять дані для перетворення. Далі ми повинні вказати назву UDTT, в який ми хочемо виконати перетворення. Його можна передавати зі схемою або без неї, а також у квадратних дужках. Потім нам потрібно створити з’єднання з базою даних. Воно буде використовуватися для отримання порядку стовпців UDTT. Врешті-решт, у нас є необов’язковий параметр транзакції, який можна передати, якщо перетворення відбувається у складі будь-якої транзакції.

Логіка досить проста. Спочатку ми встановлюємо змінні значення схеми та рядка імен UDTT. Потім ми робимо запит до бази даних, щоб отримати порядок стовпців UDTT. На основі отриманого результату ми додаємо стовпці в DataTable у правильному порядку з типом, взятим зі знайденого реквізиту в моделі C# (або без типу, якщо реквізиту не знайдено). Пізніше ми додаємо рядок до DataTable для кожного об’єкта у вхідному значенні Enumerable і присвоюємо значення відповідним стовпчикам на основі імені (або null, якщо не знайдено). Наприкінці ми викликаємо оригінальний метод AsTableValuedParameter у Dapper і отримуємо його результат.

Як його використовувати? Насамперед не забудьте його додати за допомогою DapperTVPOrderedExample.DapperExtensions. Тоді ми зможемо використовувати наші методи на будь-якому об’єкті, IEnumerable або Списку об’єктів безпосередньо наступним чином:

using var conn = new SqlConnection(connectionString);
conn.Open();

// some example data
List<Project> projects = new()
{
    new Project { Id = 1, Name = "Name1", ProjectStartDate = DateTimeOffset.Parse("2022-11-01"), Active = true, Draft = false },
    new Project { Id = 2, Name = "Name2", ProjectStartDate = DateTimeOffset.Parse("2022-12-01"), Active = false, Draft = true }
};

// create parameters
var parameters = new
{
    projects = await projects.AsTableValuedParameter("[dbo].[udtt_Project]", conn)
};

// execute Stored Procedure
return await conn.ExecuteScalarAsync<int>(
    "[dbo].[ProcedureWhichAcceptsProjectsAsTVP]",
    param: parameters,
    commandType: CommandType.StoredProcedure);

Ось і все. Нам немає потреби турбуватися про:

правильний порядок стовпців,
додавання стовпців, які існують в UDTT, але відсутні в моделі C#,
пропуск реквізитів, які існують в моделі C#, але відсутні в UDTT.

Однак нам потрібно звернути увагу на кілька інших аспектів:

імена властивостей повинні збігатися з іменами стовпців UDTT,
якщо в UDTT є додатковий стовпець, а в моделі C# його немає, то стовпець у TVP буде додано з усіма нульовими значеннями без жодного попередження,
кожне перетворення на TVP надсилатиме додатковий запит до бази даних.

Звичайно, ми можемо розвинути цю ідею далі, щоб усунути ці проблеми. Ми можемо реалізувати Атрибут (Attribute) для властивостей, який зберігатиме відображення назви стовпця UDTT, якщо захочемо назвати властивості по-іншому. Ми можемо реалізувати протоколювання, яке сповіщатиме про неспівпадіння якогось стовпця UDTT з будь-якою властивістю класу. Також ми можемо звернути увагу на кеш, якщо досить часто використовуємо у своєму додатку UDTT, щоб зменшити кількість запитів до бази даних для визначення UDTT. Цей код є лише загальною ідеєю, покликаною надихнути вас на подальші експерименти.

Висновок

Підтримка TVP у вашому додатку може дати суттєві переваги з точки зору ефективності. Сподіваюся, що автоматизація створення TVP, представлена в 2-му підході, полегшить вам життя.

Пам’ятайте, що хоча у цьому прикладі використано SQL Server/Azure SQL, ви можете використовувати і будь-яку іншу систему баз даних. Потрібно лише замінити запит, який відповідає за отримання стовпців UDTT у відповідному порядку.