C# closing sqlconnection and sqldatareader or not

csqlconnectionsqldatareader

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?

Best Answer

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