关闭sqlconnection和sqldatareader或不是?

我有这段代码:


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();

现在我的问题是。 如果返回,我的连接和dtr会自动关闭还是应该在连接关闭后使用bool变量并执行返回?


返回前必须关闭连接。 最好的方法是使用USING块,因为SqlConnection实现了IDisposable接口。 在这种情况下,您不必记住即使抛出异常也必须关闭连接。

看下面的例子:

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

以下是如何改进您的代码:

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

这样你就不用担心关闭,处理......


你最好打赌它使用一个using块。 即使您在方法中间返回,也会强制执行Dispose调用:

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

这是可行的,因为using实际上是一个try/finally块,即使你返回,finally块也会被执行并在你的SqlCommandSqlDataReader上运行Dispose

链接地址: http://www.djcxy.com/p/44025.html

上一篇: c# closing sqlconnection and sqldatareader or not?

下一篇: C# SQL Columns into ComboBox