Connection opening and closing

  • Thread starter Thread starter Ravimama
  • Start date Start date
R

Ravimama

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.
 
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é
 
Just keep in mind that not all dBs have connection pooling (e.g.: SQL
Server Compact Edition does not have it!).
Click to expand...

I thought connection pooling was a feature of the CLR, not the database?
 
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
 
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.
 
Back
Top