ADO.NET connection pooling is on by default.  The connection must be closed
or a call to Dispose must be made to release the connection back to the
pool.  In C# use the "using" clause.

sqlConnection uses name/value pairs to determine the pool and it is on by
default, but you can turn it off with Pooling 'False'.  The Enlist attribute
is also set to 'True', and your connection automagically enlists you in a
transaction if your context has a transaction.

So no, 5 clients does not equate to 5 connections, unless you are holding
onto the connections for a long time and not releasing them back to the
pool.  The model is the same as it used to be, create/open/close within
scope.  The quicker you release it, the quicker it is back in the pool.

Most of the time you won't be using open and close though because the
Command object opens and closes the connection for you.  The Fill method
does that.  There are pitfalls to that though, like blocking.

ADO.NET pools are based on attributes just like ADO, but the pool is also
subdivided into sub-divisions based on transactions.  A transaction is not
based on a connection, and connections can be closed without effecting the
state of the transaction.  Therefore, the model described above works best
when used on a per method basis.  There is no need to pass the connection
around.  In the next method, when you create the connection, it simply looks
for a pool of connections with those attributes grabs one and returns or
creates one if the pool does not exist.  You can commit and abort
transactions at a higher level even though the connections are all closed.

If you are not in COM+ you don't have a context associating transactions
when you open your connections, so you can create one using BeginTransaction
on the Connection Object, although you will have to control everything.

----- Original Message -----
From: "franklin gray" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, May 22, 2002 2:24 PM
Subject: Re: [ADVANCED-DOTNET] Help Architecting A Middle Tier


I have a questions about connection pooling.

For connection pooling to work, doesn't the objects that create the
connection be working in a Com+ environment?

With my current setup, I have Web Services as the backend of my app so that
I can go through firewalls.  My web services create objects and execute
methods of that object such as Save or Retrieve.  In the methods, I create
an object that contains a sqlConnection and I pass this connection object
around to the commands to perform the process.  This is not in a Com+
environment (yet).  Do you think this will cause much more of a strain on
the server?

If I have 5 clients that are retrieving some data, I have 5 connections
open, right?  Right now the SQL Server is on the same machine as the Web
Service, so we don't have much network traffic dealing with the connections,
but we trying to design it to be scalable so that some users will use
different DBs and different Servers when we get into the hosting phase.  I
wonder how much of a problem that will be.

You can read messages from the Advanced DOTNET archive, unsubscribe from
Advanced DOTNET, or
subscribe to other DevelopMentor lists at http://discuss.develop.com.

You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced 
DOTNET, or
subscribe to other DevelopMentor lists at http://discuss.develop.com.

Reply via email to