[quoted text, click to view]

I thought connection pooling was a feature of the CLR, not the database?

Hi People,

I have been coding in vb.net and c# for last six months, I am new to
coding and we are using 3 tier architecture. In the DAL in each and
every method I open the connection and close it in the finally block
of the try catch block, if I am not using a DataAdapter.
1. Is this correct?
2. should I be using only the dataadapter always, like for insertion
of single record, updation of single record, etc. The general practice
is to use the Dataadapter to only when a DataTable or Dataset comes
into picture.
3. Will there be any performance issues?

This question I have may be silly, but I need this. I want to have the
right code.
:)

Thanks in advance.

Hi Jose,

thanks for the reply, really some matter to think about.


"--Personally, I let the "Business Layer" decide about when to open/
close the
connection (and Commit/RollBack transactions). To do so, I overload
the DAL
constructor to allow specifying in which context I want to use this
DAL
object (transactional mode, directly open the connection, combine DAL
object
with an already existing DAL object (and therefore use the same
connection),...). Doing so, I normally have a better reusability and a
better speed. --"

Could you plz help me how I can do the above. any Sample code will be
very helpful.


Ravi

My 2 cents:

If in your DAL, for each and every methods, you open and close the
connection, this means you cannot combine several DAL methods together to
include them in a transactional action.
Personally, I let the "Business Layer" decide about when to open/close the
connection (and Commit/RollBack transactions). To do so, I overload the DAL
constructor to allow specifying in which context I want to use this DAL
object (transactional mode, directly open the connection, combine DAL object
with an already existing DAL object (and therefore use the same
connection),...). Doing so, I normally have a better reusability and a
better speed.

Keeping the connection open as short as possible is a best practice.
Assuming the dB you are targetting has a connection pooling, the cost of
obtaining new connection is really low. Just keep in mind that not all dBs
have connection pooling (e.g.: SQL Server Compact Edition does not have
it!).

Regarding the point #2, I would agree with you


- José

"Ravimama" <mailto.lravi@gmail.com> a écrit dans le message de news:
1184933365.541241.221130@j4g2000prf.googlegroups.com...
[quoted text, click to view]

Look at:
http://groups.google.ch/group/microsoft.public.sqlserver.ce/browse_frm/thread/4ee6782cf10cd75d/02400462d14ca085?lnk=st&q=&rnum=2&hl=fr#02400462d14ca085

- José


"Scott M." <s-mar@nospam.nospam> a écrit dans le message de news:
eZ$sf8syHHA.600@TK2MSFTNGP05.phx.gbl...
[quoted text, click to view]

Seems to me that pooling is implemented by the provider, not the database
itself. I think that thread indicates that indirectly by talking about the
provider.

A database supports connections to it (and has some maximum limit), but I
belive it is the provider that governs how the connections are managed from
the calling application, not the database itself.




"José Joye" <jose.joye__KILLTHESPAMS__SMAPSEHTLLIK__@bluewin.ch> wrote in
message news:ed1GKJtyHHA.748@TK2MSFTNGP04.phx.gbl...
[quoted text, click to view]