In this article I am going to give an example of webgrid control in asp.net mvc. What is the unique feature in this article is dynamic paging, sorting and search in webgrid using ajax.
Generally we are loading data in to grid with a single server side call. For eg:
Select * From Employee (in sql) or context.Employee.ToList() (in linq)
But this query will be fail where we have lot more data in our database tables. For eg. where we 20000 or 30000 records in a table. It can adversely affect our application and sometimes response time will be massive.
In this article we will figure out this problem and will make a generic solution for an asp.net mvc application.
Points to be covered:
1) Creating webgrid in asp.net mvc
2) Dynamic Paging using Ajax
3) Dynamic Sorting using Ajax
4) Dynamic Search using Ajax
5) Generic Sorting and Paging classes.
Prerequisites:
1) Microsoft Visual Studio
2) SQL Server
Packages:
1) EntityFramework (install-package entityframework)
2) jQuery (install-package jQuery)
3) Bootstrap (install-package Bootstrap)
4) LinqKit (install-package LinqKit)
Now follow the steps in order to achieve this task:
1) Open Microsoft Visual Studio
2) Create a new Asp.Net MVC application project
3) Install the packages given above
4) Create a Model class named Employee
public class Employee
{
public int EmployeeId { get; set; }
[Required]
[StringLength(20)]
public string FirstName { get; set; }
[Required]
[StringLength(20)]
public string LastName { get; set; }
public float Salary { get; set; }
[Required]
[StringLength(50)]
public string Country { get; set; }
[Required]
[StringLength(50)]
public string State { get; set; }
public DateTime DateofBirth { get; set; }
public bool IsActive { get; set; }
public DateTime CreatedDate { get; set; }
}
5) Create a db context class. For this tutorial I am using entity framework codefirst approach so it will create and add data in db automatically by using c# context classes.
public class EmployeeDbContext : DbContext
{
public EmployeeDbContext()
: base()
{
Database.SetInitializer<EmployeeDbContext>(new EmployeeDbContextInitializer());
}
public DbSet<Employee> Employees { get; set; }
}
public class EmployeeDbContextInitializer : DropCreateDatabaseIfModelChanges<EmployeeDbContext>
{
protected override void Seed(EmployeeDbContext context)
{
var list = new List<Employee>
{
// create object of Employees
};
list.ForEach(m =>
{
context.Employees.Add(m);
});
context.SaveChanges();
base.Seed(context);
}
}
6) Add connection string in web.config:
<connectionStrings>
<add name="EmployeeDbContext" connectionString="Data Source=SONY-VAIO\SQLEXPRESS;Initial Catalog=EmpDb;Integrated Security=true;" providerName="System.Data.SqlClient"/>
</connectionStrings>
7) Add the following utilities class for paging and sorting
a) SortOrderDTO
b) SortingPagingDTO
c) SortingPaging
8) Create a Data Access Layer class named EmployeeDAL and paste the following code:
public class EmployeeDAL
{
public List<Employee> GetEmployees(SortingPagingDTO sortingPaging, ref long totalCount, string strSerchStartWith)
{
using (EmployeeDbContext context = new EmployeeDbContext())
{
Expression<Func<Employee, bool>> pb = PredicateBuilder.True<Employee>();
if (!string.IsNullOrWhiteSpace(strSerchStartWith))
{
pb = pb.And(c => c.FirstName.ToLower().Contains(strSerchStartWith.ToLower()) || c.LastName.ToLower().Contains(strSerchStartWith.ToLower()));
}
var query = (from x in context.Employees.AsExpandable().Where(pb)
orderby x.CreatedDate
select x) as IOrderedQueryable<Employee>;
totalCount = query.Count();
return query.ApplySortingPagingToList(sortingPaging).ToList();
}
}
}
9) Create a Business Logic Layer class named EmployeeBLL
public class EmployeeBLL
{
public static List<Employee> GetEmployees(SortingPagingDTO sortingPaging, ref long totalCount, string strSerchStartWith)
{
EmployeeDAL obj = new EmployeeDAL();
{
return obj.GetEmployees(sortingPaging, ref totalCount, strSerchStartWith);
}
}
}
10) Create a Home controller and use the following code:
public class HomeController : Controller
{
public ActionResult Index(int? page, int? pagesize, string sort, string sortdir, string search)
{
SortingPagingDTO objSortPaging = null;
SetSortingPaging(page, pagesize, sort, sortdir, out objSortPaging);
long totalCount = 0;
var list = EmployeeBLL.GetEmployees(objSortPaging, ref totalCount, HttpUtility.HtmlEncode(search));
ViewBag.RecordCount = totalCount;
ViewBag.PageSize = pagesize ?? 10;
if (Request.IsAjaxRequest())
{
return PartialView("_Result", list);
}
return View(list);
}
private void SetSortingPaging(int? page, int? pagesize, string sort, string sortdir, out SortingPagingDTO objSortPaging)
{
objSortPaging = new SortingPagingDTO();
if (string.IsNullOrEmpty(sort))
objSortPaging.SortOrders.Add(new SortOrderDTO("FirstName", SortOrderDTO.SortOrder.Ascending));
else
{
if (sortdir == "ASC")
objSortPaging.SortOrders.Add(new SortOrderDTO(sort, SortOrderDTO.SortOrder.Descending));
else
objSortPaging.SortOrders.Add(new SortOrderDTO(sort, SortOrderDTO.SortOrder.Ascending));
}
if (page.HasValue)
objSortPaging.PageNumber = page.Value;
if (pagesize.HasValue)
objSortPaging.PageSize = pagesize.Value;
}
}
11) Add a Index view and insert the following code:
@model IEnumerable<MvcWebGrid.Models.Employee>
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<link href="~/Content/bootstrap.min.css" rel="stylesheet" />
<script src="~/Scripts/jquery-2.1.4.min.js"></script>
<script src="~/Scripts/jquery.unobtrusive-ajax.min.js"></script>
<script src="~/Scripts/app.js"></script>
</head>
<body>
<div class="container">
<br />
@Html.Partial("_Search")
<div id="employee-grid">
@Html.Partial("_Result", Model)
</div>
</div>
</body>
</html>
12) Create a partial view _Search.cshtml
@using (Ajax.BeginForm("Index", new { }, new AjaxOptions
{
HttpMethod = "GET",
UpdateTargetId = "employee-grid"
}, new { @id = "form-search" }))
{
<div class="row">
<div class="col-md-2">
<select class="form-control" name="pagesize">
<option value="10">10</option>
<option value="20">20</option>
</select>
</div>
<div class="col-md-10" style="float:right;">
<input type="text" name="search" id="search" class="form-control" placeholder="Search">
</div>
</div>
}
13) Create a partial view _Result.cshtml
@model IEnumerable<MvcWebGrid.Models.Employee>
<div class="row">
<div class="col-md-12" id="employee-grid-table">
@{
var grid = new WebGrid(canSort: true, canPage: true, rowsPerPage: (int)ViewBag.PageSize, ajaxUpdateContainerId: "employee-grid-table");
grid.Bind(rowCount: (int)ViewBag.RecordCount, source: Model, autoSortAndPage: false);
}
@grid.GetHtml(
fillEmptyRows: false,
tableStyle: "table table-striped table-hover",
mode: WebGridPagerModes.All,
columns: grid.Columns
(
grid.Column("FirstName", header: "First Name " + WebGridSortHelper.SortDirection(null, ref grid, "FirstName")),
grid.Column("LastName", header: "Last Name " + WebGridSortHelper.SortDirection(null, ref grid, "LastName")),
grid.Column("DateofBirth", header: "DOB " + WebGridSortHelper.SortDirection(null, ref grid, "DateofBirth"), format: (item => String.Format("{0:dd-MM-yyyy}", item.DateofBirth))),
grid.Column("Salary", header: "Salary " + WebGridSortHelper.SortDirection(null, ref grid, "Salary"), format: (item => String.Format("{0:F2}", item.Salary))),
grid.Column("State", header: "State " + WebGridSortHelper.SortDirection(null, ref grid, "State")),
grid.Column("Country", header: "Country " + WebGridSortHelper.SortDirection(null, ref grid, "Country"))
)
)
</div>
</div>
14) Create a app.js and paste the following code in order to submit ajax search form for filtering data and page size:
$(function () {
var thread = null;
$("#form-search input[name='search']").keyup(function (event) {
clearTimeout(thread);
var code = event.keyCode || event.which;
if ((code >= 65 && code <= 90) || (code >= 48 && code <= 57) || code == 8 || code == 32) {
thread = setTimeout(function () {
$("#form-search").submit();
}, 500);
}
});
$("#form-search select[name='pagesize']").change(function (event) {
$("#form-search").submit();
});
});
Now you can search First Name or Last Name by typing in search textbox. You can also change page size by selecting option from dropdown.
Thanks for reading this article. I think this will help a lot in terms of dynamic data loading.
If you want to apply for the source code of this article, you can communicate with me via contact form given in the right hand side. You can enter your full name, email and a short description only.
