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

In this article we will see how to get data from a DataList using a 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" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<asp:ImageButton ID="lnkbtnPrevious" runat="server"
ImageUrl="~/images/prevpage.gif" onclick="lnkbtnPrevious_Click"
ToolTip="Previous" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<asp:ImageButton ID="lnkbtnNext" runat="server"
ImageUrl="~/images/nextpage.gif" onclick="lnkbtnNext_Click" style="width: 10px"
ToolTip="Next" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<asp:ImageButton ID="Last" runat="server" ImageUrl="~/images/lastpage.gif"
onclick="Last_Click1" ToolTip="Last" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

</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.