"Sierra" <Sie***@discussions.microsoft.com> wrote in message
news:92EC4D1A-03B5-49DF-84F1-1D97688CA452@microsoft.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;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