Jeśli pracujesz z SQL Server lub Azure SQL, prawdopodobnie słyszałeś o Table-Valued Parameters. Jeśli na dodatek jesteś programistą backend, myślę, że warto, abyś dowiedział się, jak łatwo możemy je wykorzystać w naszej aplikacji z Dapperem.
Table-Valued Parameters
TVP to jedna z najbardziej użytecznych funkcji SQL Server. Takie parametry zapewniają łatwy sposób organizowania wielu wierszy danych z aplikacji klienckiej do usługi SQL Server. Oznacza to, że możemy przekazać całą tabelę do procedury składowanej lub funkcji w jednym parametrze. Aby ich użyć, musimy zacząć od utworzenia typu w bazie danych.
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
)
W następnym kroku możemy utworzyć procedurę składowaną, która przyjmie nasz typ tabelaryczny zdefiniowany przez użytkownika jako parametr.
CREATE PROCEDURE [dbo].[ProcedureWhichAcceptsProjectsAsTVP]
@projects [dbo].[udtt_Project] readonly
AS
BEGIN
SELECT count(*)
from @projects;
END
To wszystko, co musimy zrobić w bazie danych. Jednak zanim będziemy mogli użyć naszej procedury składowanej, musimy nauczyć się, jak przekazywać Table-Valued Parameter za pomocą Dappera…
Przekazywanie Table-Valued Parameter z wykorzystaniem Dappera
Aby utworzyć Table-Valued Parameter, musimy wywołać metodę AsTableValuedParameter. Metoda ta konwertuje DataTable lub IEnumerable<T> where T : IDataRecord na ICustomQueryParameter. Skupimy się na DataTable, ponieważ uważam, że będzie łatwiejszy do zrozumienia.
Jako programiści zwykle nie używamy tych typów, ale raczej listy określonych modeli. Oznacza to, że aby utworzyć TVP, musimy przekonwertować naszą Listę na DataTable, a następnie wywołać AsTableValuedParameter. Zakładając, że mamy model C#, który dokładnie reprezentuje nasz typ tabelaryczny (User-Defined Table Type), który wygląda następująco:
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; }
}
Pierwsza myśl, jaka przychodzi nam do głowy, to po prostu utworzenie DataTable, ręczne dodanie potrzebnych kolumn i wypełnienie ich danymi w następujący sposób:
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);
Tyle wystarczy, aby wszystko zadziałało. Dodatkowo, możesz dołączyć fragment kodu odpowiedzialny za tworzenie DataTable jako metodę statyczną w klasie Project, aby ponownie wykorzystać go w innych miejscach.
Wygenerowany skrypt SQL
Sprawdźmy teraz, co Dapper wygenerował i przekazał do bazy danych. W narzędziu Profiler widzimy, że został wykonany następujący skrypt:
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
Czy widzisz coś niepokojącego? Cóż, wszystkie dane wstawiane do zmiennej @p1 są mapowane porządkowo. Instrukcja Insert nie określa żadnych kolumn, więc cała operacja opiera się na prawidłowej kolejności kolumn po stronie DataTable w C# oraz User-Defined Table Type w SQL.
Należy zawsze pamiętać, że nie ma mapowania według nazwy kolumny i za każdym razem podczas tworzenia DataTable powinniśmy zadbać o:
- właściwą kolejność kolumn,
- dodawanie kolumn, które istnieją w UDTT, ale nie w modelu C#,
- pomijanie właściwości, które istnieją w modelu C#, ale nie w UDTT.
Automatyzacja tworzenia TVP
W sumie nauczyliśmy się już przekazywać TVP za pomocą Dappera, więc co więcej możemy powiedzieć? Cóż, wyobraź sobie, że używamy w aplikacji wielu różnych UDTT lub często dochodzi do zmian w ich strukturze podczas developmentu. Jesteśmy wtedy zmuszeni ręcznie zdefiniować strukturę DataTable dla wszystkich UDTT i poprawić je, jeśli wystąpią jakieś zmiany. Oczywiście nie chcemy tracić czasu na takie rzeczy, dlatego postaramy się zautomatyzować proces budowania DataTable.
Zautomatyzowane tworzenie TVP – podejście numer 1
Pierwsza myśl, jaka przychodzi do głowy, to automatyczne tworzenie DataTable na podstawie właściwości klasy. Nie będziemy musieli tego robić za każdym razem, gdy pojawi się nowy UDTT. Po prostu tworzymy model w języku C# z dokładnie takimi samymi właściwościami i uruchamiamy metodę, która generuje DataTable. Wygląda dobrze? Dobra, zaczynamy!
Możemy zdefiniować metodę rozszerzającą, która konwertuje IEnumerable<T> na DataTable na podstawie właściwości zdefiniowanych w 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;
}
i użyć jej do budowania TVP w następujący sposób:
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);
To podejście wygląda obiecująco. Nie musimy już ręcznie definiować DataTable. Wystarczy tylko wywołać metodę ToDataTable na dowolnym IEnumerable<T>.
Niestety, takie podejście ma swoje wady. Pamiętasz, jak Dapper tworzy TVP? Dodaje dane do kolumn w określonej kolejności, a nie według nazw kolumn. Jeśli się nad tym zastanowić, jest kilka sytuacji, w których to podejście nie zadziała:
- Jeśli kolejność właściwości modelu C# nie pasuje do kolejności kolumn UDTT (może w modelach będzie jakieś dziedziczenie, które zaburzy kolejność itp.?).
- Jeśli w modelu C# jest jakaś dodatkowa właściwość, której nie ma w UDTT (będziemy próbowali przekazać za dużo kolumn i dostaniemy błąd).
- Jeśli w UDTT istnieje kolumna, która nie istnieje w modelu C# (podanie mniejszej liczby kolumn niż jest to wymagane zakłóca dopasowanie ich w odpowiedniej kolejności i może powodować błąd zgodności typu w kolumnach).
Biorąc pod uwagę powyższe, takie podejście będzie działać tylko wtedy, gdy liczba i kolejność kolumn będą dokładnie takie same między modelem C# oraz UDTT. Oczywistą zaletą jest automatyczne tworzenie DataTable, jednak uwzględniając wady tego podejścia, można powiedzieć, że nie jest ono tak wszechstronne, jak byśmy tego chcieli.
Mimo wszystko możesz je zastosować, jeśli w twoim projekcie wszystkie modele C# dokładnie pasują do UDTT i zadbasz o to, żeby tak pozostało. Jeśli choć trochę martwi Cię błędne mapowanie, zapraszam do kolejnej sekcji, w której postaramy się opracować lepsze rozwiązanie.
Zautomatyzowane tworzenie TVP – podejście numer 2
Nasze pierwsze podejście miało wiele wad, które musimy wziąć pod uwagę oraz je wyeliminować. Jednak, aby zapewnić prawidłową kolejność kolumn lub uzupełniać brakujące kolumny, musimy znać zarówno definicję modelu w języku C#, jak i UDTT w bazie danych. Spróbujemy więc odczytać definicję UDTT z bazy danych i spróbować dopasować każdą właściwość do konkretnej kolumny UDTT według nazwy. Raz na zawsze pożegnamy bałagan związany z kolejnością kolumn lub błędami wynikającymi ze złej liczby kolumn.
Zobaczmy zatem implementację:
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);
}
}
}
W powyższym kodzie używamy tylko Dapper i System.Data. Rzeczy takie jak:
- namespace,
- nazwa klasy,
- nazwy metod
możesz śmiało zmienić, jeśli chcesz. Nazwałem metody AsTableValuedParameter (tak samo jak oryginalna metoda Dappera), aby jasno pokazać, że zwracają ten sam typ, co Dapper.
Warto wspomnieć, że mamy 3 metody statyczne, z których 2 tylko przekazują dane zgodnie z odpowiednim typem. Skupimy się więc na pierwszej metodzie, która zawiera całą logikę.
Pierwszy parametr to Enumerable obiektów zawierający dane do konwersji. Następnie, musimy podać nazwę UDTT, na który chcemy dokonać konwersji. Można ją przekazać z lub bez schematu lub nawiasów kwadratowych. Kolejno przekazujemy połączenie z bazą danych. Zostanie ono użyte do uzyskania kolejności kolumn UDTT. Jako ostatni mamy opcjonalny parametr transakcji, który można przekazać, jeśli konwersja ma miejsce w ramach dowolnej transakcji.
Logika jest dość prosta. Początkowo ustawiamy zmienne typu string ze schematem i nazwą UDTT. Następnie wysyłamy zapytanie do bazy danych, aby uzyskać kolejność kolumn UDTT. Na podstawie wyniku dodajemy kolumny do DataTable w odpowiedniej kolejności z typem zaczerpniętym ze znalezionej właściwości w modelu C# (lub bez typu, jeśli właściwość nie została znaleziona). Później dodajemy wiersz do DataTable dla każdego obiektu z wejściowego Enumerable i przypisujemy wartości do odpowiednich kolumn na podstawie nazwy (lub null, jeśli nie znaleziono właściwości). Na koniec wywołujemy oryginalną metodę AsTableValuedParameter Dappera i zwracamy jej wynik.
Jak użyć naszych nowych metod? Przede wszystkim pamiętaj o dodaniu using DapperTVPOrderedExample.DapperExtensions. Następnie możesz ich użyć bezpośrednio na dowolnym obiekcie, IEnumerable lub Liście obiektów w następujący sposób:
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);
I to wszystko. Nie musimy się już martwić o:
- odpowiednią kolejność kolumn,
- dodawanie kolumn, które istnieją w UDTT, ale nie w modelu C#,
- pomijanie właściwości, które istnieją w modelu C#, ale nie w UDTT.
Musimy jednak zwrócić uwagę na kilka innych rzeczy:
- nazwy właściwości muszą być zgodne z nazwami kolumn UDTT,
- jeśli w UDTT jest dodatkowa kolumna, której nie ma w modelu C#, kolumna w TVP zostanie dodana z wartościami null bez żadnego ostrzeżenia,
- każda konwersja do TVP spowoduje wysłanie dodatkowego zapytania do bazy danych.
Oczywiście, możemy dalej rozwijać ten pomysł, aby wyeliminować te problemy. Możemy zaimplementować atrybut dla właściwości, który będzie zawierał mapowanie nazw kolumn UDTT, jeśli chcemy nazwać właściwości inaczej. Mamy możliwość zaimplementowania logów, które będą ostrzegać, jeśli jakaś kolumna UDTT nie zostanie dopasowana do żadnej właściwości klasy. Możemy również pomyśleć o cache, jeśli dość często używamy UDTT w naszej aplikacji, aby zmniejszyć liczbę zapytań do bazy danych w celu odczytania definicji UDTT.
To tylko ogólne pomysły, które mają na celu zainspirować Cię do dalszego rozwoju.
Wnioski
Wsparcie TVP w Twojej aplikacji może przynieść ogromne korzyści w zakresie wydajności. Mam nadzieję, że automatyzacja tworzenia TVP przedstawiona w „drugim podejściu” ułatwi Ci nieco pracę.
Pamiętaj, że chociaż ten przykład używa SQL Server/Azure SQL, możesz go używać z dowolnym innym systemem bazy danych. To tylko kwestia zamiany zapytania, które odpowiada za uzyskanie kolumn UDTT we właściwej kolejności.
***
Jeśli interesuje Cię tematyka SQL lub .Net, polecamy inne artykuły naszych ekspertów m.in.: Analizowanie danych JSON w bazie danych MSSQL za pomocą SQL, Tabele i kolumny, czy może grafy, dokumenty i mapy, czyli jaki mamy wybór planując bazy danych? oraz ML.NET – uczenie maszynowe w wydaniu Microsoftu.
Zostaw komentarz