SQLConnection with the Using statement, calling SQLDataReader from inside it?

Question

Just want to make sure this is the best way to call a connection, and grabbing data from a database, or should I some how call the datareader outside of the using statement? (in order to have the connection close quicker?) or is there anything you would personal change to this?

using (SqlConnection cn = new SqlConnection(connStr))
        {
            using (SqlCommand cm = new SqlCommand(connStr, cn))
            {  
                cm.CommandType = CommandType.StoredProcedure;
                cm.CommandText = "GetExchRatesByDate";
                cm.Parameters.Add("@Date", SqlDbType.VarChar).Value = txtStartDate.Text;
                cn.Open();
                SqlDataReader dr = cm.ExecuteReader();

                while (dr.Read())
                {
                    firstName = (string)dr["GivenName"];
                    lastName = (string)dr["sn"];;
                }
                dr.Close();
            }
        }

Answer 1

You can't successfully call the datareader outside of the using statement as it requires an open connection to read the data.

The connection will close fast enough the way you have it, and doesn't even really "close". It will be returned to the connection pool (assuming you are using one). Since you probably are, you don't need to worry about how fast the connection closes in this context as other parts of the application needing a connection will grab an available one from the pool. This is assuming that you don't have a really high traffic application, it could become important in that scenario, but that is many, many, many concurrent users, and you can alleviate that issue, with upping the number of connections in the pool.

Chris brought up a good point too: It should be in a using statement:

  SqlDataReader dr = cm.ExecuteReader();

                while (dr.Read())
                {
                    firstName = (string)dr["GivenName"];
                    lastName = (string)dr["sn"];;
                }
                dr.Close();

In this instance, if your reader throws an exception, it won't ever get to dr.Close(); so it will be left open a lot longer than it needs to be (Maybe even for the life of the application).

Data Reader

Answer 2

Not only you cannot make a call to the SqlDataReader outside of the using statement as all declared within variables will be disposed, and you need an opened connection to read the data, you had better write an object that will be returned, or even a list of your object.

public class MyObject {
    public string FirstName { get; set; }
    public string Surname { get; set; }
}

public IEnumerable<MyObject> GetObjects() {
    ICollection<MyObject> myObjects = new List<MyObject>();

    using (SqlConnection cn = new SqlConnection(connStr))
    {
        using (SqlCommand cm = new SqlCommand(connStr, cn))
        {  
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "GetExchRatesByDate";
            cm.Parameters.Add("@Date", SqlDbType.VarChar).Value = txtStartDate.Text;
            cn.Open();

            using(SqlDataReader dr = cm.ExecuteReader()) 
                while (dr.Read()) {
                    MyObject myObject = new MyObject();
                    myObject.FirstName = (string)dr["GivenName"];
                    myObject.Surname = (string)dr["sn"];
                    myObjects.Add(myObject);
                }
        }
    }
    return myObjects;
}