Data Browser - Viewing Site  Sector 23 Code Bank Logged in as:  Guest  




           


How to convert SQL exceptions to more meaningful exceptions
SQL exceptions are hard to handle and are not user friendly. A very simple way to process them is to handle the common ones and return custom exceptions which you can capture on your UI and handle accordingly. Other unexpected exceptions can be assumed to be fatal and you can just redirect to an error page.

Here's the exceptions I generally capture, and how to do it using delegates with C# / .NET 4.0

1. Create your exception classes inheriting from Exception. You don't really need anything in them, the Type is sufficient to catch and handle them. I typically use three:

/// <summary>
/// The exception that is thrown when attempting to create data which already exists and cannot be duplicated
/// </summary>
public sealed class EntityAlreadyExistsException : System.Exception
{
private const string defaultMessage = "This entity already exists.";
/// <summary>
/// Ctor
/// </summary>
public EntityAlreadyExistsException() : base(defaultMessage) { }
/// <summary>
/// Ctor
/// </summary>
/// <param name="message"></param>
public EntityAlreadyExistsException(string message) : base(message) { }
/// <summary>
/// Ctor
/// </summary>
/// <param name="message"></param>
/// <param name="innerException"></param>
public EntityAlreadyExistsException(string message, Exception innerException) : base(message, innerException) { }

} // class

/// <summary>
/// The exception that is thrown when attempting to save/validate data which is invalid.
/// </summary>
public sealed class EntityInvalidException : System.Exception
{
private const string defaultMessage = "This entity is invalid and cannot be saved.";
/// <summary>
/// Ctor
/// </summary>
public EntityInvalidException() : base(defaultMessage) { }
/// <summary>
/// Ctor
/// </summary>
/// <param name="message"></param>
public EntityInvalidException(string message) : base(message) { }
/// <summary>
/// Ctor
/// </summary>
/// <param name="message"></param>
/// <param name="innerException"></param>
public EntityInvalidException(string message, Exception innerException) : base(message, innerException) { }

} // class

/// <summary>
/// The exception that is thrown when attempting to delete data which is being referenced by another entity.
/// </summary>
public sealed class EntityInUseException : System.Exception
{
private const string defaultMessage = "This entity is in use and cannot be deleted.";
/// <summary>
/// Ctor
/// </summary>
public EntityInUseException() : base(defaultMessage) { }
/// <summary>
/// Ctor
/// </summary>
/// <param name="message"></param>
public EntityInUseException(string message) : base(message) { }
/// <summary>
/// Ctor
/// </summary>
/// <param name="message"></param>
/// <param name="innerException"></param>
public EntityInUseException(string message, Exception innerException) : base(message, innerException) { }

} // class

2. Create your delegate and function to catch exceptions:
public delegate void MethodDelegate();
/// <summary>
/// Ensures that the expected SQL exception is thrown when the function is invoked
/// </summary>
/// <param name="function"></param>
internal static void WrapSqlCall(MethodDelegate function)
{
try
{
function.Invoke();
}
catch (SqlException ex)
{
if (ex.Number == 2601)
{ // duplicate unique index
throw new EntityAlreadyExistsException("Record already exists in the system.");
}
else if (ex.Number == 2627)
{ // duplicate primary key
throw new EntityAlreadyExistsException("Record already exists in the system.");
}
else if (ex.Number == 547)
{ // foreign key
if (ex.Message.Contains("INSERT statement"))
throw new EntityInvalidException(ex.Message); // entering data with bad key
else if (ex.Message.Contains("CHECK constraint"))
throw new EntityInvalidException(ex.Message);
else if (ex.Message.Contains("UPDATE statement"))
throw new EntityInvalidException(ex.Message); // updating data to invalid key
throw new EntityInUseException("This record is being used by another record: " + ex.Message); // trying to delete record referenced by a key
}
else
{
throw; // unexpected
}
}
}

3. Wrap any SQL call that performs insert/update/delete in the handler by using the following:
Database.WrapSqlCall(() => { MyDbFunctionHere(any_params); });

An ADO example:
Database.WrapSqlCall(() => { cmd.ExecuteNonQuery(); });
A LINQ example:
Database.WrapSqlCall(() => { dataContext.SpDeleteProc(this.Id); });

Created By: amos 11/19/2013 4:31:56 PM
Updated: 11/19/2013 4:33:34 PM