Question Connection timing out?

[delstar]

I'm currently working on a project to help with inventory/maintenance a bit. Basically, I've created a service that reports back to a DB statistics it collects from WMI. At first, everything worked fine, except I couldn't get the rows to update correctly. After finding and fixing that issue, I've run into another problem. The service will throw an exception stating that the connection timed-out. I keep re-stating that the service is doing this, because when I setup a test form with basically the same code and a button to start the process, the exception doesn't occur. I've also noticed that about the time the service crashes, Management Studio will have problems querying the DB. It will work again a few moments after the service crashes. I have been banging my head on the wall over this. I'm hoping someone here can look over the following code and see if they can spot what's going wrong.

Imports System.Data.SqlClient
Imports System.IO
Imports JDPCCommonLib.JDPCCommonLib
Imports System.Diagnostics

Public Class COGDBService

    Dim LstColumns As New List(Of String)
    Private worker As New Worker

    Protected Overrides Sub OnStart(ByVal args() As String)
        Dim wt As System.Threading.Thread
        Dim ts As System.Threading.ThreadStart
        ts = AddressOf worker.DoWork
        wt = New System.Threading.Thread(ts)
        wt.Start()
    End Sub

    Protected Overrides Sub OnStop()
        worker.StopWork()
    End Sub
End Class

Public Class Worker
    Private ThrMain As System.Threading.Thread
    Private StopThread As Boolean = False
    Private RndTime As New Random(Now.Millisecond)
    Dim computer As New Computer(My.Computer.Name)
    Dim db As New GDBLINQDataContext
    Dim strProcs, strPrinters, strSoftware, strDrivers, strDriverState, strProcThreadCount, strProcTime, strHD, strHDSize, strHDFree, strServices, strServiceState, strShares As String
    Dim DBCompObj As RComputer = New RComputer
    Public Sub StopWork()
        StopThread = True
        If Not ThrMain Is Nothing Then
            If Not ThrMain.Join(100) Then
                ThrMain.Abort()
            End If
        End If
    End Sub
    Public Sub DoWork()
        ThrMain = System.Threading.Thread.CurrentThread
        While Not StopThread
            computer.fillinfo()
            ProcessLists()
            If RowExists() Then
                Dim UpdateRow = From comp In db.RComputers Where comp.ComputerName = My.Computer.Name Select comp
                For Each comp As RComputer In UpdateRow
                    comp.ComputerName = computer.Name
                    comp.BIOS = computer.BIOS
                    comp.CPU = computer.CPU.Item(0)
                    .
                    .
                    .
                Next
            Else
                BuildRow()
                db.RComputers.InsertOnSubmit(DBCompObj)
            End If
            db.SubmitChanges()
        End While
    End Sub


    Private Sub ProcessLists()

        strProcs = String.Join(",", computer.Processes.ToArray())
        strPrinters = String.Join(",", computer.printers.ToArray())
        strServices = String.Join(",", computer.Services.ToArray())
        strServiceState = String.Join(",", computer.ServiceState.ToArray())
        strProcThreadCount = String.Join(",", computer.ProcThreadCount.ToArray())
        strProcTime = String.Join(",", computer.ProcessTime.ToArray())
        strServices = String.Join(",", computer.Services.ToArray())
        strServiceState = String.Join(",", computer.ServiceState.ToArray())
        strSoftware = String.Join(",", computer.software.ToArray())
        strDrivers = String.Join(",", computer.Drivers.ToArray())
        strDriverState = String.Join(",", computer.DriverState.ToArray())
        strHD = String.Join(",", computer.HD.ToArray())
        strHDFree = String.Join(",", computer.HDFree.ToArray())
        strHDSize = String.Join(",", computer.HDSize.ToArray())
        strShares = String.Join(",", computer.Shares.ToArray())

    End Sub

    Private Sub BuildRow()
        DBCompObj.ComputerName = computer.Name
        DBCompObj.BIOS = computer.BIOS
        DBCompObj.CPU = computer.CPU.Item(0)
        .
        .
        .
    End Sub

    Private Function RowExists() As Boolean
        Dim conn As SqlConnection = New SqlConnection(My.Settings.GalvestonConnectionString)
        Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Computers WHERE ComputerName = '" + My.Computer.Name + "'", conn)
        Try
            conn.Open()
            If (cmd.ExecuteReader().HasRows()) Then
                Return True
            Else : Return False
            End If
            conn.Close()
        Catch
            conn.Close()
            Return False
        End Try
    End Function
End Class

 

[JohnH]

In RowExists function you are not closing the connection, Using statement is useful.

It look to me you don't need RowExists function though, "UpdateRow" (results) get the same result?

While Not StopThread
  computer.fillinfo()
  ProcessLists()
  Dim results = From comp In db.RComputers Where comp.ComputerName = My.Computer.Name Select comp
  If results.Count > 0 Then
    For Each comp In results

    Next
  Else
    'insert
  End If
End While

 

[delstar]

*slaps forehead*
Leave it to me to make it more complicated than it needed to be. It appears to be working now, with the code you provided.

If there are any other obvious problems you see in the code, please let me know. This is my first full program utilizing LINQ. Also, would I be correct in assuming that since all instances of this program will be updating a row unique to themselves that concurrency shouldn't be an issue?

Thanks, your help is greatly appreciated.