Why can't you use a local SqlCommand variable in a using statement

Question

I'm not sure if i'm just having a dumb moment here or I generally do not understand but can someone explain to me why this will run:

using System.Data.SqlClient;
using System.Configuration;

public void SomeMethod()
{
    using(SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString))
    {
        using(SqlCommand qry = new SqlCommand(someSqlString, dbConnection))
        {
             dbConnection.Open();
             using(SqlDataReader reader = qry.ExecuteReader())
             {
                ...
             }
         }
     }
}

and this will not run throwing an InvalidOperationException (The ConnectionString property has not been initialized) when it hits dbConnection.Open():

using System.Data.SqlClient;
using System.Configuration;

private SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString);

public void SomeMethod()
{
    using(dbConnection)
    {
        using(SqlCommand qry = new SqlCommand(someSqlString, dbConnection))
        {
             dbConnection.Open();
             using(SqlDataReader reader = qry.ExecuteReader())
             {
                ...
             }
         }
     }
}

It probably has something to do with the fact that a using statement is going to dispose of that connection, and your variable will never get re-instantiated. — Logarr
– Logarr, Commented Mar 8, 2017 at 22:23

Joel Coehoorn · Accepted Answer · 2017-03-08 22:29:26Z

4

The second example will run (at least it should)... but only the first time you call SomeMethod(). This is because after the first call to the method, you have Dispose()'d the connection object.

ADO.Net has a built-in feature called Connection Pooling that intelligently re-uses the underlying database connection resources. For the most part, you should not try to use the same Dbconnection object over and over. Instead, just re-use the connection string, create the connection objects as you need them, and Dispose() of them as soon as possible when done. Otherwise, you are conflicting with the built-in connection pooling and making things worse, rather than better.

In other words, the first sample is the correct way to do this. It's faster and more efficient than the second, even though the second seems to save you an object allocation here and there.

About the only time you should try to re-use a connection object is when you're going to have a bunch of queries in a tight loop, such that you might open the connection once, execute a bunch of queries one after the other, and then close it when you've finished the whole batch. And even then, most of the time when I've seen this the code was better off refactored in some way to group the sql statements into a single batch.

edited Mar 8, 2017 at 22:29

answered Mar 8, 2017 at 22:23

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

2 Comments

Tom Carroll Over a year ago

Yes that makes sense, thanks for the insight! I didn't make it clear in my question that this was not the first time I was trying to use the connection but that is exactly what was happening

Joel Coehoorn Over a year ago

Seems someone disagreed, but didn't feel like sharing why?

Nino · Accepted Answer · 2017-03-08 22:28:20Z

MSDN explains:

You can instantiate the resource object and then pass the variable to the using statement, but this is not a best practice. In this case, the object remains in scope after control leaves the using block even though it will probably no longer have access to its unmanaged resources. In other words, it will no longer be fully initialized. If you try to use the object outside the using block, you risk causing an exception to be thrown. For this reason, it is generally better to instantiate the object in the using statement and limit its scope to the using block.

So, to simplify it... using statement ensures that the object will be disposed after code block is executed and will ensure the object will go out of scope. So, in your second example, you're using it wrong, and the connection is no longer managed so it behavies unpredictably.

Collectives™ on Stack Overflow

Why can't you use a local SqlCommand variable in a using statement

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related