How to Get Data From DataList Using Stored Procedure and Pagination of the DataList Control

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.