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

Just want to make sure this is the best way to call a connection, and开发者_StackOverflow中文版 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();
            }
        }

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

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;
}

The datareader implements IDisposable so it should also be wrapped in a using clause. Everything else looks good.

Others have covered it but one more thing that I like to use is AddWithValue:

cm.Parameters.Add("@Date", SqlDbType.VarChar).Value = txtStartDate.Text;

could be written as:

cm.Parameters.AddWithValue("@Date", txtStartDate.Text);

I would consider putting the SQLConnection into a factory method. (if it's called loads)

Also I would not have a while loop around the dr.Read You are only expecting one answer, so what happens when there are no results or many results???? Not too sure if I like the casting, but probably OK.

I amsume that you have unit tests around this code with the connectionstring being passed in etc... (obviously the factory idea would make unit testing harder, so perhaps not worth doing...)

Your using statements look fine to me.