I have a page that is making two Ajax requests at start up (they should be combined, I know, but I'm interested in what the correct solution is other than that) - they each make a request to a WebAPI controller to get data which is returned via JSON.
Individually they work great, but firing both Ajax requests off together results in the famous "there is already an open datareader associated with this command" error.
That appears to be due to the fact that I set up a single database connection in Application_Start and use that connection for all controllers. If I create a new connection in each controller it works okay.
So my question is, is it correct to create a new database connection in each controller, or is there some option to lock / share a single connection?
Do not use a single connection across application.
Create and open database connection as late as possible and close as early as possible. That should be the rule.
With respect to WebAPI, you should open the connection on each request, get the data, close connection and return data to the client. Let ADO.Net connection pooling takes care of your connections.