Getting output parameter(s) after passing input parameters on an array in ExecuteNonQuery

Topics: Data Access Application Block
Aug 8, 2012 at 11:07 PM
Edited Aug 8, 2012 at 11:13 PM

Hello to everyone on EntLib discussions.

I am trying to abbreviate the way I use the ExecuteNonQuery command on EntLib 5.0, for this when I am working with stored procedures I have created a function that returns the parameters as an array of Objects, these are obtained after passing a BusinessObject that has the parameters values in all/some of its properties.

Once the array is createad I pass it to the ExecuteNonQuery method of the Command in this way:

Dim dbCheckItems As Database = dbContainer.Resolve(Of Database)()
Dim obl() As Object = ReturnParsObject(objMyObject)
dbCheckItems.ExecuteNonQuery("StoredProcedureName", obl)

This works fine with input parameters, but when I have an output parameter of Int Type and I define it previously:

dbCheckItems.AddOutParameter(dbCheckItems.GetStoredProcCommand("StoredProcedureName"), "OutputParameterName", DbType.Int16, Int16.MaxValue)

I get the following error after executing the command:
"The number of parameters does not match number of values for stored procedure."

So I added one Object that has a 0/null value at the end of the array and the command is executed fine but I get an error when trying to discover the value of the output parameter on this way:

dbCheckItems.GetParameterValue(dbCheckItems.GetStoredProcCommand("StoredProcedureName"), "OutputParameterName")

The error says that the "OutputParameterName" can not be found and the weird thing is that when I retrieve the number of parameters in the command after execution I get 0 of them.

Is there a way to work around this so I can still add my parameters dynamically?.

Thank you very much for your help.

Aug 9, 2012 at 4:55 AM

Thanks for the question -- it's an interesting one.  You are on the right track in trying to get a command object to retrieve the output parameter -- I think you are going to have to use a DbCommand object.  The issue you are hitting is that GetStoredProcCommand creates a new DbCommand instance but we need access to the instance used to execute the query.  Unfortunately, a DbCommand instance is created and used internally by the Database class when invoking ExecuteNonQuery("storedProcName", paramarray) but that instance is not available to the caller.  

So you will need to create your own DbCommand, populate the parameters and pass that in to the Database object.  Also note that you will need to supply all parameters whether they are input or output.  Here is a code snippet that does what you want:

Dim db = EnterpriseLibraryContainer.Current.GetInstance(Of Database)()

Dim inParameters As Object() = ReturnParsObject()
Dim outParameters As Object() = New Object() {0}

Dim parameters As New List(Of Object)()

Dim cmd = db.GetStoredProcCommand("GetMaxId")
db.AssignParameters(cmd, parameters.ToArray())
db.ExecuteNonQuery(cmd) ' Last parameter (since first param is return_value) Dim dbParam = cmd.Parameters((inParameters.Length + outParameters.Length)) ' Or we can use the name of the parameter derived from the stored procedure definition Dim dbParamByName = cmd.Parameters("@ID")

Randy Levy
Enterprise Library support engineer 

Aug 9, 2012 at 5:03 AM
Edited Aug 9, 2012 at 7:21 AM

Hi Randy.

Thanks for your effort to make this library better and better, I will try what you suggest later and let you know how it goes.

EDIT: I just tried and it worked smooth as you predicted, I actually saw the method AssignParameters before but I didn't use it because I thought it was the same one called in while assigning the parameters to the command right before executing it in:

'I thought: cmd AssignParameters +ExecuteNonQuery on itself
dbCheckItems.ExecuteNonQuery("StoredProcedureName", obl)

'To retrieve I thought: Retrieve such cmd with its name as you did and get what I wanted (in my case the number of 'parameters) from the collection then

Is interesting because sounds really similar to your solution but is actually not behind the scenes, would be interesting if you guys could implement something easier to figure out for the dynamic ussage I detailed here.

Thanks again and have a nice day!