Dear Readers, I going to explain how to search data from the
database according to the input date and ending date in MVC application. I am
using SQL Server for database and Visual Studio 2015 IDE.
This blog is especially for those programmers who have not used Entity
Framework till now and are into the habit of using old ADO.NET framework for
database coding.
Note: Here I am
considering that you are aware of basics of MVC application creation such as
View, Model and Controller. If not, Please get some basics of MVC First Application.
So, let’s start with an example.
First of all, create table
(name it – OrderSampleData ) in your database according to the
following figure:
Put some data in the table: (You can design your separate
application for inserting data also)
Now create a model of name – SearchDataByDateModel . The code is:
public class SearchDataByDateModel
{
public int id { get; set; } // Property For order id
public string itemname {get; set; } // Property for item name
public DateTime orderdate { get; set; } // Property for orderdate
public DateTime datefrom { get; set; } // Property for starting date
public DateTime todate { get; set; } //Property for Ending Date
// List for storing data which is coming
from the DataBase
public List<SearchDataByDateModel> orderdata { get; set; }
}
Descriptions are given in the comments(green color).
Now create action in Controller. I am taking Home Controller
in this example.
[HttpGet]
public ActionResult SearchDataByDate()// ---- For get request
{
SearchDataByDateModel obj = new SearchDataByDateModel(); // ---- Point-1
obj.orderdata = null; // ---- Point-2
return View(obj); //
---- Point-3
}
[HttpPost]
public ActionResult SearchDataByDate(SearchDataByDateModel obj) // ---- For post request
{
DataTable dt = DAL.GetOrderData(obj.datefrom,
obj.todate);//
---- Point-4
List<SearchDataByDateModel> listorderdata = new List<SearchDataByDateModel>();// ---- Point-5
foreach (DataRow dr in dt.Rows) // ---- Point-6
{
listorderdata.Add(new SearchDataByDateModel() {
id = Convert.ToInt32(dr["id"]),
itemname = Convert.ToString(dr["itemname"]),
orderdate = Convert.ToDateTime(dr["orderdate"])
});
}
obj.orderdata = listorderdata; // ---- Point-7
return View(obj); //
---- Point-8
}
Descriptions:
Point-1: Creating
object of model, so that we can access its properties like ‘id’, ‘itemname’
etc.
Point-2: We have
a list type orderdata property in Model. We are setting it null for
first time otherwise it will throw a null exception.
Point-3: We are
passing object of Model with the view.
Point-4: We are
using a method of DAL class to get data from the database. The code is:
public static DataTable GetOrderData(DateTime dtfrom, DateTime dateTo)
{
SqlConnection con = new SqlConnection("Data
Source=servername;Initial Catalog=DBname;Integrated Security=True");
SqlDataAdapter da = new SqlDataAdapter("select * from
OrderSampleData where orderdate Between '" + dtfrom + "' AND '"+ dateTo +"'", con);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
I have used in-line query
here but I highly recommend to use store
procedure for retrieving data in place of in-line queries. To learn about
basics of Store procedures, see my videos here.
Point-5: Creating
an object of List type so that we can add data in it which is stored in the
DataTable object.
Point-6: We are using
foreach loop to fetch data from DataTable object and add data into the List
object.
Point-7: We are
storing the list object into the List property of Model Class. So that it can
be accessed in the view.
Point-8: Sending
Object of Model into the view.
Note: I recommend to define codes from Point-5 to Point-7 in the Separate
class which should contain Business Logics. Or in other words we can say that BAL
(Business Layer Class).
Now create view for this. Right click inside the action methods
and choose Add View option.
Now put the code in following manner in your View:
@model MVC18042016.Models.SearchDataByDateModel
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Search Data By Date</title>
</head>
<body>
@using (Html.BeginForm("SearchDataByDate", "Home", FormMethod.Post))
{
<div>
Please Select Dates :
Start Date @Html.TextBox("datefrom", Model.datefrom)
End Date @Html.TextBox("todate", Model.todate)
<input type="submit" name="submit" id="btnsubmit" value="Get Report" />
</div>
if(Model.orderdata != null)
{
<table border="1">
<tr>
<th>Order ID </th>
<th>Order Item</th>
<th>Order Date</th>
</tr>
@foreach (var item in Model.orderdata)
{
<tr>
<td>@Html.DisplayFor(modelItem
=>item.id)</td>
<td>@Html.DisplayFor(modelItem
=> item.itemname)</td>
<td>@Html.DisplayFor(modelItem
=> item.orderdate)</td>
</tr>
}
</table>
}
}
</body>
</html>
In the above code,
if(Model.orderdata
!= null) is used to show the data only when the list is
not null. If the list is null, the blocked code will not execute.
@foreach (var item in Model.orderdata) - loop is
used to iterate through the orderdata list.
@Html.DisplayFor
– It is a helper which is used to display data in non-editable format.
Build and Run the project.
I hope it will be helpful for you.
Happy Coding.
Find my youtube channel from HERE.
Rudra Pratap Singh
Software
Engineer
singhrudrapratap29[at]gmail[dot]com
No comments:
Post a Comment