Database Connection Management

Discussion in 'ASP .Net' started by OL, Nov 14, 2004.

  1. OL

    OL Guest

    Hello All,

    I need help understanding DB connection mgmt.

    Scenario:

    - 3 separate Web application
    - IIS 5 or 6
    - dynamic pages for most part
    - DB Backend is Adaptive server Anywhere from sybase (max 10 concurrent
    connections)
    - Single machine: win 2k server + IIS + DB engine (80GB HDD, P4 2.8GHz, 1GB
    RAM)

    10-11k users /day (each downloads as many as 10 - 12 dynamic pages/forms)

    Using ODBC for DB connections (and queries, updates, deletes etc...).
    This functionality is in a dll which I initialize in "global.asax"
    "App_start" event, for each web application.

    The webforms call functions in this dll. Queries are returned in a
    datastore. Actions (updates, deletes etc...) return success/fail code.

    1. I am concerned about deadlocks
    2. would like to limit:
    web app 1 to max 5 concurrent connection
    web app 2 to max 3 concurrent connection
    web app 3 to max 1 connection

    leaving 1 connection for admin purpose.


    not sure if this matters but the calls inside the dll are like so:
    Queries
    1. getDS(ByVal selectStr As String, ByRef ds As DataSet) As DataSet
    getDS calls DoAdapterCreate(selectStr, objDA, cn)
    objDA.Fill(ds)
    Me.DoAdapterDestroy(objDA, cn)
    return DS

    2. DoAdapterCreate(selectStr, objDA, cn)
    DoAdapterCreate calls:
    doConnect(cn) - which returns a connected ODBC Connection to
    DoAdapterCreate
    DoAdapterCreate returns objDA (an OdbcDataAdapter)

    3. Me.DoAdapterDestroy(objDA, cn)
    objDA.Dispose()
    objDA = Nothing
    DoDisConnect(cn) closes CN and dispose of connection object

    Do I need to set a Application("_connectionCount") variable? and make sure
    count is not > then x (where x is number of allowed connections)

    NonQueries
    Operate in simmilar fashion call mades from webforms to dll

    TIA

    OL
     
    OL, Nov 14, 2004
    #1
    1. Advertising

  2. OL

    Scott Allen Guest

    Hi OL:

    I'm afraid I do not know anything about Adaptive Server, but a couple
    things came to mind about your post.

    Some data providers allow you to specify the max # of connections to
    use in the connection string, you might want to check the Sybase docs
    to see if they do this.

    If not, you could use a synchronization object known as a semaphore. A
    semaphore allows from 1 .. n threads access to a protected resource,
    for your scenario you could set n at 3 or 5 depending on the web app.
    Unfortuantely there is no semaphore class provided by .NET in 1.x, but
    there are semaphore functions you can PInvoke in Win32. See for
    instance: http://pinvoke.net/default.aspx/kernel32.CreateSemaphore

    Also, make certain you are closing your connection as soon as
    possible. You probably want to put your code inside a try / catch /
    finally block to ensure DoDisConnect happens 100% of the time - even
    if something else blows up with an exception.

    --
    Scott
    http://www.OdeToCode.com/blogs/scott/

    On Sun, 14 Nov 2004 10:35:08 -0500, "OL" <> wrote:

    >Hello All,
    >
    >I need help understanding DB connection mgmt.
    >
    >Scenario:
    >
    >- 3 separate Web application
    >- IIS 5 or 6
    >- dynamic pages for most part
    >- DB Backend is Adaptive server Anywhere from sybase (max 10 concurrent
    >connections)
    >- Single machine: win 2k server + IIS + DB engine (80GB HDD, P4 2.8GHz, 1GB
    >RAM)
    >
    >10-11k users /day (each downloads as many as 10 - 12 dynamic pages/forms)
    >
    >Using ODBC for DB connections (and queries, updates, deletes etc...).
    >This functionality is in a dll which I initialize in "global.asax"
    >"App_start" event, for each web application.
    >
    >The webforms call functions in this dll. Queries are returned in a
    >datastore. Actions (updates, deletes etc...) return success/fail code.
    >
    >1. I am concerned about deadlocks
    >2. would like to limit:
    > web app 1 to max 5 concurrent connection
    > web app 2 to max 3 concurrent connection
    > web app 3 to max 1 connection
    >
    >leaving 1 connection for admin purpose.
    >
    >
    >not sure if this matters but the calls inside the dll are like so:
    >Queries
    >1. getDS(ByVal selectStr As String, ByRef ds As DataSet) As DataSet
    > getDS calls DoAdapterCreate(selectStr, objDA, cn)
    > objDA.Fill(ds)
    > Me.DoAdapterDestroy(objDA, cn)
    > return DS
    >
    >2. DoAdapterCreate(selectStr, objDA, cn)
    > DoAdapterCreate calls:
    > doConnect(cn) - which returns a connected ODBC Connection to
    >DoAdapterCreate
    > DoAdapterCreate returns objDA (an OdbcDataAdapter)
    >
    >3. Me.DoAdapterDestroy(objDA, cn)
    > objDA.Dispose()
    > objDA = Nothing
    > DoDisConnect(cn) closes CN and dispose of connection object
    >
    >Do I need to set a Application("_connectionCount") variable? and make sure
    >count is not > then x (where x is number of allowed connections)
    >
    >NonQueries
    >Operate in simmilar fashion call mades from webforms to dll
    >
    >TIA
    >
    >OL
    >
     
    Scott Allen, Nov 15, 2004
    #2
    1. Advertising

  3. OL

    OL Guest

    Hello Scott,

    Thank you for responding to my post.

    First thanx for the "try / catch / finally" reminder, I will correct the
    code as it requires correcting...

    Now as to managing the connections.
    From your reply I understand that I still MUST manage the connections usage
    my self.

    What I am not clear about is the general idea on how it's done.
    Do I do "CreateSemaphore" and let it manage the connections?
    Do I create an application var for "used connection count" and then from
    each session do
    a app.lock/update count/app.unlock (then use connection if available or wait
    for connection if not available )?

    I guess I can serialize nonQuery action to prevent deadlocks (1 connection)
    and use the other connections for Queries.

    I guess what I'm missing is the general concept of where to put which code,
    what does dotnet manage for me (connection pool) etc. Is there an article or
    KB out there , that you know of, that I can read

    thanks again,

    OL





    "Scott Allen" <bitmask@[nospam].fred.net> wrote in message
    news:...
    > Hi OL:
    >
    > I'm afraid I do not know anything about Adaptive Server, but a couple
    > things came to mind about your post.
    >
    > Some data providers allow you to specify the max # of connections to
    > use in the connection string, you might want to check the Sybase docs
    > to see if they do this.
    >
    > If not, you could use a synchronization object known as a semaphore. A
    > semaphore allows from 1 .. n threads access to a protected resource,
    > for your scenario you could set n at 3 or 5 depending on the web app.
    > Unfortuantely there is no semaphore class provided by .NET in 1.x, but
    > there are semaphore functions you can PInvoke in Win32. See for
    > instance: http://pinvoke.net/default.aspx/kernel32.CreateSemaphore
    >
    > Also, make certain you are closing your connection as soon as
    > possible. You probably want to put your code inside a try / catch /
    > finally block to ensure DoDisConnect happens 100% of the time - even
    > if something else blows up with an exception.
    >
    > --
    > Scott
    > http://www.OdeToCode.com/blogs/scott/
    >
    > On Sun, 14 Nov 2004 10:35:08 -0500, "OL" <> wrote:
    >
    >>Hello All,
    >>
    >>I need help understanding DB connection mgmt.
    >>
    >>Scenario:
    >>
    >>- 3 separate Web application
    >>- IIS 5 or 6
    >>- dynamic pages for most part
    >>- DB Backend is Adaptive server Anywhere from sybase (max 10 concurrent
    >>connections)
    >>- Single machine: win 2k server + IIS + DB engine (80GB HDD, P4 2.8GHz,
    >>1GB
    >>RAM)
    >>
    >>10-11k users /day (each downloads as many as 10 - 12 dynamic pages/forms)
    >>
    >>Using ODBC for DB connections (and queries, updates, deletes etc...).
    >>This functionality is in a dll which I initialize in "global.asax"
    >>"App_start" event, for each web application.
    >>
    >>The webforms call functions in this dll. Queries are returned in a
    >>datastore. Actions (updates, deletes etc...) return success/fail code.
    >>
    >>1. I am concerned about deadlocks
    >>2. would like to limit:
    >> web app 1 to max 5 concurrent connection
    >> web app 2 to max 3 concurrent connection
    >> web app 3 to max 1 connection
    >>
    >>leaving 1 connection for admin purpose.
    >>
    >>
    >>not sure if this matters but the calls inside the dll are like so:
    >>Queries
    >>1. getDS(ByVal selectStr As String, ByRef ds As DataSet) As DataSet
    >> getDS calls DoAdapterCreate(selectStr, objDA, cn)
    >> objDA.Fill(ds)
    >> Me.DoAdapterDestroy(objDA, cn)
    >> return DS
    >>
    >>2. DoAdapterCreate(selectStr, objDA, cn)
    >> DoAdapterCreate calls:
    >> doConnect(cn) - which returns a connected ODBC Connection to
    >>DoAdapterCreate
    >> DoAdapterCreate returns objDA (an OdbcDataAdapter)
    >>
    >>3. Me.DoAdapterDestroy(objDA, cn)
    >> objDA.Dispose()
    >> objDA = Nothing
    >> DoDisConnect(cn) closes CN and dispose of connection object
    >>
    >>Do I need to set a Application("_connectionCount") variable? and make
    >>sure
    >>count is not > then x (where x is number of allowed connections)
    >>
    >>NonQueries
    >>Operate in simmilar fashion call mades from webforms to dll
    >>
    >>TIA
    >>
    >>OL
    >>

    >
     
    OL, Nov 15, 2004
    #3
  4. OL

    Scott Allen Guest

    Hi OL:

    You might want to check with Sybase first to see what their
    documentation has. Some of these companies oferring thier own ADO.NET
    data providers have some pretty quirky functionality.

    Keeping track of the number of connections used would involve a
    counter and a lock, this is essentially what a semaphore does.
    Hopefully Sybase can provide this for you, but if not you'll need to
    implement something inside of the methods where you connect and create
    the datasets. Locking is dangerous in a web app if you don't have
    experience writing that kind of code, hopefully you can avoid it.

    This document is not specific to Sybase but it has a plethora of good
    information and links:

    ..NET Data Access Architecture Guide
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag.asp

    Particularly the sections "Managing database connections" and "
    Error handling".

    --
    Scott
    http://www.OdeToCode.com/blogs/scott/

    On Mon, 15 Nov 2004 11:14:00 -0500, "OL" <> wrote:

    >Hello Scott,
    >
    >Thank you for responding to my post.
    >
    >First thanx for the "try / catch / finally" reminder, I will correct the
    >code as it requires correcting...
    >
    >Now as to managing the connections.
    >From your reply I understand that I still MUST manage the connections usage
    >my self.
    >
    >What I am not clear about is the general idea on how it's done.
    >Do I do "CreateSemaphore" and let it manage the connections?
    >Do I create an application var for "used connection count" and then from
    >each session do
    >a app.lock/update count/app.unlock (then use connection if available or wait
    >for connection if not available )?
    >
    >I guess I can serialize nonQuery action to prevent deadlocks (1 connection)
    >and use the other connections for Queries.
    >
    >I guess what I'm missing is the general concept of where to put which code,
    >what does dotnet manage for me (connection pool) etc. Is there an article or
    >KB out there , that you know of, that I can read
    >
    >thanks again,
    >
    >OL
    >
    >
    >
    >
    >
    >"Scott Allen" <bitmask@[nospam].fred.net> wrote in message
    >news:...
    >> Hi OL:
    >>
    >> I'm afraid I do not know anything about Adaptive Server, but a couple
    >> things came to mind about your post.
    >>
    >> Some data providers allow you to specify the max # of connections to
    >> use in the connection string, you might want to check the Sybase docs
    >> to see if they do this.
    >>
    >> If not, you could use a synchronization object known as a semaphore. A
    >> semaphore allows from 1 .. n threads access to a protected resource,
    >> for your scenario you could set n at 3 or 5 depending on the web app.
    >> Unfortuantely there is no semaphore class provided by .NET in 1.x, but
    >> there are semaphore functions you can PInvoke in Win32. See for
    >> instance: http://pinvoke.net/default.aspx/kernel32.CreateSemaphore
    >>
    >> Also, make certain you are closing your connection as soon as
    >> possible. You probably want to put your code inside a try / catch /
    >> finally block to ensure DoDisConnect happens 100% of the time - even
    >> if something else blows up with an exception.
    >>
    >> --
    >> Scott
    >> http://www.OdeToCode.com/blogs/scott/
    >>
    >> On Sun, 14 Nov 2004 10:35:08 -0500, "OL" <> wrote:
    >>
    >>>Hello All,
    >>>
    >>>I need help understanding DB connection mgmt.
    >>>
    >>>Scenario:
    >>>
    >>>- 3 separate Web application
    >>>- IIS 5 or 6
    >>>- dynamic pages for most part
    >>>- DB Backend is Adaptive server Anywhere from sybase (max 10 concurrent
    >>>connections)
    >>>- Single machine: win 2k server + IIS + DB engine (80GB HDD, P4 2.8GHz,
    >>>1GB
    >>>RAM)
    >>>
    >>>10-11k users /day (each downloads as many as 10 - 12 dynamic pages/forms)
    >>>
    >>>Using ODBC for DB connections (and queries, updates, deletes etc...).
    >>>This functionality is in a dll which I initialize in "global.asax"
    >>>"App_start" event, for each web application.
    >>>
    >>>The webforms call functions in this dll. Queries are returned in a
    >>>datastore. Actions (updates, deletes etc...) return success/fail code.
    >>>
    >>>1. I am concerned about deadlocks
    >>>2. would like to limit:
    >>> web app 1 to max 5 concurrent connection
    >>> web app 2 to max 3 concurrent connection
    >>> web app 3 to max 1 connection
    >>>
    >>>leaving 1 connection for admin purpose.
    >>>
    >>>
    >>>not sure if this matters but the calls inside the dll are like so:
    >>>Queries
    >>>1. getDS(ByVal selectStr As String, ByRef ds As DataSet) As DataSet
    >>> getDS calls DoAdapterCreate(selectStr, objDA, cn)
    >>> objDA.Fill(ds)
    >>> Me.DoAdapterDestroy(objDA, cn)
    >>> return DS
    >>>
    >>>2. DoAdapterCreate(selectStr, objDA, cn)
    >>> DoAdapterCreate calls:
    >>> doConnect(cn) - which returns a connected ODBC Connection to
    >>>DoAdapterCreate
    >>> DoAdapterCreate returns objDA (an OdbcDataAdapter)
    >>>
    >>>3. Me.DoAdapterDestroy(objDA, cn)
    >>> objDA.Dispose()
    >>> objDA = Nothing
    >>> DoDisConnect(cn) closes CN and dispose of connection object
    >>>
    >>>Do I need to set a Application("_connectionCount") variable? and make
    >>>sure
    >>>count is not > then x (where x is number of allowed connections)
    >>>
    >>>NonQueries
    >>>Operate in simmilar fashion call mades from webforms to dll
    >>>
    >>>TIA
    >>>
    >>>OL
    >>>

    >>

    >
    >
     
    Scott Allen, Nov 15, 2004
    #4
    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. Tomasz Kaszuba

    Re: Data Base connection string management

    Tomasz Kaszuba, Jun 30, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    452
    Tomasz Kaszuba
    Jun 30, 2003
  2. Floris van Haaster

    Project management / bug management

    Floris van Haaster, Sep 23, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    1,266
    Jon Paal
    Sep 23, 2005
  3. Mythran
    Replies:
    5
    Views:
    5,001
    Mythran
    Oct 5, 2005
  4. pouet
    Replies:
    2
    Views:
    805
    Will Hartung
    Jul 30, 2004
  5. Ted
    Replies:
    3
    Views:
    5,679
Loading...

Share This Page