DAAB: Parameter Discovery fails for parameters with default values

May 7, 2007 at 6:18 PM
Consider a stored procedure like this -
CREATE PROC dbo.CheckLogin(
      @pUsername      varchar(50)
    , @pPassword      varchar(50)
    , @pBypass   bit = 0
When I try to creat a DbCommand with Parameter Discovery -
myDb.GetStoredProcCommand( "CheckLogin", username, password); 
I am getting and error which says -
"The number of parameters does not match number of values for stored procedure".

It works fine with -
myDb.GetStoredProcCommand( "CheckLogin", username, password, 0);
Do I always have to pass in values for parameters with default values?
May 8, 2007 at 2:10 AM

The DAAB uses SqlCommandBuilder.DeriveParameters to get all the parameters associated with a stored procedure. It then assigns the values you provide to the input parameters.

It would have to include all parameters. If other parameters were also optional, it wouldn't be able to know which parameter values you were providing.




David Hayden
Microsoft MVP C#
May 8, 2007 at 4:49 PM
Makes sense. Thanks Dave!
Oct 22, 2007 at 1:24 PM
Edited Oct 22, 2007 at 1:27 PM
Is there a sane workaround around this?

I have a preexisting database with lots of sp's. Some of them have 1 or 2 "real" parameters, and about 10 to 20 optional parameters, that just filter the result set in one way or the other. If i have to provide them all every time I use them, it would basically offset any advantages that DAAB gives me.
Closest I've got to a solution is to add the parameters myself and execute the procedure via a DbCommand, which is a far from elegant solution.

Ideally it would be a version of SqlCommandBuilder.DeriveParameters that is aware about optionality of the parameters, so would not throw an exception if not enough parameters are suplied.
Apr 14, 2010 at 3:55 PM

This is unacceptable.  This means that enterprise library (I am using 4.1 now) is unable to take advantage of procedures with default parameters even though it was able to do so in the past!  The library needs to be changed so that it supports this feature.

Apr 15, 2010 at 12:45 AM

Well, I wouldn't say it's not supported because you can still execute commands even though you don't supply optional parameters.  But if you really want the behavior you want for the GetStoredProcCommand method to be included in the library, you can create a work item in the Issue Tracker and get people to vote.  For the meantime, another workaround would be to create an extension method for this. 


Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.