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?

Answer 1

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
            {
                ...
            }
        }
    }
}

Answer 2

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.