Share SqlConnection object in ASP.NET Web App Between DB calls

Is there a way to efficiently share a single SqlConnection instance between calls from at least the same pa开发者_如何学JAVAge or either same request or whole app?

---

If you are using ADO.NET or any framework that makes use of ADO.NET, then it is likely already connection pooling for you.

What database are you using? - connection pooling could possibly vary depending on the type of database.

You could always verify this by monitoring your database server connections.

For example, on SQL Server, sp_who2 will list all of the connections. Look for the connection(s) from your machine and notice that the spid (unique identity for each connection) stays the same between your web application page loads.

---

If you want to know how to do this, the answer is by declaring a static variable of type SqlConnection inside the page and use it for your database access inside all of your page methods; however, THIS IS A TERRIBLE IDEA and the reasons are many:

1. As many people have pointed out, SQLConnections are pooled and there's no need to try an avoid opening a connection for every database access. There won't be any performance gains by doing so.

2. Remember that every request made to your applications will be served by a different Thread taken from the Application Pool. If you have a static variable like this, 2 concurrent requests to the same page may end up accessing the same SqlConnection instance and you could be caught in a scenario in which while the second request is retrieving data from the DB, the first request may close the connection unexpectedly.

3. You should perform the data access operations at a different layer of your application's architecture. A good approach is to have a Business Layer that has a reference to an application Data Access Layer which in turn is in charge to perform the data access operations. This provides greater encapsulation, separation of concerns and increases maintainability, reusability, etc. Here's a good article explaining the idea.

---

That's a really bad idea. SqlConnection implements IDisposable which means you must dispose of it to free resources as soon as possible.

As has already been mentioned, under the hood connection pooling already optimises the creation of connections. If you start holding on to connections you could end up with serious problems, I see it time and time again.