[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

Code causing the SQL connection pool to reach its limit...

Friends,

I have the following code snips, and in it, it will work, as long as I don't call the UpdateQualificationSessionHistory too often; however, it is now becoming a problem.

I am calling it often enough in my program that it is causing SQL to reach its connection pool limit.  I think it's because I'm not handling my connections efficiently enough.

I'm hoping someone can look at this and help me trap were connections are not getting closed properly.


Public Sub UpdateQualificationSessionHistory()

        Dim UQSHCarNumber As String
        Dim UQSHNumberOfWarmupLaps As Integer
        Dim UQSHNumberOfQualificationLaps As Integer
        Dim UQSHLapType As String

        Try

            Dim data As New DataSet
            Dim r As DataRow

            QCUMainForm.lvQualificationSessionHistory.Items.Clear()
            TruncateDifferenceTables()

            dsn = GetDSN()

            Dim conUpdateQualificationSessionHistory As New SqlClient.SqlConnection(dsn)

            'fill in the data for a lap ...
            data = New DataSet

            Dim dr As SqlClient.SqlDataReader

            conUpdateQualificationSessionHistory = New SqlClient.SqlConnection(dsn)

            conUpdateQualificationSessionHistory.Open()

            Dim c As New SqlClient.SqlCommand("SELECT CarNumber, NumberOfWULaps, NumberOfQualLaps" & vbCrLf _
            & "FROM QualificationVerification" & vbCrLf _
            & "WHERE ((AMBMLPWarmupLapTime1 IS NOT NULL)" & vbCrLf _
            & "OR (PCWarmupLapTime1 IS NOT NULL)" & vbCrLf _
            & "OR (CameraWarmupLapTime1 IS NOT NULL))" & vbCrLf _
            & "ORDER BY QualAttemptTimeandDate DESC", conUpdateQualificationSessionHistory)

            dr = c.ExecuteReader(CommandBehavior.SingleResult)

            'loop through the records
            If dr.HasRows Then
                While dr.Read()

                    Dim UQSHWarmupLapNumber As Integer = 0
                    Dim UQSHQualLapNumber As Integer = 0

                    UQSHCarNumber = (dr("CarNumber"))
                    UQSHNumberOfWarmupLaps = (dr("NumberOfWULaps"))
                    UQSHNumberOfQualificationLaps = (dr("NumberOfQualLaps"))


                    UQSHQualLapNumber = UQSHNumberOfQualificationLaps

                    For QualLoopCounter As Integer = 0 To UQSHNumberOfQualificationLaps - 1
                        UQSHLapType = "QualLap"
                        BuildLapRecordToBeDisplayed(UQSHCarNumber, UQSHLapType, UQSHQualLapNumber)
                        UQSHQualLapNumber = UQSHQualLapNumber - 1
                    Next

                    UQSHWarmupLapNumber = UQSHNumberOfWarmupLaps

                    For WarmupLoopCounter As Integer = 0 To UQSHNumberOfWarmupLaps - 1
                        UQSHLapType = "WarmupLap"
                        BuildLapRecordToBeDisplayed(UQSHCarNumber, UQSHLapType, UQSHWarmupLapNumber)
                        UQSHWarmupLapNumber = UQSHWarmupLapNumber - 1
                    Next

                End While
            Else
                conUpdateQualificationSessionHistory.Close()
                conUpdateQualificationSessionHistory = Nothing

            End If

            conUpdateQualificationSessionHistory.Close()
            conUpdateQualificationSessionHistory = Nothing

            QCUMainForm.txtMaxPCDiff.Text = GetPCMaxDiff()
            QCUMainForm.txtAvgPCDiff.Text = GetPCAvgDiff()
            QCUMainForm.txtMaxCameraDiff.Text = GetCameraMaxDiff()
            QCUMainForm.txtAvgCameraDiff.Text = GetCameraAvgDiff()

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try
        'Second, loop through the warmup laps details, adding a row to the listview, for the number warmup laps listed.
        'Third, loop through the qual laps details the number of times that are listed for the, adding a row to the listview for each
    End Sub

    Public Sub BuildLapRecordToBeDisplayed(ByVal CarNumber As String, ByVal LapType As String, ByVal LapNumber As Integer)

        Try

            Dim DisplayCarNumber As String
            Dim DisplayPCLapTime As String
            Dim DisplayAMBLapTime As String
            Dim DisplayCameraLapTime As String
            Dim DisplayPCDifference As String
            Dim strPCDifference As String
            Dim PCDifference As Decimal = 0
            Dim strCameraDifference As String
            Dim DisplayCameraDifference As String
            Dim CameraDifference As Decimal = 0
            Dim LargestDifference As Decimal

            Dim data As New DataSet
            Dim r As DataRow

            Dim conBuildDisplayedLapRecord As New SqlClient.SqlConnection(dsn)

            'fill in the data for a lap ...
            data = New DataSet

            Dim dr As SqlClient.SqlDataReader

            dsn = GetDSN()

            conBuildDisplayedLapRecord = New SqlClient.SqlConnection(dsn)

            conBuildDisplayedLapRecord.Open()

            Dim c As New SqlClient.SqlCommand("SELECT CarNumber," & vbCrLf _
            & "PC" & LapType & "Time" & LapNumber & "=COALESCE( CAST(PC" & LapType & "Time" & LapNumber & " as varchar(40)),'No Data')," & vbCrLf _
            & "AMBMLP" & LapType & "Time" & LapNumber & "=COALESCE( CAST(AMBMLP" & LapType & "Time" & LapNumber & " as varchar(40)),'No Data')," & vbCrLf _
            & "Camera" & LapType & "Time" & LapNumber & "=COALESCE( CAST(Camera" & LapType & "Time" & LapNumber & " as varchar(40)),'No Data')" & vbCrLf _
            & "FROM QualificationVerification" & vbCrLf _
            & "WHERE CarNumber = '" & CarNumber & "'", conBuildDisplayedLapRecord)

            dr = c.ExecuteReader(CommandBehavior.SingleResult)

            'loop through the records
            If dr.HasRows Then
                While dr.Read()

                    DisplayCarNumber = (dr("CarNumber"))

                    DisplayPCLapTime = (dr("PC" & LapType & "Time" & LapNumber))
                    DisplayAMBLapTime = (dr("AMBMLP" & LapType & "Time" & LapNumber))
                    DisplayCameraLapTime = (dr("Camera" & LapType & "Time" & LapNumber))

                End While

                conBuildDisplayedLapRecord.Close()
                conBuildDisplayedLapRecord = Nothing

                If DisplayPCLapTime = "No Data" And DisplayAMBLapTime = "No Data" And DisplayCameraLapTime = "No Data" Then
                    Exit Sub
                Else


                    DisplayPCDifference = CalculatePhotoCellDifference(DisplayPCLapTime, DisplayAMBLapTime)

                    DisplayCameraDifference = CalculateCameraDifference(DisplayPCLapTime, DisplayAMBLapTime, DisplayCameraLapTime)

                    If DisplayPCDifference = "No P-C" Or DisplayPCDifference = "No AMB" Then 'strPCDifference = "No AMB" Then
                        PCDifference = 0
                    Else
                        PCDifference = CType(DisplayPCDifference, Decimal)

                    End If

                    If DisplayCameraDifference = "No Camera" Then
                        CameraDifference = 0
                    Else
                        CameraDifference = CType(DisplayCameraDifference, Decimal)
                    End If

                    If PCDifference > CameraDifference Then
                        LargestDifference = PCDifference
                    Else
                        LargestDifference = CameraDifference
                    End If

                End If

                QCUMainForm.AddItemsToQualificationSessionHistory(DisplayCarNumber, DisplayPCLapTime, DisplayPCDifference, DisplayAMBLapTime, DisplayCameraDifference, DisplayCameraLapTime, LargestDifference)

            End If

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

    End Sub

Thanks in advance!
0
indy500fan
Asked:
indy500fan
1 Solution
 
planoczCommented:
All you have to do is make sure that everytime you open a Connection to your database load your dataset  and then close your  Connection.
when you need to update  your database your your connection will automatically open and update then close it.
Also you have to many "conUpdateQualificationSessionHistory = New SqlClient.SqlConnection(dsn)" open you only need one.
0
 
SanclerCommented:
In the second part of your code you have

            Dim conBuildDisplayedLapRecord As New SqlClient.SqlConnection(dsn)

            [...]

            conBuildDisplayedLapRecord = New SqlClient.SqlConnection(dsn)

            conBuildDisplayedLapRecord.Open()

That is (a) you are using New twice and (b) you are opening the connection.  I am not sure that (a) would cause the problem, but it is unnecessary.  Turning to (b), although later in your code you have

                conBuildDisplayedLapRecord.Close()
                conBuildDisplayedLapRecord = Nothing

but that is enclosed in a block starting

            If dr.HasRows Then

So, if dr does not have rows, it will never be executed.  That could certainly be a problem.

But rather than fiddling with this code, would it not be possible for you to declare the connection outside (but with scope for) this sub and just .Open it, use it, and .Close it in this sub?  So far as I can see, although you give it different names in the early part of the sub and in the later part of the sub it has the same connection string - dsn - which is obtained by

            dsn = GetDSN()

If you did that, no matter how many times it was called, there would only be ONE connection - opened and closed as necessary - so you wouldn't get this problem.

Roger
0
 
indy500fanAuthor Commented:
Hey guys, I just got back into the office.  I'm going to look into your suggestions and I'm sure I'll have some more questions!  :)

Thanks,
Eric
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
indy500fanAuthor Commented:
I'm struggling with this a bit, but I wanted you both to know I haven't forgotten you guys...
0
 
indy500fanAuthor Commented:
Ugh,

I'm working around supporting practice sessions, spotty internet, and other fun stuff.  Anyway, I can't see where else to change my code so that I'm not closing my connections properly.  Can you guys, more specifically, show me where I might be leaving them open?

Right now, the only one I saw was here:

  If dr.HasRows Then
                While dr.Read()

                    DisplayCarNumber = (dr("CarNumber"))

                    DisplayPCLapTime = (dr("PC" & LapType & "Time" & LapNumber))
                    DisplayAMBLapTime = (dr("AMBMLP" & LapType & "Time" & LapNumber))
                    DisplayCameraLapTime = (dr("Camera" & LapType & "Time" & LapNumber))

                End While

                'conBuildDisplayedLapRecord.Close()
                'conBuildDisplayedLapRecord = Nothing <------------------From Here To...

                If DisplayPCLapTime = "No Data" And DisplayAMBLapTime = "No Data" And DisplayCameraLapTime = "No Data" Then
                    Exit Sub
                Else


                    DisplayPCDifference = CalculatePhotoCellDifference(DisplayPCLapTime, DisplayAMBLapTime)

                    DisplayCameraDifference = CalculateCameraDifference(DisplayPCLapTime, DisplayAMBLapTime, DisplayCameraLapTime)

                    If DisplayPCDifference = "No P-C" Or DisplayPCDifference = "No AMB" Then 'strPCDifference = "No AMB" Then
                        PCDifference = 0
                    Else
                        PCDifference = CType(DisplayPCDifference, Decimal)

                    End If

                    If DisplayCameraDifference = "No Camera" Then
                        CameraDifference = 0
                    Else
                        CameraDifference = CType(DisplayCameraDifference, Decimal)
                    End If

                    If PCDifference > CameraDifference Then
                        LargestDifference = PCDifference
                    Else
                        LargestDifference = CameraDifference
                    End If

                End If

                conBuildDisplayedLapRecord.Close() <------------------Here!!!...
                conBuildDisplayedLapRecord = Nothing
0
 
SanclerCommented:
No, that won't always do it.  That's because of the lines that immediately follow the code you've commented out

                If DisplayPCLapTime = "No Data" And DisplayAMBLapTime = "No Data" And DisplayCameraLapTime = "No Data" Then
                    Exit Sub
                Else

That would mean that the Exit Sub could fire without the Connection being closed.

What you could to is have it in BOTH places.  Two ways of doing this.  One is to match the

           If dr.HasRows Then

with an Else, and put the the code for the second time in that.  Like this

[...]                QCUMainForm.AddItemsToQualificationSessionHistory(DisplayCarNumber, DisplayPCLapTime, DisplayPCDifference, DisplayAMBLapTime, DisplayCameraDifference, DisplayCameraLapTime, LargestDifference)

            Else
                conBuildDisplayedLapRecord.Close()
                conBuildDisplayedLapRecord = Nothing
            End If

The other is to put it after the End If, as you've just suggested, but surround it by checks to see if the connection has already been closed and disposed of.  Like this

                [...]
                End If

                If Not conBuildDisplayedRecord Is Nothing Then
                   If Not conBuildDisplayedLapRecord.State = ConnectionState.Closed Then
                      conBuildDisplayedLapRecord.Close()
                   End If
                   conBuildDisplayedLapRecord = Nothing
                End if

Roger
0
 
coopzzCommented:
ok couple of things.  you can open a datareader to close the connection when you close the datareader. now it's up to you which way you want to do but to make sure that you always close on the connection and the datareader you do it in this sort of format..
'--------------------------------
        Dim dr As SqlClient.SqlDataReader
        Try
            dr = cDA.OpenFireHose(sSQL) 'open your data from a generic place with .ExecuteReader(CommandBehavior.CloseConnection)
            With dr
                If (dr.Read) Then
                    Do
                        Dim s As String = CType(.Item("SomeValue"), String)
                        'do something with it
                    Loop While (.Read)
                End If
            End With
        Catch ex As Exception
            cLog.LogError(ex, "BindTheData()", ReThrow:=True) 'Error recording class
        Finally
            If (dr Is Nothing) = False Then dr.Close() 'this will now close both reader and connection and will run regardlesss of errors occured.
        End Try
'-----------------
but to sum up any data access where an open connection can possibly be left open should be wrapped in a try catch finally block so you can close it and stop your pool hitting it's limit.
0
 
indy500fanAuthor Commented:
Sancler and others...

Just to let you know, it is difficult for me to look at some of this stuff on demand right now.  You see, I was working hot and heavy on this last weekend, because our servers were up and on at the event.  Right now, our servers that I am working on are on a Truck on it's way to the next track.  Tomorrow, or Thursday, our equipment should be out and in place, and then I should be able to test your guys' suggestions for solutions.

Thanks for being patient with me.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now