Search (Filter) records between tow Dates in ASP.Net Core MVC

Last Reply 4 days ago By dharmendr

Posted 5 days ago

How to filter record between start and end date in asp.net core mvc 

Posted 4 days ago

Hi rani,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Namespaces

using System.Data;
using System.Data.SqlClient;
using System.Globalization;

Controller

public class HomeController : Controller
{
    public IActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public IActionResult Index(string startDate, string endDate)
    {
        string constr = @"Server=.\SQL2014;DataBase=Northwind;UID=sa;PWD=pass@123";
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT OrderID, OrderDate, ShipName, ShipCity FROM Orders WHERE OrderDate BETWEEN @From AND @To", con))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    cmd.Parameters.AddWithValue("@From", Convert.ToDateTime(startDate, new CultureInfo("en-GB")));
                    cmd.Parameters.AddWithValue("@To", Convert.ToDateTime(endDate, new CultureInfo("en-GB")));
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    ViewBag.Data = dt;
                }
            }
        }

        return View();
    }
}

View

@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <script type="text/javascript" src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.3.min.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.0.3/js/bootstrap.min.js"></script>
    <link rel="stylesheet" media="screen" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.0.3/css/bootstrap.min.css" />
    <link type="text/css" rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/css/bootstrap-datepicker.css" />
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/js/bootstrap-datepicker.js"></script>
</head>
<body class="container">
    <form asp-controller="Home" asp-action="Index" method="post">
        Start: <input type="text" id="txtStartDate" name="startDate" class="startDate form-control" />
        End: <input type="text" id="txtEndDate" name="endDate" class="endDate form-control" />
        <input type="submit" value="Submit" class="btn btn-primary" />
        <hr />
        @if (ViewBag.Data != null)
        {
            <table class="table">
                <tr>
                    <th>Order ID</th>
                    <th>Order Date</th>
                    <th>Ship Name</th>
                    <th>Ship City</th>
                </tr>
                @foreach (System.Data.DataRow dr in ViewBag.Data.Rows)
                {
                    <tr>
                        <td>@dr["OrderID"]</td>
                        <td>@dr["OrderDate"]</td>
                        <td>@dr["ShipName"]</td>
                        <td>@dr["ShipCity"]</td>
                    </tr>
                }
            </table>
        }
        <script type="text/javascript">
            $(function () {
                $(".startDate").datepicker({
                    autoclose: true,
                    format: 'dd/mm/yyyy'
                }).on('changeDate', function (selected) {
                    var minDate = new Date(selected.date);
                    minDate.setDate(minDate.getDate() + 1);
                    $('.endDate').datepicker('setStartDate', minDate);
                });

                $(".endDate").datepicker({
                    autoclose: true,
                    format: 'dd/mm/yyyy'
                }).on('changeDate', function (selected) {
                    var minDate = new Date(selected.date);
                    minDate.setDate(minDate.getDate() - 1);
                    $('.startDate').datepicker('setEndDate', minDate);
                });
            });
        </script>
    </form>
</body>
</html>

Screenshot