How to use one database connection object per page request

[Arsalan Ahmad]

Hi all,

I am creating a website in which in an Item detail page there are a number
of web controls (7 or 8) and what is happening that inside each of control's
Page_Load() function I am creating a database object to query data from
database (using MySQL database). So it means that for each page request I am
using 7 or 8 database connection which is something quite bad as far as
performance is concerned. So please tell me how can change my design to use
only one database connection object per page request? Important thing is I
need to close database connection also once page request is fulfil and if I
dont close database connection then database connection is still there for a
very long time and it could cause bottleneck at my database server for more
new connections.

Thanks,

Arsalan

[Jon Paal]

as each control is loaded, it will open, run, then close the connection. There should be no problem so long as each has the
open/close statements in the code.


"Arsalan Ahmad" <ar*****@hotmai l.com> wrote in message news:eC******** ******@TK2MSFTN GP03.phx.gbl...

Hi all,

I am creating a website in which in an Item detail page there are a number of web controls (7 or 8) and what is happening that
inside each of control's Page_Load() function I am creating a database object to query data from database (using MySQL database).
So it means that for each page request I am using 7 or 8 database connection which is something quite bad as far as performance is
concerned. So please tell me how can change my design to use only one database connection object per page request? Important thing
is I need to close database connection also once page request is fulfil and if I dont close database connection then database
connection is still there for a very long time and it could cause bottleneck at my database server for more new connections.

Thanks,

Arsalan

[Lance]

This good be total off but http://www.asp.net/guidedtour2/s11.aspx

I am new, but i happen to come across the link.

[Damien]

Arsalan Ahmad wrote:

Hi all,

I am creating a website in which in an Item detail page there are a number
of web controls (7 or 8) and what is happening that inside each of control's
Page_Load() function I am creating a database object to query data from
database (using MySQL database). So it means that for each page request I am
using 7 or 8 database connection which is something quite bad as far as
performance is concerned. So please tell me how can change my design to use
only one database connection object per page request? Important thing is I
need to close database connection also once page request is fulfil and if I
dont close database connection then database connection is still there for a
very long time and it could cause bottleneck at my database server for more
new connections.

Thanks,

Arsalan

Which data provider are you using? The OleDbConnection and
OdbcConnection classes both support connection pooling, so if you're
using either of them, it shouldn't be an issue to follow the "open a
connection, do a command, close the connection" model. It looks like
you'll use a lot of connections, but behind the scenes the connection
pool will manage it for you.

Damien

[Arsalan Ahmad]

Hi,

I am using MySQL .NET Connector and I am afraid that it does not support
connection pooling and that is why I think that opening a new connection by
each control in a page request will make it quite slow.

Regards,

Arsalan

"Damien" <Da************ *******@hotmail .com> wrote in message
news:11******** **************@ 38g2000cwa.goog legroups.com...

Arsalan Ahmad wrote:

Hi all,

I am creating a website in which in an Item detail page there are a
number
of web controls (7 or 8) and what is happening that inside each of
control's
Page_Load() function I am creating a database object to query data from
database (using MySQL database). So it means that for each page request I
am
using 7 or 8 database connection which is something quite bad as far as
performance is concerned. So please tell me how can change my design to
use
only one database connection object per page request? Important thing is
I
need to close database connection also once page request is fulfil and if
I
dont close database connection then database connection is still there
for a
very long time and it could cause bottleneck at my database server for
more
new connections.

Thanks,

Arsalan

Which data provider are you using? The OleDbConnection and
OdbcConnection classes both support connection pooling, so if you're
using either of them, it shouldn't be an issue to follow the "open a
connection, do a command, close the connection" model. It looks like
you'll use a lot of connections, but behind the scenes the connection
pool will manage it for you.

Damien

[PeterKellner]

On Sun, 21 May 2006 20:22:23 +0200, "Arsalan Ahmad"
<ar*****@hotmai l.com> wrote:

Hi,

I am using MySQL .NET Connector and I am afraid that it does not support
connection pooling and that is why I think that opening a new connection by
each control in a page request will make it quite slow.

Regards,

Arsalan

"Damien" <Da************ *******@hotmail .com> wrote in message
news:11******* *************** @38g2000cwa.goo glegroups.com.. .

Arsalan Ahmad wrote:

Hi all,

I am creating a website in which in an Item detail page there are a
number
of web controls (7 or 8) and what is happening that inside each of
control's
Page_Load() function I am creating a database object to query data from
database (using MySQL database). So it means that for each page request I
am
using 7 or 8 database connection which is something quite bad as far as
performance is concerned. So please tell me how can change my design to
use
only one database connection object per page request? Important thing is
I
need to close database connection also once page request is fulfil and if
I
dont close database connection then database connection is still there
for a
very long time and it could cause bottleneck at my database server for
more
new connections.

Thanks,

Arsalan

Which data provider are you using? The OleDbConnection and
OdbcConnection classes both support connection pooling, so if you're
using either of them, it shouldn't be an issue to follow the "open a
connection, do a command, close the connection" model. It looks like
you'll use a lot of connections, but behind the scenes the connection
pool will manage it for you.

Damien

My understanding is that if you put connectionpooli ng in your connect
string to mysql, independent of the driver in .net, you will get
connection pooling to the database. Let me know if this is wrong, but
I don't think so.
Peter Kellner
http://peterkellner.net

[Damien]

Arsalan Ahmad wrote:

Hi,

I am using MySQL .NET Connector and I am afraid that it does not support
connection pooling and that is why I think that opening a new connection by
each control in a page request will make it quite slow.

Regards,

Arsalan
In that case, I would suggest (if you want to do one connection per
request), to create a base page which in an early event (say in the
Init event), opens a connection and puts it into
HttpContext.Cur rent.Items, and which closes it in the Unload event.
Then everything that needs a connection in between will retrieve it
from the Items connection.

However, I wouldn't expect this to scale well. You may be better suited
to roll your own connection pool (assuming that your connection string
is always the same, this may not be too onerous), assuming that your
individual operations are independent.

Damien
"Damien" <Da************ *******@hotmail .com> wrote in message
news:11******** **************@ 38g2000cwa.goog legroups.com...

Arsalan Ahmad wrote:

Hi all,

I am creating a website in which in an Item detail page there are a
number
of web controls (7 or 8) and what is happening that inside each of
control's
Page_Load() function I am creating a database object to query data from
database (using MySQL database). So it means that for each page request I
am
using 7 or 8 database connection which is something quite bad as far as
performance is concerned. So please tell me how can change my design to
use
only one database connection object per page request? Important thing is
I
need to close database connection also once page request is fulfil and if
I
dont close database connection then database connection is still there
for a
very long time and it could cause bottleneck at my database server for
more
new connections.

Thanks,

Arsalan

Which data provider are you using? The OleDbConnection and
OdbcConnection classes both support connection pooling, so if you're
using either of them, it shouldn't be an issue to follow the "open a
connection, do a command, close the connection" model. It looks like
you'll use a lot of connections, but behind the scenes the connection
pool will manage it for you.

Damien