Connection opening and closing

[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.

[José Joye]

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" <ma**********@gmail.coma écrit dans le message de news:
11**********************@j4g2000prf.googlegroups.c om...

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.

[Scott M.]

>Just keep in mind that not all dBs have connection pooling (e.g.: SQL

>Server Compact Edition does not have it!).

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

[Ravimama]

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

[José Joye]

Look at:
http://groups.google.ch/group/micros...400462d14ca085

- José
"Scott M." <s-***@nospam.nospama écrit dans le message de news:
eZ*************@TK2MSFTNGP05.phx.gbl...

Just keep in mind that not all dBs have connection pooling (e.g.: SQL
Server Compact Edition does not have it!).

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

[Scott M.]

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" <jo*************************************@bluewin.c hwrote in
message news:ed*************@TK2MSFTNGP04.phx.gbl...

Look at:
http://groups.google.ch/group/micros...400462d14ca085

- José
"Scott M." <s-***@nospam.nospama écrit dans le message de news:
eZ*************@TK2MSFTNGP05.phx.gbl...

>Just keep in mind that not all dBs have connection pooling (e.g.: SQL
Server Compact Edition does not have it!).

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