InvalidOperationException The connection was not c

问题:

Why does this code throw an Invalid Operation Exception?

private SqlCommand cmd; // initialized in the class constructor

public void End(string spSendEventNotificationEmail) {
  try {
    cmd.CommandText = spSendEventNotificationEmail;
    cmd.Parameters.Clear();
    cmd.Parameters.Add("@packetID", SqlDbType.Int).Value = _packetID;
    cmd.Parameters.Add("@statusID", SqlDbType.Int).Value = _statusID;
    cmd.Parameters.Add("@website", SqlDbType.NVarChar, 100).Value = Tools.NextStep;
    cmd.Connection.Open();
    cmd.ExecuteNonQuery();
  } finally {
    cmd.Connection.Close();
    cmd.Parameters.Clear();
    cmd.Dispose();
  }
  endCall = true;
}

回答1:

You're trying to open a connection which is already open, this results in exception.

Solution 1 (recommended):

Inspect your code, check all the parts where cmd.Connection connection is opened and ensure that it's always closed properly.

Solution 2 (quick'n'dirty fix):

before line

cmd.Connection.Open();

add the following check/cleanup code:

if (cmd.Connection.State == ConnectionState.Open)
{
    cmd.Connection.Close();
}

回答2:

There's very little need for keeping the Sql* objects at the class level, especially based on what you're showing. You'll also lose the benefits of connection pooling by attempting to do it yourself.

With this method, you remove the possibility of your error because you're not sharing any objects

private readonly _connectionString = "...";

public void End(string spSendEventNotificationEmail) {
  using(var conn = new SqlConnection(_connectionString))
  using(var cmd = conn.CreateCommand())
  {
    cmd.CommandText = spSendEventNotificationEmail;
    cmd.Parameters.Add("@packetID", SqlDbType.Int).Value = _packetID;
    cmd.Parameters.Add("@statusID", SqlDbType.Int).Value = _statusID;
    cmd.Parameters.Add("@website", SqlDbType.NVarChar, 100).Value = Tools.NextStep;
    conn.Open();
    cmd.ExecuteNonQuery();
  }
  endCall = true;
}