Opening an ADO connection during the Application On Start Event

  • Thread starter Thread starter ryan.d.rembaum
  • Start date Start date
R

ryan.d.rembaum

Hello,

I have an application in which all users are connecting to the same
database. I know that opening a connection is very resource, etc, and
that cetain classes in ADO.net support connection pooling, which is all
great.

What I am wondering is, if all users are accessing the same database
throughout the application, why not just open one connection to the
database during the Application_Start event and use that throughout all
the other pages. i.e. Assign the connection object to a global
(Application) variable. This way the resource needed to open the
connection would only be required once.

Does this cause problems? And if so what are they? I feel sure there
must be some trouble with this since I have not seen it recommended,
but am not sure.

Thanks!
Ryan
 
You shouldn't do this because it really doesn't buy you anything. There is
NO advantage to doing this. Even though you are pooling a constantly open
SqlConnection instance, you are essentially doing what connection pooling
would do for you .. only better.

So it's funny that you state everything correctly in the first paragraph and
yet ask the third paragraph.

So it is equal to reinventing the wheel by creating your rudimentary and not
so sexy connection pooling architecture - that is a problem, other than
that, what problems it may cause ///

Unecessary object sitting on IIS
Concurrent requests getting blocked, parallel datareaders not working (if
you think you won't have parallel data readers, think again)
Locking issues between different requests due that you may get Unpredictable
behavior
Scalability issues (what do you do, open another connection when your app
gets overloaded?)
.....

The list is endless. IMO you shouldn't do that, leave it to ADO.NET to pool
your connections. And it's not like the SqlConnection object occupies a lot
of memory so just use and throw instances of that, that is what GC is for
anyway.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
Back
Top