To connect to a database.

[jc]

Hi there:

Reading from internet i found that the best way to use a database is
to connect and disconnect on every single database request, so if you
are loading a listbox manually from a database and on the same page,
loading a grid from the database, then the best way is to connect to
database, load the listbox, disconnect, connect again, load the grid
and disconnect.

what is the cause because to reconnect many times is more
efficient?.

I tried running some test on about it,connecting once on page request
is almost 20% more fast that connecting many times.

Also, i checked the number of session open on oracle and in both
method did keep the same number of session. In any case, aspnet will
keep many connections opens (persistent?). May be this method of open
and close for request is efficient with sqlserver but other database.

 

[Patrice]

20 % from what ? Even 100 % wouldn't mean anything. If it's small enough
this is anyway non visible to the end user...

This is not really "more efficient". It just much simpler and it guarantees
that connection are released as soon as possible...

If you keep the connection opn :
- any failure to close the connection could cause problem
- it could be closed later than needed making this connection not available
even though not used
- if you have some processing between those openings, this particular
connection is not available even though not used at this time...

As most often this is a matter of trade off... The thing to check is if
your DB supports connection pooling...

 

[Mark Rae [MVP]]

Reading from internet i found that the best way to use a database is
to connect and disconnect on every single database request, so if you
are loading a listbox manually from a database and on the same page,
loading a grid from the database, then the best way is to connect to
database, load the listbox, disconnect, connect again, load the grid
and disconnect.

That's the generally accepted wisdom, yes...

what is the cause because to reconnect many times is more
efficient?.

Yes, because of a feature of ADO.NET called connection pooling. With web
applications, "little and often" is definitely the way to go...

I tried running some test on about it,connecting once on page request
is almost 20% more fast that connecting many times.

How are you creating / destroying the connections...?

In any case, ASP.NET will keep many connections opens (persistent?).

If your web app has persistent connections with your RDBMS, then you need to
rethink your connectivity method...

 

[Mark Rae [MVP]]

The thing to check is if your DB supports connection pooling...

The OP is using Oracle which definitely does support connection pooling, but
only if accessed correctly:
http://weblogs.asp.net/ngur/archive/2004/01/21/61207.aspx

 

[Leon Mayne]

Reading from internet i found that the best way to use a database is
to connect and disconnect on every single database request, so if you
are loading a listbox manually from a database and on the same page,
loading a grid from the database, then the best way is to connect to
database, load the listbox, disconnect, connect again, load the grid
and disconnect.

Depends on the situation. In most circumstances it is better to just pull
back what you need then close the connection and reopen to get any
additional info. I would certainly close the connection after each query,
but you could have a Populate() method in your code which calls one stored
procedure that returns multiple resultsets for all the information required
for the page, close the connection, and then populate all the controls from
the data. Then you are using the connection for the least amount of time and
not opening and closing connections:

CREATE PROCEDURE uspPopulateOrderPage
@pintOrderId INT
AS
-- Get the listbox data
SELECT
Id, Description
FROM
tblListItems

-- Get the order details
SELECT
CustomerName,
MoreInformation
FROM
tblOrder
WHERE
OrderId = @pintOrderId

 

[jc]

Depends on the situation. In most circumstances it is better to just pull
back what you need then close the connection and reopen to get any
additional info. I would certainly close the connection after each query,
but you could have a Populate() method in your code which calls one stored
procedure that returns multiple resultsets for all the information required
for the page, close the connection, and then populate all the controls from
the data. Then you are using the connection for the least amount of time and
not opening and closing connections:

CREATE PROCEDURE uspPopulateOrderPage
@pintOrderId INT
AS
-- Get the listbox data
SELECT
    Id, Description
FROM
    tblListItems

-- Get the order details
SELECT
    CustomerName,
    MoreInformation
FROM
    tblOrder
WHERE
    OrderId = @pintOrderId

But with this method the code will become a bit spaguetti.

I checked that close() and dispose() in fact don't close the
connection but a consecutive open() will not open a new connection.
So, in both cases, connection are spooled and from the database
viewpoint (oracle), the number of connections is stable.

I think that there aren't a simple method but depend on the specific
case, in my case i will open and close once per page request but still
will left open the option to open/close connection by request basis,
just in case.