Hi all,

I have some questions in regard to ASP.net and ADO.net.

Is it resource extensive, to open a connection in Session_Start method in
global.asax [and close when session stops] or to open connection whenever I
need it?

I intend to open 2 db connection to 2 sql servers.

Cheers

Open it whenver you need it , just make sure connection pooling is on and
make sure the connection is closed as soon as you need it.
Show quoteHide quote

On Sun, 5 Feb 2006 00:39:15 +1100, "Jacky Ngew"
<jackyngew#@#hotmail.com> wrote:

It's never a good idea to leave connections open when you don't need
them.

Open your connection, use it and close it all in the same action,
every time.

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com

My advice would be to not reinvent connection pooling by putting them in
session (or anything similar).

The right design would be, Open as late as possible and close as early as
you can - and then liberally use SqlConnection instances which spend most of
their time closed.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________


Show quoteHide quote

Jacky,

If you use a dataadapter it is not even needed that you open the connection.
The Dataadapter does that for you . Be aware that if you open it, you would
close it as soon as possible and consequent. (By using there where it exist
the "using" and in VB.Net 2002/2003 the finaly block)

I hope this helps,

Cor

hello jacky,

connection is resource extensive and as my personal practice I open
connection as late as possible and close it as early as possible. The
reason for this is scalability which measn you allow you application to
be used by as many users as possible.


look at it this way, if your server allows 10 concurrent connections
only and your code leaves those connections opened. If you code that on
Session_Start and Session_End, it means you only allow 10 connections
exclusive to 10 user sessions. It means in worst cases, other users may
not be able to connect and ur not fully utilizing your servers.

connection pooling plays important role here since you dnt have to
re-create existing connections if you are acessing the sama db server
and database.

regards,
rodel e. dagumampan
http://community.devpinoy.org/blogs/dehranph

OleDbException
dataAdapter not updating - but sqlcommand works OK
Multiple Active Result Sets (MARS)
How to bend ADO.Net to my liking...
performance of single database vs multiple databases