Inserting single quotes into null fields

Question

I created a very simple application to simulate a small client registration, but when testing the manipulation of data by the application I could see in the database that the fields that are null in the register are inserted in the database with two simple quotes ('').

In other applications I can solve this by changing the data type of the parameter to NpgsqlDbType.Text, but I don’t know a way to do this at EF.

I use an entity POCO class mapped in this way:

[Table("cliente", Schema = "public")]
public class Cliente
{
    [Key]
    [Column("id")]
    public int Id { get; set; }

    [Required(ErrorMessage = "Nome não pode ser nulo.")]
    [Column("nome")]
    public string Nome { get; set; }

    [Required(AllowEmptyStrings = true)]
    [Column("endereco")]
    public string Endereco { get; set; }


    [Column("bairro", TypeName="text")]
    public string Bairro { get; set; }

    [Required(ErrorMessage = "Cidade não pode ser nulo.")]
    [Column("cidade")]
    public int CidadeID { get; set; }

    [ForeignKey("CidadeID")]
    public Cidade Cidade { get; set; }

    [Column("cpfcnpj")]
    public string CPFCNPJ { get; set; }

    [Column("telefone")]
    public string Telefone { get; set; }

    [Column("ativo")]
    public bool Ativo { get; set; }

    public virtual IQueryable<Cliente> Clientes { get; set; }

}

Below method that inserts the customer data informed in the form in the database:

    public static void InserirCliente(Cliente cli)
    {
        using (var db = new Repositorio.DBContexto())
        {
            try
            {
                db.Clientes.Add(cli);
                var usuarioSalvo = db.SaveChanges();
            }
            catch (Exception)
            {
                throw;
            }
        }
    }

Below method of the event started by Click of a Save button:

    private void btnSalvar_ItemClick(object sender, ItemClickEventArgs e)
    {

        try
        {
            var cliente = new Cliente();
            cliente.Nome = Convert.ToString(txtNome.EditValue);
            cliente.Telefone = Convert.ToString(txtTelefone.EditValue);
            cliente.CPFCNPJ = Convert.ToString(txtCPF.EditValue);

            cliente.Endereco = Convert.ToString(txtEndereco.EditValue);
            cliente.Bairro = Convert.ToString(txtBairro.EditValue);
            cliente.CidadeID = Convert.ToInt32(lkeCidade.EditValue);
            cliente.Ativo = true;

            DAL.ClienteDAL.InserirCliente(cliente);

            MessageBox.Show("Cliente Inserido com Sucesso!", "Sucesso", MessageBoxButtons.OK, MessageBoxIcon.Information);
            this.DialogResult = DialogResult.OK;
            this.Close();

        }
        catch (Exception ex)
        {
            MessageBox.Show(string.Format("{0}\n\n{1}", ex.Message, ex.InnerException), "Ooops", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

    }

Is there any way to prevent the insertion of "single quotes" in fields that allow null values?

Answer 1

The problem is that the data in the application is as string empty and not null, so save a string empty. If you want to save a null to the database you need to ensure that the data is null.

If the data is already null, just do this:

cliente.Nome = txtNome.EditValue;

I see no reason to make a conversion to string of something that already is string. The conversion from a null to string gives an empty and not a null object, as demonstrated by documentation. The error is there, so the other fields work.

Taking advantage, if there is any chance of not having a valid number in the integer value field below, there will be an unnecessary exception (this is programming error):

Convert.ToInt32(lkeCidade.EditValue)

I put in the Github for future reference.

Also, one should never capture an exception just to relaunch it. This only causes problems. If you have nothing useful to do when catching an exception, do not capture it.

Capture Exception also not usually suitable in most situations. As well as trying to close some things without a design pattern that ensures this will be closed. Current code can leak open resources easily.

Answer 2

Thanks to Mr Maniero’s comment I went to make sure that the value of the field was zero... I added a validation in the conversion of the values to the object Cliente and bingo!

I basically added this validation to the value pass.

 cliente.Bairro = (txtBairro.EditValue == null ? null : Convert.ToString(txtBairro.EditValue));