Reading a stored procedure's output variable in .NET

I recently wrote some C# code to call a stored procedure, pass some input and output parameters, read the resulting recordset, and then read the value of an output parameter. When I tried to read the output parameter's value, the result was null. It took a long time to figure out why.

It turns out that, when using SqlDataReader.ExecuteReader -- as opposed to ExecuteScalar or ExecuteNonQuery, you must close the reader before you can obtain the value of an output variable. (A tip of the hat to The Code Project for that information.)

Here's an example:

The SQL code:
CREATE PROCEDURE test
@foo INT,
@goo INT OUTPUT
AS
BEGIN
SET @goo = @foo * @foo
SELECT @goo AS hoo
END

First version of the C# -- wrong, output value is null:
string connectionString = "...";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("test", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@foo", 9);
SqlParameter outParam = cmd.Parameters.AddWithValue("@goo", 0);
outParam.Direction = ParameterDirection.Output;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
rdr.Read();

// obtain a value from the recordset
int result = Int32.Parse(rdr["hoo"].ToString());

// obtain the value of the output parameter
// reader is still open
// System.NullReferenceException occurs because the value is null
Label1.Text = cmd.Parameters["@goo"].Value.ToString();
} // reader is closed here
}

Second version of the C# -- right, output value is 81:
string connectionString = "...";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("test", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@foo", 9);
SqlParameter outParam = cmd.Parameters.AddWithValue("@goo", 0);
outParam.Direction = ParameterDirection.Output;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
rdr.Read();

// obtain a value from the recordset
int result = Int32.Parse(rdr["hoo"].ToString());

// obtain the value of the output parameter
// reader is still open
// System.NullReferenceException occurs because the value is null
} // reader is closed here
Label1.Text = cmd.Parameters["@goo"].Value.ToString();
}