SqlDataReader capture a RETURN code

[Stedo]

hi,

I am working with the development of an application which called stored procedures on a MS SQL Server. I get data from the stored procedure using a SqlDataReader, and manage the transaction handling in the stored procedure itself. No problem there. However sometimes an error is caused during the execution of the stored procedure and I want to get the value that is returned from the SQL statement RETURN. I've looked in SqlDataReader but can't see anything that supports this. Can anyone help??

Thanks in advance
Stephen

 

[stsuing]

From Google Groups
To summarise for any other eyes, the approach taken has been to have
different return values as indicated in the SQL below. Then the C# code can
as follows. Note that the return value cannot be obtained until the
SqlDataReader.Close() has been called.


Cheers,
David


...
SqlCommand cmdDist = new SqlCommand("PersonGet", m_oConn);
cmdDist.CommandType = CommandType.StoredProcedure;
cmdDist.Parameters.Add("@IDDistributor", lIDDistributor);
cmdDist.Parameters.Add("@sUsername", sUsername);
// Set up the return value
SqlParameter prmReturn = new SqlParameter("@ReturnValue", SqlDbType.Int);
prmReturn.Direction = ParameterDirection.ReturnValue;
cmdDist.Parameters.Add(prmReturn);
myReader = cmdDist.ExecuteReader();


// If data has been returned.
if (myReader.HasRows)
{
// Obtain the IDPerson
myReader.Read();
if (!myReader.IsDBNull(0))
{
long lIDPerson = myReader.GetInt32(0);
// Close the reader
myReader.Close();


// Now that the reader has been closed, obtain the return code from
the 'PersonGet' stored procedure.
long lResult = cmdDist.Parameters["@ReturnValue"].Value.ToString())
== 0);
...