Calling sproc with "ExecuteScalar" (etc.) throws exception.

Topics: Data Access Application Block
Aug 24, 2010 at 12:49 AM

EntLib is throwing an exception under the following circumstance:

  • I have EntLib 5.0 installed and am developing with VS 2010
  • I have a simple sproc (SQL Server 2008) which takes a couple strings and has a third parameter (int) which is optional
  • I am calling ExecuteScalar like this — object obj = db.ExecuteScalar("AddPerson", firstName, lastName);
  • I was warned that using this call (and others that are similar, like ExecuteSprocAccessor), would throw an exception when calling a sproc which had an optional parameter
  • The exception message is "The number of parameters does not match number of values for stored procedure."

Is there a way to use these calls on sprocs that have an optional parameter? I've heard that a solution to this is to declare an array of Params myself and pass that in, but these methods don't have an overload (that I saw) which accepts an "array of Param" parameter. I want to be able to add an optional parameter to a sproc if I want to without editing code, and I just love the ease with which I can call a sproc with EntLib 5.0, but this seems like a major problem.

Thanks,
Jay 

Aug 24, 2010 at 2:41 AM

Hi Jay,

Please see this blog if it may help. In this blog StanleyGu has a good example on how to deal with optional parameter in a stored procedure.  I believe he also used entlib DAAB here. HTH

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Aug 24, 2010 at 2:54 AM

That blog did not help at all. Thanks anyway.

Jay

Aug 24, 2010 at 3:09 AM

Hmm I'm just wondering if this doesn't work for you or is this is just not exactly what you need? Could you tell us if there is still something we can do to help you regarding your problem. 

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Aug 24, 2010 at 3:17 AM

That blog wasn't what I need. I know how to add optional parameters to a sproc. I need to know if it's possible to call a sproc that has optional parameters from EntLib. It doesn't seem like it, but there may be some trick I don't know.

Aug 24, 2010 at 3:51 AM

Actually in that blog he did used entlib DAAB there (DatabaseFactory.CreateDatabase method has been utilize). And to answer if calling a sproc with optional parameters using DAAB is possible, yes it is possible (see documentation ).

I probably missing something here, so please let me know if there is any :-)

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

 

Aug 24, 2010 at 4:31 AM

Okay. This is a step in the right direction. In fact, it will be adequate. Here's the sproc:

create proc AddPerson( @FirstName varchar(10), @LastName varchar(10) ,@Age int = 21)
as begin

insert into Person (FirstName, LastName)
values (@FirstName, @LastName)

select scope_identity()
end

 

 

Very simple. Calling it like this works:

SqlDatabase db = new SqlDatabase(Common.GetConnectionString());
DbCommand cmd = db.GetStoredProcCommand("AddPerson");
db.AddInParameter(cmd, "FirstName", DbType.String, firstName); // a variable
db.AddInParameter(cmd, "LastName", DbType.String, lastName); // a variable
object obj = db.ExecuteScalar(cmd);

Why, however, does calling it like this:

SqlDatabase db = new SqlDatabase(Common.GetConnectionString());
object obj = db.ExecuteScalar("AddPerson", firstName, lastName);

...throw the following exception:

"The number of parameters does not match number of values for stored procedure."

The second way is much cleaner and I prefer it. Why doesn't it work?

Thanks,
Jay 

 

Aug 24, 2010 at 6:23 AM

Thanks for clarifying :-) Unfortunately, the approach you want I believe is somehow related to this issue logged in the Issue Tracker (vote for it).

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Aug 24, 2010 at 7:26 AM

That is not my issue. I do not need to preface the sproc name with dbo or anything else. If the sproc does not contain the optional parameter, the code works fine. When I add the optional parameter, it breaks with the error I already shared with you.

Jay

Aug 24, 2010 at 8:39 AM

I do understand your problem. I only referenced to you the issue logged in case you may want to add the scenario you have encountered with the same error message mentioned in the issue. You are correct that it breaks in the straight forward approach (db.ExecuteScalar("AddPerson", firstName, lastName)), this happens because daab checks if the added parameter value count is equal to the parameter expected in the sproc (regardless if optional or not) which doesn't happen when used the other approach. I agree with you that this can be consider as a bug so what I can suggest for now is to logged this in the issue tracker.

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Aug 26, 2010 at 4:59 AM

From the research I've done, there is a way to tell if a parameter is optional using SMO, but that's more code and more calls and would almost certainly hurt performance. So, for now, I won't pursue that. Another alternative would be to give optional parameters special names like "@AgeOptional = 15" and just parse each param name to determine if it's optional. However, EntLib will still choke on this. One way that will work is to comment out the following lines:

public virtual void AssignParameters(DbCommand command, object[] parameterValues)
{
parameterCache.SetParameters(command, this); 

       //if (SameNumberOfParametersAndValues(command, parameterValues) == false)
       //{
       //   throw new InvalidOperationException(Resources.ExceptionMessageParameterMatchFailure);
       //} 

       AssignParameterValues(command, parameterValues);
}

Arguably, this is not a perfect solution, but if you have good tests, it shouldn't be a problem. So, that's a feature request for the next version of .NET...tell me if a SqlParameter is optional.

Jay

Aug 27, 2010 at 4:22 AM

You can log feature request in the Issue Tracker.

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Aug 27, 2010 at 4:28 AM

Except it's a .NET library feature, not an EntLib feature.