Differentiate between a database connection problem and database read problem
-
In my app I need to come up with a way to differentiate between a database connection error and a database read error. Let me give you an example. Say I have a Class Category with a function GetCategory that returns a category object like this,
Private Function GetCategory(ByVal CategoryID) As Category
Using mycon As New MySqlConnection(My.Settings.Orders) Dim myCat As New Category mycon.Open() 'Code to return category her Return myCat End Using End FunctionNormally to call this you would have some code like this :
Try
Dim newCategory As Category = GetCategory(myCatID)
Catch ex As MySqlException
'error handling code here
End Trybut if you do it like this then you can't tell whether the exception was caused by a faulty db connection or whether the exception was thrown in reading the table. What I have done so far is to create 2 custom exceptions (DBRead and DBOpen Exceptions) and then trap the mysqlexception twice in each routine like this
Private Function GetCategory(ByVal CategoryID) As Category
Dim mycon As New MySqlConnection(My.Settings.Orders) Try Dim myCat As New Category mycon.Open() Try 'code to get category from db Catch ex As MySqlException Throw New DBReadException(ex.Message, ex) End Try Return myCat Catch ex As MySqlException Throw New DBOpenException(ex.Message, ex) Finally If mycon.State = ConnectionState.Open Then mycon.Close() End If End Try End Functionbut this solution seems really klunky and very inelegant, as I would have to trap errors twice in each function as a pose to only when I call the function as in the first example. If someone has a better approach I would really appreciate knowing how you do it.
-
In my app I need to come up with a way to differentiate between a database connection error and a database read error. Let me give you an example. Say I have a Class Category with a function GetCategory that returns a category object like this,
Private Function GetCategory(ByVal CategoryID) As Category
Using mycon As New MySqlConnection(My.Settings.Orders) Dim myCat As New Category mycon.Open() 'Code to return category her Return myCat End Using End FunctionNormally to call this you would have some code like this :
Try
Dim newCategory As Category = GetCategory(myCatID)
Catch ex As MySqlException
'error handling code here
End Trybut if you do it like this then you can't tell whether the exception was caused by a faulty db connection or whether the exception was thrown in reading the table. What I have done so far is to create 2 custom exceptions (DBRead and DBOpen Exceptions) and then trap the mysqlexception twice in each routine like this
Private Function GetCategory(ByVal CategoryID) As Category
Dim mycon As New MySqlConnection(My.Settings.Orders) Try Dim myCat As New Category mycon.Open() Try 'code to get category from db Catch ex As MySqlException Throw New DBReadException(ex.Message, ex) End Try Return myCat Catch ex As MySqlException Throw New DBOpenException(ex.Message, ex) Finally If mycon.State = ConnectionState.Open Then mycon.Close() End If End Try End Functionbut this solution seems really klunky and very inelegant, as I would have to trap errors twice in each function as a pose to only when I call the function as in the first example. If someone has a better approach I would really appreciate knowing how you do it.
I don't know the MySqlException class, but by analogy to SqlException and OracleException, I would guess that there is some sort of error code that you can look at to determine the exact nature of the underlying error. C.f. SqlException.Errors or OracleException.Code. By looking at this you should be able to tell whether it is a read error or not. As for creating your own exception type, use the same approach: you have one exception, MyDbException, and add a property to that, e.g. MyDbException.ErrorCode where ErrorCode tells you what went wrong (Connect or Read). Then you only have one exception to deal with. There are other advantages to this, for example you are then hiding the dependency on MySql so if you ever do switch to a different database you won't have so much code to rework.
-
I don't know the MySqlException class, but by analogy to SqlException and OracleException, I would guess that there is some sort of error code that you can look at to determine the exact nature of the underlying error. C.f. SqlException.Errors or OracleException.Code. By looking at this you should be able to tell whether it is a read error or not. As for creating your own exception type, use the same approach: you have one exception, MyDbException, and add a property to that, e.g. MyDbException.ErrorCode where ErrorCode tells you what went wrong (Connect or Read). Then you only have one exception to deal with. There are other advantages to this, for example you are then hiding the dependency on MySql so if you ever do switch to a different database you won't have so much code to rework.
In the MySqlException Class you have a Number Property that returns an int containting the MySql Error Code (The ErrorCode Property Returns the HResult) but that could be all of nearly 4000 codes, depending on the specific error (i.e 1049 - Unknown Database Error, 1045 - Access Denied Error) and to check for these seems overboard really. I like your idea of adding a property to my custom exceptions so that in the calling function I only need to trap one error instead of 2, although I can't see it helping me when I trap the original error unless I can get the MySql connector to actually throw my custom exception (impossible ?). I don't really know to be honest, but many thanks for your input.