Catching SQL exceptions in .NET

Posted by Joe Enos on August 18, 2021 · 7 mins read

Here’s one way of handling SQL errors from .NET:

For expected errors, like data validation, raise an error with a specific error code and state. Unexpected errors will still blow up with whatever SQL Server’s error message is:

create procedure dbo.TestProc
(
@customError bit
,@sqlError bit
)
as
begin
begin transaction;
begin try
if @customError = 1
begin
raiserror('This is my custom error.', 16, 99);
return;
end;

if @sqlError = 1
begin
exec ('select * from SomeFakeTable');
return;
end;

select 1;

commit transaction;
end try
begin catch
if @@trancount > 0
rollback transaction;
declare @errorMessage nvarchar(4000) = error_message();
declare @errorSeverity int = error_severity();
declare @errorState int = error_state();
declare @procName nvarchar(4000) = object_name(@@procid);

-- TODO: Log Error information

raiserror(@errorMessage, @errorSeverity, @errorState);
end catch;
end;
go

In .NET, just check the code and state of the exception – if it matches your custom values, then you can pass the error message on to the client or whatever else you want to do with it.

try {
string cs = "...";
bool customError = false, sqlError = false;

using (var conn = new SqlConnection(cs)) {
conn.Open();
using (var comm = conn.CreateCommand()) {
comm.CommandText = "dbo.TestProc";
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddRange(new[] {
new SqlParameter("@customError", customError),
new SqlParameter("@sqlError", sqlError)
});
using (var rdr = comm.ExecuteReader()) {
if (rdr.Read()) {
Console.WriteLine("Success: " + rdr[0]);
}
}
}
}
} catch (SqlException ex) {
if (ex.Class == 16 && ex.State == 99) {
Console.WriteLine("Custom error: " + ex.Message);
} else {
Console.WriteLine("SQL error: " + ex.Message);
}
}
catch (Exception ex) {
Console.WriteLine(ex);
}

// For executing a proc through Entity Framework
catch (EntityCommandExecutionException ex) {
var sqlException = ex.InnerException as SqlException;
if (sqlException != null && sqlException.Class == 16 && sqlException.State == 99) {
// Custom error
}
}