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

Answer 1

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.

Answer 2

Excuse me for stating it so directly, but you seem to not have a clear picture of what a data reader (System.Data.IDataReader) exactly is. So, let me demonstrate:

using System;
using System.Data;
using System.Data.SqlClient;

string connectionString = "...";

// create and open a DB connection:
using (IDbConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    // create a command object:
    using (IDbCommand query = connection.CreateCommand())
    {
        query.CommandType = CommandType.Text;
        query.CommandText = "SELECT ID, Name, Birthdate FROM SomeTable";

        // execute the query and retrieve a data reader object:
        using (IDataReader reader = query.ExecuteReader())
        {
            // the data reader is now used to read the query result:
            while (reader.Read())
            {
                int id = reader.GetInt32(0);
                string name = reader.GetString(1);
                DateTime birthdate = reader.GetDateTime(0);
            }
        }

        // at this point, the data reader object has been disposed.
        // the DB connection can now be used again, e.g. by issuing
        // another query:

        command.CommandText = "SELECT COUNT(*) FROM SomeOtherTable";
        ...
    }
}

Note: I have refrained from using var, so you see exactly what types are involved. (Note also btw. that I'm using mostly interface types like IDbConnection instead of concrete implementation classes such as SqlConnection. This makes the code more easily adaptable to other RDBMS.)

You can see now that in your code, you've never actually used a data reader. Therefore your code worked, because there was never an open data reader that could have "blocked" your DB connection in the first place.