c# closing sqlconnection and sqldatareader or not?

Question

I have this piece of code:


SqlConnection conn;
string strconString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLCONN"].ToString();
conn = new SqlConnection(strconString);
string cmdstr = "select status from racpw where vtgid = " + vtgid;
SqlCommand cmdselect = new SqlCommand(cmdstr, conn);
conn.Open();
SqlDataReader dtr = cmdselect.ExecuteReader();
if (dtr.Read())
{
return;
}
else
{
...
}
dtr.Close();
conn.Close();

Now my question is. If return, does my connection and dtr get closed automatically or should I use a bool variable and perform a return after my connections get closed?

Steven · Accepted Answer · 2010-12-08 09:35:53Z

10

You have to close connection before return. The best way to do it is USING block, because SqlConnection implements IDisposable interface. In that case you don't have to keep in mind that you have to close connection even if exception was thrown.

See the example below:

using (var conn = new SqlConnection(strconString))
{
    string cmdstr = 
        "select status from racpw where vtgid = " + vtgid;
    using (var cmdselect = new SqlCommand(cmdstr, conn))
    {
        conn.Open();
        using(var dtr = cmdselect.ExecuteReader())
        {
            if (dtr.Read())
            {
                return;
            }
            else
            {
                ...
            }
        }
    }
}

edited Dec 8, 2010 at 9:35

Steven

174k26 gold badges355 silver badges453 bronze badges

answered Dec 8, 2010 at 9:26

Egor4eg

2,7081 gold badge22 silver badges41 bronze badges

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

Comments

Steven · Accepted Answer · 2010-12-08 09:37:38Z

Your best bet it to use a usingblock instead. This will enforce a call to Disposeeven if you return in the middle of the method:

string strconString = System.Configuration.ConfigurationManager
    .ConnectionStrings["SQLCONN"].ToString();

using (SqlConnection conn = new SqlConnection(strconString))
{
    string cmdstr = 
        "select status from racpw where vtgid = " + vtgid;

    using(SqlCommand cmdselect = new SqlCommand(cmdstr, conn))
    {
        conn.Open();
        using( SqlDataReader dtr = cmdselect.ExecuteReader())
        {
            if (dtr.Read())
            {
                return;
            }
            else
            {
                ...
            }
        }
    }
}

This works because using is in fact a try/finallyblock, and even if you return, the finally block is executed and runs Dispose on your SqlCommandand SqlDataReader.

Steven · Accepted Answer · 2010-12-08 09:39:18Z

4

Here's how to improve your code:

var connectionString = System.Configuration.ConfigurationManager
    .ConnectionStrings["SQLCONN"].ToString();

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = 
            "select status from racpw where vtgid = @vtgid";

        cmd.Parameters.AddWithValue("@vtgid", vtgid);

        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                ...
            }
        }
    }
}

This way you don't need to worry about closing, disposing, ...

edited Dec 8, 2010 at 9:39

Steven

174k26 gold badges355 silver badges453 bronze badges

answered Dec 8, 2010 at 9:25

Darin Dimitrov

1.0m275 gold badges3.3k silver badges3k bronze badges

Comments

MattDavey · Accepted Answer · 2010-12-08 09:34:46Z

1

As the others have pointed out, SqlConnection implements IDisposable. IDisposable exists so that you can have control over when resources are released. If you do not call Dispose yourself, your connection will still be closed automatically, but you have no control over when this might happen (FYI it will happen when the garbage collector collects the object)

answered Dec 8, 2010 at 9:34

MattDavey

9,0473 gold badges34 silver badges56 bronze badges

1 Comment

Egor4eg Over a year ago

But he possible uses a connection pool. That means, connection won't be closed by the garbage collector. In that case he will face InvalidOperationException when he try to create and open a new connection again.

Collectives™ on Stack Overflow

c# closing sqlconnection and sqldatareader or not?

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related