时间戳

首页 > 解决方案 > 查询按日期范围过滤数据表

c# - 查询按日期范围过滤数据表

问题描述

我有一个包含这些列的 DataTable：

| 身份证 | 客户 ID | 付款金额 | 下一个时间表日期 |

所有列都包含字符串值（例如，NextScheduleDate 的第 0 行单元格可能是字符串“11/1/2020”）。我想过滤它并创建一个新的 DataTable，它仅由NextScheduleDate值落在日期范围内的行组成。所以用户选择范围，方法应该相应地过滤表格。某些NextScheduleDate单元格值可能为空。我对 Linq 很陌生，所以非常感谢任何和所有的帮助。到目前为止，这是我的方法，它根本不起作用。

protected void LoadPayments()
{
    // User provides textbox values
    string startDate = txtStartDateRange.Text;
    string endDate = txtEndDateRange.Text;

    // This methods gets ALL payments, some of which have already happened
    // and some of which are scheduled as future payments
    DataTable tblPayments = Payments.GetAllRecurringPayments();

    // We only want payments where the NextScheduleDate column value is the range selected by the user

    // How to filter so that NextScheduleDate is in between startDate and endDate?
    // (Needs to return values where startDate > NextScheduleDate > endDate

    DataTable futurePayments = tblPayments.AsEnumerable()
    .Where(r => r.Field<string>("NextScheduleDate") == ?????)
    .CopyToDataTable();

    // Bind
    grdPayments.DataSource = futurePayments;
    grdPayments.DataBind();
}

标签： c#asp.netlinqdatatable

解决方案

我按原样显示以下代码，我没有运行它。我指出了代码可能在哪里中断以及在什么情况下。

为清楚起见，我假设您提供的日期可以使用 Parse 方法转换为 DateTime。如果他们不能，推理仍然有效，只有您将使用 TryParse 并为日期无效的情况提供逻辑。

另外，我假设您的数据库在日期字段的内容为空时返回 DBNull.Value。

protected void LoadPayments()
{
    // User provides textbox values
    string startDate = txtStartDateRange.Text;
    string endDate = txtEndDateRange.Text;
    // Converts dates to DateTime (breaks if Dates are not valid)
    DateTime s = DateTime.Parse(startDate);
    DateTime e = DateTime.Parse(endDate);

    // This methods gets ALL payments, some of which have already happened
    // and some of which are scheduled as future payments
    DataTable tblPayments = Payments.GetAllRecurringPayments();

    // We only want payments where the NextScheduleDate column value is the range selected by the user

    // How to filter so that NextScheduleDate is in between startDate and endDate?
    // (Needs to return values where startDate > NextScheduleDate > endDate

    DataTable futurePayments = tblPayments.AsEnumerable()
    .Where(r =>
    {
        // Excludes if there is no date.
        // Assumes the DB returns DBNull.Value if the Field is null
        if (r.Field("NextScheduleDate") == DBNull.Value) { return false; }
        // Breaks if Date is not valid
        DateTime d = DateTime.Parse(r.Field<string>("NextScheduleDate"));
        if (d < s) { return false; }
        if (d > e) { return false; }
        return true;
    });

    .CopyToDataTable();

    // Bind
    grdPayments.DataSource = futurePayments;
    grdPayments.DataBind();
}

推荐阅读