.NET Framework - How best pass errors from SQL to business, user tiers?

Asked By Ronald S. Cook

26-Jun-07 12:03 PM

How would you recommend passing errors back from your proc to your business
tier to the client (assuming you have full control of all layers)?

Take InsertUser for instance.  The proc will automatically raise an error if
someone tries to create a user name for themselves that is already taken.

I could relay that error to the client but the message is not so user
friendly.

I could do a SELECT in my proc to see if the user name is already in use and
could then send back @@ERROR = 99 or some other value.  But then that would
require my two layers "getting together" on what each value will mean.

Should I instead be sending back more user friendly error strings from the
proc that are then passed back to the client?

What is a best practice that you would recommend?

Thanks,
Ron

26-Jun-07 01:38 PM

Do you have a unique constraint on your database column?

Or are you adding code to do the check (instead of the constraint)?

A ~~~~long time ago, I wrote something like this (below).

Where certain numbered TSQL numbers were matched to a description.
This was way back when the EMAB (exception management application block) was
recent technology.




Public Class TsqlBaseException : Inherits
Microsoft.ApplicationBlocks.ExceptionManagement.BaseApplicationException


Private m_errorNumber As Int32
Private m_errorDescription As String

Public Sub New(ByVal tsqlErrorNumber As Int32)

MyBase.New((MessageArchive.LookupMessage(tsqlErrorNumber)),
Nothing)

Me.m_errorNumber = tsqlErrorNumber
Me.m_errorDescription =
MessageArchive.LookupMessage(ErrorNumber)

End Sub

Public ReadOnly Property ErrorNumber() As Int32
Get
Return Me.m_errorNumber
End Get
End Property

Public ReadOnly Property ErrorDescription() As String
Get
Return Me.m_errorDescription
End Get
End Property


End Class





Public Class MessageArchive


Public Const DBERROR_SUCCESS As Integer = 0
'' Use 50000-59999 for "generic errors"
Public Const DBERROR_FAILURE As Integer = 50000  ' used for general
failure
Public Const DBERROR_CONCURRENCY_FAILURE As Integer = 50001
Public Const DBERROR_ITEM_EXISTS As Integer = 50002
Public Const DBERROR_NO_DATA_FOUND As Integer = 50003


'' Use 60001-69999 for "app specific errors"
Public Const DBERROR_TITLENAME_DOESNOTEXIST As Integer = 60001  ' used
for specific failure
Public Const DBERROR_TITLENAME_EXISTS_BUTNOT_TITLEID As Integer = 60002
' used for specific failure


Private Sub New()
End Sub

Public Shared Function LookupMessage(ByVal errorNumber As Integer) As
String

Dim returnValue As String = String.Empty
Select Case errorNumber
Case DBERROR_SUCCESS
returnValue = "The procedure was sucessful"

Case DBERROR_FAILURE
returnValue = "The procedure failed"

Case DBERROR_CONCURRENCY_FAILURE 'This was the timestamp one
returnValue = "The procedure failed due to a concurrency
issue"

Case DBERROR_ITEM_EXISTS
returnValue = "The procedure failed because the data item
with the necessary unique value already exists"

Case DBERROR_NO_DATA_FOUND
returnValue = "The procedure failed because no data was
found matching the input criteria"


''' App Specific Below this Line (>60000)

Case DBERROR_TITLENAME_DOESNOTEXIST
returnValue = "A title with that value does not exist"

Case DBERROR_TITLENAME_EXISTS_BUTNOT_TITLEID
returnValue = "A title with that value exists, but the
titleId is invalid"


Case Else
returnValue = "An Unknown Error has occurred.  Return_Value
= '" + returnValue.ToString() + "'  Please alert a developer that there is a
mismatch between the ErrorNumber and the Lookup Value"

End Select

Return returnValue

End Function




End Class

26-Jun-07 02:46 PM

Ysgrifennodd Ronald S. Cook:

We use custom exceptions.  That is, we throw an exception of an
appropriate type, with an appropriate message.

Our exceptions are defined in their own libraries, which we reference
wherever they are needed.  Needless to say all our custom exceptions
share a namespace.


Peter