Re: Database Connection Management

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Scott Allen (bitmask_at_[nospam)
Date: 11/15/04 

Date: Sun, 14 Nov 2004 20:17:38 -0500

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" <nospam@verizon.net> 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 
>

Relevant Pages

  • Re: Database Connection Management
    ... Hopefully Sybase can provide this for you, but if not you'll need to ... >a app.lock/update count/app.unlock (then use connection if available or wait ... you could use a synchronization object known as a semaphore. ... >> for your scenario you could set n at 3 or 5 depending on the web app. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: VS .net (Windows vs. Web Application)
    ... churn numbers and not employ the server. ... costly, in terms of connection cost, esp. ... For me, a web app is useful, as it only consumes data when using it. ...
    (microsoft.public.vsnet.general)
  • Re: Access DB ASP connection problems
    ... > I'm developing a simple web app in DRW to display some products ... The better technique is to instantiate a connection object: ... new one, ADO will look in the session pool, and, if a connection is ... > to the database and nothing else can access it. ...
    (microsoft.public.inetserver.asp.db)
  • Connection.Open Performance
    ... I've just upgraded my Visual Studio project to version 2005. ... a web app that consumes our own web services to link to MSAS 2000 SP4. ... When I open the connection, it takes between 7 and 20 seconds for the ... ADOMD.net and SOAP SDK. ...
    (microsoft.public.sqlserver.olap)
  • Re: RADiest Client for SQL Server
    ... >> if it really should be a web app. ... One computer, located at the customers ... >> Of course access is going to be the 'RADiest' front end to use but ...
    (microsoft.public.sqlserver.server)