I can not insert into DateTime fields in sql database

0

I can not insert into the dataCriation and dataRefMove fields in the sql database

     private Contexto contexto;
    public void Inserir(RegCabecalho regCabecalho)
    {       
        var strQuery = "";
        strQuery += " INSERT INTO regCabecalho (tipoReg, dataCriacao,horaCriacao,dataRefMovimento,IDArq,codParceiro
      ,numeroSeq,versaoLayout) ";
      strQuery += string.Format(" VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}') " ,
      regCabecalho.tipoReg, regCabecalho.dataCriacao,
      regCabecalho.horaCriacao, regCabecalho.dataRefMovimento, regCabecalho.IDArq,
      regCabecalho.codParceiro,
      regCabecalho.numeroSeq, regCabecalho.versaoLayout
            );
        using (contexto = new Contexto())
        {
            contexto.ExecutaComando(strQuery);
        }
    }
    
asked by anonymous 24.03.2016 / 23:08

1 answer

1

1. Use some data access library

Entity Framework , Dapper , something , but do not write SQL in hand . Getting concatenated data in the hand in the middle of SQL leaves you open SQL security issues and makes your application difficult to maintain later.

2. Dates do not have universal representation in databases

You have to deliver dates in the pattern that the database expects, usually a formatted string in a specific way. You are concatenating the data in hand, which generates the date in a string format, but that may not be the format the database is waiting for.

Your computer is probably formatting dates in the day / month / year schema, but the database is waiting in the American (month / day / year) format or in ISO 8601 format.

Create the class below somewhere ...

public static class DateExtensions
{
    public static string ToIso8601Date( this DateTime date )
    {
        return date.ToString( "yyyy-MM-dd" );
    }
}

... and add ToIso8601Date() to the end of regCabecalho.dataCriacao and regCabecalho.dataRefMovimento . See if it works and then comment on it.

    
25.03.2016 / 00:20