Filter records based on Date Or DateTime value using Linq in ASP.Net MVC

zeeshanpas
 
on Jul 05, 2022 07:09 AM
Sample_637588.zip
1035 Views

This is my code where i am trying to fetch the data from DataTable.

Here i want to filter based on only date without time or date with time.

It depends on suppose if want data to that particular date means all records of that date else with the time.

Scenario

a) 04/19/2022 12:00:00 pm--->with timings format(mm/dd/yyyy hh:mi:ss am)--

b) 04/19/2022 -->without time only date

In the above code which i posted SCHEDULE_START_TIME is the column which i want to compare to date and DateTime

var listImport = (from DataRow _ctx in dt.Rows
                  select new
                  {
                      SESSION_ID = _ctx["session_id"] == DBNull.Value ? "" : _ctx["session_id"].ToString(),
                      STUDENT_ID = _ctx["student_id"] == DBNull.Value ? "" : _ctx["student_id"].ToString(),
                      STUDENT_NAME = _ctx["student_name"] == DBNull.Value ? "" : _ctx["student_name"].ToString(),
                      Assigned_TUTOR_ID = _ctx["preferred_tutor_id"] == DBNull.Value ? "" : _ctx["preferred_tutor_id"].ToString(),
                      Assigned_TUTOR_NAME = _ctx["Assigned_TUTOR_NAME"] == DBNull.Value ? "" : _ctx["Assigned_TUTOR_NAME"].ToString(),
                      Assigned_TUTOR_USERNAME = _ctx["user_name"] == DBNull.Value ? "" : _ctx["user_name"].ToString(),
                      GRADE = _ctx["grade_name"] == DBNull.Value ? "" : _ctx["grade_name"].ToString(),
                      SUBJECT = _ctx["subject_name"] == DBNull.Value ? "" : _ctx["subject_name"].ToString(),
                      School = _ctx["school_name"] == DBNull.Value ? "" : _ctx["school_name"].ToString(),
                      District = _ctx["district_name"] == DBNull.Value ? "" : _ctx["district_name"].ToString(),
                      COURSE = _ctx["course_name"] == DBNull.Value ? "" : _ctx["course_name"].ToString(),
                      SCHOOL_LEVEL = _ctx["School_level"] == DBNull.Value ? "" : _ctx["School_level"].ToString(),
                      BUCKET_ID = _ctx["bucket_id"] == DBNull.Value ? "" : _ctx["bucket_id"].ToString(),
                      TITLE = _ctx["title"] == DBNull.Value ? "" : _ctx["title"].ToString(),
                      SCHEDULE_START_TIME = _ctx["scheduled_starttime"].ToString() == "" ? (Nullable<DateTime>)null : Convert.ToDateTime(_ctx["scheduled_starttime"].ToString()),
                  }).Distinct().ToList();

could you please help me in this as soon as possible?

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 06, 2022 04:47 AM
on Jul 16, 2022 08:31 AM

Hi zeeshanpas,

Generate the DateTime from the controls and pass the value for filtering the record from DataTable.

Check the below example.

Namespaces

using System.Collections.Generic;
using System.Data;
using System.Linq;

Controller

public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        return View(GetData());
    }

    private static DataTable GetData()
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[2] {
            new DataColumn("Name"),
            new DataColumn("ScheduleTime",typeof(DateTime))
        });
        dt.Rows.Add("Ram", "04/19/2022 12:00:00 AM");
        dt.Rows.Add("Rahim", "04/19/2022 08:00:00 PM");
        dt.Rows.Add("John", "04/19/2022 09:00:00 PM");

        return dt;
    }

    [HttpPost]
    public ActionResult Index(string date, string time, string meridian)
    {
        DataTable dt = GetData();
        List<DataRow> dataRows = new List<DataRow>();
        if (!string.IsNullOrEmpty(time) && !string.IsNullOrEmpty(meridian))
        {
            dataRows = (from DataRow dr in dt.Rows
                        where Convert.ToDateTime(dr["ScheduleTime"]) == Convert.ToDateTime(string.Format("{0} {1} {2}", date.Trim(), time.Trim(), meridian.Trim()))
                        select dr).ToList();
        }
        else
        {
            if (!string.IsNullOrEmpty(date))
            {
                dataRows = (from DataRow dr in dt.Rows
                            where Convert.ToDateTime(dr["ScheduleTime"]).ToString("dd/MM/yyyy") == Convert.ToDateTime(date).ToString("dd/MM/yyyy")
                            select dr).ToList();
            }
        }

        if (dataRows.Count > 0)
        {
            dt = dataRows.CopyToDataTable();
        } 
        else
        {
            dt.Clear();
        }

        return View(dt);
    }
}

View

@model DataTable
@using System.Data
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    @using (Html.BeginForm("Index", "Home", FormMethod.Post))
    {
        <table>
            <tr>
                <td>Date:</td>
                <td><input type="text" name="date" style="width:145px" /></td>
            </tr>
            <tr>
                <td>Hours:</td>
                <td>
                    <select name="time" style="width:150px">
                        <option value=""></option>
                        <option value="12:00">12:00</option>
                        <option value="08:00">08:00</option>
                    </select>
                </td>
            </tr>
            <tr>
                <td>AM/PM:</td>
                <td>
                    <select name="meridian" style="width:150px">
                        <option value=""></option>
                        <option value="AM">AM</option>
                        <option value="PM">PM</option>
                    </select>
                </td>
            </tr>
            <tr>
                <td colspan="2"><input type="submit" value="Search" /></td>
            </tr>
        </table>
    }
    <hr />
    <table>
        @foreach (DataRow dr in Model.Rows)
        {
            <tr>
                <td>@dr["Name"]</td>
                <td>@Convert.ToDateTime(dr["ScheduleTime"]).ToString("MM/dd/yyyy hh:mm:ss tt")</td>
            </tr>
        }
    </table>
</body>
</html>

Screenshot