Q: Is there a better way to handle SqlExceptions?
The below examples rely on interpreting the text in the message.
Eg1: I have an existing try catch to handle if a table does not exist.
Ignore the fact that I could check if the table exists in the first place.
try
{
//code
}
catch(SqlException sqlEx)
{
if (sqlEx.Message.StartsWith("Invalid object name"))
{
//code
}
else
throw;
}
Eg2: without the try catch showing duplicate key exception
if (sqlEx.Message.StartsWith("Cannot insert duplicate key row in object"))
Solution: The start of my SqlExceptionHelper
//-- to see list of error messages: select * from sys.messages where language_id = 1033 order by message_id
public static class SqlExceptionHelper
{
//-- rule: Add error messages in numeric order and prefix the number above the method
//-- 208: Invalid object name '%.*ls'.
public static bool IsInvalidObjectName(SqlException sex)
{ return (sex.Number == 208); }
//-- 2601: Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls.
public static bool IsDuplicateKey(SqlException sex)
{ return (sex.Number == 2601); }
}
When called in a CATCH block, ERROR_MESSAGE returns the complete text of the error message that caused the CATCH block to run. The text includes the values supplied for any substitutable parameters - for example, lengths, object names, or times. ERROR_MESSAGE returns NULL when called outside the scope of a CATCH block.
CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection. A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.
You can easily trace all errors of Stored Procedures in MS SQL Server. To do this, first create a table called Error. Now if the procedure gives any error, the error details will be saved into the Error table. By this way you can easily get all error details from the Error table and can take the necessary steps.
This class is created whenever the . NET Framework Data Provider for SQL Server encounters an error generated from the server. (Client side errors are thrown as standard common language runtime exceptions.) SqlException always contains at least one instance of SqlError.
The SqlException has a Number property that you can check. For duplicate error the number is 2601.
catch (SqlException e)
{
switch (e.Number)
{
case 2601:
// Do something.
break;
default:
throw;
}
}
To get a list of all SQL errors from you server, try this:
SELECT * FROM sysmessages
Update
This can now be simplified in C# 6.0
catch (SqlException e) when (e.Number == 2601)
{
// Do something.
}
Sort of, kind of. See Cause and Resolution of Database Engine Errors
class SqllErrorNumbers
{
public const int BadObject = 208;
public const int DupKey = 2627;
}
try
{
...
}
catch(SqlException sex)
{
foreach(SqlErrorCode err in sex.Errors)
{
switch (err.Number)
{
case SqlErrorNumber.BadObject:...
case SqllErrorNumbers.DupKey: ...
}
}
}
The problem though is that a good DAL layer would us TRY/CATCH
inside the T-SQL (stored procedures), with a pattern like Exception handling and nested transactions. Alas a T-SQL TRY/CATCH
block cannot raise the original error code, will have to raise a new error, with code above 50000. This makes client side handling a problem. In the next version of SQL Server there is a new THROW construct that allow to re-raise the original exception from T-SQL catch blocks.
It is better to use error codes, you don't have to parse.
try
{
}
catch (SqlException exception)
{
if (exception.Number == 208)
{
}
else
throw;
}
How to find out that 208 should be used:
select message_id
from sys.messages
where text like 'Invalid object name%'
If you want list of error messages met in Sql server, you can see with
SELECT *
FROM master.dbo.sysmessages
You can evaluate based on severity type. Note to use this you must be subscribed to OnInfoMessage
conn.InfoMessage += OnInfoMessage;
conn.FireInfoMessageEventOnUserErrors = true;
Then your OnInfoMessage would contain:
foreach(SqlError err in e.Errors) {
//Informational Errors
if (Between(Convert.ToInt16(err.Class), 0, 10, true)) {
logger.Info(err.Message);
//Errors users can correct.
} else if (Between(Convert.ToInt16(err.Class), 11, 16, true)) {
logger.Error(err.Message);
//Errors SysAdmin can correct.
} else if (Between(Convert.ToInt16(err.Class), 17, 19, true)) {
logger.Error(err.Message);
//Fatal Errors 20+
} else {
logger.Fatal(err.Message);
}}
This way you can evaluate on severity rather than on error number and be more effective. You can find more information on severity here.
private static bool Between( int num, int lower, int upper, bool inclusive = false )
{
return inclusive
? lower <= num && num <= upper
: lower < num && num < upper;
}
With MS SQL 2008, we can list supported error messages in the table sys.messages
SELECT * FROM sys.messages
If you are looking for a better way to handle SQLException, there are a couple things you could do. First, Spring.NET does something similar to what you are looking for (I think). Here is a link to what they are doing:
http://springframework.net/docs/1.2.0/reference/html/dao.html
Also, instead of looking at the message, you could check the error code (sqlEx.Number
). That would seem to be a better way of identifying which error occurred. The only problem is that the error number returned might be different for each database provider. If you plan to switch providers, you will be back to handling it the way you are or creating an abstraction layer that translates this information for you.
Here is an example of a guy who used the error code and a config file to translate and localize user-friendly error messages:
https://web.archive.org/web/20130731181042/http://weblogs.asp.net/guys/archive/2005/05/20/408142.aspx
I am working with code first, C# 7 and entity framework 6.0.0.0. it works for me
Add()
{
bool isDuplicate = false;
try
{
//add to database
}
catch (DbUpdateException ex)
{
if (dbUpdateException.InnerException != null)
{
var sqlException = dbUpdateException.InnerException.InnerException as SqlException;
if(sqlException != null)
isDuplicate = IsDuplicate(sqlException);
}
}
catch (SqlException ex)
{
isDuplicate = IsDuplicate(ex);
}
if(isDuplicate){
//handle here
}
}
bool IsDuplicate(SqlException sqlException)
{
switch (sqlException.Number)
{
case 2627:
return true;
default:
return false;
}
}
N.B: my query for add item to db is in another project(layer)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With