Introduction
Error handling implementation in Windows database applications is a must for any Application Developer today. The main idea of error handling is to avoid application crashes by finding out the occurred errors and fixing them. Different programming languages have different ways of implementing error handling. Microsoft Visual Basic .NET (VB.NET) is the most common programming language for developing windows database applications today. VB.NET codes are provided in many websites, books and materials without error handling implementation. In this article I would like to introduce you to the basics of error handling implementation in MySQL/VB.NET windows database applications. I’ll be covering structured exception error handling, which was introduced for the first time in VB.NET 2002. I will also go over the latest technologies of disposing unmanaged recourses in .NET Framework with the Using statement. To write exception errors to a log text file, a generic procedure will be developed while MySQL data is loading, inserting, updating and deleting. Required Software

MySQL Database Server 5.1.30
MySQL Connector/NET 5.2.5
Toad for MySQL Freeware 4.1
Microsoft Visual Basic 2008 Express Edition
Why is Exception Error Handling Required?


I assume this question would be easy to answer for any Application Developer. Any developer would like to know why its program does not work properly, and how to fix it. To find out these questions the Application Developers need to write the code with the error handling. Below are the three main reasons why error handling implementation in business applications development is very important:
1. Avoid application crashes
2. Show the occurred errors, in a user-friendly way, to the users
3. Store occurred errors for future application upgrades and auditing

I always recommend my clients and students to develop business applications with error handling implementation. I also suggest that they properly store these occurred errors for future reference. In general, many Application Developers store these errors in a log text file or in a specific database error-capture table. Both approaches would be acceptable. I like to use a log text file because the error may occur with the database connection or any database problem, in this case I cannot insert the error record into the table. Let’s look at the error handling implementation in VB.NET 2002 and 2003 first so we can compare them with the latest 2008 version. Structured Exception Error Handling

Structured exception handling implementation appears in Microsoft technologies for the first time in VS.NET 2002. This error handling was implemented by using Try…Catch…Finally statement in .NET Framework. The Try…Catch…Finally statement guarantees an easy way to handle all possible errors (exception errors) that may occur while the application is running. It contains three main blocks:

Try – contains the real programming code to be controlled for any possible error.

Catch – produces the error occurred during applications execution from Try block.

Finally – always executes last, regardless of whether the code in the Catch block has also executed. In general, this block is used for cleanup resources, like closing files and releasing created custom and unmanaged resources (objects).

Below, in Listing 1, is the standard code of the exception error structure using MySQLException class:

' Declaring and initializing objects
Try
' Programming lines of code
Catch exError As MySqlException
' Error handling code
Finally
' Cleanup custom and unmanaged resources if necessary
End Try

Listing 1: Structured exception error handling code using MySQLException class

So far, the main problems I found were in the Try and Finally blocks. Most Application Developers understand the purpose of the Try block very well. It’s very clear that the Try block must include the entire real programming code of the application. If any code is written outside the Try block and it generates an execute error, the application will inevitably crash. I believe no one has any doubt about this. So, why do VB.NET Application Developers write code outside this block today? I have no idea!

Let’s look at the following code below (Listing 2). As you can see the connection string property (ConnectionString) of the MySQL ADO.NET connection object MySqlConnectionObject has been hardcode outside the error handling structure. This is a very bad programming practice because the connection string should not be hardcoded and set outside the error handling structure. One more thing, the ADO.NET connection object has been closed inside the Try block. This line of code should be done in the Finally block as required by definition (Listing 3). Don’t dispose the connection object at the end of the Try block because if for any reason the application crashes before, the connection object will not be destroyed. It’ll be floating on the server waiting for the Garbage Collector (GC) process.

Dim MySqlConnectionObject = New MySqlConnection
MySqlConnectionObject.ConnectionString = “server=???;user id=???;password=???;database=???;"
Try
MySqlConnectionObject.Open()
' More programming code…
MySqlConnectionObject.Close()
Catch exError As MySqlException
MsgBox("An Error Occurred. " & exError.Number & “ – & exError.Message)
Finally
' Cleanup custom and unmanaged resources
End Try

Listing 2: Open and close the MySQL ADO.NET connection object inside the Try block
Dim MySqlConnectionObject = New MySqlConnection
MySqlConnectionObject.ConnectionString = “server=???;user id=???;password=???;database=???;"
Try
MySqlConnectionObject.Open()
' More programming code…
Catch exError As MySqlException
MsgBox("An Error Occurred. " & exError.Number & “ – & & exError.Message)
Finally
MySqlConnectionObject.Close()
' Cleanup custom and unmanaged resources
End Try

Listing 3: Close the MySQL ADO.NET connection object inside the Finally block
A simple approach could be to write two generic functions for opening and closing the MySQL ADO.NET connection object inside the Try block as shown in Listing 4. We can see that the connection string was passed by value to the generic function MySQLOpenConnection() and it was set to the connection object inside the Try block. For more info about MySQL ADO Connection String please read my paper "Define and Store MySQL ADO Connection String in VB.NET 2005" and downloads the source code from my website Visual WWW Downloads. Just to mention the connection object has been closed properly in the Try block in generic function MySQLCloseConnection(). In real business application development its bad programming practices to keep open a MySQL ADO.NET connection object all the times. We don’t want many connection objects open at the same time in a production server. It's better to open the connection object, do the required database transactions (load, insert, update and delete) and then close it properly. I think, in general, these two functions could be applied in any MySQL/VB.NET Windows database applications development.

Public Sub MySQLOpenConnection(ByVal pConnectionString As String, _
ByRef pErrorMessageString As String)
Try
MySqlConnectionObject.ConnectionString = pConnectionString
MySqlConnectionObject.Open()
Catch exError As MySqlException
pErrorMessageString = exError.Number & " - " & exError.Message
End Try
End Sub

Public Sub MySQLCloseConnection(ByRef pErrorMessageString As String)
Try
If Not MySqlConnectionObject Is Nothing Then
If MySqlConnectionObject.State = ConnectionState.Open Then
MySqlConnectionObject.Close()
MySqlConnectionObject.Dispose()
End If
End If
Catch exError As MySqlException
pErrorMessageString = exError.Number & " - " & exError.Message
End Try
End Sub

Listing 4: Generic functions to open and close MySQL ADO.NET connection object

Let’s look at our VB.NET 2008 example project. To do a better implementation of error handling and code organization let’s create a class object ExceptionClass shown in Listing 5. As you can see, I always use in my MySQL/VB.NET papers, a disposal class object. It was created to properly release the ExceptionClass recourses by implementing the interface IDisposable. The class object ExceptionClass is designed inside the namespace WritingExceptionLibrary and two libraries have been imported: the MySQL Connector/NET data library MySqlClient for database connection and transactions; and the input/output system library IO for reading and writing to a text file.

Imports MySql.Data.MySqlClient
Imports System.IO

Namespace WritingExceptionLibrary
Public Class ExceptionClass
Inherits ObjectDisposeClass
Private MySqlConnectionObject As New MySqlConnection
Private PositionStreamWriter As StreamWriter
' Developed custom properties, methods and events…
End Class

Public Class ObjectDisposeClass
Implements IDisposable
Private disposedValue As Boolean = False
Public Sub Dispose() Implements IDisposable.Dispose
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
' TODO: free unmanaged resources when explicitly called
End If
' TODO: free shared unmanaged resources
End If
Me.disposedValue = True
End Sub
End Class
End Namespace


Listing 5: Class object ExceptionClass design code

In Listing 6 we see the code of the test form object WritingExceptionErrorsForm. This form includes two imported libraries, the MySQL Connector/NET data library MySqlClient and the namespace project WritingExceptionLibrary. A new instant ExceptionClassObject of the class object ExceptionClass is declared and initialized. The connection string MySQLConnectionString and the name of the exception log file ExceptionErrorFileString have been defined and stored in the application configuration settings file as shown in Table 1. The path of the exception log file has been defined in the same folder of the application executable file as Application.StartupPath().

Imports MySql.Data.MySqlClient
Imports WritingExceptionErrors.WritingExceptionLibrary

Public Class WritingExceptionErrorsForm
Private ExceptionClassObject As New ExceptionClass
Private MySQLConnectionString As String = My.Settings.MySQLConnectionString
Private ExceptionErrorFileString As String = Application.StartupPath() & "\" & My.Settings.ExceptionErrorFile
Private WriteErrorMessageString As String
Private ErrorMessageString As String

Private Sub WritingExceptionErrorsForm_FormClosed(ByVal sender As Object, _
ByVal e As System.Windows.Forms.FormClosedEventArgs) _
Handles Me.FormClosed
If Not ExceptionClassObject Is Nothing Then
ExceptionClassObject.Dispose()
ExceptionClassObject = Nothing
End If
Dispose()
End Sub

Private Sub ExitButton_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles ExitButton.Click
Close()
End Sub

End Class

Listing 6: Form object WritingExceptionErrorsForm code
Name
Type
Scope
Value
MySQLConnectionString
(ConnectionString)
Application
Server=???;Database=???;Uid=???;Pwd=???;
ExceptionErrorFile
String
Application
ExceptionErrorFile.log
Table 1: Application configuration settings for MySQL connection string and exception error log file path
After we designed our VB.NET example project structure and before continuing to write more error handling codes, let’s look at the .NET Using statement.

Full article...