When displaying large amounts of data it's often best to only display a portion of the data, allowing the user to step through the data ten or so records at a time. Additionally, the end user's experience can be enhanced if they are able to sort the data by one of the columns. Custom paging and sorting is the best solution for this case. You can see the gridview custom paging and sorting using T-SQL post here. In this article I will explain how to populate the ASP.Net GridView control from database using LINQ and how to sort GridView row using its SortExpression property. For this sample to work you will need to download the Microsoft Northwind database here.
First, open Visual Studio 2013 and create one project "WebAppBlog" and then add Class Library "Northwind" as follows:
Inside "Northwind" project, add ADO.NET Entity Data Model as below:
After adding "Northwind.edmx", build the project.
Secondly, create one more Class Library "WebAppBlog.Process".
Create "Customer.cs" and replace the following code with existing:
Finally, go to "WebAppBlog" project and add Reference "WebAddBlog.Procecss" and then create "Customer.aspx" page and add the Gridview control as follows:
 |
| GridView Custom Paging and Sorting |
First, open Visual Studio 2013 and create one project "WebAppBlog" and then add Class Library "Northwind" as follows:
Inside "Northwind" project, add ADO.NET Entity Data Model as below:
After adding "Northwind.edmx", build the project.
Secondly, create one more Class Library "WebAppBlog.Process".
Add Reference "EntityFramework" from "YourProjectsFolder\packages\EntityFramework.6.0.0\lib\net45\EntityFramework.dll"and "Northwind" from Northwind project as shown below:
using Northwind;
namespace WebAppBlog.Process
{
public class Customer
{
#region Pager
private int numberOfRecords;
public int NumberOfRecords
{
get { return numberOfRecords; }
internal set { numberOfRecords = value; IsDirty = true; }
}
private int pageSize;
public int PageSize
{
get { return pageSize; }
internal set { pageSize = value; IsDirty = true; }
}
private int currentPageIndex;
public int CurrentPageIndex
{
get { return currentPageIndex; }
internal set { currentPageIndex = value; IsDirty = true; }
}
public string SortExpression { get; private set; }
public bool IsSortDescending { get; private set; }
#endregion
public bool IsDirty { get; set; }
public List<CustomerDto> CustomerList;
public void Initialize()
{
CurrentPageIndex = 0;
NumberOfRecords = 0;
PageSize = 10;
SortExpression = "CustomerID";
IsSortDescending = true;
CustomerList = new List<CustomerDto>();
IsDirty = false;
}
public void Paginate(int pageIndex)
{
CurrentPageIndex = pageIndex;
GetCustomerList();
}
public void ResetPageSize(int pageSize)
{
PageSize = pageSize;
CurrentPageIndex = 0;
GetCustomerList();
}
public void Sort(string sortExp)
{
IsSortDescending = SortExpression == sortExp ? !IsSortDescending : false;
SortExpression = sortExp;
GetCustomerList();
}
public void GetCustomerList()
{
CustomerList.Clear();
using (NorthwindEntities context = new NorthwindEntities())
{
NumberOfRecords = context.Customers.Count();
IEnumerable<Northwind.Customer> allCustomerList = context.Customers.ToList();
IList<Northwind.Customer> customerList = new List<Northwind.Customer>();
if(NumberOfRecords > 0)
customerList = GetSortedandPaginatedResult(allCustomerList);
foreach(var c in customerList)
{
CustomerList.Add(new CustomerDto
{
CustomerId = c.CustomerID,
CompanyName = c.CompanyName,
ContactName = c.ContactName,
ContactTitle = c.ContactTitle,
City = c.City
});
}
}
}
private List<Northwind.Customer> GetSortedandPaginatedResult(IEnumerable<Northwind.Customer> allCustomer)
{
var result = new List<Northwind.Customer>();
if(SortExpression == "CustomerID")
{
result = IsSortDescending ? allCustomer.OrderByDescending(o => o.CustomerID).ToList() :
allCustomer.OrderBy(o => o.CustomerID).ToList();
}
else if(SortExpression == "ContactName")
{
result = IsSortDescending ? allCustomer.OrderByDescending(o => o.ContactName).ToList() :
allCustomer.OrderBy(o => o.ContactName).ToList();
}
result = result.Skip(CurrentPageIndex * PageSize).Take(PageSize).ToList();
return result;
}
public class CustomerDto
{
public string CustomerId { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string ContactTitle { get; set; }
public string City { get; set; }
}
}
}
After adding "Customer.cs", build the project. Finally, go to "WebAppBlog" project and add Reference "WebAddBlog.Procecss" and then create "Customer.aspx" page and add the Gridview control as follows:
<asp:GridView ID="gvCustomer" runat="server" AllowCustomPaging="True" AllowPaging="True" AutoGenerateColumns="False" OnPageIndexChanging="gvCustomer_PageIndexChanging" AllowSorting="True" OnSorting="gvCustomer_Sorting"> <Columns> <asp:BoundField DataField="CustomerID" HeaderText="ID" SortExpression="CustomerID" /> <asp:BoundField DataField="CompanyName" HeaderText="Company Name" /> <asp:BoundField DataField="ContactName" HeaderText="Contact Name" SortExpression="ContactName" /> <asp:BoundField DataField="ContactTitle" HeaderText="Contact Title" /> <asp:BoundField DataField="City" HeaderText="City" /> </Columns> <EmptyDataTemplate> <asp:Label ID="Label1" runat="server" Text="No data"></asp:Label> </EmptyDataTemplate> </asp:GridView>In code-behind, replace the following code with existing one:
public WebAppBlog.Process.Customer Model
{
get { return Session["ICustomer"] as WebAppBlog.Process.Customer; }
set { Session["ICustomer"] = value; }
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
InitModel();
}
private void InitModel()
{
Model = new WebAppBlog.Process.Customer();
Model.Initialize();
Model.GetCustomerList();
}
protected void Page_Prerender(object sender, EventArgs e)
{
BindModel();
}
private void BindModel()
{
if (Model.IsDirty)
{
gvCustomer.VirtualItemCount = Model.NumberOfRecords;
gvCustomer.PageIndex = Model.CurrentPageIndex;
gvCustomer.PageSize = Model.PageSize;
gvCustomer.DataSource = Model.CustomerList;
gvCustomer.DataBind();
Model.IsDirty = false;
}
}
protected void gvCustomer_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
Model.Paginate(e.NewPageIndex);
}
protected void gvCustomer_Sorting(object sender, GridViewSortEventArgs e)
{
Model.Sort(e.SortExpression);
}
That's it. Thank you! Happy Christmas!
