Longing to know

If you recall my previous previous post Stored Procs with Entity Framework, I mentioned that you could use SqlQuery<T> to populate a list from a stored procedure.

However, I found that optional parameters must be handled properly.

As an example, I had a code snippet similar to this:

var retSet = myContext.Database.SqlQuery<ReportItem>(
    "EXEC spReport @param1, @param2, @param3, @param4, @param5, @param6",
    new SqlParameter("param1", param1 ?? DbNull.Value),
    new SqlParameter("param2", param2 ?? DbNull.Value), 
    new SqlParameter("param3", param3 ?? DbNull.Value),
    new SqlParameter("param4", param4 ?? DbNull.Value),
    new SqlParameter("param5", param5 ?? DbNull.Value),
    new SqlParameter("param6", param6 ?? DbNull.Value)
);

With that bit of code, what if we removed the first line that specified “param1?” Our stored procedure would receive “param2” first and assume that it’s the first parameter to the sproc since there is no naming specified. This makes the solution far too rigid in my mind. It also means that order of parameters now matters.

Ideally, we’d want to pass in an arbitrary list of parameters to a repository and have it deal with formatting the parameters as necessary for the sproc.

Consider a stored procedure with (6) parameters:

CREATE PROCEDURE  [dbo].[spReport]
				 @param1 INT = 1
				,@param2 VARCHAR(2) = NULL 
				,@param3 VARCHAR(6) = NULL 
				,@param4 VARCHAR(7) = NULL
				,@param5 DATE = NULL 
				,@param6 INT = NULL 
AS

We want to be able to call it, generally, and only specify any set of arbitrary parameters. Let’s say we’ll pass in 1, 2 and 4. I imagine it would look something like this:

var reportParams = new ExpandoObject().Init(new
{
    param1 = 1,
    param2 = "stuff2",
    param4 = "stuff4"
});

var reportItems = (List<ReportItems>)_repo.GetFromSproc<ReportItems>(reportParams2, "spReport");

This actually becomes a relatively straight-foward exercise in formatting the command text appropriately. It would look like this:

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();
}

Our resulting command text takes the dynamic keys and treats them as the named parameters to the sproc. Then, our SqlParameters are given new names which are simply the original names with the index (based on order) appended.

The resulting query string will look like this:

EXEC [dbo].[spReport] @param1 = @param10, @param2 = @param21, @param4 = @param42

Upon execution, our SqlParameter values are matched against their ParameterNames and our stored procedure will take its own default values for parameters not passed in (named).

This fits my use case and goal of not enforcing order for parameters and/or requiring optional parameters to be passed in. This is ideal since no one would want to pass in all (6) parameters when all they want to specify is a single value.

On a side note, these are the extension method that I use for instantiating a dynamic in-line and the method for converting a dynamic to a List<SqlParameter>

public static class ExpandoExtensions
{
    public static ExpandoObject Init(this ExpandoObject expando, dynamic obj)
    {
        var dict = (IDictionary<string, object>)expando;
        foreach (PropertyInfo propInfo in obj.GetType().GetProperties())
        {
            dict[propInfo.Name] = propInfo.GetValue(obj, null);
        }
        return expando;
    }
}

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

    return sqlParams;
}