CODEWITHSUNDEEP
c#.netsqlsql-servervalidation
ryudice
ryudice

Reputation: 37376

How can I know if an SQLexception was thrown because of foreign key violation?

I want to tell the user that a record was not deleted because it has child data, but how can I be sure that the exception was thrown because of a foreign key violation? I see that there a sqlexception class that is used for all sql exception.

Upvotes: 25

Views: 25850

Answers (2)

aronchick
aronchick

Reputation: 7128

Assume you're using SQL Server.

Using teh web archive - https://web.archive.org/web/20190120182351/https://blogs.msdn.microsoft.com/tomholl/2007/08/01/mapping-sql-server-errors-to-net-exceptions-the-fun-way/

try
{
    # SQL Stuff
}
catch (SqlException ex)
{
    if (ex.Errors.Count > 0) // Assume the interesting stuff is in the first error
    {
        switch (ex.Errors[0].Number)
        {
            case 547: // Foreign Key violation
                throw new InvalidOperationException("Some helpful description", ex);
                break;
            case 2601: // Primary key violation
                throw new DuplicateRecordException("Some other helpful description", ex);
                break;
            default:
                throw new DataAccessException(ex);
        }
    }

}

Case 547 is your man.

UPDATE The above is sample code and should not be used. Please follow the link as to explain why.

Upvotes: 51

user6274856
user6274856

Reputation: 1

You can write your exception-expected code in the Try block if any exception will be thrown it will be catch further now you can get error number.now can check is it a Foreign Key violation or not

try
 {

//your deletetion code

 }catch (SqlException ex)
    {

        if (ex.Errors.Count > 0) // Assume the interesting stuff is in the first error
        {
            switch (ex.Errors[0].Number)
            {
                case 547: // Foreign Key violation
                    lblError.Text = "Cannot Delete this Record this is associated with other record...!";

                    break;
                default:
                  throw;

            }
        }
    }

Upvotes: -3

Related Questions