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=SQLNCLI;Data Source=localhost;Initial Catalog=XDATABASE;User
Id=XUSERNAME;Password=XPASSWORD;DataTypeCompatibil ity=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("spStoredProc " & intSomeParam)
myOLE.SetTableName("TableName")
Dim dsResults As DataSet = myOLE.ExDataSet()
'Do somthing with the dataset ...

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

myOLE.SetSQL("spStoredProc3 " & x.ToString)
myOLE.ExNonQuery()
Next

myOLE.Close()

Database Connection Class:

Imports Microsoft.VisualBasic
Imports System.Data.OleDb
Imports System.Data
Imports System
Imports System.Web.UI.Page

Public Class OLESQL

'Class fields
Public strSQL As String
Public strTableName As String
Public cnMP As OleDbConnection
Public sConnectionString As String =
ConfigurationManager.AppSettings("MPSQL")

'Initializer
Public Sub New()

cnMP = New OleDbConnection(sConnectionString)
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(ByVal inTableName)
strTableName = inTableName
End Sub

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

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

'Execute and return a dataset function
Public Function ExDataSet() As DataSet
Dim daGetData As New OleDbDataAdapter(strSQL, sConnectionString)
Dim dsData As New DataSet()
daGetData.Fill(dsData, strTableName)
Return dsData
daGetData.Dispose()
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****@discussions.microsoft.comwrote in message
news:F7**********************************@microsof t.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=SQLNCLI;Data Source=localhost;Initial Catalog=XDATABASE;User
Id=XUSERNAME;Password=XPASSWORD;DataTypeCompatibil ity=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("spStoredProc " & intSomeParam)
myOLE.SetTableName("TableName")
Dim dsResults As DataSet = myOLE.ExDataSet()
'Do somthing with the dataset ...

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

myOLE.SetSQL("spStoredProc3 " & x.ToString)
myOLE.ExNonQuery()
Next

myOLE.Close()

Database Connection Class:

Imports Microsoft.VisualBasic
Imports System.Data.OleDb
Imports System.Data
Imports System
Imports System.Web.UI.Page

Public Class OLESQL

'Class fields
Public strSQL As String
Public strTableName As String
Public cnMP As OleDbConnection
Public sConnectionString As String =
ConfigurationManager.AppSettings("MPSQL")

'Initializer
Public Sub New()

cnMP = New OleDbConnection(sConnectionString)
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(ByVal inTableName)
strTableName = inTableName
End Sub

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

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

'Execute and return a dataset function
Public Function ExDataSet() As DataSet
Dim daGetData As New OleDbDataAdapter(strSQL, sConnectionString)
Dim dsData As New DataSet()
daGetData.Fill(dsData, strTableName)
Return dsData
daGetData.Dispose()
daGetData = Nothing
End Function

End Class

[sloan]

Agreed.
Public Sub New()

cnMP = New OleDbConnection(sConnectionString)
cnMP.Open()

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

public sub new( connectionString as string )

me.m_connectionString = connectionString

end sub

OPEN LATE , CLOSE EARLY.

"bruce barker (sqlwork.com)" <b_*************************@sqlwork.comwrote
in message news:Od**************@TK2MSFTNGP05.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****@discussions.microsoft.comwrote in message
news:F7**********************************@microsof t.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=SQLNCLI;Data Source=localhost;Initial Catalog=XDATABASE;User
Id=XUSERNAME;Password=XPASSWORD;DataTypeCompatibil ity=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("spStoredProc " & intSomeParam)
myOLE.SetTableName("TableName")
Dim dsResults As DataSet = myOLE.ExDataSet()
'Do somthing with the dataset ...

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

myOLE.SetSQL("spStoredProc3 " & x.ToString)
myOLE.ExNonQuery()
Next

myOLE.Close()

Database Connection Class:

Imports Microsoft.VisualBasic
Imports System.Data.OleDb
Imports System.Data
Imports System
Imports System.Web.UI.Page

Public Class OLESQL

'Class fields
Public strSQL As String
Public strTableName As String
Public cnMP As OleDbConnection
Public sConnectionString As String =
ConfigurationManager.AppSettings("MPSQL")

'Initializer
Public Sub New()

cnMP = New OleDbConnection(sConnectionString)
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(ByVal inTableName)
strTableName = inTableName
End Sub

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

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

'Execute and return a dataset function
Public Function ExDataSet() As DataSet
Dim daGetData As New OleDbDataAdapter(strSQL, sConnectionString)
Dim dsData As New DataSet()
daGetData.Fill(dsData, strTableName)
Return dsData
daGetData.Dispose()
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!

"GroupReader" 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.