Sharing database connection with controls

Discussion in 'ASP .Net' started by Mike P2, Jun 3, 2007.

  1. Mike P2

    Mike P2 Guest

    Hi. I'm writing controls that have to query the database, and it
    bothers me that I might have several of these controls on a page that
    each create, open, and close their own connection with the same
    connection string, and the page class has a connection object too.

    I'm using the code-behind way of doing things, and I considered
    leaving the database connection open as a field in the page's class
    and somehow letting the controls use that same connection, but then I
    want to be sure that every page does have a database connection
    object.

    What is the best way of sharing the database connection object between
    the page's class and the controls' classes? Can I create a connection
    object on the page automatically some way in global.asax?

    Thanks,
    Mike PII
     
    Mike P2, Jun 3, 2007
    #1
    1. Advertising

  2. If you use exactly the same connection string, asp.net (rather ado.net for
    this matter) will automatically share the same connection between all your
    objects. This is called "connection pooling". The best practice is to put
    the code to open connection just before the database access operation and to
    close it as soon as you finish the operation. Ado.net will take care about
    actual opening and closing connections. Trying to keep connection open will
    result in degrading performance.

    --
    Eliyahu Goldin,
    Software Developer & Consultant
    Microsoft MVP [ASP.NET]
    http://msmvps.com/blogs/egoldin
    http://usableasp.net


    "Mike P2" <> wrote in message
    news:...
    > Hi. I'm writing controls that have to query the database, and it
    > bothers me that I might have several of these controls on a page that
    > each create, open, and close their own connection with the same
    > connection string, and the page class has a connection object too.
    >
    > I'm using the code-behind way of doing things, and I considered
    > leaving the database connection open as a field in the page's class
    > and somehow letting the controls use that same connection, but then I
    > want to be sure that every page does have a database connection
    > object.
    >
    > What is the best way of sharing the database connection object between
    > the page's class and the controls' classes? Can I create a connection
    > object on the page automatically some way in global.asax?
    >
    > Thanks,
    > Mike PII
    >
     
    Eliyahu Goldin, Jun 3, 2007
    #2
    1. Advertising

  3. Mike P2

    Mark Rae Guest

    "Mike P2" <> wrote in message
    news:...

    > I considered leaving the database connection


    As Eliyahu mentions, this is one of the worst things you can do in terms of
    performance and scalability of an ASP.NET app...


    --
    http://www.markrae.net
     
    Mark Rae, Jun 3, 2007
    #3
  4. Mike P2

    Mike P2 Guest

    On Jun 3, 3:46 am, "Eliyahu Goldin"
    <> wrote:
    > If you use exactly the same connection string, asp.net (rather ado.net for
    > this matter) will automatically share the same connection between all your
    > objects. This is called "connection pooling". The best practice is to put
    > the code to open connection just before the database access operation and to
    > close it as soon as you finish the operation. Ado.net will take care about
    > actual opening and closing connections. Trying to keep connection open will
    > result in degrading performance.
    >
    > --
    > Eliyahu Goldin,
    > Software Developer & Consultant
    > Microsoft MVP [ASP.NET]http://msmvps.com/blogs/egoldinhttp://usableasp.net


    Cool. So is the stuff in the System.Data.SqlClient Ado.net? Or do I
    have to use those OLE classes?

    Thanks for your replies,
    Mike PII
     
    Mike P2, Jun 3, 2007
    #4
  5. System.Data.SqlClient is fine.

    --
    Eliyahu Goldin,
    Software Developer & Consultant
    Microsoft MVP [ASP.NET]
    http://msmvps.com/blogs/egoldin
    http://usableasp.net


    "Mike P2" <> wrote in message
    news:...
    > On Jun 3, 3:46 am, "Eliyahu Goldin"
    > <> wrote:
    >> If you use exactly the same connection string, asp.net (rather ado.net
    >> for
    >> this matter) will automatically share the same connection between all
    >> your
    >> objects. This is called "connection pooling". The best practice is to put
    >> the code to open connection just before the database access operation and
    >> to
    >> close it as soon as you finish the operation. Ado.net will take care
    >> about
    >> actual opening and closing connections. Trying to keep connection open
    >> will
    >> result in degrading performance.
    >>
    >> --
    >> Eliyahu Goldin,
    >> Software Developer & Consultant
    >> Microsoft MVP
    >> [ASP.NET]http://msmvps.com/blogs/egoldinhttp://usableasp.net

    >
    > Cool. So is the stuff in the System.Data.SqlClient Ado.net? Or do I
    > have to use those OLE classes?
    >
    > Thanks for your replies,
    > Mike PII
    >
     
    Eliyahu Goldin, Jun 3, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Atif Jalal

    Database connection sharing

    Atif Jalal, Aug 8, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    1,935
    Steve C. Orr, MCSD
    Aug 8, 2003
  2. David Dvali

    Sharing DB connection

    David Dvali, Jul 13, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    424
    Kevin Spencer
    Jul 13, 2005
  3. Mythran
    Replies:
    5
    Views:
    5,000
    Mythran
    Oct 5, 2005
  4. Replies:
    1
    Views:
    332
    Daniel Dittmar
    Feb 1, 2006
  5. Andrew DeFaria
    Replies:
    7
    Views:
    338
    Tad J McClellan
    Apr 2, 2008
Loading...

Share This Page