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;
}
}
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?
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.
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';
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.
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;
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With