Database Connection Management in a Multi-Thread Service

I made a windows service that listens to a port(using HttpListner) and accepts XML Repuest and once the request is valid it connects to a database (usually on the same pc) and construct the xml response and sends it back.

So far, Everything is great except when the service accepts two or more requests that require retrieving 100+ records, it crashes. First, I was using just one shared connection to the database, it does crashes, then I changed to multiple connections i.e. when you need something from the database create your connection.

I did some troubleshooting using eventlog to get the exceptions, here are some of the exception I got before crashing:

A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)

The ConnectionString property has not been initialized.

ExecuteReader requires an open and available Connection. The connection's current state is connecting.

ExecuteReader requires an open and available Connection. The connection's current state is open.

Invalid attempt to call Read when reader is closed.

Here is a sample of the code I am using:

 Private Sub StartLisitng()
    MyListener.Start()
    Dim result As IAsyncResult
    result = MyListener.BeginGetContext(New AsyncCallback(AddressOf ListnerCallback), MyListener)
End Sub

    Private Sub ListnerCallback(ByVal result As IAsyncResult)
    StartLisitng()
    If result.IsCompleted Then
        ctx = MyListener.EndGetContext(result)
        Dim HandleRequestThread As New Thread(AddressOf New HandleRequest(ctx).RequestProcess)
        HandleRequestThread.Start()

    End If
End Sub

   Public Function RemoteConnect(ByVal DNSBranch As String) As Boolean
    Dim IsRemoteConnected As Boolean = False
    RemoteConnString = "Data Source = " & DNSBranch & ";Initial Catalog=xxxxx;User ID=xxxxx;Password=xxxxx;Connect Timeout=5;MultipleActiveResultSets=True;"

    RemoteConn = New SqlConnection(RemoteConnString)
    Try
        RemoteConn.Open()
    Catch ex As Exception
        IsRemoteCon开发者_JAVA技巧nected = False
    End Try

    If RemoteConn.State = 1 Then
        IsRemoteConnected = True
    Else
        IsRemoteConnected = False
    End If

    Return IsRemoteConnected

End Function

Public Function RemoteExeComd(ByVal DNSBranch As String, ByVal query As String) As DataSet

    Dim MyDataSet As New DataSet
    RemoteConnect(DNSBranch)

    Comd = New SqlCommand(query, RemoteConn)
    Adapter = New SqlDataAdapter(Comd)

    If query.StartsWith("Se") Or query.StartsWith("se") Or query.StartsWith("SE") Then
        If RemoteConn.State = ConnectionState.Open Then
            Try
                Adapter.Fill(MyDataSet)
            Catch ex As Exception
                EventLog.WriteEntry("My Service", "Exception: " & ex.Message.ToString)
            End Try
        End If
    Else
        Try
            Comd.ExecuteNonQuery()
        Catch ex As Exception
            EventLog.WriteEntry("My Service", "Exception: " & ex.Message.ToString)
        End Try
    End If

    Comd.Dispose()
    Adapter.Dispose()

    Return MyDataSet
End Function

I do not have any problems with the listner or parsing the request, I know I have the problem on how i interact with the databse.

Any advice, Thanks for you time.