Utility to check to see if a database is alive

A while back I had a need to come up with a quick and easy solution to be able to check whether a database was available via a command line utility.  The client had a software package that had the ability to run a command line application and read the results and record that to monitor the up time for database.

I decided to go with a simple VB solution using Visual Studio 2005.  I am sure there are better ways to accomplish the same thing though time was of the essence and I needed a workable solution and only had a day to do it.

I started off creating a new Visual Basic console project in Visual Studio.  I deleted the default module that was part of the project when it was created and created a new module named MainModule.  I added a new class to the module to manage the database connections.

I started with the connection class.  I needed to be able to handle multiple types of database connections.  Best practice is to have a class for each type of database connection all of which having the same functions to more ligically handle and manage the different code needed for the different connections.  I did not have time to do best practice.  Instead I put in a switch statement to manage the different database types.

First off I created class level variables to manage connection variables.

Private _oracleConnString As String

Private _sqlConnString As String
Private _odbcConnString As String

Private _dataType As String
Private _dataSource As String
Private _userId As String
Private _password As String
Private cnOracle As OracleClient.OracleConnection
Private cnSql As SqlClient.SqlConnection
Private cnOdbc As Odbc.OdbcConnection

Private writer As StreamWriter
Private logFile As String

After the variables I added a "New" sub procedure which receives values that are assigned to the class level variables.

Public Sub New(ByVal DataType As String, ByVal DataSource As String, ByVal UserID As String, ByVal Password As String)

_dataType = DataType
_dataSource = DataSource
_userId = UserID
_password = Password

_oracleConnString = "Data Source=[DATASOURCE];Persist Security Info=True;User ID=[USERID];Password=[PASSWORD];Unicode=True"
_sqlConnString = "Data Source=[DATASOURCE];Persist Security Info=True;User ID=[USERID];Password=[PASSWORD]"
_odbcConnString = "Dsn=[DATASOURCE];uid=[USERID];pwd=[PASSWORD]"

logFile = "dbcheck.log"
writer = New StreamWriter(logFile, True)
End Sub

The last part of this class is a function for the module code to call to open the database connection and return a result.  The function starts off with a variable to store a boolean value on whether the connection was successful or not.  The value of this variable is the return result for the function.  After that I created a switch statement based upon the database connection type (MS SQL, Oracle or ODBC).  Each connection attempt is surrounded by a try/catch statement.  If there are any errors the process writes the error to an error log.
 

Dim success As Boolean = False


Select Case _dataType

Case "Oracle"
_oracleConnString = _oracleConnString.Replace("[DATASOURCE]", _dataSource)
_oracleConnString = _oracleConnString.Replace("[USERID]", _userId)
_oracleConnString = _oracleConnString.Replace("[PASSWORD]", _password)
cnOracle = New OracleClient.OracleConnection(_oracleConnString)

Try
cnOracle.Open()
cnOracle.Close()
Return 1
Catch ex As Exception
Console.Write(ex.Message)
writer.WriteLine(Now.ToString())
writer.WriteLine(ex.Message)
Return 0
End Try

Case "SQL"
_sqlConnString = _sqlConnString.Replace("[DATASOURCE]", _dataSource)
_sqlConnString = _sqlConnString.Replace("[USERID]", _userId)
_sqlConnString = _sqlConnString.Replace("[PASSWORD]", _password)
cnSql = New SqlClient.SqlConnection(_sqlConnString)

Try
cnSql.Open()
cnSql.Close()
Return 1
Catch ex As Exception
Console.Write(ex.Message)
writer.WriteLine(Now.ToString())
writer.WriteLine(ex.Message)
Return 0
End Try

Case Else
_odbcConnString = _odbcConnString.Replace("[DATASOURCE]", _dataSource)
_odbcConnString = _odbcConnString.Replace("[USERID]", _userId)
_odbcConnString = _odbcConnString.Replace("[PASSWORD]", _password)
cnOdbc = New Odbc.OdbcConnection(_odbcConnString)

Try
cnOdbc.Open()
cnOdbc.Close()
Return 1
Catch ex As Exception
Console.Write(ex.Message)
writer.WriteLine(Now.ToString())
writer.WriteLine(ex.Message)
Return 0
End Try
End Select

Now that the connection class is complete it was time to put together the code for the main sub procedure in the main module that will capture the command line arguments and perform the connection test using the provided arguments.
 
I started off with the declaration of variables

Dim _dataType As String

Dim _dataSource As String
Dim _userId As String
Dim _password As String
Dim I As Integer = 0

Now that the variables are declared I added a process to cycle through the provided command line arguments and assign the values to the proper variables.

For Each arg As String In args

If arg.ToUpper = "/DATATYPE" Then
_dataType = args(I + 1)
End If

If arg.ToUpper = "/DATASOURCE" Then
_dataSource = args(I + 1)
End If

If arg.ToUpper = "/USERID" Then
_userId = args(I + 1)
End If

If arg.ToUpper = "/PASSWORD" Then
_password = args(I + 1)
End If

i = i + 1
Next

After the arguments are processed it is time to add the code that calls the connection class and tests to see if the connection is successful.

Dim success As Integer = 0


Try
Dim cn As Connection = New Connection(_dataType, _dataSource, _userId, _password)
success = cn.Open()
Catch ex As Exception
Console.Write(ex.Message)
End Try
If success = 1 Then
Console.Write("SUCCESS")
Else
Console.Write("FAILURE")
End If

A sample command line to execute this utility is:

dbcheck.exe /DATATYPE SQL /DATASOURCE SERVERNAME /USERID USERNAME /PASSWORD PASSWORD

To download a copy of the code click this link.