Common SQL Problem in ASP.NET Applications...

Craig Gemmil blogs about a SQL problem that I have seen in a number of ASP.NET applications, especially those written by folks new to ASP.NET

Years ago, SQL Connections were very expensive to create and tear down, and so on a fat client application, you would often find a way to open a connection and share it among all the code in the application. Making this work was the fact that while a connection could actually only do one thing at a time, classic ADO would open additional underlying connections as needed.

ASP.NET (and Web apps in general) are different. Because Web applications are, under it all, stateless, data access in the ADO.NET Web world is designed to be mostly disconnected. A user requests a page, the page load event opens and closes any connections it needs (opening ass late as possible and closing as early as possible). Connection objects should be declared in the narroest scope possible, and should always be closed before the scope is exited. Opening connections is not as expensive as it used to be, since ADO.NET, by default, uses connection pooling. Connection pooling keeps underlying physical connections to the database open even after the high level connection object has closed. Then the underlying connection can be used in a subsequent connection open request.

The problem in question was caused by a developer declaring a connection as static. Thus the second user would end up resetting the connection, killing off whatever the first simultaneous user was trying to do. Again, connection objects should not be declared at application scope or made static, since this will allow multiple requests to try and simultaneously use the same connection. A very bad thing...

Posted by Douglas Reilly at 10:28 PM  Permalink