If you are working with SQL Server or Azure SQL, you probably heard about Table-Valued Parameters. If by any chance you are a backend developer you will be happy to learn how easily we can utilize them in our application with Dapper.
Table-Valued Parameters
TVP is one of the most useful features of SQL Server. They provide an easy way to marshal multiple rows of data from a client application to SQL Server. That means we can pass the entire table into a Stored Procedure or Function in one single parameter. In order to do anything, we need to start by creating a type on the database.
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
)
After this step, we can create a Stored Procedure that will accept our User-Defined Table Type as a parameter.
CREATE PROCEDURE [dbo].[ProcedureWhichAcceptsProjectsAsTVP]
@projects [dbo].[udtt_Project] readonly
AS
BEGIN
SELECT count(*)
from @projects;
END
That’s all we need to do in the database. But before we can use our Stored Procedure, we need to learn how to pass Table-Valued Parameter with Dapper…
Passing Table-Valued Parameter with Dapper
To create a Table-Valued Parameter we need to call the method AsTableValuedParameter. This method converts DataTable or IEnumerable<T> where T : IDataRecord to ICustomQueryParameter. We will focus on DataTable because I believe it will be easier to understand.
As developers, we don’t usually use these types but rather some Lists of the specified models. To create TVP, we need to convert our List to DataTable and invoke AsTableValuedParameter. Assuming that we have C# model that exactly represents our SQL User-Defined Table Type that looks like this:
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; }
}
the first thing that comes to our mind is to simply create DataTable, manually add needed columns, and fill them with data like this:
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);
This is a perfectly fine solution. In addition, you can include the piece of code responsible for creating DataTable as a static method in the Project class to reuse it in other places.
Generated SQL script
Now, let’s see what Dapper generated and passed to the database. In the Profiler tool we can see that the following script was executed:
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
Do you see anything worrying? Well, all the data that is inserted into the @p1 variable is mapped ordinal. Insert statement is not specifying any columns so the whole operation relies on the consistency of column order in C# DataTable and User-Defined Table Type in SQL. We should always keep in mind, that there is no mapping by column name and we should always manually take care of:
- proper column order,
- adding columns that exist in UDTT but do not in the C# model,
- skipping props that exist in the C# model but do not in UDTT
when creating DataTable.
Automate the creation of TVP
All in all, we’ve already learned how to pass TVP using Dapper, so what more can we say? Well, imagine we are using many different UDTTs in the application or there are frequent changes in their structure during development. We are then forced to manually define DataTable structure for all UDTTs and correct them if there are any changes. Of course, we don’t want to waste time on such things, so we’ll try to automate the process of building DataTable.
Automated TVP creation – 1st approach
The first thought that comes to mind is the automatic creation of DataTable based on the properties of the model. We won’t have to do it every time a new UDTT appears. Just create a model in C# with the exact same properties and run some method that generates DataTable. Looks good? Okay, here we go!
We can define an extension method that converts IEnumerable<T> to DataTable based on properties defined in 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;
}
and use this method when building 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);
This approach looks promising. We no longer have to define DataTable manually. We just need to invoke ToDataTable method on any IEnumerable<T>. Unfortunately, this approach has its downsides. Do you remember how Dapper creates TVP? It maps columns in a certain order, not by column names. If we think about it, there are several situations where this approach won’t work:
- If the order of C# model props doesn’t match the UDTT columns order (maybe there will be some inheritance in models that will mess with the order or something else?).
- If there is any additional property in the C# model that does not exist in UDTT (we will try to pass too many columns and get an error).
- If there is a column in UDTT that does not exist in the C# model (fewer columns than required for example messes with the order and may cause type compatibility error on columns).
Considering the above, this approach will work only when the number and order of the columns match exactly between the UDTT and C# model. The obvious advantage is the automatic DataTable creation, but considering the disadvantages of this approach, it can be said that it is not as versatile as we would like.
After all, if in your project all C# models match UDTTs exactly and you will make sure that it stays that way, you can apply this approach. If you are even a little concerned about mismapping, I invite you to the next section where we will try to develop a better solution.
Automated TVP creation – 2nd approach
Our first approach had a lot of disadvantages that we need to take into account and eliminate them. But to prevent columns from getting out of order or to fill in missing columns, we need to know both the model definition in C# and the UDTT in the database. So, we will try to read the UDTT definition from the database and try to match each property to a specific UDTT column by name. No more mess with the columns’ order or errors because the number of columns doesn’t match.
Let’s see the implementation:
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);
}
}
}
In the code above we are only using Dapper and System.Data. Things like namespace, class name, or method names can be changed so feel free to do it if you want to. I named methods AsTableValuedParameter (the same name as the original Dapper method) to state clearly that is returns the same thing as Dapper.
It’s worth mentioning that we have 3 static methods where 2 of them only pass data further according to the corresponding type. We will focus then on the 1st method which contains all the logic.
The 1st parameter is an Enumerable of objects that contain data to convert. Next, we have to specify the name of the UDTT that we want to convert to. It can be passed with or without schema or square brackets. Then we need to have a database connection. It will be used to get the UDTT column order. In the end, we have an optional transaction parameter that can be passed if the conversion occurs as part of any transaction.
The logic is pretty simple. Initially, we are setting schema and the UDTT name string variables. Then we are querying the database to get UDTT column order. Based on the result we are adding columns to DataTable in the correct order with the type taken from found prop in the C# model (or without the type if a prop is not found). Later we are adding a row to DataTable for every object in the input Enumerable and assign values to proper columns based on name (or null if not found). In the end, we are invoking Dapper’s original AsTableValuedParameter method and returning its result.
How do we use it? First of all, remember to add using DapperTVPOrderedExample.DapperExtensions. Then we can use our methods on any object, IEnumerable, or List of objects directly like this:
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);
And that’s all. We do not have to worry about:
- proper column order,
- adding columns that exist in UDTT but do not in the C# model,
- skipping props that exist in the C# model but do not in UDTT.
However, we need to pay attention to a few other things:
- property names must match UDTT column names,
- if there is an additional column in UDTT but not in the C# model, the column in TVP will be added with all null values without any warning,
- every conversion to TVP will send an additional query to the database.
Of course, we can develop this idea further to eliminate these problems. We can implement an Attribute for properties that will hold UDTT column name mapping if we want to name properties differently. We can implement logging that will alert if some UDTT column will not be matched with any class property. Also, we can think about some cache if we use the UDTT quite often in our application to reduce the number of queries to the database for UDTT definition. This code is just a general idea to inspire you to explore further.
Conclusion
Supporting TVP in your application can offer great benefits in terms of efficiency. I hope automation of TVP creation presented in the “2nd approach” will make your life easier.
Remember, even though this example is using SQL Server/Azure SQL, you can use it with any other database system. It is just a matter of replacing the query that is responsible for getting UDTT columns in the correct order.
***
If you are interested in SQL or .Net, we recommend other articles by our experts, including (PL): 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? and ML.NET – uczenie maszynowe w wydaniu Microsoftu.
Leave a comment