-2

I have a table named countries and i define the country_name field to be unique by creating a “Index/Key” of type “Unique Key” on sql servwer 2008 r2. But currently if the user insert a country_name value that already exists on my asp.net mvc3 application, then an exception of type “System.Data.Entity.Infrastructure.DbUpdateException” will be raised , which is very general.

So is there a way to define a specific exception in case the unique constraint has been violated ??? rather than just raising the general “System.Data.Entity.Infrastructure.DbUpdateException” exception?

BR

John John
  • 1
  • 72
  • 238
  • 501

1 Answers1

2

Most likely, thought I can't test it at the moment, the inner exception of DbUpdateException is probably an exception about a duplicate or foreign key constraint. More importantly, you have an opportunity to not throw any exceptions by checking to see if a country already exists. Two ways I can think of are to; check and see if the country already exists by doing a simple select, and if it doesn't, doing an insert/add or write a stored procedure that and do a select/insert or merge and return any value(s) you want back.

Update

(this is example code to demonstrate the logic flow of events and not good programming practice, specially by catching all excepts)

Exception Logic

public AddCountry(string countryTitle)
{
  using (var db = new DbContext(_connectionString)
  {
    try
    {
      // Linq to (SQL/EF)-ish code
      Country country = new Country();
      country.ID = Guid.NewGuid();
      country.Title = countryTitle;
      db.Countrys.Add(country);
      db.SubmitChanges(); // <--- at this point a country could already exist
    }
    catch (DbUpdateException ex)
    {
      // <--- at this point a country could be delete by another user
      throw Exception("Country with that name already exists");
    }
  }
}

Non-Exception Logic

public AddCountry(string countryTitle)
{
  using (var db = new DbContext(_connectionString)
  {
    using (TransactionScope transaction = new TransactionScope())
    {
      try
      {
        Country country = db.Countries 
                            .FirstOrDefault(x => x.Title = countryTitle);

        if (country == null)
        {
          country = new Country();
          country.ID = Guid.NewGuid();
          country.Title = countryTitle;
          db.Countrys.Add(country);
          db.SubmitChanges(); // <--- at this point a country 
                              // shouldn't exist due to the transaction
                              // although someone with more expertise
                              // on transactions with entity framework
                              // would show how to use transactions properly
        }
      }
      catch (<someTimeOfTransactionException> ex)
      {
        // <--- at this point a country with the same name
        // should not exist due to the transaction
        // this should really only be a deadlock exception
        // or an exception outside the scope of the question
        // (like connection to sql lost, etc)
        throw Exception("Deadlock exception, cannot create country.");
      }
    }
  }
}

Most likely the TransactionScope(Transaction transactionToUse) Constructor would be needed and configured properly. Probably with an Transactions.IsolationLevel set to Serializable

I would also recommend reading Entity Framework transaction.

Community
  • 1
  • 1
Erik Philips
  • 53,428
  • 11
  • 128
  • 150
  • thanks for your reply, i prefer to use the exception approach as using the select approach might not give 100% accurate results in case of multiple users add and delete the same country name at the same time!!... but will using the exception approach considered to be a bad practice which i should avoid to use?? – John John Apr 21 '12 at 02:35
  • In my personal opinion, each instance in which someone decides to use or not use run-time exception logic may or may not be ideal. In this specific instance, exception logic does not provide any additional benefit from the alternatives I've described so I would recommend against it. I will update my answer with why I believe that is the case. – Erik Philips Apr 21 '12 at 02:58
  • thanks for the reply, i think i understood your point. Although my approach might give false error messages if the country record where just deleted between performing the .save() and raising the exception,, but it might not happen frequently,,, so still my approach might work on most of the cases ??? am i right? – John John Apr 21 '12 at 23:12
  • Yeah this is a preference of *Not Allowing Exception If They Can Be Prevented*. There no defacto-standard on this, just pros/cons. The chance that a delete happens is almost zero anyway. Your logic is sound and will produce the results you want. – Erik Philips Apr 22 '12 at 00:00