c# - 调用存储过程时需要Connection.Open()吗？

Question

通常我会使用“Using”或仅使用connection.open()和connection.close()。但是当我调用存储过程时，不需要这样做。怎么会？ (是的，下面的代码片段无需使用或打开即可工作)。

try {
    SqlCommand cmd = new SqlCommand("***", connectionSiteDb);
    DataTable dt = new DataTable();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@ProcessSegmentID", ProcessSSegmentID));
    cmd.Parameters.Add(new SqlParameter("@PO_RecipeID", PO_RecipeID));
    cmd.Parameters.Add(new SqlParameter("@ProductSegmentVersion", ProductSegmentVersion));


    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    da.Fill(dt);
    return dt;
}
catch (Exception e) {
     Console.WriteLine(e);
     return null;
}

Answer 1

使用using statement与否并不取决于您的 CommandType 是 Text 还是 StoredProcedure。

SqlDataAdapter.Fill 打开连接本身。

来自Populating a DataSet from a DataAdapter

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 will also close the connection when Fill is finished. This can simplify your code when dealing with a single operation such as a Fill or an Update.

还有DbDataAdapter.Fill(DataTable)

The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.

由于 SqlDataAdapter 未实现 IDisposable，因此您不需要对其使用 using 语句。

如果您想深入了解，可以查看 QuietClose method和 QuietOpen method实现；

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)
{
     originalState = connection.State;
     if (ConnectionState.Closed == originalState) {
         connection.Open();
     }
}