Longing to know

Over the weekend, I needed to be able to execute a stored procedure from EntityFramework that had a table value parameter (TVP) as an input.

I had never worked with TVP’s before. Utilizing them was not obvious to me from either a code-based approach or even directly with T-SQL. Here’s how I accomplished it, though.

The non-obvious things for me that TVP’s have to be created as custom types in MS SQL and the .NET type is a “structured” input. The input can be a DataTable or SqlDataRecord. As such, the custom type has to be passed along with the TVP input so that it is declared properly.

If you’ll recall one of my previous posts on working with stored procedures from Entity Framework, I detailed how I would pass in a .NET dynamic type for the stored procedure’s inputs. I modified this code a little to to accept a specific List input to look for inputs that have a flag that indicate that they are to be used as a TVP and have an underlying List along with the MS SQL custom type name.

Declaring the dynamic is, then, pretty straightforward. Here’s an example where I have a TVP that contains a single column of integers. The dynamic key name is still the parameter name, but you’ll notice that member is itself a dynamic (expando) that contains relevant parameters indicating custom table type, column name (within that table), and the values that will be inserted into the column specified for each row:

dynamic reportParams = new ExpandoObject().Init(new
{
    PaymentIDs = new ExpandoObject().Init(new
    {
        IsTable = true,
        TableType = "MyCustomType",
        ColumnName = "MyID",
        Values = new List<int>() { 1, 2, 3, 4 }
    })
});

var report = (List<ReportItem>)_paymentRepo.GetFromSproc<ReportItem>(reportParams, "spMyStoredProc");

With that bit of code in place, the only other thing that had to be modified is the method that would convert the dynamic input to a List<SqlParameter>:

private List<SqlParameter> DynamicToSqlParameters(dynamic parameters)
{
    var dictionary = parameters as IDictionary<string, object>;
    var sqlParams = new List<SqlParameter>();
    foreach (var param in parameters)
    {
        string name = param.Key;

        var valueDict = param.Value as IDictionary<string, object>;
        if (valueDict != null && param.Value.IsTable)
        {
            // Handle tablevalue parameters as IEnumerable inputs
            string columnName = param.Value.ColumnName;
            var parameter = new SqlParameter(name, CreateTableInput(columnName, param.Value.Values));
            parameter.TypeName = string.Format("{0}.{1}", _dbContext.DbSchema, param.Value.TableType);
            parameter.SqlDbType = SqlDbType.Structured;
            sqlParams.Add(parameter);
        }
        else
        {
            sqlParams.Add(new SqlParameter(name, param.Value));
        }
    }

    return sqlParams;
}

private static IEnumerable<SqlDataRecord> CreateTableInput<T>(string name, IEnumerable<T> values)
{
    var type = typeof(T);
    var sqlType = TypeConvertor.ToSqlDbType(type);
    var records = new List<SqlDataRecord>();
    foreach (T value in values)
    {
        var metaData = new SqlMetaData[] { new SqlMetaData(name, sqlType) };
        SqlDataRecord record = new SqlDataRecord(metaData);
        record.SetValue(0, value);
        records.Add(record);
    }

    return records.AsEnumerable<SqlDataRecord>();
}

You’ll notice this code is pretty simple. If we detect that the dynamic member is of type IDictionary<string, object>, we know that is an Expando. The assumption is then made that it has the members that we look for that define our table input. The CreateTableInput method is simply creating the metadata for each SqlDataRecord and then setting the column value based on the generic list’s values. You’ll notice that I do get the SqlDbType from the .NET type for the typeof(T). This is probably not entirely necessary, but, for posterity’s sake, I go ahead and set the DbType. To accomplish this, I borrowed code from a Gist that I stumbled upon.

I’m also fairly certain I posted the “GetFromSproc” code in a previous post, but just in case I didn’t, that code looks pretty simple. I use it within my BaseRepository so that it’s available from any repository that I’ve instantiated. It utilizes the SqlQuery method available from EF’s DbContext.

private List<V> GetSproc<V>(dynamic parameters, string sproc, string schema) where V : class
{
    var dictionary = parameters as IDictionary<string, object>;
    List<SqlParameter> sqlParams = DynamicToSqlParameters(parameters);
    var executeParams = sqlParams.Select((v, i) =>
    {
        var paramName = v.ParameterName; v.ParameterName = string.Format("{0}{1}", paramName, i); return string.Format("@{0} = @{1}", paramName, v.ParameterName);
    }).ToList();
    var executeStr = string.Format("EXEC [{0}].[{1}] {2}", schema, sproc, string.Join(", ", executeParams));
    var retSet = _dbContext.Database.SqlQuery<V>(executeStr, sqlParams.ToArray());
    return retSet.ToList();
}