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,
- [Name] [varchar](50) NULL,
- [City] [varchar](50) 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 as 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 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="Seach" 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>
- @*<th></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 stored procedure, Entity Framework in ASP.NET MVC.