Connection Pooling

[TaeHo Yoo]

Hi guys,

I am not too sure if I understand the connection pooling or not. But I
am implementing an application where in one page I open connections a
few times each time I close connection properly. My question is thanks
to the connection pooling the first opened connection will stay in the
memory and reused for the rest? Am I correct?

If not, my ASP.NET application has 3 tiers. Client application layer
(HTML and code behind), business layer and database layer.

Now on business layer, in most of functions we open a connection and
close.

Something like for instance
Publisher class

public static dataset getPublisherDetails(int publisherid)
{
connection.open();
connection.close();
return resultdataset;
}

public static dataset getItemsByPublisherID(int publisherid)
{
connection.open();
connection.close();
return resultdataset;
}
and in Customer layer

we say
dataset publisherDetails = Publisher.getPublisherDetails(publisherid)
dataset publisherItems = Publisher.getItemsByPublisherID(publisherid)

as you can see we open connection twice here. But this is a very brief
way of describing all layers I actually use. but just assume that
according to our current application architecture it is really hard to
open connection once to process one page. Is it really bad way of
constructing application architecture so that we will have to
reconstructing it or ??

Look forward to hearing from you guru!!!

Thanks

 

[W.G. Ryan eMVP]

I am not too sure if I understand the connection pooling or not. But I

am implementing an application where in one page I open connections a
few times each time I close connection properly. My question is thanks
to the connection pooling the first opened connection will stay in the
memory and reused for the rest? Am I correct?

There will be one physical connection open, yes.

If not, my ASP.NET application has 3 tiers. Client application layer
(HTML and code behind), business layer and database layer.

Now on business layer, in most of functions we open a connection and
close.

Something like for instance
Publisher class

public static dataset getPublisherDetails(int publisherid)
{
connection.open();
connection.close();
return resultdataset;
}

public static dataset getItemsByPublisherID(int publisherid)
{
connection.open();
connection.close();
return resultdataset;
}
and in Customer layer

we say
dataset publisherDetails = Publisher.getPublisherDetails(publisherid)
dataset publisherItems = Publisher.getItemsByPublisherID(publisherid)

as you can see we open connection twice here. But this is a very brief
way of describing all layers I actually use. but just assume that
according to our current application architecture it is really hard to
open connection once to process one page. Is it really bad way of
constructing application architecture so that we will have to
reconstructing it or ??

Look forward to hearing from you guru!!!

Thanks

The main thing to emphasize is that you open and close yoru connections and
in C# (or VB.NET 2005), use the Using statement and/or in Vb.NET a
Try/Catch/Finally to ensure that you're connection is getting closed.

However, If I understand what you'e doing, there's no need to open the
connectoin from two layers. If it's just two layers, than fine, open it from
the business layer, but if you're separating it like this, I'd have a third
tier to handle the Data Access (and add some remoting support just in case
you decide that you want to run each of the layers on something other than
the web server), and just handle all data access rom the DALC. THe page
can call the business layer. The business layer can call the dalc. The dalc
handles all the connectoin stuff and is the only place a connection is
opened. Think of it this way, if you changed the back end, you really
wouldn't want to change the Business layer in it's entirety if possible
right? (Ok, I'll admit, to totally do this it would take a little more work
than i'm implying but you catch my drift). Granted, we can really through
in a lot of what ifs in a architecture discussion, but if I understood your
question correctly, I thiink you're on the right track.

As far as opening and closing connections twice though... if you are going
to fire a ton of quereis consecutively, you may want to leave it open
throughout the loop. But pooling is VERY effective and if it's just twice,
well, I doubt you'll notice it either way. The key point is to close that
connection the second you don't need it (remember that a DataAdapter will
open and close the connection for you) and to MAKE SURE IT GETS closed.
This can only be ensured by using a using block or closing it in a finally
(or having the DBA kill all the connections, but that approach has a lot of
collateral damage)

 

[TaeHo Yoo]

Hi Ryan,

Thank you very much for your advice.
Yes, I do have a seperate database access layer (presentation layer,
business layer and database access layer) where all database connection
handling occurs and use finally to close it properly no doubt about that


My question still remains unaswered if I understand your answer
correctly.

In each function in the business layer, to get a dataset I open the
connection in the database access layer where I use DataAdapter and
close the opened connection in the database access layer after geting
the dataset.

Now say the presentation layer calls 5 functions to the business layer
say A,B,C,E,D functions in order and in each function we open and close
the connection in the database access layer. In the scenario, when I try
to open the connection for B function after A function (where obviously
I opened and closed the connection that I used), does the system get the
connection that was used for the function A thanks to the database
connnection pooling or open a fresh connection again for B function?

I really appreciate your help.

Thanks

 

[William \(Bill\) Vaughn]

See >>>

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________