How to close a forgotten connection?

Question

I created a connection and an SqlReader but forgot to close both of them:

SqlConnection conn = new SqlConnection("connection info");
conn.Open();

string sql = "SQL command HERE";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader reader = cmd.ExecuteReader();

Now when try to run the code again it always gives me this error:

System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

This link told me how to properly open and close a connection but didn't explain anything on how to close one still running.

I tried shuting down the pc, I tried looking into the database's options on SQL server (found none useful)... I changed the code to do just the close of both the connection and the reader (it compiled and runned but the problem remained after changing back the code).

How can I close this "ghost" connection? Is there any way (brute force) to close all running connections?

---

[EDIT:] I couldn't really solve the problem. The workaround was to add MultipleActiveResultSets=true to the connection string

Answer 1

I don't think you can access the ghost object, for future, just use using construct where it's possible:

using(SqlConnection conn = new SqlConnection("connection info"))
{
   conn.Open();

   string sql = "SQL command HERE";
   SqlCommand cmd = new SqlCommand(sql, con);
   SqlDataReader reader = cmd.ExecuteReader();
....
}

Answer 2

Wrap the creation in a using statement - this will always ensure the connection gets closed:

using(SqlConnection conn = new SqlConnection("connection info"))
{
   // your code
}

Answer 3

All of these answers tell you how to avoid the problem, but they don't explain what the problem is.

A SqlDataReader provides forward-only data access, which means that once you have used it and are done, you must close create a new one. See this blog for a detailed explanation. Basically, if you don't close the DataReader, then underthehood it will remain open dedicated to that connection and command.

As others have stated, its best to ensure you close all your resources.

using (SqlConnection connection = new SqlConnection("...")) {
    connection.Open();

    string sql = "SQL command HERE";

    using (SqlCommand cmd = new SqlCommand(sql, con))
    using (SqlDataReader reader = cmd.ExecuteReader()) {
            // do your stuff
    }
}

Answer 4

Looking at all the answers, they seem to tell you how to avoid the problem.

If I'm not mistaken, what you mean is that a connection exists on both the client (your PC) and the server (The sql server) because you forgot to close it, and you're worried about it hanging out there forever.

Think of your connection to the server as a phone conversation. I could hang up on you, but it takes a few seconds for your phone to realize the connection is lost. You may sit there wondering if I've hung up, or just stopped talking. You really don't know. This is what happens on the server when a connection isn't closed properly. (On older landlines, you could leave the phone off the hook and tied up the line indefinitely.)

By closing the connection in code, you are effectively telling the server to close their end of the connection before closing your own. if you FAIL to close the conneciton, it will be closed on your end when the program exits or if you reboot, but the server may sit there with an open connection. (Think of someone sitting there wondering "Did he just hang up on me?")

If I'm not mistaken, what you want to get to is closing it at the SQL server end. (Getting them to "hang up".)

After rebooting, it is absolutely closed on your end. It should clear on its own at the server.

However, if you want to do it yourself, you can clear it at the server in code end using this info: How do you kill all current connections to a SQL Server 2005 database?

A far easier approach would be to just do it in SQL Server Management Studio as described here: http://www.mikebevers.be/blog/2009/07/kill-open-sql-processes-connections/

Answer 5

Truth be told even when you "close" or "dispose" of a connection it does not really go away unless you explicitly disable Pooling in your connection string. You can however do this

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.clearpool.aspx

Answer 6

I know this is an old post, and this may help no one. But I saw a opportunity to post what I saw wrong with this question.

First, you are creating a SqlConnection named conn but in your SqlCommand named cmd you are calling con as your connection. This is a problem:

SqlConnection conn = new SqlConnection("connection info");
conn.Open();

string sql = "SQL command HERE";
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader reader = cmd.ExecuteReader();

This might be why it's giving you the error:

System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

Second, to close a conn after you are done you use:

conn.Close();

Third, to close a SqlDataReader you use:

reader.Close();

But you just assigned the SqlDataReader to reader. You never actually opened the SqlDataReader. To open it use:

reader.Read();

Or:

while (reader.Read())
{
    // Code
}

Now a proper way to initilaize a connection and a SqlDataReader while opening and close them:

using (SqlConnection conn = new SqlConnection(sqlConnectionString))
{
    conn.Open();

    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "SELECT * FROM TableName;";

        SqlDataReader reader = cmd.ExecuteReader();

        reader.Read();

        if (reader.HasRows)
        {
            strCol1 = reader.GetValue(0).ToString();
        }

        reader.Close();
  }

  conn.Close();
}