8

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; } }
Improve this question
7
  • 1
    Sadly gotta parse... Commented Oct 17, 2014 at 12:13
  • 1
    Is it not possible to execute a query to check for duplicates rather than resorting to a constraint violation? Commented Oct 17, 2014 at 12:35
  • User1620220, Its possible but as far as i understand, it will not be good on high database load due to "Race condition" issue and preference. Commented Oct 17, 2014 at 12:41
  • You would need to execute the query and the insert/update commands within a transaction with an appropriate isolation level to eliminate the race condition. Commented Oct 17, 2014 at 12:50
  • 1
    Inside a transaction, first execute one or more SELECT ... FROM table (WITH UPDLOCK) queries to check if username or phone number exists. Then do your INSERT or UPDATE command. No updates can occur on the table until you commit the transaction. Commented Oct 17, 2014 at 18:41

3 Answers 3

Reset to default
3

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

1 Comment

So can you please suggest the best way to handle that error on very loaded database with lots of queries per second? Pleae take into the account the "Race condition" issue.
0

Asuming the error have a text simil to (restriction between quote):

Infracción de la restricción UNIQUE KEY 'CIF duplicado no admitido'. No se puede insertar una clave duplicada en el objeto 'dbo.GASTOS_Proveedores'. El valor de la clave duplicada es (asd).

you can find first and second quote and extract the message:

CIF duplicado no admitido

 create function GetRestictionError(@MensajeError nvarchar(255)) returns nvarchar(255) as begin declare @Msg nvarchar(255) set @Msg = @MensajeError declare @PrimCom int declare @SecondCom int --asci 39+ ' set @PrimCom = charindex(char(39),@MensajeError) if @PrimCom > 0 begin set @SecondCom = charindex(char(39),@MensajeError+1, @Primcom + 1) if @SecondCom > 0 set @Msg = SUBSTRING(@mensajeerror,@primcom+1,@secondcom - @primcom - 1) end return @Msg end

and then, use when you need. Sample:

begin catch /*SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; */ declare @Titulo nvarchar(255) set @Titulo = dbo.GetRestictionError(ERROR_MESSAGE()) if @Titulo = '' set @Titulo = 'Error al guardar' select 0 as Respuesta, ERROR_MESSAGE() as Registro, @Titulo as titulo -- ERROR_PROCEDURE () as Titulo end catch
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.