Is there a way to efficiently share a single SqlConnection instance between calls from at least the same page or either same request or whole app?
-
What for? Why don't use connection pooling?abatishchev– abatishchev2011-09-02 11:49:28 +00:00Commented Sep 2, 2011 at 11:49
-
I just thought that when I Close() something, .NET really closes it, LOL, but as it's brilliantly illustrated by Neil, my presumption is simply incorrect.Mr.RoyDiibs– Mr.RoyDiibs2011-09-02 12:02:26 +00:00Commented Sep 2, 2011 at 12:02
Add a comment
|
3 Answers
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.
Comments
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:
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.
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.
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.
Comments
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.
1 Comment
TheCodeKing
Reason for down vote? I've worked on many projects with memory leaks where this has been the culprit.