SQL Server connection remains open, even after closing

Question

I have a website that is designed to multi-tiered. My code works, but I have noticed that the larger my app becomes, the more SQL database connections start to stack up and remain open. This eventually causes this error:

System.InvalidOperationException: 'Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.'

My code is split into 3 layers. They are:

1. Application layer. Every time it wants to CRUD, is calls the Business Layer.
2. Business Layer - does business logic. When it wants to interface with the MS SQL db, it connects via ConnectionAdapter layer.
3. The ConnectionAdapter inherits from a SqlConnectionAdapter class and does the actual db interactions.

The following is pseudo code for each:

Application
My application may call the business layer multiple times. Particularly when doing AJAX requests. An example would be like:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim dp As New DataProxy
    Dim listOfObs As New List(Of MyObject)
    dim someId as integer = 1
    Try
        If Not Page.IsPostBack Then
          listOfObs = dp.ExampleReadFuncion(someId)
        End If
    Catch ex As Exception
        Throw
    Finally
        dp.dispose()
        dp = Nothing
        SetMenue()
    End Try
End Sub

DatatProxy (business layer)

Public Class DataProxy
    Dim scConAdapter As New ConnectionAdapter

    Public Sub New()
        Try          
            scConAdapter.Connect()        
        Catch ex As Exception
            Throw      
        End Try    
    End Sub    

    Public Sub dispose()
        scConAdapter.Dispose()
    End Sub

    Private Shared Sub Main()
    End Sub

    Public Function ExampleReadFuncion(ByVal someId As Integer) As List(Of MyObject)
        Dim successFactor As LogStatusEnum = LogStatusEnum.INFO
        Dim newEx As Exception = Nothing
        Dim conn As New ConnectionAdapter
        Dim myObj As ActivityMarker
        Dim listOfObs As New List(Of MyObject)
        Dim dr As SqlDataReader = Nothing

        Try
            successFactor = LogStatusEnum.INFO
            conn.Connect()
            dr = conn.ExampleReadFuncion(someId)
            Using dr
                If (dr.HasRows = True) Then
                    While dr.Read
                        myObj = New myObj
                        myObj.Marker_Id = dr.Item("id")
                        myObj.Acitvity_Id = dr.Item("someValue")   

                        listOfObs.Add(myObj)
                    End While
                End If
            End Using
            Return listOfObs
        Catch ex As Exception
            successFactor = LogStatusEnum.ERRORS         
            Throw
        Finally
            dr.Close()
            dr = Nothing
            conn.Dispose()
            conn = Nothing     
        End Try
    End Function

End class

ConnectionAdapter

Public Class ConnectionAdapter
    Inherits SqlConnectionAdapter

    Public Sub New()
    End Sub

    Public Function ExampleReadFuncion(ByVal someId As Integer) As SqlDataReader
        Try
            Dim dr As SqlDataReader = Nothing
            Dim selectString As New StringBuilder
            Dim cmd As SqlCommand = Nothing
            Try
                cmd = CreateCommand()

                selectString.Append("SELECT * " & vbCrLf)
                selectString.Append("FROM " & vbCrLf)
                selectString.Append("dbo.mytable " & vbCrLf)
                selectString.Append("WHERE  " & vbCrLf)
                selectString.Append("id = @SOME_ID " & vbCrLf)              

                With cmd
                    .CommandType = CommandType.Text
                    .CommandText = selectString.ToString
                    .Parameters.Add("@SOME_ID", SqlDbType.Int).Value = someId

                    dr = .ExecuteReader
                End With

            Catch ex As Exception
                Throw
            Finally           
                cmd.Dispose()
            End Try
            Return dr
        Catch ex As Exception
            Throw ex
        End Try
    End Function
end class

SqlConnectionAdapter

Public MustInherit Class SqlConnectionAdapter
    Protected CurrentTransaction As SqlTransaction
    Public Property db As SqlConnection
    Public Property Password As String
    Public Property TNSName As String
    Public Property User As String
    Public Property DBName As String
    Public Property PortNumber As Integer   

    Public Overridable Sub Dispose()
        Try
            If Not CurrentTransaction Is Nothing Then
                CurrentTransaction.Commit()
            End If

        Catch ex As Exception
            Throw
        Finally
            If Not db Is Nothing Then
                db.Close()
                db.Dispose()
                db = Nothing
            End If
        End Try
    End Sub

    Public Overridable Sub Connect()
        Try
            Dim appSettings = ConfigurationManager.AppSettings

            If (appSettings("releaseVersion") = "DEBUG") Then
                Connect(appSettings("db_sqlHost"), appSettings("db_sqlDb"))
            Else
                Connect(appSettings("db_sqlHost"), appSettings("db_sqlPort"), appSettings("db_sqlDb"), appSettings("db_sqlUser"), appSettings("db_sqlPassword"))
            End If

        Catch ex As Exception
            Throw
        End Try
    End Sub

    Public Sub Connect(ByVal GetServername As String, ByVal GetDatabaseName As String)
        Try
            TNSName = GetServername
            DBName = GetDatabaseName

            db = New SqlConnection
            db = SqlConnectionUtilities.GetConnection(GetServername, GetDatabaseName)
        Catch ex As Exception
            Throw
        End Try
    End Sub

    Public Sub Connect(ByVal GetServerName As String, ByVal GetPort As Long, ByVal GetDatabase As String, ByVal GetUsername As String, ByVal Getpassword As String)
        Try            
            User = GetUsername
            Password = Getpassword
            PortNumber = GetPort
            DBName = GetDatabase
            TNSName = GetServerName

            db = New SqlConnection
            db = SqlConnectionUtilities.GetConnection(GetServerName, GetPort, GetDatabase, GetUsername, Getpassword)
        Catch ex As Exception
            Throw
        End Try
    End Sub

    Protected Function CreateCommand() As SqlCommand
        Dim ret As SqlCommand = Nothing

        Try
            ret = db.CreateCommand

            If Not CurrentTransaction Is Nothing Then
                ret.Transaction = CurrentTransaction
            End If
        Catch ex As Exception
            Throw
        Finally

        End Try
        Return ret
    End Function
    Public Sub BeginTransaction()
        If CurrentTransaction Is Nothing Then
            CurrentTransaction = db.BeginTransaction
        End If
    End Sub
    Public Sub CommitTransaction()
        If Not CurrentTransaction Is Nothing Then
            CurrentTransaction.Commit()
            CurrentTransaction.Dispose()
            CurrentTransaction = Nothing
        End If
    End Sub
    Public Sub RollbackTransaction()
        If Not CurrentTransaction Is Nothing Then
            CurrentTransaction.Rollback()
            CurrentTransaction.Dispose()
            CurrentTransaction = Nothing
        End If
    End Sub
       Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub
End Class

Utilities class

Public Class SqlConnectionUtilities    

    Public Shared Property connectionString As String

    Public Shared Function GetConnection(ByVal ServerName As String, ByVal Port As String, ByVal Database As String, ByVal username As String, ByVal password As String) As SqlConnection
        Dim connString As New StringBuilder
        Dim con As SqlConnection
        Try
            connString.Append("Server=tcp:" & ServerName & "," & Port & ";")
            connString.Append("Initial Catalog = " & Database & "; Persist Security Info=False;")
            connString.Append("User ID = " & username & ";")
            connString.Append("Password = " & password & ";")
            connString.Append("MultipleActiveResultSets = False;")
            connString.Append("Encrypt = True;TrustServerCertificate=False;Connection Timeout=30;")

            connectionString = connString.ToString

            con = New SqlConnection(connString.ToString)
            con.Open()
            Return con
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Shared Function GetConnection(ByVal Servername As String, ByVal DatabaseName As String) As SqlConnection
        Dim ConnectString As String
        Dim con As SqlConnection
        Try
            ConnectString = "Data Source=" & Servername & ";Initial Catalog=" & DatabaseName & ";Integrated Security=True"
            connectionString = ConnectString
            con = New SqlConnection(ConnectString)
            con.Open()
            Return con
        Catch ex As Exception
            Throw
        End Try
    End Function   
End class

I can tell that connections are remaining open by running this SQL statement:

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

I set up break points when my DataProxy class is called. I run the SQL code and can see a new connection is opened. Then, I run the code again when I dispose of the DataProxy class and I can see the connection remains. This will build up until it hits 101 connections, then it causes the above error. How am I not handling the connections correctly?

Answer 1

No, even this code not work and it wait a few minutes to remove sql connection from sql server.

    using (var conn = new SqlConnection(connStr))
    {
        conn.Open();
        conn.Close();
    }
    GC.Collect();
    GC.WaitForPendingFinalizers();

Answer 2

System.InvalidOperationException: 'Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.'

How am I not handling the connections correctly?

You are "leaking" connections. IE you have some code path that opens a SqlConnection, and doesn't Close/Dispose it. The SqlConnection remains open and is sitting on the managed heap. Eventually it will be GC'd and its Finalizer will close the connection. But if you leak 100 connections before that happens, you get this error.

So you need to ensure that your SqlConnections are always closed using a USING block, or are managed by some other object that's closed with a USING block.

Note that if you are returning a SqlDataReader from a function, there's a special CommandBehavior that will close the SqlConnection when the SqlDataReader is closed.