Dapper Dynamic Parameters with Table Valued Parameters
17,815
Solution 1
First create a User Defined Table type in Database
CREATE TYPE udtt_PersonList AS TABLE
(
...
)
GO
In your code
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("@PersonList", PersonList.AsTableValuedParameter("[dbo].[udtt_PersonList]"));
dynamicParameters.Add("TestOutput", 0, Dbtype.Int32, ParameterDirection.Output);
Solution 2
As I can understand that this requirement is not supported out of the box and I may need to code the specific helper. I have resolved it using a custom base abstract class TypeMap, which can be extended by all kinds of providers, to implement the API, which are not out of he box possible using the Dapper, I am pasting my implementation related to SQL-Server, similar can be done for other ADO.Net compliant providers:
namespace Dapper
{
#region NameSpaces
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
#endregion
/// <summary>
/// Type Map class for database provider specific code
/// </summary>
internal abstract class TypeMap
{
/// <summary>
/// Only Non Input Parameters collection
/// </summary>
public abstract Dictionary<string, object> NonInputParameterCollection { get; set; }
/// <summary>
/// Method to execute the DML via TypeMap
/// </summary>
/// <param name="connection"></param>
/// <param name="sql"></param>
/// <param name="commandType"></param>
/// <param name="dapperParams"></param>
/// <returns></returns>
public abstract int Execute(IDbConnection connection,
string sql,
CommandType commandType,
IEnumerable<DapperParam> dapperParams );
/// <summary>
/// Method to execute the Select to fetch IEnumerable via TypeMap
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="sql"></param>
/// <param name="commandType"></param>
/// <param name="dapperParams"></param>
/// <returns></returns>
public abstract IEnumerable<T> Query<T>(IDbConnection connection,
string sql,
CommandType commandType,
IEnumerable<DapperParam> dapperParams) where T : new();
/// <summary>
/// Fetch the relevant TypeMap
/// </summary>
/// <param name="provider"></param>
/// <returns></returns>
public static TypeMap GetTypeMap(string provider)
{
TypeMap typeMap = null;
switch (provider)
{
case "System.Data.SqlClient":
typeMap = new SqlTypeMap();
break;
default:
// SQl Server TypeMap
typeMap = new SqlTypeMap();
break;
}
return (typeMap);
}
}
/// <summary>
/// SQL Server provider type map
/// </summary>
internal class SqlTypeMap : TypeMap
{
public SqlTypeMap()
{
NonInputParameterCollection = new Dictionary<string, object>();
}
public override sealed Dictionary<string, object> NonInputParameterCollection { get; set; }
public override int Execute(IDbConnection connection,
string sql,
CommandType commandType,
IEnumerable<DapperParam> dapperParams)
{
int returnValue = -1;
var sqlConnection = (connection as SqlConnection) ?? new SqlConnection();
using (sqlConnection)
{
SqlCommand sqlCommand = null;
sqlCommand = sqlConnection.CreateCommand();
using (sqlCommand)
{
// public SqlParameter(string parameterName, SqlDbType dbType, int size, ParameterDirection direction, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, bool sourceColumnNullMapping, object value, string xmlSchemaCollectionDatabase, string xmlSchemaCollectionOwningSchema, string xmlSchemaCollectionName);
foreach (var param in dapperParams)
{
sqlCommand.Parameters.Add(new SqlParameter
{
ParameterName = param.ParamName,
SqlValue = param.ParamValue ?? DBNull.Value,
SqlDbType = TypeToSqlDbType[param.ParamType],
Direction = Map.DirectionMap[param.ParamDirection]
});
}
sqlCommand.CommandText = sql; // Assign Sql Text
sqlCommand.CommandType = commandType; // Assign CommandType
sqlCommand.Connection.Open(); // Explicitly open connection to use it with SqlCommand object
returnValue = sqlCommand.ExecuteNonQuery(); // Execute Query
foreach (SqlParameter param in sqlCommand.Parameters.Cast<SqlParameter>().Where(param => param.Direction != ParameterDirection.Input))
NonInputParameterCollection.Add(param.ParameterName, param.Value);
}
}
return (returnValue);
}
public override IEnumerable<T> Query<T>(IDbConnection connection,
string sql,
CommandType commandType,
IEnumerable<DapperParam> dapperParams)
{
IEnumerable<T> returnEnumerable = null;
var sqlConnection = (connection as SqlConnection) ?? new SqlConnection();
using (sqlConnection)
{
var sqlCommand = sqlConnection.CreateCommand();
using (sqlCommand)
{
foreach (var param in dapperParams)
{
sqlCommand.Parameters.Add(new SqlParameter
{
ParameterName = param.ParamName,
SqlValue = param.ParamValue ?? DBNull.Value,
SqlDbType = TypeToSqlDbType[param.ParamType],
Direction = Map.DirectionMap[param.ParamDirection]
});
}
sqlCommand.CommandText = sql; // Assign Sql Text
sqlCommand.CommandType = commandType; // Assign CommandType
var sqlDataAdapter = new SqlDataAdapter(sqlCommand);
var returnDataTable = new DataTable();
sqlDataAdapter.Fill(returnDataTable);
returnEnumerable = Common.ToList<T>(returnDataTable);
foreach (SqlParameter param in sqlCommand.Parameters.Cast<SqlParameter>()
.Where(param => param.Direction != ParameterDirection.Input))
NonInputParameterCollection.Add(param.ParameterName, param.Value);
}
}
return (returnEnumerable);
}
/// <summary>
/// Data Type to Db Type mapping dictionary for SQL Server
/// https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx
/// </summary>
public static readonly Dictionary<Type, SqlDbType> TypeToSqlDbType = new Dictionary<Type, SqlDbType>
{
// Mapping C# types to Ado.net SqlDbType enumeration
{typeof (byte), SqlDbType.TinyInt},
{typeof (sbyte), SqlDbType.TinyInt},
{typeof (short), SqlDbType.SmallInt},
{typeof (ushort), SqlDbType.SmallInt},
{typeof (int), SqlDbType.Int},
{typeof (uint), SqlDbType.Int},
{typeof (long), SqlDbType.BigInt},
{typeof (ulong), SqlDbType.BigInt},
{typeof (float), SqlDbType.Float},
{typeof (double), SqlDbType.Float},
{typeof (decimal), SqlDbType.Decimal},
{typeof (bool), SqlDbType.Bit},
{typeof (string), SqlDbType.VarChar},
{typeof (char), SqlDbType.Char},
{typeof (Guid), SqlDbType.UniqueIdentifier},
{typeof (DateTime), SqlDbType.DateTime},
{typeof (DateTimeOffset), SqlDbType.DateTimeOffset},
{typeof (byte[]), SqlDbType.VarBinary},
{typeof (byte?), SqlDbType.TinyInt},
{typeof (sbyte?), SqlDbType.TinyInt},
{typeof (short?), SqlDbType.SmallInt},
{typeof (ushort?), SqlDbType.SmallInt},
{typeof (int?), SqlDbType.Int},
{typeof (uint?), SqlDbType.Int},
{typeof (long?), SqlDbType.BigInt},
{typeof (ulong?), SqlDbType.BigInt},
{typeof (float?), SqlDbType.Float},
{typeof (double?), SqlDbType.Float},
{typeof (decimal?), SqlDbType.Decimal},
{typeof (bool?), SqlDbType.Bit},
{typeof (char?), SqlDbType.Char},
{typeof (Guid?), SqlDbType.UniqueIdentifier},
{typeof (DateTime?), SqlDbType.DateTime},
{typeof (DateTimeOffset?), SqlDbType.DateTimeOffset},
{typeof (System.Data.Linq.Binary), SqlDbType.Binary},
{typeof (IEnumerable<>), SqlDbType.Structured},
{typeof (List<>), SqlDbType.Structured},
{typeof (DataTable), SqlDbType.Structured},
};
}
/// <summary>
///
/// </summary>
public static class Map
{
/// <summary>
///
/// </summary>
public static Dictionary<Type, DbType> TypeToDbType = new Dictionary<Type, DbType>()
{
{typeof (byte), DbType.Byte},
{typeof (sbyte), DbType.Byte},
{typeof (short), DbType.Int16},
{typeof (ushort), DbType.Int16},
{typeof (int), DbType.Int32},
{typeof (uint), DbType.Int32},
{typeof (long), DbType.Int64},
{typeof (ulong), DbType.Int64},
{typeof (float), DbType.Single},
{typeof (double), DbType.Double},
{typeof (decimal), DbType.Decimal},
{typeof (bool), DbType.Boolean},
{typeof (string), DbType.String},
{typeof (char), DbType.StringFixedLength},
{typeof (Guid), DbType.Guid},
{typeof (DateTime), DbType.DateTime},
{typeof (DateTimeOffset), DbType.DateTimeOffset},
{typeof (byte[]), DbType.Binary},
{typeof (byte?), DbType.Byte},
{typeof (sbyte?), DbType.Byte},
{typeof (short?), DbType.Int16},
{typeof (ushort?), DbType.Int16},
{typeof (int?), DbType.Int32},
{typeof (uint?), DbType.Int32},
{typeof (long?), DbType.Int64},
{typeof (ulong?), DbType.Int64},
{typeof (float?), DbType.Single},
{typeof (double?), DbType.Double},
{typeof (decimal?), DbType.Decimal},
{typeof (bool?), DbType.Boolean},
{typeof (char?), DbType.StringFixedLength},
{typeof (Guid?), DbType.Guid},
{typeof (DateTime?), DbType.DateTime},
{typeof (DateTimeOffset?), DbType.DateTimeOffset},
{typeof (System.Data.Linq.Binary), DbType.Binary}
};
/// <summary>
/// Parameter Direction for Stored Procedure
/// </summary>
public static readonly Dictionary<string, ParameterDirection> DirectionMap =
new Dictionary<string, ParameterDirection>(StringComparer.InvariantCultureIgnoreCase)
{
{ParamDirectionConstants.Input, ParameterDirection.Input},
{ParamDirectionConstants.Output, ParameterDirection.Output},
{ParamDirectionConstants.InputOutput, ParameterDirection.InputOutput},
{ParamDirectionConstants.ReturnValue, ParameterDirection.ReturnValue}
};
}
}
Supporting classes and API, to make the above code work:
using System;
using System.Collections.Generic;
namespace Dapper
{
public class DapperParam
{
/// <summary>
/// Parameter Type Constructor
/// </summary>
/// <param name="paramName"></param>
/// <param name="paramType"></param>
/// <param name="paramDirection"></param>
/// <param name="paramValue"></param>
public DapperParam(string paramName,
Type paramType,
string paramDirection,
object paramValue)
{
ParamName = paramName;
ParamType = paramType;
ParamDirection = paramDirection;
ParamValue = paramValue;
}
/// <summary>
/// Parameter name
/// </summary>
public string ParamName { get; set; }
/// <summary>
/// Parameter Type
/// </summary>
public Type ParamType { get; set; }
/// <summary>
/// Parameter Direction
/// </summary>
public string ParamDirection { get; set; }
/// <summary>
/// Parameter Value
/// </summary>
public object ParamValue { get; set; }
}
internal static class DataConversionMap
{
/// <summary>
/// Type conversion, handles null
/// </summary>
/// <param name="obj"></param>
/// <param name="func"></param>
/// <returns></returns>
private static object ConvertDbData(object obj, Func<object> func)
{
return (!Convert.IsDBNull(obj)) ? func() : null;
}
/// <summary>
/// Dictionary map to convert to a given DataType. Returns a Func of object,object.
/// Internally calls ConvertDbData for Data Type conversion
/// </summary>
public static readonly Dictionary<Type, Func<object, object>> Map =
new Dictionary<Type, Func<object, object>>
{
{
typeof(Int16),
objectValue => ConvertDbData(objectValue, () => Convert.ToInt16(objectValue))
},
{
typeof(Int32),
objectValue => ConvertDbData(objectValue, () => Convert.ToInt32(objectValue))
},
{
typeof(Int64),
objectValue => ConvertDbData(objectValue, () => Convert.ToInt64(objectValue))
},
{
typeof(Boolean),
objectValue => ConvertDbData(objectValue, () => Convert.ToBoolean(objectValue))
},
{
typeof(string),
objectValue => ConvertDbData(objectValue, () => Convert.ToString(objectValue))
},
{
typeof(DateTime), objectValue =>
ConvertDbData(objectValue, () =>
{
DateTime dateTime = Convert.ToDateTime(objectValue);
if (dateTime.TimeOfDay.Equals(TimeSpan.Zero))
return dateTime.ToShortDateString();
return dateTime.ToString("MM/dd/yyyy HH:mm");
})
},
{
typeof(Byte),
objectValue => ConvertDbData(objectValue, () => Convert.ToByte(objectValue))
},
{
typeof(Double),
objectValue => ConvertDbData(objectValue, () => Convert.ToDouble(objectValue))
},
{
typeof(Decimal),
objectValue => ConvertDbData(objectValue, () => Convert.ToDecimal(objectValue))
},
{
typeof(TimeSpan),
objectValue => ConvertDbData(objectValue, () => TimeSpan.Parse(objectValue.ToString()))
},
{
typeof(Guid),
objectValue => ConvertDbData(objectValue, () => new Guid(objectValue.ToString()))
},
{
typeof(Byte[]),
objectValue => ConvertDbData(objectValue, () => (Byte[])(objectValue))
}
};
}
}
Common APIs
public static class Common
{
/// <summary>
/// Convert IEnumerable<T> to DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="collection"></param>
/// <returns></returns>
public static DataTable CreateTable<T>(this IEnumerable<T> collection)
{
// Fetch the type of List contained in the ParamValue
var tableType = typeof(T);
// Create DataTable which will contain data from List<T>
var dataTable = new DataTable();
// Fetch the Type fields count
int columnCount = tableType.GetProperties().Count();
var columnNameMappingDictionary = new Dictionary<string, string>();
// Create DataTable Columns using table type field name and their types
// Traversing through Column Collection
for (int counter = 0; counter < columnCount; counter++)
{
var propertyInfo = tableType.GetProperties()[counter];
var parameterAttribute = propertyInfo.GetParameterAttribute();
string columnName = (parameterAttribute != null) ? parameterAttribute.Name : propertyInfo.Name;
columnNameMappingDictionary.Add(propertyInfo.Name,
(parameterAttribute != null) ? parameterAttribute.Name : propertyInfo.Name);
dataTable.Columns.Add(columnName, tableType.GetProperties()[counter].PropertyType);
}
// Return parameter with null value
if (collection == null)
return dataTable;
// Traverse through number of entries / rows in the List
foreach (var item in collection)
{
// Create a new DataRow
DataRow dataRow = dataTable.NewRow();
// Traverse through type fields or column names
for (int counter = 0; counter < columnCount; counter++)
{
// Fetch Column Name
string columnName = columnNameMappingDictionary[tableType.GetProperties()[counter].Name];
//Fetch Value for each column for each element in the List<T>
dataRow[columnName] = item
.GetType().GetProperties()[counter]
.GetValue(item);
}
// Add Row to Table
dataTable.Rows.Add(dataRow);
}
return (dataTable);
}
/// <summary>
/// Convert IEnumerable<T> to DataTable
/// </summary>
/// <param name="paramValue"></param>
/// <returns></returns>
public static DataTable CreateTable(object paramValue)
{
// Fetch the type of List contained in the ParamValue
Type tableType = paramValue.GetType().GetGenericArguments()[0];
// Create DataTable which will contain data from List<T>
var genericDataTable = new DataTable();
// Fetch the Type fields count
int fieldCount = tableType.GetProperties().Count();
// Create DataTable Columns using table type field name and their types
// Traversing through Column Collection
for (int counter = 0; counter < fieldCount; counter++)
{
genericDataTable.Columns.Add(tableType.GetProperties()[counter].Name,
tableType.GetProperties()[counter].PropertyType);
}
// Traverse through number of entries / rows in the List
foreach (var item in (IEnumerable)paramValue)
{
// Create a new DataRow
DataRow dataRow = genericDataTable.NewRow();
// Traverse through type fields or column names
for (int counter = 0; counter < fieldCount; counter++)
{
// Fetch Column Name
string columnName = tableType.GetProperties()[counter].Name;
//Fetch Value for each column for each element in the List<T>
dataRow[columnName] = item
.GetType().GetProperties()[counter]
.GetValue(item);
}
// Add Row to Table
genericDataTable.Rows.Add(dataRow);
}
return genericDataTable;
}
/// <summary>
/// Convert DataTable to List<T>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dataTable"></param>
/// <returns></returns>
public static List<T> ToList<T>(DataTable dataTable) where T : new()
{
// Final result List (Converted from DataTable)
var convertedList = new List<T>();
// Traverse through Rows in the DataTable
foreach (DataRow row in dataTable.Rows)
{
// Type T of generic list object
var dataObject = new T();
// Traverse through Columns in the DataTable
foreach (DataColumn column in dataTable.Columns)
{
// Fetch column name
string fieldName = column.ColumnName;
// Fetch type PropertyInfo using reflection
var propertyInfo = dataObject.GetType()
.GetProperty(fieldName,
BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
// For Null PropertyInfo, check whether ViewrColumn attribute is applied
propertyInfo = propertyInfo ?? Parameter.GetColumnAttribute(dataObject.GetType(), fieldName);
// Set the value for not null property Info
// Continue the loop for a null PropertyInfo (needs correction either in type description or DataTable selection)
if (propertyInfo == null) continue;
// Property value
var value = row[column];
// New - Work for Nullable Types
propertyInfo.SetValue(dataObject,
DataConversionMap.Map[propertyInfo.PropertyType](value), null);
}
// Add type object to the List
convertedList.Add(dataObject);
}
return (convertedList);
}
}
Author by
Mrinal Kamboj
I am Chief Product Architect with Impact Analytics. We develop AI based solutions for the retail industry, to help them optimize their promotions, buy allocation and anything that technology can help predict. Our solutions are B2B, which use React as Front-end, NodeJS and Python as back-end, Postgresql and Big Query for Back-end. Simulation and Optimization solutions are in Python and R.
Updated on June 08, 2022Comments
-
Mrinal Kamboj almost 2 years
I was trying to create a generic method, which can read the parameters name and value from a class at Runtime and create parameter collection for Dapper query execution. Realized that till the point all parameters are Input type it works well, but if I have to add an
Output / ReturnValuetype parameters, then I need to work withDynamicParameters, else I cannot fetch the value ofOutput / ReturnValueparametersSP has following parameters:
PersonList - TableValued - Input TestOutput - Int - OutputI am not able to make following piece of code work:
var dynamicParameters = new DynamicParameters(); dynamicParameters.Add("PersonList", <DataTable PersonList>); dynamicParameters.Add("TestOutput", 0, Dbtype.Int32, ParameterDirection.Output);Exception is:
System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@PersonList"): Data type 0x62 (sql_variant) has an invalid type for type- specific metadata.
Issue as I can understand is there's no valid DbType available for adding a TVP to the Dynamic Parameters, since I am not using the SqlDbType, so there's no replacement for SqlDbType.Structured in the DbType.
Any pointer or workaround to resolve the issue
-
Iain about 5 yearsJust what I needed, for clarity for the next person PersonList is of Type DataTable.
-
Kenneth Carter almost 3 yearsAsTableValuedParameter does not exist in dapper v2.0.90
