If we cannot retrieve output parameters until SqlDataReader is closed, then why...?

Question

1)

While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.

If the above claim is true, then why is the following method able to retrieve a value from output parameter before the reader is closed:

    public int Something()
    {
        using (SqlConnection con = new SqlConnection(this.ConnectionString))
        {
            SqlCommand cmd = new SqlCommand("some_procedure", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output;
            con.Open();
            cmd.ExecuteReader();
            return (int)cmd.Parameters["@ID"].Value;
        }
    }

2)

You can reset the CommandText property and reuse the SqlCommand object. However, you must close the SqlDataReader before you can execute a new or previous command.

Why must you close sqldatareader before you execute new command?

thanx

For #1, you're posting the documentation very specific to SqlDataReader...yet that's not what you're using. This doesn't contradict the documentation, it's just not applicable. — Nick Craver

cjk cjk · Accepted Answer · 2010-04-13T19:38:01

In your first question, you are executing a NonQuery - therefore there is no reader to close before you get the output parameter.

For the second question, you just do. The command won't let you call another reader while one is open.

If we cannot retrieve output parameters until SqlDataReader is closed, then why...?

1 Answers