How To Catch Sql Server Error from Ado.net

Question

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

I did find this link which shows a VB example of catching a Sql Server Error, but so far, I still can't catch the actual exception. support.microsoft.com/kb/321903/en-us — Greg Gum
– Greg Gum, Commented Sep 25, 2013 at 16:53
Change ExecuteNonQueryAsync to ExecuteNonQuery, or research how the task-asynchronous pattern works. — usr
– usr, Commented Sep 25, 2013 at 17:00

Brian · Accepted Answer · 2013-09-25 19:01:47Z

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.

Collectives™ on Stack Overflow

How To Catch Sql Server Error from Ado.net

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related