Data Access Application block in C# does not detect optional parameters of SP
<p>I am using SQLHelper.cs from Microsoft Data Access Application Block for .NET. The following method discovers parameters of a SP and assigns values to them</p>

<pre><code>public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
    {
        if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
        if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) &amp;&amp; (parameterValues.Length &gt; 0)) 
        {
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

            AssignParameterValues(commandParameters, parameterValues);

            return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        }
        else 
        {
            // Otherwise we can just call the SP without params
            return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
        }
    }
</code></pre>

<p>The function which discovers parameters from SP is as follows</p>

<pre><code>private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
    {
        if( connection == null ) throw new ArgumentNullException( "connection" );
        if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

        SqlCommand cmd = new SqlCommand(spName, connection);
        cmd.CommandType = CommandType.StoredProcedure;

        connection.Open();
        SqlCommandBuilder.DeriveParameters(cmd);
        connection.Close();

        if (!includeReturnValueParameter) 
        {
            cmd.Parameters.RemoveAt(0);
        }

        SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

        cmd.Parameters.CopyTo(discoveredParameters, 0);

        // Init the parameters with a DBNull value
        foreach (SqlParameter discoveredParameter in discoveredParameters)
        {
            discoveredParameter.Value = DBNull.Value;
        }
        return discoveredParameters;
    }
</code></pre>

<p>I am maintaining an application which required adding a new parameter to a SP. So i created a overloaded method in by business layer as well as DAL with one extra parameter.
I have set parameter value to null in SP so this parameter becomes optional.</p>

<p>Problem occurs in the original function whose overload i have created. As the above functions detects four parameters instead of 3 and I am assigning value to only 3. So there is a mismatch and i get an error which says "Parameter count does not match Parameter Value".</p>

<p>Is there any built in mechanism to ignore optional parameters of SP or will i have to modify this SQLHelper.cs to only assign values to parameters that are being send with a value. Please suggest a solution.</p>

<p><strong>Edit</strong></p>

<p>I can make this work by modifying the original function whose overload I am working with by creating a new parameter and assigning it to null</p>

<pre><code>arParms[3] = new SqlParameter("@Doc_Type_Group_Code", SqlDbType.VarChar);
arParms[3].Value = null;
</code></pre>

<p>but this way my previous builds have stopped working as this code is not there. How to make it SP independent.</p>

<p><strong>Edit</strong></p>

<p>Sp lookslike</p>

<pre><code>CREATE PROCEDURE [dbo].[GB_Select_Assignor_Assignee]  
(  
  @StateCode [varchar](2),  
  @CountyID [int],  
  @Doc_Type_Group_Code [varchar](10),  
  @Doc_Type_Code [varchar](50)=null  
)  
</code></pre>

<p>AS   </p>
