Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Dapper Dynamic Parameters with Table Valued Parameters

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

like image 692
Mrinal Kamboj Avatar asked Oct 12 '15 18:10

Mrinal Kamboj


3 Answers

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);
like image 129
Rohit Shetty Avatar answered Oct 12 '22 18:10

Rohit Shetty


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);
        }
    }
like image 2
Mrinal Kamboj Avatar answered Oct 12 '22 18:10

Mrinal Kamboj


Inside the add function of parameter object pass argument DbType.Object

eg: DynamicParameters parameterObject = new DynamicParameters(); parameterObject.Add("@table", dtTable, DbType.Object);

like image 1
Kasun Avatar answered Oct 12 '22 19:10

Kasun