Menu
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 / ReturnValue type parameters, then I need to work with DynamicParameters, else I cannot fetch the value of Output / ReturnValue parameters
SP has following parameters:
PersonList - TableValued - Input
TestOutput - Int - Output
I 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
692
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);
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);
}
}
Inside the add function of parameter object pass argument DbType.Object
eg: DynamicParameters parameterObject = new DynamicParameters(); parameterObject.Add("@table", dtTable, DbType.Object);
1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
