How to use one database connection object per page request

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

re: How to use one database connection object per page request

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" <arsal__@hotmail.com> wrote in message news:eC3ZlkCfGHA.1272@TK2MSFTNGP03.phx.gbl...[color=blue]
> 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
>
>
>[/color]

re: How to use one database connection object per page request

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

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

re: How to use one database connection object per page request

Arsalan Ahmad wrote:[color=blue]
> 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[/color]

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

re: How to use one database connection object per page request

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" <Damien_The_Unbeliever@hotmail.com> wrote in message
news:1148199924.229929.308770@38g2000cwa.googlegro ups.com...[color=blue]
> Arsalan Ahmad wrote:[color=green]
>> 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[/color]
>
> 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
>[/color]

re: How to use one database connection object per page request

On Sun, 21 May 2006 20:22:23 +0200, "Arsalan Ahmad"
<arsal__@hotmail.com> wrote:
[color=blue]
>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" <Damien_The_Unbeliever@hotmail.com> wrote in message
>news:1148199924.229929.308770@38g2000cwa.googlegr oups.com...[color=green]
>> Arsalan Ahmad wrote:[color=darkred]
>>> 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[/color]
>>
>> 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
>>[/color]
>[/color]

My understanding is that if you put connectionpooling 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

re: How to use one database connection object per page request

Arsalan Ahmad wrote:[color=blue]
> 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
>[/color]
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.Current.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
[color=blue]
> "Damien" <Damien_The_Unbeliever@hotmail.com> wrote in message
> news:1148199924.229929.308770@38g2000cwa.googlegro ups.com...[color=green]
> > Arsalan Ahmad wrote:[color=darkred]
> >> 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[/color]
> >
> > 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
> >[/color][/color]