Data Access Application Block (DAAB) has very nice features. Parameter discovery and caching is one of them. What it does is when the the client code creates a DbCommand object is constructed from the name of the stored procedure and the list of parameters as an array of objects, DAAB code goes to the database, discovers the stored proc parameters (types, names, direction etc.) and caches them in the simple hash-table which is defined in the base Database class. If the stored procedure parameters information has been previously cached in DAAB, then the list of parameters is retrieved from the cache and the actual values provided in array of the objects are assigned and the DbCommand is returned to the client code for further using (executing the dataset, scalar, NonQuery, XML query etc.).

The parameter cache is indexed by the name of the stored procedure and the connection string. DAAB checks the the number of parameter values provided by the client code and the actual number of parameters cached in the object of the Database class (actually one of its descendants since Database class is an abstract class) and throws an exception if they do not match.

Everything works fine if the client code executes the methods without assuming that the stored procedure declaration can be changed. If it happens during the run-time, the next time you try execute it, DAAB will throw the exception even the client code provides the correct number of parameter values in the array of objects. This is a valid case if the DAAB is used in the middle-tier and passes the calls from the data access to the database. At any moment of time the definition of the stored procedure is changed, the client code (Data Access or Business Objects assembly methods) are updated properly to match the stored procedure, but you may still find yourself in situation when the DAAB parameter cache is out of sync and must be refreshed. 

To do so, the client code must catch the exception, analyse it and clear the cache by calling the <Database>.ClearParameterCache method which throw away ALL cached entries. This may not be an ideal solution and you would like to encapsulate the cache cleanup functionality inside the DAAB and make it more precise to clear the cache specifically for tbe failed set of cached parameters. Unfortunately, the only way to make it centralized is to customize the Database class of DAAB and make this feature available to all descendant classes.

Here are the steps to customize the DAAB code

1) ParameterCache Class: Add overloaded method to clean the parameter cache for the given DbCommand Database Class

2) Database class:

2.1) Modify/add new methods that are performing the actual work against the database:  

  • DoCustomLoadDataSet(DbCommand command
  •  DoCustomExecuteNonQuery(DbCommand command
  • DoCustomExecuteScalar (DbCommand command
  • DoCustomExecuteReader(DbCommand command)

Modification details

The following pseudo-code snippet presents the modifications to be implemented. The modification lines are:

try
{
 Execute original method
}
catch (DbException ex)
{
 if (ex is related to command parameters)
       Clean parameter cache for the command
        Prepare the command
  Execute original method
 else
        throw; 
}

2.2.) Add method bool IsParameterRelated(DbException exception): This method will determine if the exception is related to the parameters.

The descendant classes will override the IsParameterRelated method to analyze the exception. So for SQL Server there are two appoaches:

3)  Detecting the parameter related error by analyzing the state of objects involved in the operations.

  • Analyze DbCommand object:  The object must be of StoredProcedure type
  • Analyze DbCommand.Parameters collection: The collection must contain at least one input, output or input/output parameter
  • Analyze DbCommand.Connection object: The object must be initialized and the connection string must contain value. The connection state must be open. The closed connection state indicates the severe error on the SQL Server side. If the error is of severity greater than 20, the connection will be closed by SQL Server.
  • Analyze if the DbCommand parameters were cached previously: If the parameters for the DbCommand were cached previously, then the most recent execution of the command was successful. The failure of new command execution may be related to the changes in the parameter definition.

Alternatively, you may want to check the Number property of SqlException object and detect if is related to stored procedure definitions. This approach is not practical as you coupling your solution with particular version of SQL Server engine.

The interesting thing is that there are certain cases that do not throw any exception unless the application that uses DAAB is restarted. For example, changing the types of the stored procedure parameter may be leveraged by SqlDataAdapter as long as there is no range violation between the value and the actual parameter definition. What it means is that the DAAB parameter cache will be OK as long as the underlying ADO.NET Data Adapter can bind the parameters.

Here is some sample code related to this issue:

private object DoCustomExecuteScalar(DbCommand command)
        {
            try
            {
                return DoExecuteScalar(command);
            }
            catch (DbException e)
            {
                if (IsParameterRelatedException(command, e))
                {
                    using (DbCommand newCommand = GetReinitializedCommand(command))
                    {
                        return DoExecuteScalar(newCommand);
                    }
                }
                else
                    throw;
            }
        }

protected DbCommand GetReinitializedCommand(DbCommand oldCommand)
       {
            if (oldCommand.CommandType != CommandType.StoredProcedure)
                throw new ArgumentException(Resources.ExceptionMessageParameterMatchFailure, "oldCommand");

            parameterCache.Clear(oldCommand, this);
           
            // Copy old parameters value
            int parameterIndexShift = UserParametersStartIndex();
            object[] parameterValues = new object[oldCommand.Parameters.Count - parameterIndexShift];
            for (int i = parameterIndexShift; i < oldCommand.Parameters.Count - 1; i++)
                parameterValues[i] = oldCommand.Parameters[i].Value;

            DbCommand newCommand = GetStoredProcCommand(oldCommand.CommandText, parameterValues);
            PrepareCommand(newCommand, oldCommand.Connection);
            return newCommand;
        }

protected virtual bool IsParameterRelatedException(DbCommand command, DbException exception)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (exception == null) throw new ArgumentNullException("exception");

            bool result =
                CheckCommandType(command)
                && CheckCommandConnecton(command)
                && CheckCommandParameters(command);
               
            return result;
        }