{"id":16,"date":"2023-03-30T08:01:17","date_gmt":"2023-03-30T06:01:17","guid":{"rendered":"https:\/\/sii.ua\/blog\/?p=16"},"modified":"2024-02-16T09:45:39","modified_gmt":"2024-02-16T08:45:39","slug":"using-table-valued-parameters-with-dapper-in-net","status":"publish","type":"post","link":"https:\/\/sii.ua\/blog\/en\/using-table-valued-parameters-with-dapper-in-net\/","title":{"rendered":"Using Table-Valued Parameters with Dapper in .NET"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

Table-Valued Parameters<\/strong><\/h2>\n\n\n\n

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<\/a>. 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.<\/p>\n\n\n

\nCREATE TYPE [dbo].[udtt_Project] AS TABLE\n(\n\t[Id] int NULL,\n\t[Name] nvarchar(4000) NOT NULL,\n\t[ProjectStartDate] DateTimeOffset NULL,\n\t[Active] bit NOT NULL, \n\t[Draft] bit NOT NULL\n)\n<\/pre><\/div>\n\n\n
After this step, we can create a Stored Procedure that will accept our User-Defined Table Type as a parameter.<\/p>\n\n\n
\nCREATE PROCEDURE [dbo].[ProcedureWhichAcceptsProjectsAsTVP]\n\t@projects [dbo].[udtt_Project] readonly\nAS\nBEGIN\n\tSELECT count(*)\n\tfrom @projects;\nEND\n<\/pre><\/div>\n\n\n
That\u2019s 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\u2026<\/p>\n\n\n\n
Passing Table-Valued Parameter with Dapper<\/strong><\/h2>\n\n\n\n
To create a Table-Valued Parameter we need to call the method AsTableValuedParameter<\/em>. This method converts DataTable<\/em> or IEnumerable<T> where T : IDataRecord<\/em> to ICustomQueryParameter<\/em>. We will focus on DataTable<\/em> because I believe it will be easier to understand.<\/p>\n\n\n\n
As developers, we don\u2019t 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<\/em>. Assuming that we have C# model that exactly represents our SQL User-Defined Table Type that looks like this:<\/p>\n\n\n
\npublic class Project\n{\n    public int? Id { get; set; }\n    public string Name { get; set; } = String.Empty;\n    public DateTimeOffset? ProjectStartDate { get; set; }\n    public bool Active { get; set; }\n    public bool Draft { get; set; }\n}\n<\/pre><\/div>\n\n\n
the first thing that comes to our mind is to simply create DataTable<\/em>, manually add needed columns, and fill them with data like this:<\/p>\n\n\n
\nusing var conn = new SqlConnection(connectionString);\nconn.Open();\n\n\/\/ some example data\nList<Project> projects = new()\n{\n    new Project { Id = 1, Name = "Name1", ProjectStartDate = DateTimeOffset.Parse("2022-11-01"), Active = true, Draft = false },\n    new Project { Id = 2, Name = "Name2", ProjectStartDate = DateTimeOffset.Parse("2022-12-01"), Active = false, Draft = true }\n};\n\n\/\/ create DataTable\nDataTable projectsDT = new();\nprojectsDT.Columns.Add(nameof(Project.Id), typeof(int));\nprojectsDT.Columns.Add(nameof(Project.Name), typeof(string));\nprojectsDT.Columns.Add(nameof(Project.ProjectStartDate), typeof(DateTimeOffset));\nprojectsDT.Columns.Add(nameof(Project.Active), typeof(bool));\nprojectsDT.Columns.Add(nameof(Project.Draft), typeof(bool));\n\n\/\/ add rows to DataTable\nforeach (var project in projects)\n{\n    var row = projectsDT.NewRow();\n    row[nameof(Project.Id)] = project.Id ?? (object)DBNull.Value;\n    row[nameof(Project.Name)] = project.Name;\n    row[nameof(Project.ProjectStartDate)] = project.ProjectStartDate ?? (object)DBNull.Value;\n    row[nameof(Project.Active)] = project.Active;\n    row[nameof(Project.Draft)] = project.Draft;\n    projectsDT.Rows.Add(row);\n}\n\n\/\/ create parameters\nvar parameters = new\n{\n    projects = projectsDT.AsTableValuedParameter("[dbo].[udtt_Project]")\n};\n\n\/\/ execute Stored Procedure\nreturn await conn.ExecuteScalarAsync<int>(\n    "[dbo].[ProcedureWhichAcceptsProjectsAsTVP]",\n    param: parameters,\n    commandType: CommandType.StoredProcedure);\n<\/pre><\/div>\n\n\n
This is a perfectly fine solution. In addition, you can include the piece of code responsible for creating DataTable<\/em> as a static method in the Project<\/em> class to reuse it in other places.<\/p>\n\n\n\n
Generated SQL script<\/strong><\/h3>\n\n\n\n
Now, let\u2019s see what Dapper generated and passed to the database. In the Profiler tool we can see that the following script was executed:<\/p>\n\n\n
\ndeclare @p1 dbo.udtt_Project\ninsert into @p1 values(1,N'Name1','2022-11-01 00:00:00 +01:00',1,0)\ninsert into @p1 values(2,N'Name2','2022-12-01 00:00:00 +01:00',0,1)\n\nexec [dbo].[ProcedureWhichAcceptsProjectsAsTVP] @projects=@p1\n<\/pre><\/div>\n\n\n
Do you see anything worrying? Well, all the data that is inserted into the @p1<\/em> variable is mapped ordinal. Insert<\/em> statement is not specifying any columns so the whole operation relies on the consistency of column order in C# DataTable<\/em> and User-Defined Table Type<\/em> in SQL. We should always keep in mind, that there is no mapping by column name and we should always manually take care of:<\/p>\n\n\n\n
    \n
  • proper column order,<\/li>\n\n\n\n
  • adding columns that exist in UDTT but do not in the C# model,<\/li>\n\n\n\n
  • skipping props that exist in the C# model but do not in UDTT<\/li>\n<\/ul>\n\n\n\n
    when creating DataTable<\/em>.<\/p>\n\n\n\n
    Automate the creation of TVP<\/strong><\/h2>\n\n\n\n
    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<\/em> 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<\/em>.<\/p>\n\n\n\n
    Automated TVP creation \u2013 1st<\/sup> approach<\/strong><\/h3>\n\n\n\n
    The first thought that comes to mind is the automatic creation of DataTable<\/em> based on the properties of the model. We won\u2019t 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<\/em>. Looks good? Okay, here we go!<\/p>\n\n\n\n
    We can define an extension method that converts IEnumerable<T><\/em> to DataTable<\/em> based on properties defined in T<\/em><\/p>\n\n\n
    \npublic static DataTable ToDataTable<T>(this IEnumerable<T> enumerable)\n{\n    var dataTable = new DataTable();\n    var propertyDescriptorCollection = TypeDe-scriptor.GetProperties(typeof(T));\n    for (int i = 0; i < propertyDescriptorCollection.Count; i++)\n    {\n        var propertyDescriptor = propertyDescriptorCollection[i];\n        var type = propertyDescriptor.PropertyType;\n\n        if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))\n            type = Nullable.GetUnderlyingType(type)!;\n\n        dataTable.Columns.Add(propertyDescriptor.Name, type);\n    }\n    var values = new object[propertyDescriptorCollection.Count];\n    foreach (T iListItem in enumerable)\n    {\n        for (int i = 0; i < values.Length; i++)\n        {\n            values[i] = propertyDescriptorCollec-tion[i].GetValue(iListItem)!;\n        }\n        dataTable.Rows.Add(values);\n    }\n    return dataTable;\n}\n<\/pre><\/div>\n\n\n
    and use this method when building TVP:<\/p>\n\n\n
    \nusing var conn = new SqlConnection(connectionString);\nconn.Open();\n\n\/\/ some example data\nList<Project> projects = new()\n{\n    new Project { Id = 1, Name = "Name1", ProjectStartDate = DateTimeOff-set.Parse("2022-11-01"), Active = true, Draft = false },\n    new Project { Id = 2, Name = "Name2", ProjectStartDate = DateTimeOff-set.Parse("2022-12-01"), Active = false, Draft = true }\n};\n\n\/\/ create DataTable\nvar projectsDT = projects.ToDataTable();\n\n\/\/ create parameters\nvar parameters = new\n{\n    projects = projectsDT.AsTableValuedParameter("[dbo].[udtt_Project]")\n};\n\n\/\/ execute Stored Procedure\nreturn await conn.ExecuteScalarAsync<int>(\n    "[dbo].[ProcedureWhichAcceptsProjectsAsTVP]",\n    param: parameters,\n    commandType: CommandType.StoredProcedure);\n<\/pre><\/div>\n\n\n
    This approach looks promising. We no longer have to define DataTable<\/em> manually. We just need to invoke ToDataTable<\/em> method on any IEnumerable<T><\/em>. 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:<\/p>\n\n\n\n
      \n
    • If the order of C# model props doesn\u2019t match the UDTT columns order (maybe there will be some inheritance in models that will mess with the order or something else?).<\/li>\n\n\n\n
    • 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).<\/li>\n\n\n\n
    • 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).<\/li>\n<\/ul>\n\n\n\n
      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<\/em> creation, but considering the disadvantages of this approach, it can be said that it is not as versatile as we would like.<\/p>\n\n\n\n
      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.<\/p>\n\n\n\n
      Automated TVP creation \u2013 2nd<\/sup> approach<\/strong><\/h3>\n\n\n\n
      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\u2019 order or errors because the number of columns doesn\u2019t match.<\/p>\n\n\n\n
      Let\u2019s see the implementation:<\/p>\n\n\n
      \nusing Dapper;\nusing System.Data;\n\nnamespace DapperTVPOrderedExample.DapperExtensions\n{\n    internal static class DapperExtensions\n    {\n        \/\/\/ <summary>\n        \/\/\/ This extension converts enumerable of objects to a Dapper TVP with columns ordered by T-SQL UDTT columns.\n        \/\/\/ Class properties names must correspond to T-SQL UDTT column names.\n        \/\/\/ <\/summary>\n        \/\/\/ <typeparam name="T">Type to convert from<\/typeparam>\n        \/\/\/ <param name="enumerable"><\/param>\n        \/\/\/ <param name="typeName">database type name; if schema is not defi-ned then 'dbo' schema is used by default<\/param>\n        \/\/\/ <param name="connection">database connection needed to determine column order<\/param>\n        \/\/\/ <param name="transaction">optional transaction if conversion is done inside any<\/param>\n        \/\/\/ <returns>Table-Valued Parameter<\/returns>\n        public static async Task<SqlMapper.ICustomQueryParameter> AsTableValu-edParameter<T>(this IEnumerable<T> enumerable, string typeName, IDbConnection connection, IDbTransaction? transaction = null)\n        {\n            \/\/ set type schema and name\n            var schema = "dbo";\n            var isolatedTypeName = "";\n            var typeNameParts = typeName.Replace("[", "").Replace("]", "").Split(".");\n            if (typeNameParts.Length == 1)\n            {\n                isolatedTypeName = typeNameParts[0];\n            }\n            else if (typeNameParts.Length == 2)\n            {\n                schema = typeNameParts[0];\n                isolatedTypeName = typeNameParts[1];\n            }\n            else\n            {\n                throw new ArgumentException($"Argument typeName = {typeName} can have only one dot dividing schema from type name.");\n            }\n\n            \/\/ query for column names in order\n            var typeColumnsQuery = $@"SELECT c.name\n            FROM sys.columns c\n            join sys.table_types t on t.type_table_object_id = c.object_id\n            join sys.schemas s on s.schema_id = t.schema_id\n            where s.name = '{schema}'\n            and t.[name] = '{isolatedTypeName}'\n            order by column_id;";\n\n            var orderedColumnNames = await connec-tion.QueryAsync<string>(typeColumnsQuery, transaction: transaction);\n            if (orderedColumnNames == null || !orderedColumnNames.Any())\n                throw new Exception($"Table type {typeName} returned no co-lumns");\n\n            \/\/ create DataTable\n            using var dt = new DataTable();\n            var props = typeof(T).GetProperties();\n\n            \/\/ add columns in specific order\n            foreach (string colName in orderedColumnNames)\n            {\n                var prop = props.SingleOrDefault(p => p.Name == colName);\n                if (prop == null)\n                    dt.Columns.Add(colName);\n                else\n                    dt.Columns.Add(prop.Name, Nulla-ble.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);\n            }\n\n            \/\/ add row to DataTable for every item\n            foreach (var item in enumerable)\n            {\n                var row = dt.NewRow();\n                \/\/ find corresponding prop for every column and add its value or null\n                foreach (string colName in orderedColumnNames)\n                {\n                    try\n                    {\n                        var prop = props.SingleOrDefault(p => p.Name == colNa-me);\n                        if (prop == null)\n                            row[colName] = (object)DBNull.Value;\n                        else\n                        {\n                            var value = prop.GetValue(item, null);\n                            row[prop.Name] = value ?? (object)DBNull.Value;\n                        }\n                    }\n                    catch (Exception ex)\n                    {\n                        throw new Exception($"Exception when assigning value to DataTable column when colName = '{colName}'", ex);\n                    }\n\n                }\n                dt.Rows.Add(row);\n            }\n            \/\/ return TVP\n            return dt.AsTableValuedParameter(typeName);\n        }\n\n        \/\/\/ <summary>\n        \/\/\/ This extension converts an object to a 1-row Dapper TVP with co-lumns ordered by T-SQL UDTT columns.\n        \/\/\/ Class properties names must coorrestopond to T-SQL UDTT column names.\n        \/\/\/ <\/summary>\n        \/\/\/ <typeparam name="T">Type to convert from<\/typeparam>\n        \/\/\/ <param name="item"><\/param>\n        \/\/\/ <param name="typeName">database type name; if schema is not defi-ned then 'dbo' schema is used by default<\/param>\n        \/\/\/ <param name="connection">database connection needed to determine column order<\/param>\n        \/\/\/ <param name="transaction">optional transaction if conversion is done inside any<\/param>\n        \/\/\/ <returns>Table-Valued Parameter<\/returns>\n        public static async Task<SqlMapper.ICustomQueryParameter> AsTableValu-edParameter<T>(this T item, string typeName, IDbConnection connection, IDb-Transaction? transaction = null) where T : class\n        {\n            return await (new List<T> { item }).AsEnumerable().AsTableValuedParameter(typeName, connection, transaction);\n        }\n\n        \/\/\/ <summary>\n        \/\/\/ This extension converts list of objects to a Dapper TVP with co-lumns ordered by T-SQL UDTT columns.\n        \/\/\/ Class properties names must coorrestopond to T-SQL UDTT column names.\n        \/\/\/ <\/summary>\n        \/\/\/ <typeparam name="T">Type to convert from<\/typeparam>\n        \/\/\/ <param name="list"><\/param>\n        \/\/\/ <param name="typeName">database type name; if schema is not defi-ned then 'dbo' schema is used by default<\/param>\n        \/\/\/ <param name="connection">database connection needed to determine column order<\/param>\n        \/\/\/ <param name="transaction">optional transaction if conversion is done inside any<\/param>\n        \/\/\/ <returns>Table-Valued Parameter<\/returns>\n        public static async Task<SqlMapper.ICustomQueryParameter> AsTableValu-edParameter<T>(this List<T> list, string typeName, IDbConnection connection, IDbTransaction? transaction = null)\n        {\n            return await list.AsEnumerable().AsTableValuedParameter(typeName, connection, transaction);\n        }\n\n\n    }\n}\n<\/pre><\/div>\n\n\n
      In the code above we are only using Dapper<\/em> and System.Data<\/em>. 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<\/em> (the same name as the original Dapper method) to state clearly that is returns the same thing as Dapper.<\/p>\n\n\n\n
      It\u2019s 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<\/sup> method which contains all the logic.<\/p>\n\n\n\n
      The 1st<\/sup> parameter is an Enumerable<\/em> 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.<\/p>\n\n\n\n
      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<\/em> 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<\/em> for every object in the input Enumerable<\/em> and assign values to proper columns based on name (or null if not found). In the end, we are invoking Dapper\u2019s original AsTableValuedParameter<\/em> method and returning its result.<\/p>\n\n\n\n
      How do we use it? First of all, remember to add using DapperTVPOrderedExample.DapperExtensions<\/em>. Then we can use our methods on any object, IEnumerable, or List of objects directly like this:<\/p>\n\n\n
      \nusing var conn = new SqlConnection(connectionString);\nconn.Open();\n\n\/\/ some example data\nList<Project> projects = new()\n{\n    new Project { Id = 1, Name = "Name1", ProjectStartDate = DateTimeOffset.Parse("2022-11-01"), Active = true, Draft = false },\n    new Project { Id = 2, Name = "Name2", ProjectStartDate = DateTimeOffset.Parse("2022-12-01"), Active = false, Draft = true }\n};\n\n\/\/ create parameters\nvar parameters = new\n{\n    projects = await projects.AsTableValuedParameter("[dbo].[udtt_Project]", conn)\n};\n\n\/\/ execute Stored Procedure\nreturn await conn.ExecuteScalarAsync<int>(\n    "[dbo].[ProcedureWhichAcceptsProjectsAsTVP]",\n    param: parameters,\n    commandType: CommandType.StoredProcedure);\n<\/pre><\/div>\n\n\n
      And that\u2019s all. We do not have to worry about:<\/p>\n\n\n\n