c# – Inserting single quotes in null fields

Question:

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

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

I use a POCO class of entity mapped like this:

[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 is the method that inserts the data of the customer 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 triggered by the 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:

The problem is that the data in the application is as an empty string and not as null, so it writes an empty string . If you want to write 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 don't see why doing a conversion to string of something that is already string . Converting a null to string gives a void and not a null-valued object, as the documentation demonstrates. The error is there, that's why 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 it on GitHub for future reference .

Also, you should never catch an exception just to rethrow it. This only causes problems. If you have nothing useful to do when catching an exception, don't catch it.

Catching Exception is also not usually suitable in most situations. As well as trying to close some things without a design pattern that guarantees it will close. Current code can leak open resources easily.

Post Views: 31
Scroll to Top