CODEWITHSUNDEEP
c#ado.netsql-server-2008-r2
Greg Gum
Greg Gum

Reputation: 37905

How To Catch Sql Server Error from Ado.net

I have the following code which calls a stored procedure. I want to be able to trap any error that occurs during the running of the stored procedure.

try {
            using (var connection = GetConnection()) {

                using (SqlCommand cmd = connection.CreateCommand()) {
                    connection.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "VerifyInitialization";
                    cmd.Parameters.Add(new SqlParameter("@userId", user.Id));
                    cmd.Parameters.Add(new SqlParameter("@domainId", user.DomainId));
                    cmd.ExecuteNonQueryAsync();
                }

            }
        }
        catch (Exception ex) {
            throw new LoginException(LoginExceptionType.Other, ex.Message);
        }

This is the stored procedure, which basically just calls other stored procedures.

 ALTER PROCEDURE [dbo].[VerifyInitialization] 
-- Add the parameters for the stored procedure here
@userId int, 
@domainId int
 AS
 BEGIN
Begin Try
SET NOCOUNT ON;
Exec VerifyInitializationOfDefaultLocalizationItems
Exec VerifyInitializationOfLayoutLists @domainId
Exec VerifyInitializationOfLayoutListItems @domainId
Exec VerifyInitializationOfLocalizationItems @domainId
Exec VerifyInitializationOfLookupLists @domainId
Exec VerifyInitializationOfLookupListItems @domainId
End try

Begin Catch
  -- Raise an error with the details of the exception
    DECLARE 
    @ErrMsg nvarchar(4000) = Error_message(), 
    @ErrSeverity int = ERROR_SEVERITY();

    RAISERROR(@ErrMsg, @ErrSeverity, 1) 
End Catch
End

What do I need to do to catch an error in the Stored Proc that will be returned back to C#? Say for example a field name is renamed which prevents one of the stored procs from running. I don't want it to fail silently.

Greg

Upvotes: 5

Views: 7172

Answers (1)

Brian
Brian

Reputation: 5119

Using ExecuteNonQueryAsync() in your case, isn't as good as using ExecuteNonQuery().

try {
   using (var connection = GetConnection()) {
      using (SqlCommand cmd = connection.CreateCommand()) {
         connection.Open();
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.CommandText = "VerifyInitialization";
         cmd.Parameters.Add(new SqlParameter("@userId", user.Id));
         cmd.Parameters.Add(new SqlParameter("@domainId", user.DomainId));
         //cmd.ExecuteNonQueryAsync(); - This line should be .ExecuteNonQuery()
         cmd.ExecuteNonQueryAsync(); 
      }
   }
}

catch (Exception ex) {
   throw new LoginException(LoginExceptionType.Other, ex.Message);
}

Another thing you may want to consider is catching a more specific SqlException as opposed to the more general Exception, like this:

catch (SqlException exc) {
   throw new SqlException(/* Handle your exception messaging here. */);
}

catch (Exception ex) {
   throw new LoginException(LoginExceptionType.Other, ex.Message);
}

EDIT: I posted this answer not realizing that @usr had already answered it in the comments above. I will delete if you like.

Upvotes: 3

Related Questions