Database Connection Management

[OL]

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]

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/kern...reateSemaphore

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" <no****@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

[OL]

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:m9********************************@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/kern...reateSemaphore

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" <no****@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

[Scott Allen]

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/de.../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" <no****@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:m9********************************@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/kern...reateSemaphore

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" <no****@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