Search Records Between Two Dates Using Stored Procedure And Entity Framework In ASP.NET MVC

Searching records between two dates is very simple. In here, we will see how we can perform this using a stored procedure with Entity Framework in ASP.NET MVC.

Step 1. Open SQL Server Management Studio and create a table named 'Employee'.

CREATE TABLE [dbo].[Employee] (
    [Id]          [int]          NOT NULL,
       NULL,
       NULL,
    [JoiningDate] [date]         NULL,
    PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    ) WITH (
        PAD_INDEX              = OFF, 
        STATISTICS_NORECOMPUTE  = OFF, 
        IGNORE_DUP_KEY          = OFF, 
        ALLOW_ROW_LOCKS         = ON, 
        ALLOW_PAGE_LOCKS        = ON
    ) ON [PRIMARY]
) ON [PRIMARY];

GO

SET ANSI_PADDING OFF;
GO

Step 2. Create a stored procedure to find the data between two dates.

CREATE PROC [dbo].[Usp_Empsearch]
    @Fromdate DATETIME,
    @Todate   DATETIME
AS
BEGIN
    SELECT * 
    FROM Employee 
    WHERE JoiningDate BETWEEN @Fromdate AND @Todate;
END

Step 3. Open Visual Studio and create a new project.

Change the name to SearchRecord and select MVC as its template.

Step 4. Right-click the Models folder from Solution Explorer and go to Add >> New Item >> data.

Select EF designer from the database and click the "Next" button.

Add the connection properties and select the database name on the next page followed by a click on OK.

Check the table and stored procedure checkboxes. The internal options will be selected by default. Now, click the "Finish" button.

Our data model is created now.

Step 5. Right-click on Edmx, add New >Function Import >enter the name and import function and table. Now, click on the "Get column information" option.

Step 6. Now, right-click on the Controllers folder and add a new MVC controller named 'SearchController'.

Add the following code to that.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace SearchRecord.Controllers
{
    public class SearchController : Controller
    {
        // GET: Search
        public ActionResult Index()
        {
            Test1Entities1 DB = new Test1Entities1();
            return View(DB.Employees.ToList());
        }

        [HttpPost]
        public ActionResult Index(DateTime From, DateTime To)
        {
            Test1Entities1 DB = new Test1Entities1();
            return View(DB.SearchData(From, To));
        }
    }
}

Step 7. Right-click on the Index method and add a View.

Step 8. Now, add a form in the View and two date pickers. Add the following code in this View.

@model IEnumerable<SearchRecord.Employee>

@{
    ViewBag.Title = "Index";
}

@section scripts {
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
    <script src="~/Scripts/jquery.validate.min.js"></script>
    <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
    <script src="~/Scripts/bootstrap-datepicker.js"></script>
    <script src="~/Scripts/bootstrap-datepicker.min.js"></script>
    <link href="~/Content/bootstrap-datepicker.css" rel="stylesheet" />
    <link href="~/Content/bootstrap-datepicker.min.css" rel="stylesheet" />
    <script type="text/javascript">
        var $j = $.noConflict(true);
    </script>
    <script type="text/javascript">
        $j("#datepicker").datepicker({
            autoclose: true,
            todayHighlight: true,
            format: "dd/mm/yyyy"
        });
    </script>
    <script type="text/javascript">
        $j("#datepicker1").datepicker({
            autoclose: true,
            todayHighlight: true,
            format: "dd/mm/yyyy"
        });
    </script>
}

<div>
    <div class="row">
        <div class="col-sm-12 btn btn-success">
            How to search records between two dates using stored procedure, Entity Framework in ASP.NET MVC
        </div>
    </div>
</div>

<br /><br />

<div class="container">
    @using (Html.BeginForm("Index", "Search", FormMethod.Post))
    {
        <div class="row">
            <div class="col-sm-2 text-lg-left">
                <label>From Date</label>
            </div>
            <div class="col-sm-3">
                <input type="text" id="datepicker" name="From" class="form-control" placeholder="Select Date" />
            </div>
            <div class="col-sm-2">
                <label>To Date</label>
            </div>
            <div class="col-sm-3">
                <input type="text" id="datepicker1" name="To" class="form-control" placeholder="Select Date" />
            </div>
            <div class="col-sm-2">
                <input type="submit" value="Search" class="btn btn-primary form-control" />
            </div>
            <br /><br />
        </div>

        <div class="row">
            <table class="table table-bordered">
                <tr>
                    <th style="width:33%">
                        @Html.DisplayNameFor(model => model.Name)
                    </th>
                    <th style="width:33%">
                        @Html.DisplayNameFor(model => model.City)
                    </th>
                    <th style="width:33%">
                        @Html.DisplayNameFor(model => model.JoiningDate)
                    </th>
                </tr>

                @foreach (var item in Model)
                {
                    <tr>
                        <td>
                            @Html.DisplayFor(modelItem => item.Name)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.City)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.JoiningDate)
                        </td>
                    </tr>
                }
            </table>
        </div>
    }
</div>

Step 9. Run the project and check the result.

Summary

In this article, we learned how to search records between two dates using the stored procedure, Entity Framework in ASP.NET MVC.