L
Lucvdv
Is there a way to determine if a server is still running, without having to
rely on exceptions thrown when a query is executed?
I'm trying to teach my VB app how to survive short server downtimes.
I've got one static (shared) connection object that's just sleeping most of
the time.
Once this connection has been established, almost everything is done using
new connections opened with code like this:
cn2 = New SqlClient.SqlConnection(cn1.ConnectionString)
cn2.Open
Start of the problem is that cn2.ConnectionState always returns Open, even
if the server was unavailable at the time of the "Open" call.
A subsequent attempt to run a query on the new connection results in error
11, "general network error".
So far it's no big problem, I can catch this exception and loop until the
operation succeeds. I created wrappers around the data access methods I'm
using (ExecuteReader, ExecuteScalar and ExecuteNonQuery) to handle it.
The important part is:
Public Function SqlExecReader(ByVal cmd As SqlCommand) As SqlDataReader
Try
SqlExecReader = cmd.ExecuteReader
Catch ex As SqlClient.SqlException
If ex.Number = 11 Then
Dim f As New frmWaitDatabase
f.Show()
Do
Try
Application.DoEvents()
System.Threading.Thread.Sleep(1000)
If cmd.Connection.State = ConnectionState.Closed Then _
cmd.Connection.Open()
SqlExecReader = cmd.ExecuteReader
Catch ex2 As SqlClient.SqlException
cmd.Connection.Close() ' initially not done here
....
Loop While SqlExecReader Is Nothing
f.Close
...
But after a couple of passes, I get another exception instead:
InvalidOperationException: "Timeout expired. The timeout period elapsed
prior to obtaining a connection from the pool. This may have occurred
because all pooled connections were in use and max pool size was
reached."
Initially the "...Connection.Close()" line wasn't there, because the
connection state _was_ "Closed" after the failure. Adding the explicit
Close() didn't stop this exception from occurring either.
It sounds dangerous to loop on this one too, because I might fail to detect
it if it occurs for another reason (I could end up in an endless loop if
there really are too many connections in use).
It doesn't seem normal either that ado.Net is using up connections until
the pool runs out, by just closing and reopening the same connection.
rely on exceptions thrown when a query is executed?
I'm trying to teach my VB app how to survive short server downtimes.
I've got one static (shared) connection object that's just sleeping most of
the time.
Once this connection has been established, almost everything is done using
new connections opened with code like this:
cn2 = New SqlClient.SqlConnection(cn1.ConnectionString)
cn2.Open
Start of the problem is that cn2.ConnectionState always returns Open, even
if the server was unavailable at the time of the "Open" call.
A subsequent attempt to run a query on the new connection results in error
11, "general network error".
So far it's no big problem, I can catch this exception and loop until the
operation succeeds. I created wrappers around the data access methods I'm
using (ExecuteReader, ExecuteScalar and ExecuteNonQuery) to handle it.
The important part is:
Public Function SqlExecReader(ByVal cmd As SqlCommand) As SqlDataReader
Try
SqlExecReader = cmd.ExecuteReader
Catch ex As SqlClient.SqlException
If ex.Number = 11 Then
Dim f As New frmWaitDatabase
f.Show()
Do
Try
Application.DoEvents()
System.Threading.Thread.Sleep(1000)
If cmd.Connection.State = ConnectionState.Closed Then _
cmd.Connection.Open()
SqlExecReader = cmd.ExecuteReader
Catch ex2 As SqlClient.SqlException
cmd.Connection.Close() ' initially not done here
....
Loop While SqlExecReader Is Nothing
f.Close
...
But after a couple of passes, I get another exception instead:
InvalidOperationException: "Timeout expired. The timeout period elapsed
prior to obtaining a connection from the pool. This may have occurred
because all pooled connections were in use and max pool size was
reached."
Initially the "...Connection.Close()" line wasn't there, because the
connection state _was_ "Closed" after the failure. Adding the explicit
Close() didn't stop this exception from occurring either.
It sounds dangerous to loop on this one too, because I might fail to detect
it if it occurs for another reason (I could end up in an endless loop if
there really are too many connections in use).
It doesn't seem normal either that ado.Net is using up connections until
the pool runs out, by just closing and reopening the same connection.