Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get the exact "constraint name" from SQLException

Is there a way to get the exact "constraint name"\"index name" from C# "Microsoft SQL Exception", Error number 2601 or 2627, but without parsing the text of the "Message property"?

For example:

catch (SqlException e)
{
   switch (e.Number)
   {
      case 2601:
         /* Here i want to know the constraint name in case i have 
            more than one on a specific table, so i will be able to 
            display the correct error message to the user. 

          For example: 

          case IX_Username:
           throw new Exception("Username duplication")
          case IX_PhoneNumber:
           throw new Exception("PhoneNumber duplication")  

        */
         break;
      default:
         throw;
   }
 }
like image 692
Omtechguy Avatar asked Oct 17 '14 12:10

Omtechguy


People also ask

What is a constraint name in SQL?

The constraint name contains the name of the table ( student) and the name of the column ( name ). How Do You Write a SELECT Statement in SQL?

How do you generate a SQLException?

In both cases, a SqlException is generated by the method executing the command. For information about the warning and informational messages sent by SQL Server, see Database Engine Events and Errors. The SqlException class maps to SQL Server severity. The following is general information on handling exceptions.

How do I display constraints in SQL Server?

We use INFORMATION_SCHEMA.TABLE_CONSTRAINTS to display the constraints. Here, we display the name (CONSTRAINT_NAME) and the type of the constraint (CONSTRAINT_TYPE) for all existing constraints. SELECT INFORMATION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='TABLE_NAME';

What does the column constraint_type indicate?

The column constraint_type indicates the type of constraint: PRIMARY KEY for the primary key type, FOREIGN KEY for the foreign key type, UNIQUE for the unique values, and CHECK for the constraint check. For example, the primary key in the student table has the constraint name PK_student_3213E83F357C7D1D.


2 Answers

Use naming convention for constraints e.g. name these to always contain underscore like FK_Xxx or UQ_Xxx then use regex to parse errors for names like this

    var match = Regex.Matches("Foreign key FK_Xxx violation.", @"\b\w*_\w*\b")
        .Cast<Match>().FirstOrDefault();
    return match != null ? match.Value : null;
like image 146
wqw Avatar answered Oct 18 '22 05:10

wqw


Is there a way to get the exact "constraint name"\"index name" from C# "Microsoft SQL Exception", Error number 2601 or 2627, but without parsing the text of the "Message property"?

No, there is no such way.

like image 25
Erti-Chris Eelmaa Avatar answered Oct 18 '22 03:10

Erti-Chris Eelmaa