CODEWITHSUNDEEP
vb.netwinformssqlconnection
boilers222
boilers222

Reputation: 1989

In vb.net project, how to check if SQL connection is still available and end all processing?

I have a problem in a vb.net Windows Form project. My company is having network issues and is losing connection between the Windows Form application and the SQL server. When this happens, the application locks up and closes. Before any SQL commands are executed, is there a way to check if the SQL connection is even available?

I've tried using this:

If (cmd.Connection.State <> ConnectionState.Closed)

after the cmd is setup like this:

Dim cmd As SqlCommand
cmd = MSSQL_CONN.CreateCommand

but the state is often Open because the network connection fails after the SQL command was initialized.

I've tried to catch the error like this:

Private m_conn As SqlConnection

Try
    m_conn = New SqlConnection(cn_str)
    Call m_conn.Open()
Catch e As Exception
    MessageBox.Show("Error")
    If MSSQL_CONN.TransactionStarted Then
        MSSQL_CONN.RollbackTransaction()
     End If
End Try

but this has problems because it's trying to do a rollback for any other errors and the rollback throws an error because of the loss of connection state. This also starts a cascade into other timers and background processes that continue to run and try to connect to the SQL server.

I'm hoping there something like

If *SQL connection is even still available* Then
  Call m_conn.Open()
Else
  *Don't execute any other SQL on this form or any other background forms*
End If

Upvotes: 0

Views: 2185

Answers (1)

Karen Payne
Karen Payne

Reputation: 5157

Normally it's best to use a using statement for a connection where the connection is scoped to the method you are working with the database. Otherwise you could create the connection as needed e.g. check the connection state as on error.

Public Class DataOperations
    Public Shared Async Function Connection() As Task(Of SqlConnection)
        Dim cn As New SqlConnection With {.ConnectionString = "TODO"}

        Try
            Await cn.OpenAsync()
            Return cn
        Catch ex As Exception
            Return New SqlConnection
        End Try
    End Function
End Class

Then there is locally scoped where the choice is OpenAsync or Open.

Public Shared Function GetCatagories() As DataTable
    Dim dt As New DataTable

    Using cn As New SqlConnection With {.ConnectionString = "TODO"}
        Using cmd As New SqlCommand With {.Connection = cn}
            cmd.CommandText = "TODO"

            Try
                cn.Open()
                dt.Load(cmd.ExecuteReader())

            Catch ex As Exception
                ' decide on how to handle
            End Try
        End Using
    End Using

    Return dt

End Function

EDIT:

Public Shared Async Function IsServerConnected() As Task(Of Boolean)
    Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
        Try
            Await cn.OpenAsync()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Using
End Function

Upvotes: 1

Related Questions