Tags: Paging in MVC, How to apply paging without any third party tool,
Get required data from Database in the MVC application.
Description: we are going to apply paging in the data of web page in MVC without
using any third party tool.
Process: we will send the
page number of data to the database and get only required number of records
from the database. For example, we have 1000 records in our database, and
we want see 20 records per page, then we will send number of the page and only
20 records will come out according to the requirement. If page number is 2, 20
records from 21 to 40 will come from the database. The benefit of doing this is
to less the load of database to the server.
Note: Here I am considering that you are aware of basics of MVC
application creation such as View, Model and Controller. If not, please learn
some basics of MVC First Application.
Here I am showing an example to perform
this task. We are taking a database records of a Followup Enquiries.
Step
-1 Create table with name – followup and put
some data in it, for example as below –
Step-2 Create a MVC project in your Visual Studio of any name what you
want (for example - EnquiryManagement)
Step-3 Create a Model of Name followupModel inside Models Folder.
And put code in it as given below:
Code:
public class followupModel
{
public int followupid { get; set; }
public int followupFor { get; set; }
public string
followupBy { get; set; }
public string
visitorResponse { get; set; }
public string
comment { get; set; }
public DateTime
folloupdate { get; set; }
public DateTime
nextfollowupdate { get; set; }
public int totalRecordCount { get; set; } // For Total num of records
public int CurrentPageNumber { get; set; } // For current page number
public List<followupModel>
lstfollowup { get; set; } //To store data
coming from DB
}
Step-4 Create Action Method in your controller (say - Home) of name - ShowAllQueries.
The Code is given below:
Code:
[HttpGet]
public ActionResult
ShowAllQueries()
{
followupModel
objfollow = new followupModel();
objfollow.lstfollowup
= objEnqBAL.Getfolloup(objfollow, "ALL",1);
return
View(objfollow);
}
[HttpPost]
public ActionResult
ShowAllQueries(followupModel objfollow)
{
objfollow.lstfollowup
= objEnqBAL.Getfolloup(objfollow, "ALL", objfollow.CurrentPageNumber);
return View(objfollow);
}
Step-5 Create procedure to fetch data from Table
CREATE procedure spgetAllenquiries
(
@pageNo int -- Receiving Current Number of page
)
as
Begin
declare @start int=0;
declare @end int=0;
declare @number int=0;
declare @perPageRec int=2; -- number of Records per
page. You can set it by passing param
if(@pageNo=0)
begin
set @start=0;
set @pageNo=1; -- If page number is zero, automatically sets to 1
set @end=@pageNo*@perPageRec;
end
else
begin
set @end=@pageNo*@perPageRec; -- Last Record Number
set @start=@end-@perPageRec; -- First record Number
end
End
Begin
with TR as (
select followupid,followupFor,followupBy,visitorResponse,comment,folloupdate,nextfollowupdate,
(ROW_NUMBER() over (order by followupid desc)) as RowNum
from followup
)
Select *,(select count(*) from TR) as totalRecordCount from TR where RowNum>= @start+1 and RowNum <=@end
End
What
are we doing in the procedure?
We are passing current page number in the
procedure and according to this number, we are setting start page and End page number.
From the procedure, we are fetching Row number and according to the row number
the number of records will be fetched. We are also fetching total number of
records so that we can use it in the view.
Step-6 Create View of the Action method by right clicking on its name and
choosing option Add View….
The code for the View is –
@model EnquiryManagement.Models.followupModel
<script src="~/scripts/jquery-1.10.2.min.js"></script>
<script type="text/javascript">
// Function to set current page
number in model’s property
function fnGetPageNumber(pageno) {
$('#CurrentPageNumber').val(pageno); // Setting current page
number
$('#frmShowAllQueries').submit(); //Re submit the page
}
</script>
<style type="text/css">
/* This
is the style area for paging numbers */
.paging {
font-size : larger;
}
</style>
@using
(Html.BeginForm("ShowAllQueries", "Home",FormMethod.Post,new { id= "frmShowAllQueries" }))
{
<h3>FOLLOW-UP DATA</h3>
if (Model.lstfollowup != null &&
Model.lstfollowup.Count > 0)
{
@Html.HiddenFor(x => x.CurrentPageNumber)
<table class="table" style="border:1px solid brown; background-color:bisque;" border="1" cellspacing="0" cellpadding="0">
<tr>
<th>
@Html.DisplayNameFor(model
=> model.followupid)
</th>
<th>
@Html.DisplayNameFor(model
=> model.followupFor)
</th>
<th>
@Html.DisplayNameFor(model
=> model.followupBy)
</th>
<th>
@Html.DisplayNameFor(model
=> model.visitorResponse)
</th>
<th>
@Html.DisplayNameFor(model
=> model.comment)
</th>
<th>
@Html.DisplayNameFor(model
=> model.folloupdate)
</th>
<th>
@Html.DisplayNameFor(model
=> model.nextfollowupdate)
</th>
</tr>
@foreach (var item in Model.lstfollowup)
{
<tr>
<td>
@Html.DisplayFor(modelItem
=> item.followupid)
</td>
<td>
@Html.DisplayFor(modelItem
=> item.followupFor)
</td>
<td>
@Html.DisplayFor(modelItem => item.followupBy)
</td>
<td>
@Html.DisplayFor(modelItem
=> item.visitorResponse)
</td>
<td>
@Html.DisplayFor(modelItem
=> item.comment)
</td>
<td>
@Html.DisplayFor(modelItem
=> item.folloupdate)
</td>
<td>
@Html.DisplayFor(modelItem
=> item.nextfollowupdate)
</td>
</tr>
}
<tr> @*Row For paging*@
<td colspan="7" style="border:1px solid brown; background-color:gainsboro; text-align:center;">
@if (Model.lstfollowup != null && Model.lstfollowup.Count
> 0)
{
var totalrow =
Model.lstfollowup[0].totalRecordCount;
var Forpaging = Convert.ToInt32(totalrow)
/ 2; // 2 is number of rec per page
var getmode = totalrow %
2;
if (getmode > 0
&& getmode < 2)
{ Forpaging =
Forpaging + 1; }
if (Forpaging == 1)
{
<text> </text>
}
else
{
for (int j = 1; j <=
Forpaging; j++)
{
<label class="paging" onclick="fnGetPageNumber(@j)" onmouseover="" style="cursor: pointer;">@j</label>
}
}
}
else
{
<text><p style="color:white;">No
Record Found</p></text>
}
</td>
</tr>
</table>
}
else
{
<div>Data Not Found</div>
}
}
What
is happening in the View Code?
Data is coming in the list property of
model, named lstfollowup, from controller. And by using Loop we are showing it in the table.
An extra row is added at the last of the table. Please see the code of view
thoroughly to understand the view. Comments are given at the required places in
green color.
Step-7 Now we have to create the function through which data will come. For
this I have created two folder in the project of names – DAL (Data Access
Layer) & BAL (Business Access Layer). In DAL, we will put Data Accessing
code and in BAL, we will put business logic.
Create a class in DAL with name EnquiryDAL and
put the code given below:
Namespaces:
using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Collections.ObjectModel;
public class EnquiryDAL
{
// collection of Methods related to Database Access
in this class
// defining connection of SQL database. Define your Connection string in
web.config file of the project
SqlConnection
_Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["EMConStr"].ConnectionString);
private void OpenConnection()
{
if (this._Connection.State.Equals(ConnectionState.Closed))
this._Connection.Open();
}
private void CloseConnection()
{
if (this._Connection.State.Equals(ConnectionState.Open))
this._Connection.Close();
}
public int ExecuteProcedure(string
procedure, Collection<SqlParameter>
Parameters)
{
// This is the method to execute procedure with passing parameters
int Rows = 0;
using (SqlCommand Cmd = new SqlCommand(procedure,
this._Connection))
{
Cmd.CommandType = CommandType.StoredProcedure;
if (Parameters != null)
{
foreach (SqlParameter Param in Parameters)
{
if (Param != null)
{
if (Param.Value == null)
Param.Value = DBNull.Value;
Cmd.Parameters.Add(Param);
}
}
}
this.OpenConnection();
Rows =
Cmd.ExecuteNonQuery();
}
return Rows;
}
}
Step-8 Create a class in DAL with name EnquiryBAL and put the code
given below:
Namespaces:
using EnquiryManagement.Models;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Data;
using System;
using System.Collections.Generic;
using System.Globalization;
using EnquiryManagement.DAL;
Code is:
EnquiryDAL objDAL = new EnquiryDAL(); // Creating Object of EnquiryDAL class so that we can use methods of that
class
public List<followupModel> Getfolloup(followupModel objfollow, string optype, int pageNo)
{
List<followupModel>
lstfolloupDetails = new List<followupModel>();
if (optype == "ALL")
{
Collection<SqlParameter>
collection = new Collection<SqlParameter> {
new SqlParameter("@pageNo",pageNo)
};
DataSet ds =
objDAL.ExecProcedurewithdataset("spgetAllenquiries", collection);
DataTable dt =
ds.Tables[0];
// Saving Data from datatable to List
foreach (DataRow dr in dt.Rows)
{
lstfolloupDetails.Add(new followupModel()
{
followupid
= Convert.IsDBNull(dr["followupid"]) ? default(int) : Convert.ToInt32(dr["followupid"]),
followupFor
= Convert.IsDBNull(dr["followupFor"]) ? default(int) : Convert.ToInt32(dr["followupFor"]),
followupBy
= Convert.ToString(dr["followupBy"]),
visitorResponse = Convert.ToString(dr["visitorResponse"]),
comment = Convert.ToString(dr["comment"]),
folloupdate = Convert.ToDateTime(dr["folloupdate"]),
nextfollowupdate = Convert.ToDateTime(dr["nextfollowupdate"]),
totalRecordCount=
Convert.IsDBNull(dr["totalRecordCount"]) ? default(int) : Convert.ToInt32(dr["totalRecordCount"])
});
}
}
return lstfolloupDetails; // Returning List
}
Step-8 Now all code is done now. Save
all the opened windows, build the Project and Run.
The expected output is like this. We have
three records in the database and we are doing paging for two records per page,
so in the output we are getting two paging numbers.
I hope it will be helpful for you. Let me know if there is any confusion or
query.
Happy Coding.
Find my youtube channel from HERE.
Rudra Pratap Singh
Software Engineer
singhrudrapratap29[at]gmail[dot]com
