2

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

3
  • 2
    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. Commented Apr 13, 2010 at 19:38
  • 1
    and where is the SqlDataReader? Commented Apr 13, 2010 at 19:39
  • Damn it...a typo...I will correct it Commented Apr 13, 2010 at 19:40

1 Answer 1

3

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.

Sign up to request clarification or add additional context in comments.

3 Comments

I've asked before how the connection can "tell" that another DataReader is open.. never got answered..
@Earlz - sorry, I have no idea how it does it. You need Jon Skeet :)
@stakx but I mean. If you open a datareader on a connection and then assign all of the commands and datareaders associated with that connection to null, and try to open another datareader on it, it will throw an error. How does the connection itself know that a datareader is using it? (the connection, not the command)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.