Using DbParameter instead of SqlParameter with _context.Database.SqlQuery for stored proc execution

Question

I am using Entity Framework 6.1 and GenericRepository. To execute stored procedure I am using SqlQuery method. Currently I am using SqlParameters but being a GenericRepository I don't want to use some specific class. I tried multiple options but how can I use DbParameter here. I tried to access providerName (System.Data.SqlClient) from dbcontext but that is also not available. Any direction would help. Please let me know incase you want me to provide more informaiton.

    public IEnumerable<T> GetAllFromStoredProcedure(string storedProcedureName, List<StoredProcedureParameter> parameters)
    {
        List<SqlParameter> sqlParameters = new List<SqlParameter>();

        foreach (StoredProcedureParameter parameter in parameters)
        {
            sqlParameters.Add(new SqlParameter(parameter.Name, parameter.Type) { Value = parameter.Value});
        }

        return _context.Database.SqlQuery<T>(storedProcedureName, sqlParameters.ToArray());
    }

calling as -

    clientRepository.GetAllFromStoredProcedure("GetClients", parameters).ToList();

score 0 · Accepted Answer · answered Mar 21 '17 at 12:07

What I did for this -

In my Domain project (that has the Context and Entities created using Code First From Database), I added a class StoredProcedureParameter -

public class StoredProcedureParameter
{
    public string Name { get; set; }
    public DbType Type { get; set; }
    public object Value { get; set; }

    public static object[] CreateParameters(List<StoredProcedureParameter> parameters)
    {
        List<SqlParameter> sqlParameters = new List<SqlParameter>();

        foreach (StoredProcedureParameter parameter in parameters)
        {
            sqlParameters.Add(new SqlParameter(parameter.Name, parameter.Type) { Value = parameter.Value });
        }

        return sqlParameters.ToArray();
    }
}

And then in my Repository project (that has the GenericRepository and UnitOfWork), I added a new method -

    public IEnumerable<T> GetAllFromStoredProcedure(string storedProcedureName, List<StoredProcedureParameter> parameters)
    {
        return _context.Database.SqlQuery<T>(storedProcedureName, StoredProcedureParameter.CreateParameters(parameters));
    }

Now when calling Repository->GetAllFromStoredProcedure method (from my Application project) I will create the parameter list from Domain->StoredProcedureParameter->CreateParameters method. This ensures the parameters passed are of the correct type, my GenericRepository remains generic and my Application project is also unaffected.

Using DbParameter instead of SqlParameter with _context.Database.SqlQuery for stored proc execution

1 Answers1