Leaking Database Connections from ASP.NET 2 Web App

[Sierra]

Problem:
Database connections are not being reused properly. SP_WHO2 shows upwards
of 200 connections being created per page request. Most connections exist for
60 seconds then close without being reused. A few connections are reused. SQL
System Profiler shows many “Audit Login� and many “RPC: Completed� records
for each page request – often involving the exact same SQL statement called
in exactly the same manner from within a single program loop.

System Description:
• SQL Server 2005 (SP1)
• ASP.NET 2 (2.0.50727)
• IIS 6
• Windows 2003 Server (64bit production, x86 development – both having the
same issue)
• All critical updates applied

Connection String:
• Globally called from web.config
• Never modified
• Provider=SQLNCL I;Data Source=localhos t;Initial Catalog=XDATABA SE;User
Id=XUSERNAME;Pa ssword=XPASSWOR D;DataTypeCompa tibility=80;

Connection Details:
• All database connections connect through a common DB Connection class.
• All database calls are in the form of stored procedures
• All connections are closed and disposed

Page Example:

Dim myOLE As New OLESQL()
myOLE.SetSQL("s pStoredProc " & intSomeParam)
myOLE.SetTableN ame("TableName" )
Dim dsResults As DataSet = myOLE.ExDataSet ()
'Do somthing with the dataset ...

For Each x In xGroup
myOLE.SetSQL("s pStoredProc2 " & x.ToString)
intReturn = myOLE.ExScalar( )

myOLE.SetSQL("s pStoredProc3 " & x.ToString)
myOLE.ExNonQuer y()
Next

myOLE.Close()

Database Connection Class:

Imports Microsoft.Visua lBasic
Imports System.Data.Ole Db
Imports System.Data
Imports System
Imports System.Web.UI.P age

Public Class OLESQL

'Class fields
Public strSQL As String
Public strTableName As String
Public cnMP As OleDbConnection
Public sConnectionStri ng As String =
ConfigurationMa nager.AppSettin gs("MPSQL")

'Initializer
Public Sub New()

cnMP = New OleDbConnection (sConnectionStr ing)
cnMP.Open()

End Sub

'Close the database connection
Public Sub Close()

If cnMP.State = ConnectionState .Open Then
cnMP.Close()
cnMP.Dispose()
cnMP = Nothing
End If

End Sub

'Set the SQL value
Public Sub SetSQL(ByVal inSQL)
strSQL = inSQL
End Sub

'Set the table name value
Public Sub SetTableName(By Val inTableName)
strTableName = inTableName
End Sub

'Execute a non query
Public Sub ExNonQuery()
Dim cmdData As New OleDbCommand(st rSQL, cnMP)
cmdData.Execute NonQuery()
cmdData.Dispose ()
cmdData = Nothing
End Sub

'Execute a scalar function
Public Function ExScalar() As String
Dim cmdData As New OleDbCommand(st rSQL, cnMP)
Dim strOutput As String
strOutput = cmdData.Execute Scalar
Return strOutput
cmdData.Dispose ()
cmdData = Nothing
End Function

'Execute and return a dataset function
Public Function ExDataSet() As DataSet
Dim daGetData As New OleDbDataAdapte r(strSQL, sConnectionStri ng)
Dim dsData As New DataSet()
daGetData.Fill( dsData, strTableName)
Return dsData
daGetData.Dispo se()
daGetData = Nothing
End Function

End Class

[bruce barker \(sqlwork.com\)]

you have shouldn't open a connecton on your constructor. all routines need a
try/catch to close the connection on errors.

-- bruce (sqlwork.com)

"Sierra" <Si****@discuss ions.microsoft. comwrote in message
news:F7******** *************** ***********@mic rosoft.com...

Problem:
Database connections are not being reused properly. SP_WHO2 shows upwards
of 200 connections being created per page request. Most connections exist
for
60 seconds then close without being reused. A few connections are reused.
SQL
System Profiler shows many "Audit Login" and many "RPC: Completed" records
for each page request - often involving the exact same SQL statement
called
in exactly the same manner from within a single program loop.

System Description:
. SQL Server 2005 (SP1)
. ASP.NET 2 (2.0.50727)
. IIS 6
. Windows 2003 Server (64bit production, x86 development - both having the
same issue)
. All critical updates applied

Connection String:
. Globally called from web.config
. Never modified
. Provider=SQLNCL I;Data Source=localhos t;Initial Catalog=XDATABA SE;User
Id=XUSERNAME;Pa ssword=XPASSWOR D;DataTypeCompa tibility=80;

Connection Details:
. All database connections connect through a common DB Connection class.
. All database calls are in the form of stored procedures
. All connections are closed and disposed

Page Example:

Dim myOLE As New OLESQL()
myOLE.SetSQL("s pStoredProc " & intSomeParam)
myOLE.SetTableN ame("TableName" )
Dim dsResults As DataSet = myOLE.ExDataSet ()
'Do somthing with the dataset ...

For Each x In xGroup
myOLE.SetSQL("s pStoredProc2 " & x.ToString)
intReturn = myOLE.ExScalar( )

myOLE.SetSQL("s pStoredProc3 " & x.ToString)
myOLE.ExNonQuer y()
Next

myOLE.Close()

Database Connection Class:

Imports Microsoft.Visua lBasic
Imports System.Data.Ole Db
Imports System.Data
Imports System
Imports System.Web.UI.P age

Public Class OLESQL

'Class fields
Public strSQL As String
Public strTableName As String
Public cnMP As OleDbConnection
Public sConnectionStri ng As String =
ConfigurationMa nager.AppSettin gs("MPSQL")

'Initializer
Public Sub New()

cnMP = New OleDbConnection (sConnectionStr ing)
cnMP.Open()

End Sub

'Close the database connection
Public Sub Close()

If cnMP.State = ConnectionState .Open Then
cnMP.Close()
cnMP.Dispose()
cnMP = Nothing
End If

End Sub

'Set the SQL value
Public Sub SetSQL(ByVal inSQL)
strSQL = inSQL
End Sub

'Set the table name value
Public Sub SetTableName(By Val inTableName)
strTableName = inTableName
End Sub

'Execute a non query
Public Sub ExNonQuery()
Dim cmdData As New OleDbCommand(st rSQL, cnMP)
cmdData.Execute NonQuery()
cmdData.Dispose ()
cmdData = Nothing
End Sub

'Execute a scalar function
Public Function ExScalar() As String
Dim cmdData As New OleDbCommand(st rSQL, cnMP)
Dim strOutput As String
strOutput = cmdData.Execute Scalar
Return strOutput
cmdData.Dispose ()
cmdData = Nothing
End Function

'Execute and return a dataset function
Public Function ExDataSet() As DataSet
Dim daGetData As New OleDbDataAdapte r(strSQL, sConnectionStri ng)
Dim dsData As New DataSet()
daGetData.Fill( dsData, strTableName)
Return dsData
daGetData.Dispo se()
daGetData = Nothing
End Function

End Class

[sloan]

Agreed.
Public Sub New()

cnMP = New OleDbConnection (sConnectionStr ing)
cnMP.Open()

End Sub
store the connection string, and do not open the connection here
private m_connectionStr ing as string = string.empty

public sub new( connectionStrin g as string )

me.m_connection String = connectionStrin g

end sub

OPEN LATE , CLOSE EARLY.

"bruce barker (sqlwork.com)" <b_************ *************@s qlwork.comwrote
in message news:Od******** ******@TK2MSFTN GP05.phx.gbl...

you have shouldn't open a connecton on your constructor. all routines need

a

try/catch to close the connection on errors.

-- bruce (sqlwork.com)

"Sierra" <Si****@discuss ions.microsoft. comwrote in message
news:F7******** *************** ***********@mic rosoft.com...

Problem:
Database connections are not being reused properly. SP_WHO2 shows

upwards

of 200 connections being created per page request. Most connections

exist

for
60 seconds then close without being reused. A few connections are

reused.

SQL
System Profiler shows many "Audit Login" and many "RPC: Completed"

records

for each page request - often involving the exact same SQL statement
called
in exactly the same manner from within a single program loop.

System Description:
. SQL Server 2005 (SP1)
. ASP.NET 2 (2.0.50727)
. IIS 6
. Windows 2003 Server (64bit production, x86 development - both having

the

same issue)
. All critical updates applied

Connection String:
. Globally called from web.config
. Never modified
. Provider=SQLNCL I;Data Source=localhos t;Initial Catalog=XDATABA SE;User
Id=XUSERNAME;Pa ssword=XPASSWOR D;DataTypeCompa tibility=80;

Connection Details:
. All database connections connect through a common DB Connection class.
. All database calls are in the form of stored procedures
. All connections are closed and disposed

Page Example:

Dim myOLE As New OLESQL()
myOLE.SetSQL("s pStoredProc " & intSomeParam)
myOLE.SetTableN ame("TableName" )
Dim dsResults As DataSet = myOLE.ExDataSet ()
'Do somthing with the dataset ...

For Each x In xGroup
myOLE.SetSQL("s pStoredProc2 " & x.ToString)
intReturn = myOLE.ExScalar( )

myOLE.SetSQL("s pStoredProc3 " & x.ToString)
myOLE.ExNonQuer y()
Next

myOLE.Close()

Database Connection Class:

Imports Microsoft.Visua lBasic
Imports System.Data.Ole Db
Imports System.Data
Imports System
Imports System.Web.UI.P age

Public Class OLESQL

'Class fields
Public strSQL As String
Public strTableName As String
Public cnMP As OleDbConnection
Public sConnectionStri ng As String =
ConfigurationMa nager.AppSettin gs("MPSQL")

'Initializer
Public Sub New()

cnMP = New OleDbConnection (sConnectionStr ing)
cnMP.Open()

End Sub

'Close the database connection
Public Sub Close()

If cnMP.State = ConnectionState .Open Then
cnMP.Close()
cnMP.Dispose()
cnMP = Nothing
End If

End Sub

'Set the SQL value
Public Sub SetSQL(ByVal inSQL)
strSQL = inSQL
End Sub

'Set the table name value
Public Sub SetTableName(By Val inTableName)
strTableName = inTableName
End Sub

'Execute a non query
Public Sub ExNonQuery()
Dim cmdData As New OleDbCommand(st rSQL, cnMP)
cmdData.Execute NonQuery()
cmdData.Dispose ()
cmdData = Nothing
End Sub

'Execute a scalar function
Public Function ExScalar() As String
Dim cmdData As New OleDbCommand(st rSQL, cnMP)
Dim strOutput As String
strOutput = cmdData.Execute Scalar
Return strOutput
cmdData.Dispose ()
cmdData = Nothing
End Function

'Execute and return a dataset function
Public Function ExDataSet() As DataSet
Dim daGetData As New OleDbDataAdapte r(strSQL, sConnectionStri ng)
Dim dsData As New DataSet()
daGetData.Fill( dsData, strTableName)
Return dsData
daGetData.Dispo se()
daGetData = Nothing
End Function

End Class

[GroupReader]

1) The Microsoft Database Access Application Block ("MS DAAB") works
great and will solve all your db-connect and connection pooling
problems. Download it as part of the MS Enterprise Library. Highly
recommended. Give it a try. The latest version is a huge improvement
over previous versions.

2) In an asp.net app, you shouldn't be making that many round-trips to
the db anyway, regardless of whether your connection pooling is
working. Consider making a single round-trip to the db if possible.
Where you have the "for each x in xGroup" loop, my guess is that you
don't really need to keep coming back to the web server... You could
gather up all of your "x" strings/ints and pass them to the db at once.
Then do something will all of them at once on the db side.

[Sierra]

Thanks all.

Bruce and sloans' suggestions cut down on the number of connections created,
but only partially. Installing and using MS DAAB knocked them out of the park
completely without the need for a massive code overhaul. As an added benefit
the DAAB class replicated most of the functionality I was trying to implement
with my DB access class in the first place, but did it better!

"GroupReade r" wrote:

1) The Microsoft Database Access Application Block ("MS DAAB") works
great and will solve all your db-connect and connection pooling
problems. Download it as part of the MS Enterprise Library. Highly
recommended. Give it a try. The latest version is a huge improvement
over previous versions.

2) In an asp.net app, you shouldn't be making that many round-trips to
the db anyway, regardless of whether your connection pooling is
working. Consider making a single round-trip to the db if possible.
Where you have the "for each x in xGroup" loop, my guess is that you
don't really need to keep coming back to the web server... You could
gather up all of your "x" strings/ints and pass them to the db at once.
Then do something will all of them at once on the db side.