Friday, September 26, 2008

Custom Paging in GridView, DataList or Repeater Using Stored Procedure.

·

First of all of I have created table named tblEmployee as belove figure.


Using Row_Number : Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.


Query : select *,ROW_NUMBER() over(Order By Name) as PageNum from tblEmployee


This query generates one extra column as PageNum title and it contains index of data row starting at 1for the first row.

Now to perform Paging in store procedure, I Pass two parameter @PageIndex and @PageSizes and get back total number of cecords in the table as out parameter.

Store Procedure


CREATE PROCEDURE usp_List_Employees

(
@pageIndex int,
@PageSize int,
@OutParameter int out
)

AS

Select * from
(
select *,ROW_NUMBER() over(Order By Name) as PageNum
from tblEmployee
)
as tblEmployee
where PageNum Between (@PageIndex - 1) * @PageSize + 1 and @PageIndex * @PageSize

select @outParameter=count(*) from tblEmployee


Inline code

<table cellpadding="5" cellspacing="2" width="400px">
<tr>
<td colspan="8">
<asp:GridView ID="grid" runat="server" AutoGenerateColumns="False" CellPadding="4"
ForeColor="#333333" GridLines="None" Height="184px" Width="491px">
<FooterStyle BackColor="#5D7B9D" ForeColor="White" Font-Bold="True" />
<RowStyle ForeColor="#333333" BackColor="#F7F6F3" HorizontalAlign="left" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" HorizontalAlign="left" />
<Columns>
<asp:TemplateField HeaderText="Sr.No">
<ItemTemplate>
<%# Eval("PageNum") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Employee Name">
<ItemTemplate>
<%#Eval("Name") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Email Address">
<ItemTemplate>
<%#Eval("Email") %>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
</td>
</tr>
<tr>
<td width="10px">
<asp:LinkButton ID="lnkFirst" runat="server" Text="First" OnClick="Paging_Event"></asp:LinkButton></td>
<td width="10px">
<asp:LinkButton ID="lnkPre" runat="server" Text="Pre" OnClick="Paging_Event"></asp:LinkButton></td>
<td width="10px">
<asp:LinkButton ID="lnkNext" runat="server" Text="Next" OnClick="Paging_Event"></asp:LinkButton></td>
<td width="10px">
<asp:LinkButton ID="lnkLast" runat="Server" Text="Last" OnClick="Paging_Event"></asp:LinkButton></td>
<td width="100px"></td>
<td>
Page <asp:Label ID="lblPageNo" runat="server"></asp:Label> of 
<asp:Label ID="lblTotalPage" runat="server"></asp:Label></td>
<td>
</td>
<td align="right">
Jump to <asp:DropDownList ID="ddl" runat="server" AutoPostBack="true" OnSelectedIndexChanged="Jump_ToPaging">
</asp:DropDownList></td>
</tr>
</table>




code behind


<add key="conString" value="server=ServerName; Data Source=DataSourceName; uid=username pw=password"/>


using System.Data.SqlClient;


private string conString = System.Configuration.ConfigurationManager.AppSettings["conString"];
private int currentPageIndex = 1;
private int pageSize = 5;
private int totalRecords=0;
private double totalPage = 0;


protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bind_Employees();
BindPageIndex();
}
}


public void bind_Employees()
{
grid.DataSource = GetData();
grid.DataBind();
PageSetting();
}


public void PageSetting()
{
totalPage = Convert.ToDouble(totalRecords) / pageSize;
totalPage = System.Math.Ceiling(totalPage);
lblPageNo.Text = totalPage == 0 ? "0" : currentPageIndex.ToString();
lblTotalPage.Text = totalPage.ToString();


if (currentPageIndex == 1)
{
lnkFirst.Enabled = false;
lnkPre.Enabled = false;
}
else
{
lnkFirst.Enabled = true;
lnkPre.Enabled = true;
}
if (totalPage > 1)
{
if (currentPageIndex != totalPage)
{
lnkNext.Enabled = true;
lnkLast.Enabled = true;
}
else
{
lnkNext.Enabled = false;
lnkLast.Enabled = false;
}
}
else
{
lnkNext.Enabled = false;
lnkLast.Enabled = false;
}
}


public void BindPageIndex()
{
ddl.Items.Clear();
for (int i = 1; i <= totalPage; i++)
{
ddl.Items.Add(new ListItem(i.ToString(), i.ToString()));
}
}


public DataSet GetData()
{
SqlConnection con = new SqlConnection(conString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "usp_List_Employees";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;

SqlParameter prm = new SqlParameter();
prm.ParameterName = "@pageIndex";
prm.Direction = ParameterDirection.Input;
prm.SqlDbType = SqlDbType.Int;
prm.SqlValue = currentPageIndex.ToString();
cmd.Parameters.Add(prm);

prm = new SqlParameter();
prm.ParameterName = "@PageSize";
prm.Direction = ParameterDirection.Input;
prm.SqlDbType = SqlDbType.Int;
prm.SqlValue = pageSize;
cmd.Parameters.Add(prm);

prm = new SqlParameter();
prm.ParameterName = "@OutParameter";
prm.Direction = ParameterDirection.Output;
prm.SqlDbType = SqlDbType.Int;
cmd.Parameters.Add(prm);


SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
totalRecords = int.Parse(cmd.Parameters["@OutParameter"].Value.ToString());
return ds;
}


protected void Paging_Event(object sender, EventArgs e)
{
switch (((LinkButton)sender).ID)
{
case "lnkFirst":
currentPageIndex = 1;
break;
case "lnkLast":
currentPageIndex = Int32.Parse(lblTotalPage.Text);
break;
case "lnkNext":
currentPageIndex = Int32.Parse(lblPageNo.Text) + 1;
break;
case "lnkPre":
currentPageIndex = Int32.Parse(lblPageNo.Text) - 1;
break;
}
bind_Employees();
ddl.SelectedValue = currentPageIndex.ToString();
}


protected void Jump_ToPaging(object sender, EventArgs e)
{
currentPageIndex = int.Parse(ddl.SelectedValue);
bind_Employees();
}

0 comments: