DataAccess Application Block

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to return multiple output parameters form a stored proc using
the Microsoft DataAccess Application Block? I need 2 values back from a
table and I do not want to bring back a dataset and I want to do it in one
trip to the database.

Thanks,
 
Yes absolutely I am assuming you are using V2.0 and not Enterprise
Library. You know how it accepts an array of SqlParameter objects,
just add the output parameters to that array, insure you specify the
ParameterDirection as output. make sure the name, datatype, precision
and all that are set properly. I think you would want to use the
ExecuteNonQuery method, pass the correct methods to it. then read out
those parameter values from the array after you executed the
appropriate sqlhelper method.

For using the Data Access Application Block check out my article on
CoDe Magazines web site
http://www.code-magazine.com/Article.aspx?quickid=0411061
 
One of the overloads accepts a param array of SqlParameter objects. Send in
multiple output parameters and you are golden.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Thank you very much. One last question. I created the param array and
tested the stored proc in query analyzer. The stored proc works as desired,
returning the 2 output parms I need. Here is my call to SqlHelper. How do I
get the return values out of the oRes object?

object oRes = new object;
oRes = SqlHelper.ExecuteNonQuery(cn, CommandType.StoredProcedure,
"verifyCPIandBatchId_sp",
oParms);

Robert
 
Once you set up the params and pass them into the block, you can pull the
values back off. Your setup should be something like this:

//The params are set above. This makes the param array
SqlParameter[] oParms = { param1, param2, param3);

oRes = SqlHelper.ExecuteNonQuery(cn, CommandType.StoredProcedure,
"verifyCPIandBatchId_sp", oParms);

//Assuming 2 and 3 are output
int MyOutputVal1 = param2.Value;
int MyOutputVal2 = param3.Value;

--
Gregory A. Beamer
MVP; MCP: +I, SD, SE, DBA

*************************************************
Think outside the box!
*************************************************
 
Mystery solved. The piece of the ouzzle missing for me was getting the
return values from the output parameters.

Thanks...

Cowboy (Gregory A. Beamer) said:
Once you set up the params and pass them into the block, you can pull the
values back off. Your setup should be something like this:

//The params are set above. This makes the param array
SqlParameter[] oParms = { param1, param2, param3);

oRes = SqlHelper.ExecuteNonQuery(cn, CommandType.StoredProcedure,
"verifyCPIandBatchId_sp", oParms);

//Assuming 2 and 3 are output
int MyOutputVal1 = param2.Value;
int MyOutputVal2 = param3.Value;

--
Gregory A. Beamer
MVP; MCP: +I, SD, SE, DBA

*************************************************
Think outside the box!
*************************************************
Robert said:
Thank you very much. One last question. I created the param array and
tested the stored proc in query analyzer. The stored proc works as desired,
returning the 2 output parms I need. Here is my call to SqlHelper. How do I
get the return values out of the oRes object?

object oRes = new object;
oRes = SqlHelper.ExecuteNonQuery(cn, CommandType.StoredProcedure,
"verifyCPIandBatchId_sp",
oParms);

Robert
Click to expand...
Click to expand...
 
Back
Top