Navigation for dotNetTips.com - Common SQLConnection Methods in VB.NET

Below are some converted VB6 methods that do common database connection tasked. The first one (CheckSQLConnection) checks to make sure that a connection can be made to the database. This could be used at the beginning of your application or web application to make sure that the database is up. If it's not, doubt if there is much need in continuing.

The next method (CloseSQLConnection), closes a SQLConnection object if it is open. NEVER assume that the connection was successfully opened during the course of your application.

The last method (OpenSQLConnection), will open a SQLConnection object (and pass it back to the calling method) based on the connection string passed to it. The method will return True if it succeeded with no errors.

I always uses these methods wrapped with exception handling to open and close SQLConnections... safer that way. Also these methods use the new .NET SQLConnection object not the ADO object. The built in SQL objects in .NET have faster access to SQL than the ADO objects do! 

Imports System.Data
Imports System.Data.SqlClient

Public Function CheckSQLConnection(ByVal DBConnection As String) As Boolean
Dim pcnConnection As SQLConnection

  Try
    pcnConnection = New SQLConnection()
  Catch poExcep As Exception
    Return False
    Exit Function
  End Try

  Try
    pcnConnection.ConnectionString = DBConnection
    pcnConnection.Open()
  Catch poExcep As Exception
    'Could not make connection
    Return False
    Exit Function
  End Try

  If pcnConnection.State = ConnectionState.Open Then
    Return True
    Else
      Return False
  End If
  If Not pcnConnection Is Nothing Then
    CloseSQLConnection(pcnConnection)
  End If

End Function

Public Sub CloseSQLConnection(ByRef SQLConnObj As SQLConnection)

  Try
    If SQLConnObj.State <> ConnectionState.Closed Then
      SQLConnObj.Close()
    End If
  Catch poExcep As Exception
    'Blow by any errors
  End Try
End Sub

Public Function OpenSQLConnection(ByVal DBConnection As String, _
                                    ByRef SQLConnObj As SQLConnection, _
                                    Optional ByVal ConnectionTimeout As Integer = 15 _
                                    ) As Boolean

  If SQLConnObj Is Nothing Then
    Try
      SQLConnObj = New SQLConnection()
    Catch poExcep As Exception
      Return False
      Exit Function
    End Try
  End If

  'Setup Connection
  Try
    SQLConnObj.ConnectionTimeout = ConnectionTimeout
    SQLConnObj.ConnectionString = DBConnection
    SQLConnObj.Open()
  Catch poExcep As Exception
    Return False
    Exit Function
  End Try

  'Make sure it's open
  If SQLConnObj.State = ConnectionState.Open Then
    Return True
    Else
      Return False
  End If

End Function

Tip By: David McCarter