In this article I will describe how to get data from a DataList using a stored procedure and pagination of the DataList control.
Stored Procedure:
ALTER PROCEDURE dbo.NewSelectCommand
AS
SET NOCOUNT ON;
SELECT EmpID, EmpName,EmpAddress, EmpPhone FROM Employee
Default.aspx
<div class="center">
<asp:DataList ID="DataList1" runat="server" CellPadding="4" ForeColor="#333333"
GridLines="Both" RepeatColumns="1" Width="657px">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingItemStyle BackColor="White" />
<ItemStyle BackColor="#EFF3FB" />
<SelectedItemStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderTemplate>
<table width="100%">
<tr>
<th width="25%">Country Code</th>
<th width="25%">Region</th>
<th width="25%">City</th>
<th width="25%">Sub Area Code</th>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table width="100%">
<tr>
<td width="25%"><asp:Label ID="Label1" runat="server" Text='<%# Eval("EmpID") %>'></asp:Label></td>
<td width="25%"><asp:Label ID="Label2" runat="server" Text='<%# Eval("EmpName") %>'></asp:Label></td>
<td width="25%"><asp:Label ID="Label3" runat="server" Text='<%# Eval("EmpAddress") %>'></asp:Label></td>
<td width="25%"><asp:Label ID="Label4" runat="server" Text='<%# Eval("EmpPhone") %>'></asp:Label></td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
<div style="text-align:center; width: 657px;">
<asp:DataList ID="DataList2" runat="server"
onitemcommand="DataList2_ItemCommand" onitemdatabound="DataList2_ItemDataBound"
RepeatDirection="Horizontal">
<ItemTemplate>
<asp:LinkButton ID="lnkbtnPaging" runat="server" CommandArgument='<%# Eval("PageIndex") %>' CommandName="lnkbtnPaging" Text='<%# Eval("PageText") %>' style="text-align:center;"></asp:LinkButton>
</ItemTemplate>
</asp:DataList>
</div>
<div style="text-align:right; width: 657px;">
<asp:ImageButton ID="First" runat="server" ImageUrl="~/images/firstpage.gif"
onclick="First_Click1" ToolTip="First" />
<asp:ImageButton ID="lnkbtnPrevious" runat="server"
ImageUrl="~/images/prevpage.gif" onclick="lnkbtnPrevious_Click"
ToolTip="Previous" />
<asp:ImageButton ID="lnkbtnNext" runat="server"
ImageUrl="~/images/nextpage.gif" onclick="lnkbtnNext_Click" style="width: 10px"
ToolTip="Next" />
<asp:ImageButton ID="Last" runat="server" ImageUrl="~/images/lastpage.gif"
onclick="Last_Click1" ToolTip="Last" />
</div>
Default.aspx.cs
protected void Page_Load(object sender, EventArgs
e)
{
if
(!IsPostBack)
{
BindGrid();
}
}
private void BindGrid()
{
DataTable
dt = new DataTable();
string
connect = WebConfigurationManager.ConnectionStrings["Con"].ConnectionString;
SqlConnection
con1 = new SqlConnection(connect);
SqlCommand
cmd = new SqlCommand("NewSelectCommand", con1);
cmd.CommandType = CommandType.StoredProcedure;
con1.Open();
SqlDataAdapter
da = new SqlDataAdapter(cmd);
PagedDataSource
pds = new PagedDataSource();
da.Fill(dt);
pds.DataSource = dt.DefaultView;
pds.AllowPaging = true;
pds.PageSize = 5;
pds.CurrentPageIndex = CurrentPage;
lnkbtnNext.Enabled = !pds.IsLastPage;
lnkbtnPrevious.Enabled = !pds.IsFirstPage;
ViewState["TotalPages"]
= pds.PageCount;
DataList1.DataSource = pds;
DataList1.DataBind();
doPaging();
}
public int CurrentPage
{
get
{
if (this.ViewState["CurrentPage"]
== null)
return
0;
else
return
Convert.ToInt16(this.ViewState["CurrentPage"].ToString());
}
set
{
this.ViewState["CurrentPage"] = value;
}
}
private void doPaging()
{
DataTable
dt = new DataTable();
dt.Columns.Add("PageIndex");
dt.Columns.Add("PageText");
for (int i = 0; i < pds.PageCount; i++)
{
DataRow
dr = dt.NewRow();
dr[0] = i;
dr[1] = i + 1;
dt.Rows.Add(dr);
}
DataList2.DataSource = dt;
DataList2.DataBind();
}
protected void
DataList2_ItemCommand(object source, DataListCommandEventArgs e)
{
if
(e.CommandName.Equals("lnkbtnPaging"))
{
CurrentPage = Convert.ToInt16(e.CommandArgument.ToString());
BindGrid();
}
}
protected void
DataList2_ItemDataBound(object sender, DataListItemEventArgs e)
{
LinkButton
lnkbtnPage = (LinkButton)e.Item.FindControl("lnkbtnPaging");
if
(lnkbtnPage.CommandArgument.ToString() == CurrentPage.ToString())
{
lnkbtnPage.Enabled = false;
lnkbtnPage.Font.Bold = true;
}
}
protected void
First_Click1(object sender, ImageClickEventArgs e)
{
CurrentPage = 0;
BindGrid();
}
protected void
lnkbtnPrevious_Click(object sender, ImageClickEventArgs e)
{
CurrentPage -= 1;
BindGrid();
}
protected void
lnkbtnNext_Click(object sender, ImageClickEventArgs e)
{
CurrentPage += 1;
BindGrid();
}
protected void
Last_Click1(object sender, ImageClickEventArgs e)
{
CurrentPage = (Convert.ToInt32(ViewState["TotalPages"]) - 1);
BindGrid();
}
Thanks.