Avoid duplicate records in C # and PostgreSQL

Question

Avoid duplicate records in C # and PostgreSQL

Navigation

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

1

Hello colleagues I have a form where I register the members of a club but I would like to insert a record if I reject it if it already exists and if it does not exist that I insert it, this is my code.

                NpgsqlConnection cn = new NpgsqlConnection("SERVER=localhost;PORT=5432;UID=postgres;PWD=1234;DATABASE=Proyecto;");
                cn.Open();

                NpgsqlCommand query = new NpgsqlCommand("INSERT INTO miembros (idmiembro,nombre,apellidos,sexo,grupo,fecha_nacimiento,domicilio,status)" + "VALUES ('" + txtId.Text + "','" + txtNombre.Text + "','"
                + txtApellidos.Text + "','" + txtSexo.Text + "','" + txtGrupo.Text + "' ,'" + txtNacimiento.Text + "' ,'" + txtDomicilio.Text + "' ,'" + txtStatus.Text + "')", cn);
                    query.ExecuteNonQuery();
                    cn.Close();

                    MessageBox.Show("Miembro registrado exitosamente");
                    txtId.Text = "";
                    txtNombre.Text = "";
                    txtApellidos.Text = "";
                    txtSexo.Text = "";
                    txtGrupo.Text = "";
                    txtNacimiento.Text = "";
                    txtDomicilio.Text = "";
                    txtStatus.Text = "";
                }
            }

    }

c# postgresql

asked by karol 19.11.2016 в 22:07

source

3 answers

2

You could ask first in the database if the record you are going to insert exists, if it exists, it sends you a message that is already registered, otherwise you will register a new member.

public static bool Exist(string name)
    {
        using (var cn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
        {
            cn.Open();
            using (var cmd = cn.CreateCommand())
            {
                cmd.CommandText = "SELECT COUNT(*) FROM Clientes WHERE RazonSocial=?name";
                cmd.Parameters.AddWithValue("?name", name);

                var count = Convert.ToInt32(cmd.ExecuteScalar());
                if (count == 0)
                    return false;
                else
                    return true;
            }
        }
    }

Apply validation:

bool exist = ClienteRepository.Exist(entity.RazonSocial);
            if(exist)
                throw new ArgumentException("El Cliente, que intenta registrar ya existe.");
            else
            {
                ClienteRepository.Create(entity);
            }

This is a sample code, you must adapt it to your needs.

answered by 20.11.2016 в 21:21

0

What you could do is create a query that first updated, if the result did not generate any affected row then it does not exist. Then you proceed to insert the record.

I hope this example helps you:

update STEVNTF 
            set DESEVT = @desc_acti,
                STSTAB = @est_acti, 
                TPGST = @tpo_gasto
            where CCIA = @compania 
            and TIPEVT = @unid_neg 
            and CODEVT = @cod_acti

        if @@ROWCOUNT = 0   
            begin   
                insert into STEVNTF(CCIA,TIPEVT,CODEVT,DESEVT,STSTAB,TPGST) 
                values(@compania,@unid_neg,@cod_acti,@desc_acti,@est_acti,@tpo_gasto)



            end

answered by 23.11.2016 в 00:35

help error can not format given object as a date how to do a SELECT of two different fields of the same table in a single column in Oracle 11g?

score 4 · Accepted Answer

There are two ways to solve the problem that you indicate:

At the application level. That is, in your application (in this case, using C #) you create a function / method that searches if the user exists in the database. If it does not exist, register it. The method can be as simple as executing this query:

-- buena práctica, en Postgres es mejor usar COUNT(1)
-- que COUNT(*), el primero solo cuenta 1 elemento por fila
-- mientras que COUNT(*) traerá todos los elementos de la fila
-- para contarlos como 1, lo cual hace que COUNT(1) sea más rápido
SELECT COUNT(1) FROM miembros WHERE <condición de único>

Then you can run this query from your application before registering the value. The code would look like:

//buena práctica: las cadenas que vas a reutilizar en múltiples lugares
//deberían ser escritas como constantes en tu clase
const string CADENA_CONEXION = "SERVER=localhost;PORT=5432;UID=postgres;PWD=1234;DATABASE=Proyecto;";

//buena práctica: encapsula la información en clases
//y usa objetos para interactuar entre las capas de
//tu aplicación
public boolean existeMiembro(Miembro miembro)
{
    Int64 contador = 0;
    try
    {
        NpgsqlConnection cn = new NpgsqlConnection(CADENA_CONEXION);
        cn.Open();
        //buena práctica: usa parámetros en tus consultas
        NpgsqlCommand cmd = new NpgsqlCommand("SELECT COUNT(1) FROM miembros WHERE id = @id");
        cmd.Parameters.AddWithValue("@id", miembro.Id);
        contador = (Int64)cmd.ExecuteScalar();
        cn.Close();
    } catch (Exception ex)
    {
        //maneja la excepción
    }
    return contador > 0;
}

public void registraMiembro(Miembro miembro)
{
    if (!existeMiembro(miembro))
    {
        try
        {
            //resto de tu código
        }
    }
}

Note: this implies that instead of working directly with the fields of your form, record all that information in an object of class Miembro and then call your method registraMiembro with this object Miembro . This will make it easier to maintain your application.

This solution is good when your application is only used by one user at a time. If multiple users can use your application and register the same Miembro at the same time, then this solution falls short and you will still have duplicate records. To solve that problem, the next alternative.

Create a unique index in your table in the database. In Postgres, this is achieved with the following sentence:

CREATE UNIQUE INDEX miembro_id_unico ON miembros (id);

With that, when you insert a record with the same id, the database will throw an exception:

ERROR duplicate key value violates unique constraint "member_id_unico"