RETURN VALUES AND OUTPUT PARAMETERS USING THE DATA ACCESS APPLICATION BLOCK

[John]

For all those that have suffered trying to figure this out!
I hope this helps!
John

RETURN VALUES AND OUTPUT PARAMETERS USING THE DATA ACCESS APPLICATION
BLOCK
The overloaded method that takes an array of objects cannot return an
output parameter or a return value. You must pass in a parameter
array.
Therefore, we discover the parameters on our own first, if we want the
return value we set the Boolean to true.
We pass the discovered parameters into a parameter array.
The array is then passed to the ExecuteReader method
When we use the ExecuteReader method we must be sure that the reader
is closed first.

//////////////////////////////////////////////////////////////////////////
///Discover the parameters
//////////////////////////////////////////////////////////////////////////
SqlParameter[] parameters =
SqlHelperParameterCache.GetSpParameterSet(cnString,
"AutoNumberTest",true);
SqlDataReader d = SqlHelper.ExecuteReader(cnString,
CommandType.StoredProcedure, "SPName", parameters);
d.Close();
return parameters[0].Value.ToString(); //The return value is always
first
//////////////////////////////////////////////////////////////////////////
///OR Manually create the parameter array
//////////////////////////////////////////////////////////////////////////
SqlParameter returnID = new
SqlParameter("@RETURN_VALUE",SqlDbType.Int);
returnID.Direction = ParameterDirection.ReturnValue;
SqlParameter paramID = new
SqlParameter("@OutputParamName",SqlDbType.NVarChar,50);
paramID.Direction = ParameterDirection.Output;
SqlParameter[] parameters = {returnID,paramID };
SqlHelperParameterCache.CacheParameterSet(cnString, "SPName",
parameters);
SqlDataReader d = SqlHelper.ExecuteReader(cnString,
CommandType.StoredProcedure, "SPName", parameters);
d.Close();
return returnID.Value.ToString();
//////////////////////////////////////////////////////////////////////////

 

[Cowboy \(Gregory A. Beamer\) [MVP]]

I would aim towards manually setting up the params, as you know what you are
throwing in, so you are more likely to get out what you desire.

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

***************************************************************
Think outside the box!
***************************************************************