Catch Specific exception in SqlClient
-
I am writing an HR application to add new employees. Currently, I am having an issue where I want to send a specific message base on the exception that is received. In my database SNN is a unique key hence if an SSN is being added that already exist in the DB a Unique key violation exception is thrown. If the UserID already exist a Primary Key violation exception is thrown. I am certain this is possible but just not sure what is best practice. I can write a method which search the DB first and if the record is found thrown an error message based on whether it was the Unique key violation or the PK violation. I have also been searching MSDN to see if I can identify the C# SqlClient error ID for either scenarios and based on the exception ID, alert the user with the right error message. Any advice will be greatly appreciated.
Skan If you knew it would not compile why didn't you tell me?!?!?!
-
I am writing an HR application to add new employees. Currently, I am having an issue where I want to send a specific message base on the exception that is received. In my database SNN is a unique key hence if an SSN is being added that already exist in the DB a Unique key violation exception is thrown. If the UserID already exist a Primary Key violation exception is thrown. I am certain this is possible but just not sure what is best practice. I can write a method which search the DB first and if the record is found thrown an error message based on whether it was the Unique key violation or the PK violation. I have also been searching MSDN to see if I can identify the C# SqlClient error ID for either scenarios and based on the exception ID, alert the user with the right error message. Any advice will be greatly appreciated.
Skan If you knew it would not compile why didn't you tell me?!?!?!
Skanless wrote:
I have also been searching MSDN to see if I can identify the C# SqlClient error ID for either scenarios and based on the exception ID, alert the user with the right error message
All the Ids can be found in SQL Server itself.
SELECT * FROM sys.messagesorSELECT * FROM sysmessages(if you are on SQL Server 2000)
Upcoming FREE developer events: * Developer Day Scotland My website
-
Skanless wrote:
I have also been searching MSDN to see if I can identify the C# SqlClient error ID for either scenarios and based on the exception ID, alert the user with the right error message
All the Ids can be found in SQL Server itself.
SELECT * FROM sys.messagesorSELECT * FROM sysmessages(if you are on SQL Server 2000)
Upcoming FREE developer events: * Developer Day Scotland My website
-
I am writing an HR application to add new employees. Currently, I am having an issue where I want to send a specific message base on the exception that is received. In my database SNN is a unique key hence if an SSN is being added that already exist in the DB a Unique key violation exception is thrown. If the UserID already exist a Primary Key violation exception is thrown. I am certain this is possible but just not sure what is best practice. I can write a method which search the DB first and if the record is found thrown an error message based on whether it was the Unique key violation or the PK violation. I have also been searching MSDN to see if I can identify the C# SqlClient error ID for either scenarios and based on the exception ID, alert the user with the right error message. Any advice will be greatly appreciated.
Skan If you knew it would not compile why didn't you tell me?!?!?!
Use IF Exists in a trigger and then throw custom exceptions from the sp. However, SQL server will natively through the message number so there is no reason for custom handling to catch it.
Need a C# Consultant? I'm available.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway -
Use IF Exists in a trigger and then throw custom exceptions from the sp. However, SQL server will natively through the message number so there is no reason for custom handling to catch it.
Need a C# Consultant? I'm available.
Happiness in intelligent people is the rarest thing I know. -- Ernest HemingwayThis worked perfectly!!! IF EXISTS (SELECT Emp_Login_ID FROM Employees where Emp_Login_ID = @Emp_Login_ID ) RETURN -1 ELSE IF EXISTS (SELECT Emp_SSN FROM Employees where Emp_SSN = @Emp_SSN ) RETURN -2 ELSE INSERT INTO........ I am no longer receiving the Exception. However, I am not sure how to throw the Error message in C#. How would C# know to throw and Exception if the insert does nto execute? Thanks for you help. Note: All this is done in a single stored procedure. Which is then called in the application in a try, catch statement.
Skan If you knew it would not compile why didn't you tell me?!?!?!
modified on Thursday, December 06, 2007 12:44:52 AM
-
This worked perfectly!!! IF EXISTS (SELECT Emp_Login_ID FROM Employees where Emp_Login_ID = @Emp_Login_ID ) RETURN -1 ELSE IF EXISTS (SELECT Emp_SSN FROM Employees where Emp_SSN = @Emp_SSN ) RETURN -2 ELSE INSERT INTO........ I am no longer receiving the Exception. However, I am not sure how to throw the Error message in C#. How would C# know to throw and Exception if the insert does nto execute? Thanks for you help. Note: All this is done in a single stored procedure. Which is then called in the application in a try, catch statement.
Skan If you knew it would not compile why didn't you tell me?!?!?!
modified on Thursday, December 06, 2007 12:44:52 AM
Thanks for the help guys. I got it working with the following lines of code. I will simplify my code later but just need to get it working.
SqlParameter paramReturnValue = new SqlParameter();
paramReturnValue.ParameterName = "@return_value";
paramReturnValue.SqlDbType = SqlDbType.Int;
paramReturnValue.Direction = ParameterDirection.ReturnValue;cmd.Parameters.Add(paramReturnValue);
Skan If you knew it would not compile why didn't you tell me?!?!?!
-
I am writing an HR application to add new employees. Currently, I am having an issue where I want to send a specific message base on the exception that is received. In my database SNN is a unique key hence if an SSN is being added that already exist in the DB a Unique key violation exception is thrown. If the UserID already exist a Primary Key violation exception is thrown. I am certain this is possible but just not sure what is best practice. I can write a method which search the DB first and if the record is found thrown an error message based on whether it was the Unique key violation or the PK violation. I have also been searching MSDN to see if I can identify the C# SqlClient error ID for either scenarios and based on the exception ID, alert the user with the right error message. Any advice will be greatly appreciated.
Skan If you knew it would not compile why didn't you tell me?!?!?!
A System.Data.SqlClient.SqlException has an Errors array which contains the specific error(s). I search it for 2601 (unique index violation) or 2627 (primary key violation), and if present I wrap it in a custom exception. You can take a look at my DatabaseAccessor article which contains an enum of the code I support.