Reader Level:
Articles

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

By Dheeraj Rajpoot on March 22, 2012
In this article we will see how to get data from a DataList using a stored procedure and pagination of the DataList control.
  • 0
  • 0
  • 9280

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.

COMMENT USING

Trending up