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.

 

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

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

 

[PeterKellner]

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

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

 

[Damien]

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