I believe that you have already read y previous article that is N-Tier Architecture with Entity Framework and enjoyed the Entity Framework as well.
Now I will going to show you that how to use ListView with ObjectDataSource and Support Resource File. This is the best way when you are maintaining the Admin side.(As per my experience)
This Tutorial will show you all the things SQL Query, Sorting, Filtering, Paging, ResourceFile etc.
First you need to create a Generic Query for getting the Employee Detail Pagewise with Sorting functionality implemented. I have written the Query like this you can use your own standards.
CREATE PROCEDURE GetEmployee
@PageIndex INT = 0,
@PageSize INT = 10,
@SortBy NVARCHAR(50) = ”,
@SortOrder NVARCHAR(50) = ‘ASC’,
@SearchOn NVARCHAR(MAX) = ”,
@SearchText NVARCHAR(MAX) = ”,
@TotalRecCount INT OUT
AS
BEGIN
DECLARE @RangeFrom INT =(@PageIndex * @PageSize) + 1
DECLARE @RangeTo INT =(@PageIndex + 1) * @PageSize
SELECT ID, EmployeeName, Designation FROM
(SELECT ROW_NUMBER() OVER
(
ORDER BY
CASE WHEN @SortBy=’Employee’ AND @SortOrder = ‘DESC’ THEN e.Name END DESC,
CASE WHEN @SortBy=’Employee’ AND @SortOrder = ‘ASC’ THEN e.Name END ASC,
CASE WHEN @SortBy=’Designation’ AND @SortOrder = ‘DESC’ THEN d.Name END DESC,
CASE WHEN @SortBy=’Designation’ AND @SortOrder = ‘ASC’ THEN d.Name END ASC
) AS RowNumber,
e.ID ,e.Name AS EmployeeName ,d.Name AS Designation
FROM Employee e INNER JOIN Designation d ON e.ID = d.ID
WHERE @SearchOn like ‘%’ + @SearchText + ‘%’
)temp
WHERE RowNumber between CONVERT(VARCHAR(10), @RangeFrom) AND CONVERT(VARCHAR(10), @RangeTo)
SELECT @TotalRecCount = COUNT(e.ID) FROM Employee e INNER JOIN Designation d ON e.ID = d.ID
WHERE @SearchOn like ‘%’ + @SearchText + ‘%’
END
GO
Now next thing is to import this Stored Procedure in edmx and create complex type for this.
For that go to DAL Project and open edmx file and then right click and select “Update Model From Database” Import the Stored Procedure and then Create function for that. You can create Complex Type or you can use Entity as well. Here We are creating new Complex Type named “EmployeeDetail”
Now Build the DAL Project only.
Now Move back to the BAL Project. And Add reference Systes.Data.Entity and Add namespace System.Data.Objects (for ObjectParameter) . Here is the Code that you need to write into your BAL Project in Employee Entity.(In Main Project you need to create object of that entity and Call this method to get the result)
publicList<EmployeeDetail> GetEmpForList(string sortField, string sortDirection, int pageSize, int pageIndex, string searchText, string searchOn, outint totalRecords)
{
ObjectParameter TotalRecords = newObjectParameter(“TotalRecCount”, Type.GetType(“System.Int32”));
List<EmployeeDetail> lstQACategory = db.fnGetEmployee(pageIndex, pageSize, sortField, sortDirection, searchOn, searchText, TotalRecords).ToList();
totalRecords = Convert.IsDBNull(TotalRecords.Value) ? 0 : (int)TotalRecords.Value;
return lstQACategory;
}
All the Parameters used in the SQL Query needs to be passed from here.
Now That’s it done with the BAL and DAL. Now focus on the UI Side and code behind methods.
Now this is the Code that need to be there in aspx page.
<%@PageLanguage=”C#”AutoEventWireup=”true”CodeBehind=”EmpDesignation.aspx.cs”Inherits=”ArchDesign.UI.EmpDesignation”%>
<!DOCTYPEhtml>
<htmlxmlns=”http://www.w3.org/1999/xhtml”>
<headrunat=”server”>
<title></title>
</head>
<body>
<formid=”form1″runat=”server”>
<div>
<table>
<tr>
<td>
Search On:
<asp:DropDownListID=”ddlSearchOn”runat=”server”AppendDataBoundItems=”true”>
<asp:ListItemValue=”0″Text=”Select”></asp:ListItem>
<asp:ListItemValue=”1″Text=”Employee”></asp:ListItem>
<asp:ListItemValue=”2″Text=”Designation”></asp:ListItem>
</asp:DropDownList>
<asp:TextBoxID=”txtSearchValue”runat=”server”></asp:TextBox>
<asp:ButtonID=”btnSearch”runat=”server”Text=”Search”OnClick=”btnSearch_Click”/>
<asp:ButtonID=”btnReset”runat=”server”Text=”Reset”OnClick=”btnReset_Click”/>
</td>
</tr>
<tr>
<td>
<asp:ListViewID=”lvEmpDesignation”runat=”server”DataKeyNames=”ID”DataSourceID=”odsEmpDesignation”OnPreRender=”lvEmpDesignation_PreRender”>
<LayoutTemplate>
<tablewidth=”100%”border=”1″cellspacing=”0″cellpadding=”0″>
<tr>
<tdalign=”left”width=”45%”>
<asp:LinkButtonID=”lnkEmployeeName”runat=”server”CommandName=”CustomSort”
CommandArgument=”Employee”OnCommand=”Custom_Command”meta:resourcekey=”lnkEmployeeNameResource”></asp:LinkButton>
</td>
<tdalign=”left”width=”45%”>
<asp:LinkButtonID=”lnkDesignation”runat=”server”CommandName=”CustomSort”
CommandArgument=”Designation”OnCommand=”Custom_Command”meta:resourcekey=”lnkDesignationResource”></asp:LinkButton>
</td>
<tdalign=”left”width=”10%”>
<asp:LabelID=”lblAction”runat=”server”meta:resourcekey=”lblActionResource”></asp:Label>
</td>
</tr>
<asp:PlaceHolderID=”itemPlaceHolder”runat=”server”></asp:PlaceHolder>
<tr>
<tdalign=”right”colspan=”3″>
<asp:DataPagerID=”dpEmpDesignation”runat=”server”PagedControlID=”lvEmpDesignation”PageSize=”10″>
<Fields>
<asp:NumericPagerField/>
</Fields>
</asp:DataPager>
</td>
</tr>
</table>
</LayoutTemplate>
<ItemTemplate>
<tr>
<tdalign=”left”width=”45%”>
<%# Eval(“EmployeeName”) %>
</td>
<tdalign=”left”width=”45%”>
<%# Eval(“Designation”) %>
</td>
<tdalign=”center”width=”10%”>
<asp:ImageButtonID=”hypEdit”runat=”server”CommandName=”CustomEdit”CommandArgument='<%# Eval(“ID”) %>’
OnCommand=”Custom_Command”ImageUrl=”~/Images/edit.png”/>
</td>
</tr>
</ItemTemplate>
<EmptyDataTemplate>
<tablewidth=”100%”border=”0″cellspacing=”0″cellpadding=”0″>
<tr>
<tdalign=”left”width=”45%”>
<asp:LabelID=”lblEmpName”runat=”server”meta:resourcekey=”lnkEmployeeNameResource”></asp:Label>
</td>
<tdalign=”left”width=”45%”>
<asp:LabelID=”lblDesignation”runat=”server”meta:resourcekey=”lnkDesignationResource”></asp:Label>
</td>
<tdalign=”center”width=”10%”>
<asp:LabelID=”lblAction”runat=”server”meta:resourcekey=”lblActionResource”></asp:Label>
</td>
</tr>
<tr>
<tdalign=”center”colspan=”3″>
<asp:LabelID=”lblNoRecord”runat=”server”meta:resourcekey=”lblActionResource”></asp:Label>
</td>
</tr>
</table>
</EmptyDataTemplate>
</asp:ListView>
<asp:ObjectDataSourceID=”odsEmpDesignation”runat=”server”SelectMethod=”GetEmpDesignation”
SelectCountMethod=”GetTotalRowCount”EnablePaging=”true”
MaximumRowsParameterName=”pageSize”TypeName=”ArchDesign.UI.EmpDesignation”OnSelecting=”odsEmpDesignation_Selecting”>
<SelectParameters>
<asp:ParameterName=”sortField”Type=”String”/>
<asp:ParameterName=”sortDirection”Type=”String”/>
</SelectParameters>
</asp:ObjectDataSource>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Note: Method Name you need to remember because this is mandatory for this.
ListView : OnPreRender, OnCommand(Linkbutton in ItemTemplate)
ObjectDataSource : SelectMethod, SelectCountMethod
Here is the code for that
using ArchDesign.BAL;
using ArchDesign.DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ArchDesign.UI
{
publicpartialclassEmpDesignation : System.Web.UI.Page
{
int totalRecordCount;
staticstring searchText = “”;
staticstring searchOn = “”;
///<summary>
/// Page Load Event
///</summary>
///<param name=”sender”></param>
///<param name=”e”></param>
protectedvoid Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
ViewState[“SortBy”] = “Employee”;
ViewState[“AscDesc”] = “ASC”;
}
}
///<summary>
/// OnSelect Command whenver you cal DataBind of List this method calls
///</summary>
///<param name=”sender”></param>
///<param name=”e”></param>
protectedvoid odsEmpDesignation_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
e.InputParameters[“sortField”] = ViewState[“SortBy”].ToString();
e.InputParameters[“sortDirection”] = ViewState[“AscDesc”].ToString();
e.InputParameters[“searchText”] = searchText;
e.InputParameters[“searchOn”] = searchOn;
}
///<summary>
/// ListView PreRender Event (Used for set the sortig Image)
///</summary>
///<param name=”sender”></param>
///<param name=”e”></param>
protectedvoid lvEmpDesignation_PreRender(object sender, EventArgs e)
{
if (lvEmpDesignation.Items.Count > 0)
{
SetSortImage();
}
}
///<summary>
/// Handle Click Event of the button used inside the listview
///</summary>
///<param name=”sender”></param>
///<param name=”e”></param>
protectedvoid Custom_Command(object sender, CommandEventArgs e)
{
if (e.CommandName.ToLower() == “customsort”)
{
if (ViewState[“AscDesc”] == null || ViewState[“AscDesc”].ToString() == “”)
ViewState[“AscDesc”] = “ASC”;
else
{
if (ViewState[“AscDesc”].ToString() == “ASC”)
ViewState[“AscDesc”] = “DESC”;
else
ViewState[“AscDesc”] = “ASC”;
}
ViewState[“SortBy”] = e.CommandArgument;
lvEmpDesignation.DataBind();
}
elseif (e.CommandName.ToLower() == “customedit”)
{
}
}
///<summary>
/// This function is used to show the Sorting is implemented on which column
///</summary>
privatevoid SetSortImage()
{
(lvEmpDesignation.FindControl(“lnkEmployeeName”) asLinkButton).Attributes.Add(“class”, “”);
(lvEmpDesignation.FindControl(“lnkDesignation”) asLinkButton).Attributes.Add(“class”, “”);
LinkButton lnkSortedColumn = null;
if (ViewState[“SortBy”] != null)
{
switch (ViewState[“SortBy”].ToString().ToLower())
{
case”employee”:
lnkSortedColumn = lvEmpDesignation.FindControl(“lnkEmployeeName”) asLinkButton;
break;
case”designation”:
lnkSortedColumn = lvEmpDesignation.FindControl(“lnkDesignation”) asLinkButton;
break;
}
}
if (lnkSortedColumn != null)
{
if (ViewState[“AscDesc”].ToString().ToLower() == “asc”)
{
lnkSortedColumn.Attributes.Add(“class”, “ascending”);
}
else
{
lnkSortedColumn.Attributes.Add(“class”, “descending”);
}
}
}
///<summary>
/// Get total no of rows in the database
///</summary>
///<param name=”sortField”></param>
///<param name=”sortDirection”></param>
///<param name=”searchText”></param>
///<param name=”searchOn”></param>
///<returns></returns>
publicint GetTotalRowCount(string sortField, string sortDirection, string searchText, string searchOn)
{
return totalRecordCount;
}
///<summary>
/// Get the Employee Detail
///</summary>
///<param name=”sortField”></param>
///<param name=”sortDirection”></param>
///<param name=”pageSize”></param>
///<param name=”startRowIndex”></param>
///<param name=”searchText”></param>
///<param name=”searchOn”></param>
///<returns></returns>
publicList<EmployeeDetail> GetEmpDesignation(string sortField, string sortDirection, int pageSize, int startRowIndex, string searchText, string searchOn)
{
int pageIndex = startRowIndex / pageSize;
int totalRecords;
EmployeeEntity empEntity = newEmployeeEntity();
List<EmployeeDetail> lstEmpDetail = empEntity.GetEmpForList(sortField, sortDirection, pageSize, pageIndex, searchText, searchOn, out totalRecords);
totalRecordCount = totalRecords;
return lstEmpDetail;
}
///<summary>
/// Searching for any Employee/Department
///</summary>
///<param name=”sender”></param>
///<param name=”e”></param>
protectedvoid btnSearch_Click(object sender, EventArgs e)
{
int selectedValue = Convert.ToInt32(ddlSearchOn.SelectedValue);
if (selectedValue > 0)
{
switch (selectedValue)
{
case 1:
searchOn = “Employee”;
break;
case 2:
searchOn = “Department”;
break;
}
}
searchText = txtSearchValue.Text;
lvEmpDesignation.DataBind();
}
///<summary>
/// Reset the Filter applied on the ListView.
///</summary>
///<param name=”sender”></param>
///<param name=”e”></param>
protectedvoid btnReset_Click(object sender, EventArgs e)
{
searchText = “”;
searchOn = “”;
txtSearchValue.Text = “”;
lvEmpDesignation.DataBind();
}
}
}
You just need to remember the parameters that are called in the select method of the objectDataSource. All the InputParameters that are assigning to the ObjectDataSource must be passed and for addition you can use the PageSize and PageIndex(StartRowindex).
Now you need to create one Resourcefile for handle multi language data. For that Right click on the UI Project and Add Folder named “App_LocalResources” in this folder you need to create the Resource File with the same name as page here My Page Name is “EmpDesignation.aspx” so my resource file name will be “EmpDesignation.aspx.resx”. This is for English Now if you want to make it for Saudi Arabia then the resultand file name will be “EmpDesignation.aspx.ar-SA.resx”. Create this file and the open this file it will display 3 columns named “Name”, “Value”, “Comment”. Name Is the unique Identifier, Value is the text to display and Comment is just for description.
In this code I have label that having meta:resourcekeyvalue as “lnkEmployeeNameResource” In resource file you need to write following
|
Name |
Value |
Comment |
|
lnkEmployeeNameResource.Text |
Employee |
|
|
addValue |
Add Value |
|
Here resource file will replace the lnkEmployeeNameResource.Text value with “Employee” and addValue with the “Add Value”. You can get the local resource object at javascript or codebehind using following method.
this.GetLocalResourceObject(“addValue “).ToString();
General Mistakes :
- TypeName of ObjectDataSource will be the same as Inherits Property value of the Project.
- Parameters are not same in the select method. (Use all the parameter name that are preset in the select method.)
- Add PlaceHolder in the ListView
- Add DataPager and add the value PageSize.
- DataSourceID(id of the ObjectDatasource) and DataKeyNames(Unique Identifier) value must be there in the ListView.
Enjoy… have a nice Day J
Sagar Rawal 