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

In this article, we will learn how to search records between two dates using stored procedure with 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'.
  1. CREATE TABLE [dbo].[Employee](  
  2.     [Id] [intNOT NULL,  
  3.     [Name] [varchar](50) NULL,  
  4.     [City] [varchar](50) NULL,  
  5.     [JoiningDate] [dateNULL,  
  6. PRIMARY KEY CLUSTERED   
  7. (  
  8.     [Id] ASC  
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  10. ON [PRIMARY]  
  11.   
  12. GO  
  13.   
  14. SET ANSI_PADDING OFF  
  15. GO  

Step 2

Create a stored procedure to find the data between two dates.
  1. CREATE PROC [dbo].[Usp_Empsearch]  
  2. @Fromdate DATETIME,@Todate DATETIME  
  3. AS  
  4. BEGIN  
  5. SELECT * FROM Employee WHERE JoiningDate BETWEEN @Fromdate AND @Todate  
  6. END  

Step 3

Open Visual Studio and create a new project.
 
Texthow To Search Records Between Two Dates Using Stored Procedure, Entity Framework In ASP.NET MVC
 
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.
 
Texthow To Search Records Between Two Dates Using Stored Procedure, Entity Framework In ASP.NET MVC
 
Select EF designer from the database and click the "Next" button.
 
Texthow To Search Records Between Two Dates Using Stored Procedure, Entity Framework In ASP.NET MVC
 
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.
 
Texthow To Search Records Between Two Dates Using Stored Procedure, Entity Framework In ASP.NET MVC
 
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.
 
Texthow To Search Records Between Two Dates Using Stored Procedure, Entity Framework In ASP.NET MVC
 
Step 6
 
Now, right click on the Controllers folder and add a new MVC controller named 'SearchController'.
 
Add the following code to that.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6.   
  7. namespace SearchRecord.Controllers  
  8. {  
  9.     public class SearchController : Controller  
  10.     {  
  11.         // GET: Search  
  12.         public ActionResult Index()  
  13.         {  
  14.             Test1Entities1 DB = new Test1Entities1();  
  15.             return View(DB.Employees.ToList());  
  16.         }  
  17.         [HttpPost]  
  18.         public ActionResult Index(DateTime From,DateTime To)  
  19.         {  
  20.             Test1Entities1 DB = new Test1Entities1();  
  21.             return View(DB.SearchData(From,To));  
  22.         }  
  23.   
  24.     }  
  25. }  
Step 7
 
Right-click on the Index method and add a View.
 
Texthow To Search Records Between Two Dates Using Stored Procedure, Entity Framework In ASP.NET MVC
 
Step 8
 
Now, add a form in the View and two date pickers. Add the following code in this View.
  1. @model IEnumerable<SearchRecord.Employee>  
  2.   
  3. @{  
  4.     ViewBag.Title = "Index";  
  5. }  
  6. @section scripts {  
  7.     <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>  
  8.     <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>  
  9.     <script src="~/Scripts/jquery.validate.min.js"></script>  
  10.     <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>  
  11.     <script src="~/Scripts/bootstrap-datepicker.js"></script>  
  12.     <script src="~/Scripts/bootstrap-datepicker.min.js"></script>  
  13.     <link href="~/Content/bootstrap-datepicker.css" rel="stylesheet" />  
  14.     <link href="~/Content/bootstrap-datepicker.min.css" rel="stylesheet" />  
  15.     <script type="text/javascript">  
  16.         var $j = $.noConflict(true);  
  17.     </script>  
  18.     <script type="text/javascript">  
  19.         $j("#datepicker").datepicker({  
  20.             autoclose: true,  
  21.             todayHighlight: true,  
  22.             format: "dd/ mm/ yyyy"  
  23.         });  
  24.     </script>  
  25.     <script type="text/javascript">  
  26.         $j("#datepicker1").datepicker({  
  27.             autoclose: true,  
  28.             todayHighlight: true,  
  29.             format: "dd/ mm/ yyyy"  
  30.         });  
  31.     </script>  
  32. }  
  33. <div>  
  34.     <div class="row">  
  35.         <div class="col-sm-12 btn btn-success">  
  36.             How to search records between two dates using stored procedure ,Entity framework in Asp.net Mvc  
  37.         </div>  
  38.     </div>  
  39. </div>  
  40. <br /><br />  
  41. <div class="container">  
  42.     @using (Html.BeginForm("Index", "Search", FormMethod.Post))  
  43.     {  
  44.         <div class="row">  
  45.             <div class="col-sm-2 text-lg-left"><label>From Date</label></div>  
  46.             <div class="col-sm-3">  
  47.   
  48.                 <input type="text" id="datepicker" name="From" class="form-control" placeholder="Select Date" />  
  49.             </div>  
  50.             <div class="col-sm-2"><label>To Date</label></div>  
  51.             <div class="col-sm-3">  
  52.   
  53.                 <input type="text" id="datepicker1" name="To" class="form-control" placeholder="Select Date" />  
  54.             </div>  
  55.             <div class="col-sm-2">  
  56.                 <input type="submit" value="Seach" class="btn btn-primary form-control" />  
  57.             </div>  
  58.             <br /><br />  
  59.         </div>  
  60.         <div class="row">  
  61.             <table class="table table-bordered">  
  62.                 <tr>  
  63.                     <th style="width:33%">  
  64.                         @Html.DisplayNameFor(model => model.Name)  
  65.                     </th>  
  66.                     <th style="width:33%">  
  67.                         @Html.DisplayNameFor(model => model.City)  
  68.                     </th>  
  69.                     <th style="width:33%">  
  70.                         @Html.DisplayNameFor(model => model.JoiningDate)  
  71.                     </th>  
  72.                     @*<th></th>*@  
  73.                 </tr>  
  74.   
  75.                 @foreach (var item in Model)  
  76.                 {  
  77.                     <tr>  
  78.                         <td>  
  79.                             @Html.DisplayFor(modelItem => item.Name)  
  80.                         </td>  
  81.                         <td>  
  82.                             @Html.DisplayFor(modelItem => item.City)  
  83.                         </td>  
  84.                         <td>  
  85.                             @Html.DisplayFor(modelItem => item.JoiningDate)  
  86.                         </td>  
  87.                     </tr>  
  88.                 }  
  89.   
  90.             </table>  
  91.         </div>  
  92.     }  
  93. </div>  
Step 9
 
Run the project and check the result.
 
Texthow To Search Records Between Two Dates Using Stored Procedure, Entity Framework In ASP.NET MVC 
 
Summary

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