Validate unique keys with asp.net mvc and linq to sql?

Question

I have a sql server table with 2 fields, ID (primary key) and Name (unique key). I'm using linq to sql to produce model objects for asp.net MVC from this table.

To perform the model validation I've implemented IDateErrorInfo in a partial class

public partial class Company : IDataErrorInfo
{
    private Dictionary<string, string> _errors = new Dictionary<string,string>();

    partial void OnNameChanging(string value)
    {
        if (value.Trim().Length == 0)
        {
            _errors.Add("Name", "Name is required");
            return;
        }
    }
}

This performs as expected with the Model.IsValid property and Html.ValidationSummary helper.

However, this code is just checking that the newly created Company has a Name that is not blank. I also need to check if the Name has been used by another Company in the table.

I could just call the AddCompany method on my repository and catch the SQLException, but this feels dirty.

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Create([Bind(Exclude="ID")] Company companyToCreate)
{
if (!ModelState.IsValid)
{
    return View();
}
//Add to the Database
try
{
    _companyRepos.AddCompany(companyToCreate);
    return RedirectToAction("Index");
}
catch(SQLException ex)
{
    return View("do something to show the error inserting");
}
}

Ideally I want the OnNameChanging method in the partial class to perform the unique key check before I try to add the Company.

Any ideas on how I should be doing this? The only thought I've has so far is to create a fresh database connection in the partial class and query the table.

Thanks

Robert Harvey · Accepted Answer · 2012-12-27 01:16:28Z

2

One possibility is for the AddCompany method to return a boolean indicating success or failure of the operation.

However, it is customary to catch this type of error before you attempt to add the record. Put a

bool Exists(string companyName)

method in your Company Repository, and use this to catch the error before attempting to add the record.

The reason this is preferable is you know exactly why the failure occurred. Otherwise, you would either have to catch a custom exception or examine a returned error code.

edited Dec 27, 2012 at 1:16

answered Jun 10, 2009 at 4:42

Robert Harvey

182k48 gold badges349 silver badges516 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2021-09-19 13:35:10Z

1

However you slice it, you're going to obviously have to hit the database to get a list of names already in use. Therefore, I would suggest adding a method in your repository that basically just returns an IList<string> or IEnumerable<string> that simply contains all the distinct names in that table in the DB. Then, in your validating method, simply use that method on the repository to get all the unique names, and implement your check there.

edited Sep 19, 2021 at 13:35

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 10, 2009 at 4:42

BFree

104k21 gold badges164 silver badges205 bronze badges

1 Comment

Merritt Over a year ago

So who is going to consume that new repo method? The controller? Now your controller has business logic (and isn't it now a transaction script?) Is there someway to incorporate this into the business/domain layer/model?

Collectives™ on Stack Overflow

Validate unique keys with asp.net mvc and linq to sql?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related