Unique constraint validation

[Rob]

I'm having trouble figuring out how to incorporate unique constraints into model validation. For example, I'm working on an interface that will allow admins to add new users to the database. I need to make sure that two users cannot have the same username.

The database (SQL Server) has a unique constraint on the username field. I used the Domain Model tool in OpenAccess to bring the database metadata into my project (ASP.NET MVC 4). I took a look at the xml behind the rlinq file and verified that the unique constraint was successfully brought into the project. I then created an MVC view through which admins can enter the info for new users, but when I tried to put in a duplicate username, instead of a validation error (which would allow me to gracefully inform the admin that the username is already taken), I get a SQL error.

What is the best way for me to generate a validation error based on a violation of the unique constraint? Is there an elegant solution that exists as part of OpenAccess?

Note: I believe I'm using the latest version of OpenAccess. I downloaded it last week.

[Kaloyan Nikolov]

Hello Jacob,

 
For the time being OpenAccess ORM doesn't provide way to handle exceptions caused by  the unique constraints out of the box. This logic can be implemented in you business layer or in the MVC Contoller classes. 

I would offer you two approaches: 
1. To create a new method in your MVC controller that checks if the username is already taken. You can invoke that method with JavaScript when the user leaves the userName TextBox control. Then you will know it the username is taken or not and provide the user with meaningful validation message.
2. You can catch the SQL exception in your BusinessLayer/Controller when adding the new user and re-throw a custom exception with better message and then handle it in the way you prefer. 

If it is possible one and the same username to be added by more than one user simultaneously probably you should implement both.

Do not hesitate to contact us if you have other questions.

Regards,
Kaloyan Nikolov
the Telerik team

[Rob]

I like your suggestions. Although the odds of concurrency for this is low, I still would like to have something in place along the lines of #2. When a SQL exception is thrown, is it possible to somehow capture the error type? That would allow me, for example, to verify whether the error was thrown because of a violation of a unique constraint as opposed to some other error.

[Kaloyan Nikolov]

Hi Jacob,

If you want to distinguish the exception caused by unique constraint violation you can check the inner exception which provides the error number fired by the backend. See the code examples below.

try
{
    openAccessContext.Add(new UniqueTable() { Unique = 2 });
    openAccessContext.SaveChanges();
}
catch (Telerik.OpenAccess.Exceptions.DuplicateKeyException ex)
{
    var sqlEx = ex.InnerException as Telerik.OpenAccess.RT.sql.SQLException;
    if (sqlEx != null && sqlEx.Number == 2601)
    {
        //Handle unique constraint...
    }
}


try
{
    openAccessContext.Add(new RegularTable() { Id = 1 });
    openAccessContext.SaveChanges();
}
catch (Telerik.OpenAccess.Exceptions.DuplicateKeyException ex)
{
    var sqlEx = ex.InnerException as Telerik.OpenAccess.RT.sql.SQLException;
    if (sqlEx != null && sqlEx.Number == 2627)
    {
        //Handle primary key violation..
    }
}

NOTE: to use the SQLException you should add reference to: Telerik.OpenAccess.Runtime.dll

If you want to implement validation before submitting you data to the server you can have a look in the Remote Validation with DataAnnotation Attribute.

I hope that helps.

Greetings,
Kaloyan Nikolov
the Telerik team

[Rob]

Awesome, thanks! Will try that out shortly and respond once I've confirmed it works.