Having problems with connections staying open

[diddydustin]

Hello everyone,

I am having some issues with my service writen in VB.NET. I have checked all my connection objects within my code, however, somehow when the service runs connections are staying open to our database and it keeps growing and growing. I can't figure out where in my code this is happening. I have made sure to close every connection object.

Could anyone have any suggestions as to where to look or what to do? I apoligize for the long paste but I will paste the code here. Thanks!

---
'<doc author=”Dustin Lyons” date=”6/21/05”>
' Service that scrapes Amazon for ISBNs sitting in tblQue and tblQuery
' <reference use="references">spDel_tblQuery</reference>
' <reference use="references">spExec_tblQuery</reference>
' <reference use="references">spIns_tblAuthor</reference>
' <reference use="references">spIns_tblProduct</reference>
' <reference use="references">spIns_tblQue</reference>
' <reference use="references">spIns_tblQueItem</reference>
' <reference use="references">spIns_tblQueery</reference>
' <reference use="references">spSel_Report</reference>
' <reference use="references">spSel_tblAuthor</reference>
' <reference use="references">spSel_tblQue</reference>
' <reference use="references">spSel_tblQueItem</reference>
' <reference use="references">spSel_tblQueryItem</reference>
'</doc>

Imports System.ServiceProcess
Imports System.Security.Permissions
Imports Microsoft.Win32
Imports System.Math
<Assembly: RegistryPermissionAttribute( _
SecurityAction.RequestMinimum, All:="HKEY_LOCAL_MACHINE")>

Module Definitions
#Region " Reg stuff"
' The registry values for storing the time intervals are:
' HKEY_LOCAL_MACHINE/SOFTWARE/AmazonQue/SuccessTime
' Time used if we found something in the que
' HKEY_LOCAL_MACHINE/SOFTWARE/AmazonQue/FailTime
' Time used if there is nothing in the que and we are waiting
Private Function RegValue(ByVal Hive As RegistryHive, _
ByVal Key As String, ByVal ValueName As String) As String

' Select registry
Dim objParent As RegistryKey
Dim objSubkey As RegistryKey
Dim sAns As String
Select Case Hive
Case RegistryHive.ClassesRoot
objParent = Registry.ClassesRoot
Case RegistryHive.CurrentConfig
objParent = Registry.CurrentConfig
Case RegistryHive.CurrentUser
objParent = Registry.CurrentUser
Case RegistryHive.DynData
objParent = Registry.DynData
Case RegistryHive.LocalMachine
objParent = Registry.LocalMachine
Case RegistryHive.PerformanceData
objParent = Registry.PerformanceData
Case RegistryHive.Users
objParent = Registry.Users
End Select

' Open our key
objSubkey = objParent.OpenSubKey(Key)
' If can't be found, object is not initialized
If Not objSubkey Is Nothing Then
sAns = (objSubkey.GetValue(ValueName))
End If

Return sAns

objParent = Nothing
objSubkey = Nothing
End Function
#End Region
#Region " Definitions "

' Max ISBNs we will fetch
Public Const ISBN_MAX = 10

' Stored procedure to return ISBNs
Public Const STOR_PROC = "spSel_vwQue"

' Seconds for poll if success
Public SUCCESS_SEC = Int(RegValue(RegistryHive.LocalMachine, _
"SOFTWARE\eCampus\AmazonQue", "SuccessTime"))

' Seconds for poll if fail
Public FAIL_SEC = Int(RegValue(RegistryHive.LocalMachine, _
"SOFTWARE\eCampus\AmazonQue", "FailTime"))
' Default values for our timer
Public Const SUCCESS_DEFAULT = 1
Public Const FAIL_DEFAULT = 60

Public CONNECTION_STRING = RegValue(RegistryHive.LocalMachine, _
"SOFTWARE\eCampus\AmazonQue", "ConnectionString")
#End Region

End Module

Public Class Service
Inherits System.ServiceProcess.ServiceBase

#Region " Definitions "
' Our timer object
Dim tmrClock As New System.Timers.Timer
#End Region

#Region " Timer "
Public Shared Function StartTimer(ByRef myClock As System.Timers.Timer)
AddHandler myClock.Elapsed, AddressOf OnTimedEvent

With myClock
If SUCCESS_SEC <> 0 Then
.Interval = SUCCESS_SEC * 1000
Else
.Interval = SUCCESS_DEFAULT * 1000
End If
.Enabled = True
End With
End Function
Public Shared Function StopTimer(ByRef myClock As System.Timers.Timer)
myClock.Enabled = False
End Function
Private Shared Sub OnTimedEvent(ByVal source As Object, ByVal e As System.Timers.ElapsedEventArgs)
' Begin running, if we find something set time to success
If (Begin()) Then
If SUCCESS_SEC <> 0 Then
source.Interval = SUCCESS_SEC * 1000
Else
source.Interval = SUCCESS_DEFAULT * 1000
End If
Else
' Set time to fail: wait longer before we check again
If FAIL_SEC <> 0 Then
source.Interval = FAIL_SEC * 1000
Else
source.Interval = FAIL_DEFAULT * 1000
End If
End If
End Sub

#End Region
#Region " Que "
Public Shared Function Begin() As Boolean
Dim objDataSet As New System.Data.DataSet

' If there is data in the que, run it
If (CheckQue(objDataSet) > 0) Then
ExecQue(objDataSet)
Return True
Else
' We didn't find anything
Return False
End If

objDataSet.Dispose()
objDataSet = Nothing

End Function
Private Shared Function CheckQue(ByRef Data As System.Data.DataSet) As Integer

Dim objCmd As New System.Data.SqlClient.SqlCommand
Dim objDA As New System.Data.SqlClient.SqlDataAdapter
Dim objDataSet As New System.Data.DataSet
Dim objConn As New System.Data.SqlClient.SqlConnection

With objConn
.ConnectionString = CONNECTION_STRING
.Open()
End With

With objCmd
.Connection = objConn
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 100
.CommandText = STOR_PROC
End With

With objDA
.SelectCommand = objCmd
.Fill(objDataSet)
End With

' If we find data, return true, else return false
If (objDataSet.Tables(0).Rows.Count <> 0) Then
Data = objDataSet
Return objDataSet.Tables(0).Rows.Count
Else
Return 0
End If

objConn.Close()
objConn.Dispose()
objConn = Nothing

objDA.Dispose()
objDA = Nothing

objCmd.Dispose()
objCmd = Nothing

End Function
Private Shared Sub ExecQue(ByVal Data As System.Data.DataSet)

' Amazon data object
Dim objAmzData As New AmazonData

' Hold ISBNs
Dim strISBNs
Dim strISBN(ISBN_MAX) As String
Dim intQueID(ISBN_MAX) As Integer
strISBNs = ""
' For each
For x As Integer = 0 To Min(ISBN_MAX - 1, Data.Tables(0).Rows.Count - 1)
' Build ISBN list
strISBNs = strISBNs + Trim(Data.Tables(0).Rows(x)("chrISBN")) + ","
' Get ISBN and Que ID for each table row
strISBN(x) = Trim(Data.Tables(0).Rows(x)("chrISBN"))
intQueID(x) = Trim(Data.Tables(0).Rows(x)("intQueID"))
Next
strISBNs = Left(strISBNs, Len(strISBNs) - 1)

objAmzData.Run(strISBNs, strISBN, intQueID)

strISBN = Nothing
intQueID = Nothing
objAmzData = Nothing

End Sub
#End Region

#Region " Component Designer generated code "

Public Sub New()
MyBase.New()

' This call is required by the Component Designer.
InitializeComponent()

' Add any initialization after the InitializeComponent() call

End Sub

'UserService overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

' The main entry point for the process
<MTAThread()> _
Shared Sub Main()
Dim ServicesToRun() As System.ServiceProcess.ServiceBase

' More than one NT Service may run within the same process. To add
' another service to this process, change the following line to
' create a second service object. For example,
'
' ServicesToRun = New System.ServiceProcess.ServiceBase () {New Service1, New MySecondUserService}
'
ServicesToRun = New System.ServiceProcess.ServiceBase() {New Service}

System.ServiceProcess.ServiceBase.Run(ServicesToRun)
End Sub

'Required by the Component Designer
Private components As System.ComponentModel.IContainer

' NOTE: The following procedure is required by the Component Designer
' It can be modified using the Component Designer.
' Do not modify it using the code editor.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
'
'Service
'
Me.ServiceName = "AmazonQue"

End Sub

#End Region
#Region " Service Events "
Protected Overrides Sub OnStart(ByVal args() As String)
' Add code here to start your service. This method should set things
' in motion so your service can do its work.
StartTimer(tmrClock)
End Sub
Protected Overrides Sub OnStop()
' Add code here to perform any tear-down necessary to stop your service.
StopTimer(tmrClock)
End Sub
Protected Overrides Sub OnPause()
' Add code here to perform any tear-down necessary to stop your service.
StopTimer(tmrClock)
End Sub
#End Region

End Class

Public Class AmazonData
#Region " Data Members "
Dim intProductID As Integer
Dim curAmazonListPrice As Decimal
Dim curAmazonNewPrice As Decimal
Dim curAmazonUsedPrice As Decimal
Dim curAmazonCollectiblePrice As Decimal
Dim curAmazonThirdPartyNewPrice As Decimal
Dim strReleaseDate As String
Dim lngSalesRank As Long
Dim intMedia As Integer
Dim strManufacturer As String
Dim strASIN As String
Dim strISBN As String
Dim strProductName As String
Dim strAvailability As String
Dim strCatalog As String
Dim strMedia As String
Dim strAuthor As String
Dim arrAuthors() As String
Dim arrSimilars() As String
Dim strThirdPartySellerID As String
Dim strThirdPartySellerNickname As String
Dim sdtCreated As DateTime
#End Region
#Region " Functions "
Public Function Run(ByVal strArgISBN As String, ByRef strISBN() As String, ByRef intQueID() As Integer) As Boolean

' AmazonWSDL returns if it was successful or not
AmazonWSDL(strArgISBN, strISBN, intQueID)

End Function
Public Function SetFlag(ByVal strISBN As String, ByVal strFlag As Char, _
ByVal intQueID As Integer)

Dim objCmd As New System.Data.SqlClient.SqlCommand
Dim objDA As New System.Data.SqlClient.SqlDataAdapter
Dim objDataSet As New System.Data.DataSet
Dim objConn As New System.Data.SqlClient.SqlConnection

With objConn
.ConnectionString = CONNECTION_STRING
.Open()
End With

' Set the flag on the ISBN in Que
With objCmd
.Connection = objConn
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 100
.CommandText = "spUpd_tblQueItem"
.Parameters.Add("@chrISBN", SqlDbType.Char, 12).Value = strISBN
.Parameters.Add("@chrRecordStatus", SqlDbType.Char, 1).Value = strFlag
.Parameters.Add("@intQueID", SqlDbType.Int).Value = intQueID
.ExecuteNonQuery()
End With

' Clear parameters on command object
objCmd.Parameters.Clear()

' Set the flag on the ISBN in Query
With objCmd
.Connection = objConn
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 100
.CommandText = "spUpd_tblQueryItem"
.Parameters.Add("@chrISBN", SqlDbType.Char, 12).Value = strISBN
.Parameters.Add("@chrRecordStatus", SqlDbType.Char, 1).Value = strFlag
.Parameters.Add("@intQueID", SqlDbType.Int).Value = intQueID
.ExecuteNonQuery()
End With

objConn.Close()
objConn.Dispose()
objConn = Nothing

objCmd.Dispose()
objCmd = Nothing

End Function
Private Function AmazonWSDL(ByVal strArgISBN As String, ByRef strISBNs() As String, ByRef intQueID() As Integer) As System.Data.DataTable

' This code was designed to only send Amazon 1 ISBN at a time.
' Web Reference:

http://soap.amazon.com/schemas2/AmazonWebServices.wsdl

Const strASSOCIATESID = "webservices-20"
Const strDEVELOPERTOKEN = "D2ED5GR7A6RZ7Y"
Const intError = 0
Const intGood = 2
Const intTimeout = 3

Dim objAmazonSearch As New com.amazon.soap.AmazonSearchService
Dim objASIN As New com.amazon.soap.AsinRequest
Dim objProductInfo As New com.amazon.soap.ProductInfo
Dim objThirdPartyProdInfo As New com.amazon.soap.ThirdPartyProductInfo
Dim objThirdPartyProdDetails As New com.amazon.soap.ThirdPartyProductDetails
Dim strCreated As String
Dim i As Integer

With objASIN
.asin = strArgISBN
.devtag = strDEVELOPERTOKEN
.tag = strASSOCIATESID
.offer = "all"
.type = "heavy"
End With

objProductInfo = objAmazonSearch.AsinSearchRequest(objASIN)

' While we are within 1 - ISBN_MAX
While (i < ISBN_MAX)
' Get info from Amazon
With objProductInfo
curAmazonListPrice = .Details(i).ListPrice
curAmazonNewPrice = .Details(i).OurPrice
curAmazonUsedPrice = .Details(i).UsedPrice
curAmazonCollectiblePrice = .Details(i).CollectiblePrice
curAmazonThirdPartyNewPrice = .Details(i).ThirdPartyNewPrice
strReleaseDate = .Details(i).ReleaseDate
lngSalesRank = .Details(i).SalesRank
intMedia = .Details(i).NumMedia
strManufacturer = .Details(i).Manufacturer
strISBN = .Details(i).Isbn
strASIN = .Details(i).Asin
strProductName = .Details(i).ProductName
strAvailability = .Details(i).Availability
strCatalog = .Details(i).Catalog
strMedia = .Details(i).Media
arrAuthors = .Details(i).Authors
arrSimilars = .Details(i).SimilarProducts
sdtCreated = Now()

If Not (.Details(i).ThirdPartyProductInfo Is Nothing) Then
strThirdPartySellerID = .Details(i).ThirdPartyProductInfo.ThirdPartyProductDetails(0).SellerId
strThirdPartySellerNickname = .Details(i).ThirdPartyProductInfo.ThirdPartyProductDetails(0).SellerNickname
End If
End With

' If Amazon didn't return anything, set flag to E
If (strISBN Is Nothing) Then
SetFlag(strISBNs(i), "E", intQueID(i))
Else
' Insert data into tables, set flag to I
InsertData(intQueID(i))
SetFlag(strISBNs(i), "I", intQueID(i))
End If

' Increment counter
i = i + 1

End While
objProductInfo = Nothing
objASIN = Nothing
End Function
Private Function InsertData(ByVal intQueID As Integer)

Dim objCmd As New System.Data.SqlClient.SqlCommand
Dim objDA As New System.Data.SqlClient.SqlDataAdapter
Dim objDataSet As New System.Data.DataSet
Dim objConn As New System.Data.SqlClient.SqlConnection

' ISBN check
Dim blnChk As New Boolean

With objConn
.ConnectionString = CONNECTION_STRING
.Open()
End With

If (Not (arrAuthors Is Nothing)) Then
' Builds string of authors from arrAuthors()
BuildString(arrAuthors, strAuthor)
Else
strAuthor = ""
End If

' Clear parameters on command object
objCmd.Parameters.Clear()

' Insert Author
With objCmd
.Connection = objConn
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 100
.CommandText = "spIns_tblAuthor"
.Parameters.Add("@chrISBN", SqlDbType.Char, 10).Value = strISBN
.Parameters.Add("@vcName", SqlDbType.VarChar, 200).Value = strAuthor
.Parameters.Add("@sdtCreated", SqlDbType.DateTime, 8).Value = sdtCreated
.ExecuteNonQuery()
End With

' Clear parameters on command object
objCmd.Parameters.Clear()

' Insert Product
With objCmd
.CommandText = "spIns_tblProduct"
.Parameters.Add("@vcManufacturer", SqlDbType.VarChar, 100).Value = IfNull(strManufacturer, "None")
.Parameters.Add("@intQueID", SqlDbType.Int, 4).Value = IfNull(intQueID, 0)
.Parameters.Add("@vcASIN", SqlDbType.VarChar, 20).Value = IfNull(strASIN, "None")
.Parameters.Add("@chrISBN", SqlDbType.Char, 10).Value = IfNull(strISBN, "None")
.Parameters.Add("@vcProductName", SqlDbType.VarChar, 500).Value = IfNull(strProductName, "None")
.Parameters.Add("@vcCatalog", SqlDbType.VarChar, 100).Value = IfNull(strCatalog, "None")
If (Not (strReleaseDate Is Nothing)) Then
If (strReleaseDate.Length = 4) Then
strReleaseDate = "1/1/" + strReleaseDate
End If
.Parameters.Add("@sdtRelease", SqlDbType.DateTime, 8).Value = DateTime.Parse(strReleaseDate)

Else
.Parameters.Add("@sdtRelease", SqlDbType.DateTime, 8).Value = DateTime.Parse("1/1/1900")
End If
.Parameters.Add("@mnyListPrice", SqlDbType.Money, 8).Value = IfNull(curAmazonListPrice, 0)
.Parameters.Add("@mnyOurPrice", SqlDbType.Money, 8).Value = IfNull(curAmazonListPrice, 0)
.Parameters.Add("@mnyUsedPrice", SqlDbType.Money, 8).Value = IfNull(curAmazonUsedPrice, 0)
.Parameters.Add("@mnyCollectiblePrice", SqlDbType.Money, 8).Value = IfNull(curAmazonCollectiblePrice, 0)
.Parameters.Add("@mnyThirdPartyNewPrice", SqlDbType.Money, 8).Value = IfNull(curAmazonThirdPartyNewPrice, 0)
.Parameters.Add("@intSalesRank", SqlDbType.Int, 4).Value = IfNull(lngSalesRank, 0)
.Parameters.Add("@vcMedia", SqlDbType.VarChar, 100).Value = IfNull(strMedia, "None")
.Parameters.Add("@sintNumMedia", SqlDbType.SmallInt, 2).Value = IfNull(intMedia, 0)
.Parameters.Add("@vcAvailability", SqlDbType.VarChar, 100).Value = IfNull(strAvailability, "None")
.Parameters.Add("@sdtCreated", SqlDbType.DateTime, 8).Value = IfNull(sdtCreated, DateTime.Parse("1/1/1900"))
.ExecuteNonQuery()
End With

' Clear parameters on command object
objCmd.Parameters.Clear()

' If arrSimilars isn't null
If (Not (arrSimilars Is Nothing)) Then

' Insert Similars
With objCmd
.Connection = objConn
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 100
.CommandText = "spIns_tblSimilar"
End With

' Loop each similar and insert
For x As Integer = 0 To arrSimilars.Length - 1
With objCmd
.Parameters.Add("@chrISBN", SqlDbType.Char, 10).Value = strISBN
.Parameters.Add("@chrSimilarISBN", SqlDbType.VarChar, 200).Value = arrSimilars(x)
.Parameters.Add("@sdtCreated", SqlDbType.DateTime, 8).Value = sdtCreated
.ExecuteNonQuery()
End With

' Clear parameters on command object
objCmd.Parameters.Clear()
Next
End If

objConn.Close()
objConn.Dispose()
objConn = Nothing

objCmd.Dispose()
objCmd = Nothing

objDataSet.Dispose()
objDataSet = Nothing
objDA.Dispose()
objDA = Nothing
End Function
Private Function BuildString(ByRef strArg() As String, ByRef strDest As String)
strDest = strArg(0)
If (strArg.Length > 1) Then
For x As Integer = 1 To strArg.Length - 1
strDest = strDest + "|" + strArg(x)
Next
End If
End Function

#End Region
Public Function IfNull(ByVal Value As Object, ByVal ReturnVal As Object) As Object
If IsNothing(Value) Then
Return ReturnVal
Else
Return Value
End If
End Function
End Class
---

 

[JohnYingling]

What is in CONNECTION_STRING
e.g.
Connection Pooling=True could be a problem if Integrated Authentication is used with SQL.

http://www.moldyoldy.com/VBComparedemo

- free online Compare/Diff of snippets

 

[diddydustin]

.ConnectionString = "Data Source=devcat;Initial Catalog=abc_AmazonQue;user ID=IntranetUser; password=IntranetUser; application name=AmazonQue"

When I do a 'pooling=false' in the CONNECTION_STRING i don't have the problem anymore. When it is 'true', we step through the code, see the connection open, and when it is supposed to close (and vb.net shows that objConn.State is CLOSED), in SQL Server there is SILL a connection open.

I can leave connection pooling off, which allows it to work, however, is this a bad thing?

Thanks!
Dustin

 

[ThatRickGuy]

SQL Server >should< be dropping the connection when either the server side connection times out, or someone else requests a connection and the limited number of connections available are reached.

Turning off pooling will just add a bit of overhead every time the connection is opened. Leaving it on reduces the overhead, but keeps the connection 'handy' for the next request.

If the method calls the database numerous times, you would likely want pooling on. if the method only pulls data the once, then it does nothing for you.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[JohnYingling]

it may be bad but it is better than what you have until you know what is going on. Yes it will appear closed in .Net but reusable. This may be an SQL issue. What version?

http://www.moldyoldy.com/VBComparedemo

- free online Compare/Diff of snippets