StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODatabase Connection Management in a Multi-Thread Service
text
Body
copied!<p>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.</p> <p>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.</p> <p>I did some troubleshooting using eventlog to get the exceptions, here are some of the exception I got before crashing:</p> <p><em>A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)</em></p> <p><em>The ConnectionString property has not been initialized.</em></p> <p><em>ExecuteReader requires an open and available Connection. The connection's current state is connecting.</em></p> <p><em>ExecuteReader requires an open and available Connection. The connection's current state is open</em>.</p> <p><em>Invalid attempt to call Read when reader is closed.</em></p> <p>Here is a sample of the code I am using:</p> <pre><code> 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 IsRemoteConnected = 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 </code></pre> <p>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.</p> <p>Any advice, Thanks for you time.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload