c# - 如何使用 C# 检索给定日期(无论时间)的记录？

Question

我正在尝试像这样检索存储在 Access 表中的记录总和

string query = "SELECT SUM(TOTAL) AS Expr1 FROM tblTicket WHERE (DATE_SALE = @date)";

try
{
    conn.Open();
    OleDbCommand cmd = new OleDbCommand(query, conn);
    cmd.Parameters.AddWithValue("@date", dt.Date);
    maxid = cmd.ExecuteScalar().ToString();
    conn.Close();
}

它不显示任何数据，但记录具有选定的日期。数据存储在 SALE_DATE 列中，如下所示 8/27/2014 5:35:56 PM 并且 dt 对象从 DateTimePicker 获取值.

Answer 1

使用 ... WHERE DateDiff("d",[MyDateParameter],[DATE_SALE])=0 的问题是表达式不是 sargable即使 [DATE_SALE] 列上有索引，数据库引擎也将被迫进行表扫描。更好的(可搜索的)方法是这样的:

using (var conn = new OleDbConnection())
{
    conn.ConnectionString = 
            @"Provider=Microsoft.ACE.OLEDB.12.0;" +
            @"Data Source=C:\Users\Public\Database1.accdb;";
    conn.Open();
    using (var cmd = new OleDbCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = 
                "SELECT SUM(TOTAL) AS Expr1 FROM tblTicket " + 
                "WHERE DATE_SALE>=? AND DATE_SALE<?";
        DateTime dt = dateTimePicker1.Value.Date;  // date selected
        cmd.Parameters.AddWithValue("?", dt);
        cmd.Parameters.AddWithValue("?", dt.AddDays(1));
        Object returnedValue = cmd.ExecuteScalar();
        if (DBNull.Value.Equals(returnedValue)) returnedValue = 0;
        this.textBox1.Text = returnedValue.ToString();  // display result
    }
    conn.Close();
}