c# closing sqlconnection and sqldatareader or not?

问题:

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?

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

回答2:

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, ...

回答3:

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.

回答4:

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)