CODEWITHSUNDEEP
c#asp.net.netsql-server
Omtechguy
Omtechguy

Reputation: 3651

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;
   }
 }

Upvotes: 8

Views: 2480

Answers (3)

R.Alonso
R.Alonso

Reputation: 1065

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

Upvotes: 0

wqw
wqw

Reputation: 11991

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;

Upvotes: 3

Erti-Chris Eelmaa
Erti-Chris Eelmaa

Reputation: 26268

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.

Upvotes: 1

Related Questions