Can you use 2 'using' statements for both sqlconnection and sqldatareader ?

Question

Can you use 2 'using' statements like:

using (SqlConnection ..)
{
    using(SqlDataReader reader = new SqlDataReader())
    {

     }

}

I'm trying to do this put getting an error on the constructor of the SqlDataReader

Answer 1

You can and you can also format them without the extra brackets like so:

using (SqlConnection ..)
using(SqlDataReader reader = new SqlDataReader())
{

}

Which I do all the time to limit the amount of scope nesting.

Answer 2

SqlDataReader has no constructor. You are returned a datareader by calling the ExecuteReader method of a SqlCommand object.

e.g.

using (SqlConnection ..) 
{ 
    SqlCommand cmd = new SqlCommand(...);
    using(SqlDataReader reader = cmd.ExecuteReader())) 
    { 

    } 

}

Answer 3

You can't instantiate a SqlDataReader like that as mentioned above. Generally I see 2 levels of using blocks, but the inner one would be the command object, something like this:

 using (var conn = new SqlConnection(...))
 {
    conn.Open();
    using (var cmd = new SqlCommand(...))
    {
       var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }
 }