How to select a date in c#?

Question

I intend to select by the current date, like: list all the data that was registered today in my table, I did this way and does not result in anything, does not generate errors or list any of the registered information.

//Método Listar registos cadastrado Actualmente
public List<View_Caixa> Listar_Data()
{
    List<View_Caixa> lis = new List<View_Caixa>();
    SqlConnection conexao = new SqlConnection(caminho);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conexao;
    cmd.CommandType = CommandType.Text;

    string li = "select *from view_caixa where data_registo=@data_registo"; //É aqui onde eu faço o meu select
    cmd.CommandText = li;
    cmd.Parameters.AddWithValue("data_registo", DateTime.Now);
    conexao.Close();
    SqlDataReader dr;
    conexao.Open();
    dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        View_Caixa obj = new View_Caixa();
        obj.id_caixa = Convert.ToInt32(dr["id_caixa"].ToString());
        obj.funcionario = dr["funcionario"].ToString();
        obj.caixa_inicial = Convert.ToDecimal(dr["caixa_inicial"].ToString());
        obj.total_veda_caixa = Convert.ToDecimal(dr["total_veda_caixa"].ToString());
        obj.total_venda_multicaixa = Convert.ToDecimal(dr["total_venda_multicaixa"].ToString());
        obj.total_multipagamento = Convert.ToDecimal(dr["total_multipagamento"].ToString());
        obj.caixa_final = Convert.ToDecimal(dr["caixa_final"].ToString());
        obj.conciliacao = Convert.ToDecimal(dr["conciliacao"].ToString());
        obj.data_registo = Convert.ToDateTime(dr["data_registo"].ToString());
        lis.Add(obj);
    }
    return lis;
}

Answer 1

The DateTime.Now returns to date and time and in your select you are asking to list only where the date is exactly the same as the one you returned on DateTime.Now.

Change your select to (Using SQL-Server):

select * from view_caixa where data_registo between @data_registo and @data_registro2

In his DateTime.Now do this:

cmd.Parameters.AddWithValue("data_registo", DateTime.Now.ToString("dd/MM/yyyy"+" 00:00:00"));
cmd.Parameters.AddWithValue("data_registo", DateTime.Now.ToString("dd/MM/yyyy"+" 23:59:59"));

This way he will get all records from the bank within that specified period. Even if your bank is recording without the time, it will work, but note that depending on the BD, you need to pass the parameter to date. If it is oracle, for example, it would be select:

select * from view_caixa where data_registo between 
   TO_DATE(@data_registo, 'DD/MM/YYYY HH24:mi:ss') and 
   TO_DATE(@data_registro2, 'DD/MM/YYYY HH24:mi:ss')

Check how your date format is in the BD and how the query is being mounted.

Answer 2

In the call of the method AddWithValue, the parameter name must contain the arroba.

Substitute:

cmd.Parameters.AddWithValue("data_registo", DateTime.Now);

For:

cmd.Parameters.AddWithValue("@data_registo", DateTime.Now);

Edit and P.S.: George Wurthmann noticed the biggest problem in the code. His answer is the right one. The date/time comparison with Datetime.Now does not work because it is accurate up to the millisecond. You should only compare the date.