Dapper for Table Valued parameter in generic implementation

Question

Following the post Using Dapper.TVP TableValueParameter with other parameters I have been able to execute table valued parameter but my question is regarding part of its implementation. Procedure seems to be working but how can i make this implementation more generic? Adding parameters in DynamicParameters with 'new {}' is very specialized implementation according requirement. I want to be able to specify my parameter name as well.. for example

public IEnumerable<TCustomEntity> SqlQuery<TCustomEntity>(string query, IDictionary<string, object> parameters,
            CommandType commandType) where TCustomEntity : class
        {
            DataTable dt;
            DynamicParameters dp;
            var sqlConnection = _context.Database.Connection;
            try
            {

                if (sqlConnection.State == ConnectionState.Closed)
                {
                    sqlConnection.Open();
                }


                var dynamicParameters = new DynamicParameters();
                if (parameters != null)
                    foreach (var parameter in parameters)
                    {

                        if (parameter.Value is int)
                            dynamicParameters.Add(parameter.Key, parameter.Value, DbType.Int32);
                        else if (parameter.Value is string)
                            dynamicParameters.Add(parameter.Key, parameter.Value.ToString(), DbType.String);
                        else if (parameter.Value is DateTime)
                            dynamicParameters.Add(parameter.Key, parameter.Value, DbType.DateTime);
                        else if (parameter.Value is Decimal)
                            dynamicParameters.Add(parameter.Key, parameter.Value, DbType.Decimal);
                         else if (parameter.Value is DataTable)
                         {
                             dt = (DataTable)parameter.Value;
                             dt.SetTypeName(dt.TableName);
                             var parameterName = parameter.Key;

                             dp = new DynamicParameters(new { TVPName = dt });
                             // Here i want TVPName replaced with parameterName/parameter.Key but it doesn't seem possible
                             dynamicParameters.AddDynamicParams(dp);



                         }
                        else
                         dynamicParameters.Add(parameter.Key,  parameter.Value);
                    }
                var test = sqlConnection.Query<TCustomEntity>(query, dynamicParameters, commandType: commandType);
                return test;
            }
            finally
            {
                sqlConnection.Close();
            }
        }

Any advice how may i proceed on the issue by making parameter name more generic? If not, it will be specialized implementation each time i use Table value parameter

Why do you need to use `AddDynamicParams` here? Does `.Add(parameter.Key, dt)` not work?' — Marc Gravell, Apr 09 '15 at 10:30

Marc Gravell · Accepted Answer · 2015-04-09T10:45:53.737

It isn't obvious to me that any of that is necessary, since:

DynamicParameters is perfectly happy with TVPs as direct elements,
all of the data types shown would be handled automatically
dapper works happily with dictionaries
dapper already opens and closed the connection appropriately if it isn't open when invoked

It seems to me that the only interesting step here is the SetTypeName, which could be done:

foreach(object val in parameters.Values)
{
    if(val is DataTable) {
        var dt = (DataTable)val;
        dt.SetTypeName(dt.TableName);
    }
}

And then pass your original parameters object in:

return sqlConnection.Query<TCustomEntity>(query, parameters, commandType: commandType);

That leaves just:

public IEnumerable<TCustomEntity> SqlQuery<TCustomEntity>(string query,
    IDictionary<string, object> parameters, // suggestion: default to null
    CommandType commandType // suggestion: default to CommandType.Text
) where TCustomEntity : class
{
    var sqlConnection = _context.Database.Connection;

    if (parameters != null) {
        foreach (object val in parameters.Values) {
            if (val is DataTable) {
                var dt = (DataTable)val;
                // suggestion: might want to only do if dt.GetTypeName() is null/""
                dt.SetTypeName(dt.TableName);
            }
        }
    }

    return sqlConnection.Query<TCustomEntity>(query, parameters,
        commandType: commandType);
}

Thanx @Marc, you were spot on. Actually I didn't update my dapper library for year or so and was kinda stranded on TVPs. I did updated it, and its working the magic. — Abdul Rehaman, Apr 09 '15 at 11:55

Dapper for Table Valued parameter in generic implementation

1 Answers1