W
Tue Jun 28 23:33:42 CDT 2005
> 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)
--
W.G. Ryan MVP (Windows Embedded)
TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"TaeHo Yoo" <yootaeho@yahoo.com> wrote in message
news:O2c8eZEfFHA.2496@TK2MSFTNGP15.phx.gbl...
>
>
> Hi guys,
>
>
> *** Sent via Developersdex
http://www.developersdex.com ***