SqlCommand.Parameters.AddWithValue issue

Question

Hi

I have a problem with the folowwing piece of code. I am passing a parameter (List) to a methods executing the following code. When it executes sql 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 naame and value. What cou 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();
       }

Answer 1

It the .Value set to null for any? 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;

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/