Prerequisites:
Getting started:Step 1: Setup your working environment by creating new asp.net application with Ado.net entity data frame work as data model.
- In order to do so, select Add New Item> Data>ADO.net Entity Data Model.
2. Select database connection string using following wizard
3. Choose Database object that you would like to include in the data model.
Step2: Create UI and data access layer
1. Create a simple UI using asp.net as shown below
2. In order to incorporate custom sorting and pagination capability to gridview, we would need to define following properties in the code behind file
int PageSize = 5; //we want to display only five elements in the page
int RecordCount = 0;
int PageIndex //we maintain the page index in the view state,so that the value can persist across postback
{
get
{
if (ViewState["PageIndex"] != null)
return Convert.ToInt32(ViewState["PageIndex"]);
else
return 0;
}
set { ViewState["PageIndex"] = value; }
}
SortDirection CurrentSortDirection //Stores direction of sorting.i.e. Ascending or descending
{
get
{ if (ViewState["SortDirection"] != null)
return (SortDirection)Enum.Parse(typeof(SortDirection), ViewState["SortDirection"].ToString());
else
return 0;
}
set { ViewState["SortDirection"] = value; }
}
public string CurrentSortExpression //Sort expression is used to filter out the data from result set.
{
get
{
if (ViewState["SortExpression"] != null)
return Convert.ToString(ViewState["SortExpression"]);
else
return string.Empty;
}
set { ViewState["SortExpression"] = value; }
}
3. Fetch the records from database via entity framework using LINQ queries
//Create instance of entity model to query northwind database
NORTHWNDEntities1 entity = new NORTHWNDEntities1();
//Create a new data binding method,which is invoked during the page load event and subsequently when next and previous link is clicked.public void BindProducts()
{
var res = (from p in entity.Products
where p.Discontinued == false &&
p.Category.CategoryName == ddlCategory.SelectedItem.Text
orderby p.ProductName
select new { p.ProductName,
p.ProductID,
p.Category.CategoryName,
p.Supplier.CompanyName,
p.UnitPrice,
p.UnitsInStock
} );
RecordCount = res.Count();
gvProducts.DataSource = res.OrderBy(CurrentSortExpression).Skip(PageIndex * PageSize).Take(PageSize);
gvProducts.DataBind();
}
//Bind drop down list with available category
public void BindCategory()
{
ddlCategory.DataSource = from category in entity.Categories
where category.Products.Count > 0
select new
{
category.CategoryID,
category.CategoryName
};
ddlCategory.DataTextField = "CategoryName";
ddlCategory.DataValueField = "CategoryID";
ddlCategory.DataBind();
}
private void BindPager()
{
if (PageIndex == (RecordCount / PageSize))
{
btnNext.Enabled = false;
}
else
{
btnNext.Enabled = true;
}
if (PageIndex == 0)
{
btnPrev.Enabled = false;
}
else
{
btnPrev.Enabled = true;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindCategory();
BindProducts();
BindPager();
}
}
Handling custom pagination:
In the code, we have used extension methods "Skip" and "Take" for restricting number of records retrieved from the database to front end.
The "Skip" method used to skip over a given number of elements in a sequence and then return the remainder.Where as "Take" method returns only specified number records from a sequence.
For Example:
Let say, the Product table contains 100 records. During initial page load "PageIndex" property will be "zero",So the equivalent LINQ expression generated will skip the first "zero" record (i.e. no record is skipped) and fetches first five records from the resultant record set.
This way of data access fetches only fixed number of records on demand, which avoids unnecessary processing overhead at both DB layer and business layer of your application and also makes optimal use of network bandwidth.
Note: You can see the equivalent SQL statement generated for Linq methods "SKIP" and "TAKE" using "Linq pad" (Free tool for running linq queries).
Handling sorting:
In order to support custom sorting via linq, we would need some thing like "OrderBy" clause that takes "sortexpression" as parameter in linq queries. However by default Linq doesn't provide this option.
At this point, we could harness the power of extension methods to overcome the limitation.
A new extension method "OrderBy" is used here for dynamically applying orderby clause to linq queries.
4. when the pagination links "NEXT or "PREV" is clicked,the parameter "PageIndex" used in above query will be incremented or decremented accordingly.
protected void btnNext_Click(object sender, EventArgs e)
{
PageIndex = PageIndex + 1;
BindProducts();
BindPager();
}
protected void btnPrev_Click(object sender, EventArgs e)
{
PageIndex = PageIndex - 1;
BindProducts();
BindPager();
}
5. In the gridview sorting event handler the property "SortDirection" is updated and appropriate binding methods are called to update the UI.
protected void gvProducts_Sorting(object sender, GridViewSortEventArgs e)
{
if (CurrentSortDirection == SortDirection.Ascending)
{
CurrentSortExpression = e.SortExpression + " " + SortDirection.Descending.ToString();
CurrentSortDirection = SortDirection.Descending;
}
else
{
CurrentSortExpression = e.SortExpression + " " + SortDirection.Ascending.ToString();
CurrentSortDirection = SortDirection.Ascending;
}
BindProducts();
BindPager();
}
Hope you enjoyed this article.
Happy coding,
MK
No comments:
Post a Comment