Friday, July 17, 2009

Create Custom Paging For Batter Performance

Hi.
In this article i Create sample page for creating Custom paging with Searching and Sorting Functionality.

i Will put My .ASPX ,.ASPX.CS and SQL Query i have Used to Complete this Functionality,

I Will Also Put Script to generate table I have Used for My Paging.and also Insert some data in it.

hope this will help us.

(1)SearchCountry.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SearchCountry.aspx.cs" Inherits="SearchCountry" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Search Country</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnFirst" Text="First" runat="server" OnClick="btnFirst_Click" />
<asp:Button ID="btnNext" Text="Next" runat="server" OnClick="btnNext_Click" />
<asp:Button ID="btnPrev" Text="Prev" runat="server" OnClick="btnPrev_Click" />
<asp:Button ID="btnLast" Text="Last" runat="server" OnClick="btnLast_Click" />
</div>
<div>
<b>
<asp:Label ID="lblCurrentPage" runat="server"></asp:Label>
</b>
<asp:GridView ID="grvCountry" AllowSorting="True" AutoGenerateColumns="False" runat="server"
OnSorting="grvCountry_Sorting" CellPadding="4" ForeColor="#333333" GridLines="None">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<Columns>
<asp:BoundField DataField="Country" SortExpression="Country" HeaderText="Country" />
<asp:BoundField DataField="BarrelsPerDay" SortExpression="BarrelsPerDay" HeaderText="BarrelsPerDay" />
<asp:BoundField DataField="Continent" SortExpression="Continent" HeaderText="Continent" />
<asp:BoundField DataField="Region" SortExpression="Region" HeaderText="Region" />
</Columns>
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
</div>
<div>
<table>
<tr>
<td>
Country:
</td>
<td>
<asp:TextBox ID="txtCountry" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Continent:
</td>
<td>
<asp:TextBox ID="txtContinent" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Region:
</td>
<td>
<asp:TextBox ID="txtRegion" runat="server"></asp:TextBox>
</td>
</tr>
</table>
</div>
<div>
<asp:Button ID="btnSearch" Text="Search" runat="server" OnClick="btnSearch_Click" />
</div>
</form>
</body>
</html>

(2)SearchCountry.aspx.cs


using System;
using System.Data;
using System.Web.UI.WebControls;
using DataAccess;
public partial class SearchCountry : System.Web.UI.Page
{
const int PageSize = 100;
public int PageNumber
{
get
{
int p = 0;
if (ViewState["PageNumber"] == null)
{
p = 1;
}
else
{
p = Convert.ToInt32(ViewState["PageNumber"]);

}
return p;
}
set
{
ViewState["PageNumber"] = value;
}

}
private string country
{
get
{
return txtCountry.Text;
}

}
private string Continent
{
get
{
return txtContinent.Text;
}

}
private string Region
{
get
{
return txtRegion.Text;
}

}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindCountrues(1);
}
}
private void BindCountrues(int Page)
{
Country c = new Country();
DataTable tab = c.SearchCountry(Page, PageSize, country, Continent, Region, "");
grvCountry.DataSource = tab;
grvCountry.DataBind();
if (tab.Rows.Count > 0)
{
ViewState["TotalPages"] = tab.Rows[0]["TotalPages"];
}
else
{
ViewState["TotalPages"] = 1;
}
lblCurrentPage.Text = "Viewing Page:" + (PageNumber) + "(1 To " + ViewState["TotalPages"].ToString() + ")";
}
protected void btnNext_Click(object sender, EventArgs e)
{
int MaxPage = Convert.ToInt32(ViewState["TotalPages"]);
if (PageNumber < MaxPage)
{
PageNumber = PageNumber + 1;
}
BindCountrues(PageNumber);
}
protected void btnPrev_Click(object sender, EventArgs e)
{
if (PageNumber > 1)
{
PageNumber = PageNumber - 1;
}
BindCountrues(PageNumber);
}
protected void btnSearch_Click(object sender, EventArgs e)
{
BindCountrues(PageNumber);
}
protected void btnFirst_Click(object sender, EventArgs e)
{
PageNumber = 1;
BindCountrues(PageNumber);
}
protected void btnLast_Click(object sender, EventArgs e)
{
int LastPage = Convert.ToInt32(ViewState["TotalPages"]);
PageNumber = LastPage;
BindCountrues(PageNumber);
}
protected void grvCountry_Sorting(object sender, GridViewSortEventArgs e)
{
Country c = new Country();
if (ViewState["SortOrder"] == null)
{
ViewState["SortOrder"] = " Desc";
}
else if (ViewState["SortOrder"].ToString().Equals(" Desc"))
{
ViewState["SortOrder"] = " Asc";
}
else
{
ViewState["SortOrder"] = " Desc";
}
DataTable tab = c.SearchCountry(PageNumber, PageSize, country, Continent, Region, e.SortExpression + ViewState["SortOrder"].ToString());
grvCountry.DataSource = tab;
grvCountry.DataBind();
}
}


(3)Business Layer Method.

public DataTable SearchCountry(int PageNo, int PageSize, string country,string Continent, string Region, string OrderBy)
{
SqlCommand cmd = GetSqlCommand();
cmd.CommandText = "SP_SearchCountry";
cmd.Parameters.AddWithValue("@Page", PageNo);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.AddWithValue("@country", country);
cmd.Parameters.AddWithValue("@Continent", Continent);
cmd.Parameters.AddWithValue("@Region", Region);
cmd.Parameters.AddWithValue("@OrderBy", OrderBy);
return ExecuteDataTable(cmd);

}


(4)Stored Procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER Procedure [dbo].[SP_SearchCountry]
(
@Page int=0,
@PageSize int=0,
@country as varchar(20)='',
@Continent as varchar(20)='',
@Region as varchar(20)='',
@OrderBy as varchar(1000)=''
)
as
BEGIN
--Local variables
Declare @Query as nvarchar(500)
Declare @Params as nvarchar(200)
Declare @Where as varchar(200)
Declare @Order as varchar(100)
select @Where='',@Order='',@OrderBy= replace(@OrderBy,'''',''),@country= replace(@country,'''',''),@Continent= replace(@Continent,'''',''),@Region= replace(@Region,'''','')

--Prepare Where
IF(len(@country)<>0)
BEGIN
set @Where=@Where+' AND country like ''%'+@country+'%'''
END
IF(len(@Continent)<>0)
BEGIN
set @Where=@Where+' AND Continent like ''%'+@Continent+'%'''
END
IF(len(@Region)<>0)
BEGIN
set @Where=@Where+' AND Region like ''%'+@Region+'%'''
END
--Prepare Order By
IF(len(@OrderBy)>0)
BEGIN
set @Order=' Order by '+@OrderBy
END
set @Query=';With FirtTable as
(
Select ROW_NUMBER() over(order by OilProducer_ID) as RowNo, country,Barrelsperday,Continent,Region from OilProducers where 1=1 '+@Where+'
)
select country,Barrelsperday,Continent,Region,((select count(*) from FirtTable)/@PageSize) TotalPages from FirtTable where RowNO>(@Page*@PageSize) and RowNO<(@Page*@PageSize)+@PageSize+1'+@Order
set @Params='@PageSize int,@Page int'

exec sp_executesql @Query,@Params,@PageSize=@PageSize,@Page=@Page

END

(5)Table Script:

CREATE TABLE [dbo].[OilProducers](
[OilProducer_ID] [int] IDENTITY(1,1) NOT NULL,
[country] [varchar](30) NOT NULL,
[BarrelsPerDay] [int] NOT NULL,
[Continent] [varchar](80) NOT NULL,
[Region] [varchar](80) NOT NULL
) ON [PRIMARY]

(6)Sample data.

INSERT INTO [OilProducers]([country],[BarrelsPerDay],[Continent],[Region]) VALUES('India',5000,'Asia','Extern-Asia')
INSERT INTO [OilProducers]([country],[BarrelsPerDay],[Continent],[Region]) VALUES('Pakistan',4000,'Asia','Extern-Asia')
INSERT INTO [OilProducers]([country],[BarrelsPerDay],[Continent],[Region]) VALUES('Afghanistan',6000,'Asia','Extern-Asia')
INSERT INTO [OilProducers]([country],[BarrelsPerDay],[Continent],[Region]) VALUES('Austrelia',6000,'Austrelia','Austrelia')
INSERT INTO [OilProducers]([country],[BarrelsPerDay],[Continent],[Region]) VALUES('Peru',6000,'Africa','Esat-Africa')

Thanks..