SqlCommand.Parameters.AddWithValue issue: Procedure or function X expects parameter @Y, which was not supplied

I have a problem with the folowwing piece of code. I am passing a parameter (List<SqlParameter>) to a method executing the following code.

When it executes SQL Server throws an err开发者_运维知识库or saying that the proc expects a parameter that was not provided. I know this error and understand it, and when stepping through the code I can see that the cmdExecuteReader object has a collection of parameters with the correct name and value. What could be the problem?

     public SqlDataReader ExecuteReader(string storedProcedure, List<SqlParameter> parameters = null)
        {
                    SqlCommand cmdExecuteReader = new SqlCommand()
                    {
                        CommandType = System.Data.CommandType.Text,
                        Connection = conn,
                        CommandText = storedProcedure
                    };

                    if (parameters != null)
                    {
                        foreach (SqlParameter param in parameters)
                        {
                            cmdExecuteReader.Parameters.AddWithValue(param.ParameterName, param.Value);
                        }
                    }

                    if (conn.State == System.Data.ConnectionState.Closed)
                        conn.Open();
                    return cmdExecuteReader.ExecuteReader();
       }

Is the .Value set to null for any of the parameters? If so, they aren't sent. Try:

cmdExecuteReader.Parameters.AddWithValue(param.ParameterName,
        param.Value ?? DBNull.Value);

(note the null-coalescing with DBNull.Value)

Also, note that AddWithValue may impact your query-plan re-use, as (for strings etc) it uses the length of the value. If you need maximum performance it is better to setup the parameter manually with the defined sizes.

Also note that potentially some of the parameters in the incoming list could be input-output, output or result. I would be very tempted to substitute for something more like:

SqlParameter newParam = cmdExecuteReader.Parameters.Add(
      param.ParameterName, param.SqlDbType, param.Size);
newParam.Value = param.Value ?? DBNull.Value;
newParam.Direction = param.Direction;

I did the stuff that you are trying to do, here some examples:

public int ChangeState(int id, int stateId)
{
    return DbUtil.ExecuteNonQuerySp("changeDossierState", Cs, new { id, stateId });
}

public IEnumerable<Dossier> GetBy(int measuresetId, int measureId, DateTime month)
{
    return DbUtil.ExecuteReaderSp<Dossier>("getDossiers", Cs, new { measuresetId, measureId, month });
}

I recommend you to look here

and to download the samples solution (where there is a DAL Sample project included) http://valueinjecter.codeplex.com/