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

Question

Just want to make sure this is the best way to call the connection and grab the data from the database, or do I need to somehow call the datareader outside of the using statement? (to speed up the connection?) or is there anything that you could 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 cannot successfully call the datareader outside the using statement, because an open connection is required to read the data.

, , "". ( ). , , , , , , , . , , , , , , ,

: :

  SqlDataReader dr = cm.ExecuteReader();

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

, , dr.Close();, , (, ).

Data Reader

Answer 2

SqlDataReader using, , , , .

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

Answer 3

datareader IDisposable, use. .

Answer 4

, , , - AddWithValue:

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

:

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

Answer 5

SQLConnection factory. ( )

while dr.Read , , ???? , , , , .

, .. (, factory , , , ...)

Your statements for use look good to me.