Re: Database Connection Management
From: Scott Allen (bitmask_at_[nospam)
Date: 11/15/04
- Next message: ~~~ .NET Ed ~~~: "Re: Using FireFox 1.0 on new ASP.NET projects brings up authentication dialog when there should be none"
- Previous message: louise raisbeck: "posting to SQL"
- In reply to: OL: "Re: Database Connection Management"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 15 Nov 2004 11:45:22 -0500
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" <nospam@verizon.net> 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:m90gp05i4b3oiafkrqbpvjiq3hf6174r1k@4ax.com... >> 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 >>> >> > >
- Next message: ~~~ .NET Ed ~~~: "Re: Using FireFox 1.0 on new ASP.NET projects brings up authentication dialog when there should be none"
- Previous message: louise raisbeck: "posting to SQL"
- In reply to: OL: "Re: Database Connection Management"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
