Validation to verify that the record is being used before deleting

Question

Validation to verify that the record is being used before deleting

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (1 votes)

3

My project's MVC is built into services, controllers, Views, and models.

My delete screen works, but when the log is used in other tables it displays this error.

The DELETE statement conflicted with the REFERENCE constraint "FK_XYZ". The conflict occurred in database "ETEST", table "dbo.TEST", column 'ID_TEST'.
The statement has been terminated.

This happens because the table's PK is FK in other tables.

I would like before deleting, the system would check if the registry is used in the database. To display a message to the friendly user before attempting to delete. Eg: Record can not be deleted because it is in use.

How do I construct this validation?

Model

public int Id {get; set;}

[Display(Name = "Codigo", ResourceType = typeof(Resources.TestResources))]
[Required]
public string Codigo { get; set; }

[Display(Name = "Descricao", ResourceType = typeof(Resources.TestResources))]
[Required]
public string Descricao { get; set; }

[Display(Name = "UsuarioAnalistaCusto", ResourceType = typeof(Resources.TestResources))]
[Required]
public string UsuarioAnalistaCusto { get; set; }

Controller

public void Excluir(int id)
    {
        this.Service.Delete(id);
    }

Services

public void Delete(int id)
        {
            var item = this.context.Test.Find(id);
            this.context.Test.Remove(item);
            base.Save();
        }

Context

namespace TXT.Test.eTest.DataAccess
{
    public partial class EContext : DbContext
    {
        static EContext()
        {
            Database.SetInitializer<ECContext>(null);
        }

        public EContext(bool proxyCreationEnabled = true)
            : base("Name=EContext")
        {
            base.Database.CommandTimeout = 60 * 5; // TOOO: utilizar configuration
            base.Configuration.ProxyCreationEnabled = proxyCreationEnabled;

            base.Configuration.AutoDetectChangesEnabled = false;
        }

        public DbSet<Empresa> Empresas { get; set; }
        public DbSet<Fornecedor> Fornecedores { get; set; }
        public DbSet<Desenho> Desenhos { get; set; }


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new EMPRESAMap());
            modelBuilder.Configurations.Add(new FORNECEDORMap());
            modelBuilder.Configurations.Add(new DESENHOMap());
            ...
        }
    ...
    }
}

c# entity-framework asp.net-mvc

asked by anonymous 19.08.2015 / 21:55

3 answers

1

Retired from: SQL Server: how to know if any row is referencing the row to delete

If your system is multiuser, what it probably is, checking if the registry can be deleted can cause a critical race problem:

Imagine that the system determines that the registry can be deleted, and allows the user to do so (or even proceed immediately to delete).

Imagine that between the two operations another user creates a related record (much less likely if they do not pass through the user between confirming that it can be deleted and deleting, but still not impossible): exactly the error that you tried to avoid, and that, by checking before, you feel that it could not occur, and therefore probably does not treat.

So, the appropriate solution is simpler than what you asked: try to do the operation, and treat the error. If the foreign key error is thrown, treat it and return a message to the user.

19.08.2015 / 22:17

1

Exactly, I agree with RSinohara, it would be much more put a try / catch inside the service delete, in the example below it works in SQL Server:

public void Delete(int id)
{
      try
      {
         var item = this.context.Test.Find(id);
         this.context.Test.Remove(item);
         base.Save();
      }
      catch (DbUpdateException ex)
      {
         var sqlException = ex.GetBaseException() as SqlException;

         if (sqlException != null)
         {
             var numero = sqlException.Number;

             if (numero == 547)
             {
               throw ex.Message = "Existe outras entidade relacionadas a essa, não foi possível apagar.";
             }
         }
     }
}

Controller you could use another try / catch to get the launch exception message from the bottom layer and put in the ModelState.AddModelError as Gypsy did.

20.08.2015 / 15:34

Calculate time logged - PHP A dynamic field for each database record

score 1 · Accepted Answer

Your system actually uses Entity Framework sim. The correct way to warn the user that the record can not be deleted is something like:

var registro = context.Registros.Include(r => r.RegistroDependente).FirstOfDefault(/* Coloque aqui a condição para selecionar */);
if (registro.RegistroDependente != null) {
    ModelState.AddModelError("", "Este registro não pode ser apagado pelo motivo tal.");
    return View();
}