Menu

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

15 October 2004

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:

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.