Prevent from inserting duplicate records into the table

Controller : Create

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create(UserDefinedFieldsViewModel model)
{
using (var dbContextScope = _dbContextScopeFactory.Create())
{
var deletedValues = model.Values.Where(v => v.UIStatus == "DELETE").ToList();
foreach (var deletedValue in deletedValues)
{
model.Values.Remove(deletedValue);
}
if (!model.IsFreeformTextValue)
{
if (model.Values == null || (model.Values.Count == 1 && model.Values.First().UIStatus == "TEMPLATE"))
{
ModelState.AddModelError("Values", "Please enter at least one valid value.");
}
}
else
{
var userCodeType = attrRepo.Find(model.UserCodeTypeId);
if (!new string[] { "CO", "CT" }.Contains(userCodeType.Code))
{
ModelState.AddModelError("IsFreeformTextValue", "Freeform text values only allowed for Company and Contact attributes.");
}
else if (model.Values != null && model.Values.Where(v => v.UIStatus != "DELETE").Count() > 1)
{
ModelState.AddModelError("Values", "No values can be specified if 'Allow Freeform Text Value' is checked.");
}
}
if (ModelState.IsValid)
{
try
{
User user = _Login.GetCurrentUser();
UserCode userCode = model;
userCode.CustomerId = GetCustomerId();
attrRepo.AddUserCode(userCode, user);
dbContextScope.SaveChanges();
return RedirectToAction("Index");
}
catch (Exception e)
{
if (e is DbUpdateException dbUpdateEx)
{
if (dbUpdateEx.InnerException != null && dbUpdateEx.InnerException.InnerException != null)
{
if (dbUpdateEx.InnerException.InnerException is SqlException sqlException)
{
switch (sqlException.Number)
{
case 2601: // Duplicated key row error
// Constraint violation exception
ModelState.AddModelError("", "Error: You cannot create an attribute with the same name, type code as an existing attribute");
break;
default:
break;
}
}
}
}
else
{
ModelState.AddModelError("", "An error occured saving the attribute.");
}
}
}
model.UserCodeTypes = SelectListItemHelper.UserCodeTypes();
}
return View(model);
}

Controller :Edit

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit(UserDefinedFieldsViewModel model)
{
using (var dbContextScope = _dbContextScopeFactory.Create())
{
if (!model.IsFreeformTextValue)
{
if (model.Values == null || (model.Values.Where(v => v.UIStatus != "DELETE").Count() == 1 && model.Values.First().UIStatus == "TEMPLATE"))
{
ModelState.AddModelError("Values", "Please enter at least one valid value.");
}
}
else
{
var userCodeType = attrRepo.Find(model.UserCodeTypeId);
if (!new string[] { "CO", "CT" }.Contains(userCodeType.Code))
{
ModelState.AddModelError("IsFreeformTextValue", "Freeform text values only allowed for Company and Contact attributes.");
}
else if (model.Values != null && model.Values.Where(v => v.UIStatus != "DELETE").Count() > 1)
{
model.IsFreeformTextValue = false;
ModelState.AddModelError("Values", "No values can be specified if 'Allow Freeform Text Value' is checked.");
}
}
if (ModelState.IsValid)
{
model.Values.RemoveAll(x => x.UIStatus == "TEMPLATE" && x.Id == 0);
UserCode userCode = model;
// User user = _Login.GetCurrentUser();
try
{
foreach (var uvv in model.Values)
{
if (uvv.UIStatus == "DELETE")
{
try
{
attrRepo.DeleteUDFValidValue(uvv.Id, user);
var ucvv = userCode.UserCodeValidValues.Where(x => x.Id == uvv.Id).Single();
if (ucvv != null)
userCode.UserCodeValidValues.Remove(ucvv);
}
catch (RMRepositoryException re)
{
ModelState.AddModelError("Values", re.Message);
}
}
}
try
{
attrRepo.UpdateUserDefinedField(userCode, user);
} catch (NotSupportedException)
{
throw;
}
dbContextScope.SaveChanges();
return RedirectToAction("Index");
}
catch (Exception e)
{
if (e is DbUpdateException dbUpdateEx)
{
if (dbUpdateEx.InnerException != null && dbUpdateEx.InnerException.InnerException != null)
{
if (dbUpdateEx.InnerException.InnerException is SqlException sqlException)
{
switch (sqlException.Number)
{
case 2601: // Duplicated key row error
// Constraint violation exception
ModelState.AddModelError("", "Error: You cannot edit an attribute with the same name, type code as an existing attribute");
break;
default:
break;
}
}
}
}
else if (e is NotSupportedException nse)
{
ModelState.AddModelError("UserCodeTypeId", nse.Message);
}
else
{
ModelState.AddModelError("Values", e.Message);
}
}
}
var errors1 = ModelState.Values.SelectMany(v => v.Errors);
foreach (var a in errors1)
{
Debug.WriteLine(a.ErrorMessage);
}
//var errors = ModelState
// .Where(x => x.Value.Errors.Count > 0)
// .Select(x => new { x.Key, x.Value.Errors })
// .ToArray();
model.UserCodeTypes = SelectListItemHelper.UserCodeTypes();
}
return View(model);
}

Repositry.cs for Edit

public UserCode UpdateUserDefinedField(UserCode userCode, User user)
{
var dbCode = DbContext.UserCodes.Find(userCode.Id);
dbCode.Name = userCode.Name;
dbCode.Description = userCode.Description;
if (dbCode.UserCodeTypeId != userCode.UserCodeTypeId)
if (dbCode.UserCodeValidValues.Any(x=>x.UserCodeValues.Any()))
throw new NotSupportedException("You cannot change the attribute type because it has values in use.");
dbCode.UserCodeTypeId = userCode.UserCodeTypeId;
dbCode.IsFreeformTextValue = userCode.IsFreeformTextValue;
dbCode.IsMandatory = userCode.IsMandatory;
// Need to handle unique constraint violation
foreach (UserCodeValidValue ucvv in userCode.UserCodeValidValues)
{
UserCodeValidValue compare = dbCode.UserCodeValidValues.Where(x => x.Id == ucvv.Id).FirstOrDefault();
if (compare != null)
{
if (ucvv.Description != compare.Description || ucvv.CodeValue != compare.CodeValue)
{
compare.CodeValue = ucvv.CodeValue;
compare.Description = ucvv.Description;
UpdateWithUser(compare, user.UserId, "UpdateUserDefinedField");
}
}
else
{
ucvv.UserCode = dbCode;
AddWithUser(ucvv, user.UserId, "UpdateUserDefinedField");
}
}
UpdateWithUser(dbCode, user.UserId, "UpdateUserDefinedField");
return dbCode;
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Siddharth Gajbhiye6y ago
Hello Praveen ,

You can check wheather the records are already in the table or not if exist then it should br rollback and throw a message
+1
Periyasamy Vellingirisamy6y ago
Hi Praveen

Specify Unique Key for the required columns. So that you can avoid dublicate entry.
0