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

kemiller2002 · Accepted Answer · 2010-12-17 16:10:15Z

5

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

edited Dec 17, 2010 at 16:10

answered Dec 17, 2010 at 16:05

kemiller2002

116k28 gold badges201 silver badges255 bronze badges

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

1 Comment

Spooks Over a year ago

thank you, just wanted to be sure that this is an acceptable way to grab data. I will continue using this code

Will Marcouiller · Accepted Answer · 2010-12-17 16:16:05Z

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

ChrisLively · Accepted Answer · 2010-12-17 16:09:23Z

2

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

answered Dec 17, 2010 at 16:09

ChrisLively

88.3k27 gold badges174 silver badges249 bronze badges

Comments

Marcie · Accepted Answer · 2010-12-17 16:16:13Z

1

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

answered Dec 17, 2010 at 16:16

Marcie

1,2692 gold badges11 silver badges18 bronze badges

Comments

Angus Connell · Accepted Answer · 2010-12-17 16:10:09Z

0

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.

answered Dec 17, 2010 at 16:10

Angus Connell

4193 silver badges6 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related