How Many SQL Connections Should I Use?

[db.guru]

Hello,

I was wondering what the best practices are as far as how many
connections one should use for a data-driven app. Here's my situation:
I have a business object that I load data into, this object also has 3
collections which I also load data into (via their own method). Right
now each method has its own connection that uses a datareader to add
the items to the collection. I can't use the same connection between
the parent object and my 3 collections because datareaders require
their own connection. Is it ok to open 4 seperate connections just to
load one object of data?

What is the best practice? Anybody have any links where this situation
is discussed? Any help would be appreciated. Thanks!

[Marina Levit [MVP]]

Once each collection finish loading its data - wouldn't it close the
datareader? In which case, you would never have a situation where code was
trying to use a connection that still had an active datareader on it.

Regardless of this, each method that needs to load data should open up a
connection, get its data, and close the connection. The connection should
not hang around in an open state if it's not being used.

"db.guru" <aa*********@gmail.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

Hello,

I was wondering what the best practices are as far as how many
connections one should use for a data-driven app. Here's my situation:
I have a business object that I load data into, this object also has 3
collections which I also load data into (via their own method). Right
now each method has its own connection that uses a datareader to add
the items to the collection. I can't use the same connection between
the parent object and my 3 collections because datareaders require
their own connection. Is it ok to open 4 seperate connections just to
load one object of data?

What is the best practice? Anybody have any links where this situation
is discussed? Any help would be appreciated. Thanks!

[db.guru]

Well, the collection loading happens within the main object, which uses
a connection and a datareader to load its own data. I suppose I could
close the main object's datareader before loading each collection. (not
sure why I didn't consider that initially...).

So it's not too resource intensive to open and close a connection for
each method that loads data?

[Gman]

My understanding (and I would welcome being corrected) is that if you
open a new connection using the same connection string as an existing,
open connection then connection pooling kicks in. Which would mean that
you wouldn't be opening a new connection, rather using an existing one.

E.g.

- Open Connection_A and use it for something.
- Meanwhile Open Connection_B (which will actually use the same
connection as Open Connection_A).
- Close Connection_B - A is in use so it stays open
- Close Connection_A - no more connections in use so it closes properly

Now, if you are frequently opening and then closing just one connection,
I don't quite see how connection pooling can help. I've tried connecting
to Access (albeit with ADO 2.7 not ADO.NET) and noticed it's
significantly slower if you keep opening and reopening a connection
rather than keeping it open.

I haven't tested it yet with SQL Server (I shall tomorrow) but I have
been informed that when you connect to Oracle (again not .NET) the
server assigns resources to that connection. If you close and reopen
just one connection (i.e. avoiding pooling) you take up fresh resources
from the server since it doesn't tidy up instantly.

Again: this is my understanding. Someone please speak up if this is
nonsense.

db.guru wrote:

Well, the collection loading happens within the main object, which uses
a connection and a datareader to load its own data. I suppose I could
close the main object's datareader before loading each collection. (not
sure why I didn't consider that initially...).

So it's not too resource intensive to open and close a connection for
each method that loads data?