c# - 我什么时候必须显式打开 SqlConnection？

Question

我编写了以下代码(为简洁起见进行了删减):

using (SqlConnection cn = new SqlConnection("Server=test;Database=test;User=test;Password=test"))
using (SqlDataAdapter da = new SqlDataAdapter())
using (DataSet ds = new DataSet())
{
    string groupsQuery = @"SELECT GroupName FROM tblGroups ORDER BY GroupName";

    da.SelectCommand = new SqlCommand(groupsQuery, cn);
    da.Fill(ds);

    foreach (System.Data.DataRow row in ds.Tables[0].Rows)
    {
        string group = row["GroupName"].ToString();
        this.GroupList.Add(group);
    }
}

我忘记调用 cn.Open()，但令我惊讶的是，代码运行得很好。我怀疑 SqlDataAdapter 在施展魔法，所以我去找了 the source .

SqlDataAdapter 继承Fill方法来自 DbDataAdapter .

填充 调用 FillInternal ，它将其逻辑包装在一个 block 中，如下所示:

try {
    QuietOpen(activeConnection, out originalState);

    //... do the fill ...
}
finally {
    QuietClose(activeConnection, originalState);
}

QuietOpen 和 QuietClose 非常简单:

static private void QuietClose(IDbConnection connection, ConnectionState originalState) {
    // close the connection if:
    // * it was closed on first use and adapter has opened it, AND
    // * provider's implementation did not ask to keep this connection open
    if ((null != connection) && (ConnectionState.Closed == originalState)) {
        // we don't have to check the current connection state because
        // it is supposed to be safe to call Close multiple times
        connection.Close();
    }
}

// QuietOpen needs to appear in the try {} finally { QuietClose } block
// otherwise a possibility exists that an exception may be thrown, i.e. ThreadAbortException
// where we would Open the connection and not close it
static private void QuietOpen(IDbConnection connection, out ConnectionState originalState) {
    Debug.Assert(null != connection, "QuietOpen: null connection");
    originalState = connection.State;
    if (ConnectionState.Closed == originalState) {
        connection.Open();
    }
}

我很好奇，还有什么时候可以在我不需要的 SqlConnection 上调用 Open？我应该始终明确地执行此操作，还是让 .NET“悄悄地”执行它的操作？

此外，任何有关 SqlDataAdapter 为什么这样做的更多详细信息的引用都很棒。

Answer 1

来自 this page on MSDN :

The Fill method implicitly opens the Connection that the DataAdapter is using if it finds that the connection is not already open. If Fill opened the connection, it also closes the connection when Fill is finished.