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

Question

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

Goldofpleasure

Answer 1

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;

Obscurant

+1. I personally don't use AddWithValue, preferring to explicitly define the datatypes (and sizes) - otherwise you may end up with incorrect assumptions being made (such as .NET string values being passed in as NVARCHAR) which I get paranoid about – Zorn

Answer 2

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/

Pantomimist