vb.net: Checking SQL Server status

Question

Sometimes when my customers turn on or restart their computer, and open my vb.net application directly, the application opens before SQL Server has started completely.

This results in many unexpected behaviors. To avoid this situation, I need to start a splash screen and check the SQL Server state within it, and only when SQL Server state indicates that it is loaded completely, can I run the whole application.

The question is: how to check the SQL Server state, whether it is finished loading or not? The whole SQL Server, not the database.

How about pinging it and then executing some basic query to confirm it is alive? — Andrew Mortimer, Jan 20 '17 at 08:45
Same as Andrew suggestion (FYR: http://stackoverflow.com/questions/2440060/whats-the-best-way-to-test-sql-server-connection-programmatically) — Prisoner, Jan 20 '17 at 09:12
Just use a `Try Catch` and attempt to open a SQL connection to see if you can connect. — Bugs, Jan 20 '17 at 09:36
Are you referring to a SQL Server instance that runs on the same machine as the vb.net application? — Damien_The_Unbeliever, Jan 20 '17 at 14:27

score 0 · Accepted Answer · answered Jan 20 '17 at 14:23

You can instantiate a timer in your splash form that checks if it can log into the database every one second (or whatever interval you like). I'd invoke the splash form as modal so the calling app can't continue until the splash form has detected the connection and closed itself.

At the very least you need the server name to check the connection for. If it is using a named instance then the server name should also include the instance name in the format "myserver\myinstance".

I've encapsulated the connection checking logic in the 3 overloaded functions IsConnected. You can use these functions in your splash form to check connection from the timer tick. (Each depends on the next). You can use whichever function overload is suitable based on the input items you have available.

For the first overload, if the app is running under a Windows security context that can connect to the db server then you don't need to provide the username and password (pass as empty string), otherwise you need to provide those credentials needed to login to the db server. Or you can provide your own connection string or connection object for the other overloads.

(code within the splash form)...

Private Sub Timer1_Tick(sender As Object, e As System.EventArgs) Handles Timer1.Tick
    If Me.IsConnected("(local)\SQL2008R2", "", "") Then Me.Close()
End Sub


Public Function IsConnected(ServerName As String, UserID As String, Password As String) As Boolean
    Dim connStr As String = String.Format("Data Source={0}", ServerName)
    If Not String.IsNullOrEmpty(UserID) Then
        connStr &= String.Format(";User ID={0};Password={1}", UserID, Password)
    Else
        connStr &= ";Integrated Security=True"
    End If
    Return IsConnected(connStr)
End Function

Public Function IsConnected(Connection As String) As Boolean
    Static conn As SqlConnection
    If conn Is Nothing Then
        conn = New SqlConnection(Connection)
        conn.Open()
    End If
    Return IsConnected(conn)
End Function

Public Function IsConnected(ByRef Conn As SqlConnection) As Boolean
    If Conn IsNot Nothing Then Return (Conn.State = ConnectionState.Open)
    Return False
End Function

I'd invoke the splash form from the main app as a modal dialog, as such, so the app is blocked until the connection is detected.

(from the calling app form...)

        frm_Splash.ShowDialog()

Perfect! I'll try it. Thanks you. – user6218508 Jan 25 '17 at 12:18 — user6218508, Jan 25 '17 at 12:18

vb.net: Checking SQL Server status

1 Answers1