Tags

, ,

ExecuteReader is one of the most convenient ways of “reading” data from your database via the SqlCommand object.   The problem is that there are times that aside from getting your standard record set you also need the return value and the output parameters produced by your queries, and your SqlCommand object does not return these values as expected.  See the figure below:

SqlConnection conn = new SqlConnection(myConnectionString);
SqlCommand cmd = new SqlCommand(mySqlCommand, conn);
// Set up your command and parameters
cmd.Parameters.Add(“@Return”, SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
// Read your data
}
int resultCount = (int)cmd.Parameters[“@Return”].Value;
conn.Close();
return resultCount;

Syntactically and logically the above code is fine, except that realistically it doesn’t work!  Why?   For some technical reasons the return value and output parameters does not contain the expected values until you close your SqlConnection object. The “working” version of the code above is as follows:

SqlConnection conn = new SqlConnection(myConnectionString);
SqlCommand cmd = new SqlCommand(mySqlCommand, conn);
//     Set up your command and parameters
cmd.Parameters.Add(“@Return”, SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//     Read your data
}
conn.Close();
int resultCount = (int)cmd.Parameters[“@Return”].Value;
return resultCount;

As you can see there really isn’t much difference, you just have to close the connection first to have the values that you seek.

Advertisements