Asp.Net MVC double submit/request breaks SQL connection

Question

Hello all,

I am running into a strange problem I don't fully understand. The main symptom is that when I double click a link (that points to a controller action) in my MVC application, my database server connection gets blown, and I get the error :

Execution of the command requires an open and available connection. The connection's current state is broken.

If I step through starting at a breakpoint at the top of the controller action, it will step down a couple lines and then jump back up to the breakpoint. Somehow the first request isn't executing fully before the second one gets there, and somehow my database connection breaks when it gets to any query. Every time this happens, I have to restart the application server.

It was happening intermittently at first, but the double clicking of links seems to reproduce it everytime. Does this happen to anyone else? What am I missing here?

Thanks,

rusty

Update :

A.) I incorrectly tagged this as Linq-to-sql when we are actually using Linq-to-entities. B.) The connection object is defined as a member variable of the controller :

namespace C2S.Controllers
{
    public class ArtifactController : Controller
    {
        private c2sEntities _entities = new c2sEntities();
        ...

I noticed in some of the asp.net tutorials they declare the variable in the same spot but have a separate constructor for the controller where the db object is initialized. Does this make any difference?

C.) The problem is not only with the double-clicking as described above. The connection breaks at other seemingly random times; I cannot seem to reproduce the error consistently (even double-clicking does not always break it). Restarting the web site usually fixes it, although sometimes I have to restart the host machine. After its back up, repeating the same sequence of actions usually does not reproduce the same error!

Maybe there's something I don't understand about setting up my linq-to-entities classes or the nature of the database connection. Does anyone have any thoughts? I really don't even know how to investigate this one!

Thanks again

Rusty

Answer 1

I am guessing that you're not properly closing your database connection. You should consider making use of the using statement.

using(SqlConnection conn = new SqlConnection("connstring")) {
  using (SqlCommand cmd = new SqlCommand("SQLSTATEMENT", conn)) {
    // more code here......
  } 
}

This will ensure that your connection is closed even if there's an error in your code somewhere.

Read all about it here: http://davidhayden.com/blog/dave/archive/2005/01/13/773.aspx

Answer 2

It's a bit difficult to say from your description of the problem, but a first guess would be:

Is your connection object static (i.e. controller or application level) or defined locally within the action? Double clicking a link would fire the event twice and that sounds like what you are describing here. So the first call creates the connection, then the 2nd call comes in and tramps all over the 1st call to the method, breaking the connection it thinks it has.

Edit: Does the problem only occur on double clicks. Does it work as expected if you only single click on the link? An example of the code in question would help.