Determining Parameters on a Stored Procedure at Runtime with ADO.NET

I thought I’d blog this just in case anyone else was in the same situation. I tried Googling for it, but didn’t know what to search for in order to return a good result. Luckily my friend Paul Qualls was online and happened to have the answer.

In one of the application’s I write, the parameters of the stored-procedures are not known until runtime. At first I made the simple mistake of just sending all the possible arguments to the stored procedure, but, of course, this resulted in an exception under most circumstances: “Procedure or function … has too many arguments specified.” So what is the solution? The easy solution, as Paul pointed out, is to use the DeriveParameters static method of the pertinent CommandBuilder class, such as SqlCommandBuilder or OleDbCommandBuilder. You can then reconcile the potential parameters with the actual parameters by copying them over. Here’s an example:

*// Note: We are assuming a CommandType of StoredProcedure
*command.Connection.Open();
try{
  parameterFactory.DeriveParameters(result);
  // Note: You should probably cache the results here so that the database is not queried every time
}finally{
  command.Connection.Close();
}
foreach(IDbDataParameter parameter in parameters){ *// parameters is an argument to this method
*  *// Get the pre-existing parameter
*  IDbDataParameter commandArg = (command.Parameters.Contains(parameter.ParameterName)) ? IDbDataParameter)command.Parameters[parameter.ParameterName] : null;
  if(commandArg == null) continue; *// The parameter is not required
*  commandArg.Direction = parameter.Direction;|
  if(parameter.SourceColumn != null){
    commandArg.SourceColumn  = parameter.SourceColumn;
    commandArg.SourceVersion = parameter.SourceVersion;
  }else{
    commandArg.Value = parameter.Value;
    commandArg.Size  = parameter.Size;
  }
}

Of course, another thing that is determined in my application at run-time is the database implementation. For the most part, data access in .NET is easy to keep non vendor-specific through the use of interfaces (IDbConnection, IDbCommand, and so on). Of course, because the DeriveParameters method is static (and unfortunately C#/.NET does not have virtual class methods or even class references — perhaps in Chrome), you cannot simply provide a new implementation by overriding the old one, or using a class-reference to call an abstract/overridden method. Instead, you have to create your own interface — I called mine IDeriveParameters — plus the implementations you need, and then use a factory pattern to construct the proper implementation. You can see this in the code above where parameterFactory.DeriveParameters is called.