Leaking ADO.NET Connections from ASP.NET 2 Web App

[Guest]

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;DataTypeCompatibility=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

 

[Cowboy \(Gregory A. Beamer\)]

I am not sure why you are not getting reuse on the connection object, so a
step through in the debugger is the best option. It might be the dispose on
individual command objects? Just a shot in the dark.

but this is where you are pounding the database and where the connections
are most likely being created.

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

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



--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************************************************
Think outside of the box!
*************************************************