Search code examples
c#sqldatareadersqlconnection

How to close a forgotten connection?

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

Solution

  • 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/