Remove From My Forums

How to Efficiently Paging,Searching,Sorting through data using the ASP.NET

Question

User-1499457942 posted

I have below code . I want Search & Sorting functionality to be added. Secondly paging should look lije Jquery Datatable

CREATE PROCEDURE GetEmployeesPageWise
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [EmployeeID] ASC
      )AS RowNumber
      ,[EmployeeID]
      ,[FirstName] + ' ' + [MiddleName] + ' ' + [LastName] as FullName

      INTO #Results
      FROM [Employee]
     
      SELECT @RecordCount = COUNT(*)
      FROM #Results
           
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
     
      DROP TABLE #Results
END
GO


private void GetEmployeesPageWise(int pageIndex)
        {
            using (SqlConnection con = new SqlConnection(CommonFunction.connectionString))
            {
                using (SqlCommand cmd = new SqlCommand("GetEmployeesPageWise", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                    cmd.Parameters.AddWithValue("@PageSize", int.Parse(ddlPageSize.SelectedValue));
                    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
                    cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                    con.Open();
                    IDataReader idr = cmd.ExecuteReader();
                    gvwEmployee.DataSource = idr;
                    gvwEmployee.DataBind();
                    idr.Close();
                    con.Close();
                    int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
                    this.PopulatePager(recordCount, pageIndex);
                }
            }
        }
        private void PopulatePager(int recordCount, int currentPage)
        {
            double dblPageCount = (double)((decimal)recordCount / decimal.Parse(ddlPageSize.SelectedValue));
            int pageCount = (int)Math.Ceiling(dblPageCount);
            List<ListItem> pages = new List<ListItem>();
            if (pageCount > 0)
            {
                pages.Add(new ListItem("First", "1", currentPage > 1));
                for (int i = 1; i <= pageCount; i++)
                {
                    pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                }
                pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));
            }
            rptPager.DataSource = pages;
            rptPager.DataBind();
        }
        protected void PageSize_Changed(object sender, EventArgs e)
        {
            this.GetEmployeesPageWise(1);
        }
        protected void Page_Changed(object sender, EventArgs e)
        {
            int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
            this.GetEmployeesPageWise(pageIndex);
        }




    <div class="row">  
        <div class="col-lg-12 "> 
            <div class="table-responsive">
                PageSize:
                <asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSize_Changed">
                    <asp:ListItem Text="10" Value="10" />
                    <asp:ListItem Text="25" Value="25" />
                    <asp:ListItem Text="50" Value="50" />
                </asp:DropDownList>
                <asp:GridView ID="gvwEmployee"  runat="server" 
                CssClass="table table-bordered table-striped" AutoGenerateColumns="false" OnRowCommand="gvwEmployee_RowCommand" 
                BorderWidth="1px"  BorderStyle="None" BorderColor="#DEBA84" HeaderStyle-CssClass="GridHeader" ClientIDMode="Static"
                EmptyDataText="No Records Found!" EmptyDataRowStyle-ForeColor="Red" EmptyDataRowStyle-CssClass ="gvEmpty" >
                <HeaderStyle ForeColor="White" Font-Bold="True" BackColor="#428bca"></HeaderStyle>
                <Columns>
                    <asp:templatefield headertext="Action">
                        <itemtemplate>
                            <asp:linkbutton id="btnedit"  tooltip="update record" commandname="EditRow" causesvalidation="false" runat="server" cssclass="clslnkbutton"/> 
                        </itemtemplate>
                    </asp:templatefield>
                    <asp:templatefield headertext="Emp.Code">
                        <itemtemplate>
                            <asp:label runat="server" id="lblcode" text='<%#Eval("EmployeeId") %>'></asp:label>
                        </itemtemplate>
                    </asp:templatefield>
                    <asp:templatefield headertext="First Name">
                        <itemtemplate>
                            <asp:label runat="server" id="lblFirstName" text='<%#Eval("FullName") %>'></asp:label>
                        </itemtemplate>
                    </asp:templatefield>
 
                    
                </Columns>
                </asp:GridView>
                <br />
                <asp:Repeater ID="rptPager" runat="server">
                <ItemTemplate>
                    <asp:LinkButton ID="lnkPage" runat="server" Text = '<%#Eval("Text") %>' CommandArgument = '<%# Eval("Value") %>' Enabled = '<%# Eval("Enabled") %>' OnClick = "Page_Changed"></asp:LinkButton>
                </ItemTemplate>
                </asp:Repeater>
            </div>
        </div>
    </div>

Thanks

Tuesday, October 30, 2018 7:40 AM

All replies

User-893317190 posted

Hi JagjitSingh,

To use your stored procedure with sorting and searching, you should pass the value you want to filter and the column you want to order by.

So ,you should change your stored procedure.

Below is my code sorting and searching with stored procedure. I search data by customerId

 <div>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>  <asp:Button ID="Button1" runat="server" Text="search" CssClass="btn  btn-light" OnClick="Button1_Click" /> <br />
            <asp:ListView ID="ListView1" runat="server" DataKeyNames="CustomerID"  InsertItemPosition="LastItem"  OnItemUpdating="ListView1_ItemUpdating" OnItemEditing="ListView1_ItemEditing"
              OnItemCanceling="ListView1_ItemCanceling" OnSorting="ListView1_Sorting" OnItemInserting="ListView1_ItemInserting"
               OnItemDeleting="ListView1_ItemDeleting" >
                
  
                <ItemTemplate>
                    <tr style="">
                        <td>
                            <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Edit" />
                            <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="Delete" />
                        </td>
                        <td>
                            <asp:Label ID="CustomerIDLabel" runat="server" Text='<%# Eval("CustomerID") %>' />
                            <%# Container.DataItemIndex %>
                        </td>
                        <td>
                            <asp:Label ID="CompanyNameLabel" runat="server" Text='<%# Eval("CompanyName") %>' />
                        </td>
                        <td>
                            <asp:Label ID="ContactNameLabel" runat="server" Text='<%# Eval("ContactName") %>' />
                        </td>
                        <td>
                            <asp:Label ID="ContactTitleLabel" runat="server" Text='<%# Eval("ContactTitle") %>' />
                        </td>
                        <td>
                            <asp:Label ID="AddressLabel" runat="server" Text='<%# Eval("Address") %>' />
                        </td>
                        <td>
                            <asp:Label ID="CityLabel" runat="server" Text='<%# Eval("City") %>' />
                        </td>
                        <td>
                            <asp:Label ID="RegionLabel" runat="server" Text='<%# Eval("Region") %>' />
                        </td>
                        <td>
                            <asp:Label ID="PostalCodeLabel" runat="server" Text='<%# Eval("PostalCode") %>' />
                        </td>
                        <td>
                            <asp:Label ID="CountryLabel" runat="server" Text='<%# Eval("Country") %>' />
                        </td>
                        <td>
                            <asp:Label ID="PhoneLabel" runat="server" Text='<%# Eval("Phone") %>' />
                        </td>
                        <td>
                            <asp:Label ID="FaxLabel" runat="server" Text='<%# Eval("Fax") %>' />
                        </td>
                    </tr>
                </ItemTemplate>
                <LayoutTemplate>
                    <table runat="server"  class="table  table-striped table-bordered table-hover">
                        <tr runat="server">
                            <td runat="server">
                                <table id="itemPlaceholderContainer" runat="server" border="0" style="">
                                    <tr runat="server" style="">
                                        <th runat="server"></th>
                                        <th runat="server"><asp:LinkButton CssClass="btn  btn-light " ID="LinkButton1" runat="server" CommandName="Sort" CommandArgument="CustomerId">CustomerId</asp:LinkButton></th>
                                        <th runat="server"><asp:LinkButton CssClass="btn btn-light"  ID="LinkButton2" runat="server" CommandName="Sort" CommandArgument="CompanyName">CompanyName</asp:LinkButton></th>
                                        <th runat="server"><asp:LinkButton CssClass="btn btn-light"  ID="LinkButton3" runat="server" CommandName="Sort" CommandArgument="ContactName">ContactName</asp:LinkButton></th>
                                        <th runat="server"><asp:LinkButton CssClass="btn btn-light"  ID="LinkButton4" runat="server" CommandName="Sort" CommandArgument="ContactTitle">ContactTitle</asp:LinkButton></th>
                                        <th runat="server"><asp:LinkButton CssClass="btn btn-light"  ID="LinkButton5" runat="server" CommandName="Sort" CommandArgument="Address">Address</asp:LinkButton></th>
                                        <th runat="server"><asp:LinkButton CssClass="btn btn-light"  ID="LinkButton6" runat="server" CommandName="Sort" CommandArgument="City">City</asp:LinkButton></th>
                                        <th runat="server"><asp:LinkButton CssClass="btn btn-light"  ID="LinkButton7" runat="server" CommandName="Sort" CommandArgument="Region">Region</asp:LinkButton></th>
                                        <th runat="server"><asp:LinkButton CssClass="btn btn-light"  ID="LinkButton8" runat="server" CommandName="Sort" CommandArgument="PostalCode">PostalCode</asp:LinkButton></th>
                                        <th runat="server"><asp:LinkButton CssClass="btn btn-light"  ID="LinkButton9" runat="server" CommandName="Sort" CommandArgument="Country">Country</asp:LinkButton></th>
                                        <th runat="server"><asp:LinkButton CssClass="btn btn-light"  ID="LinkButton10" runat="server" CommandName="Sort" CommandArgument="Phone">Phone</asp:LinkButton></th>
                                        <th runat="server"><asp:LinkButton CssClass="btn btn-light"  ID="LinkButton11" runat="server" CommandName="Sort" CommandArgument="Fax">Fax</asp:LinkButton></th>
                                    </tr>
                                    <tr id="itemPlaceholder" runat="server">
                                    </tr>
                                </table>
                            </td>
                        </tr>
                        <tr >
                           
                        </tr>
                    
                    </table>
                </LayoutTemplate>
            
            </asp:ListView>
       
        </div>
            <nav aria-label="Page navigation example">
  <ul class="pagination justify-content-center">

      <%-- get currentPage and sort rule to generate pagination --%>
      <%CurrentPage = CurrentPage == 0 ? Convert.ToInt32(ViewState["currentPage"]) : CurrentPage;

          string pageSort = ViewState["sort"] == null ? "customerId" : ViewState["sort"].ToString();
          string search = ViewState["search"] == null ? "" : ViewState["search"].ToString();
          %>
    <li class="page-item  <%=CurrentPage==1||CurrentPage==0?"disabled":"" %>">
      <a class="page-link bg-dark text-white" href="CrudSPPagingSortingBootstrap.aspx?currentPage=<%=CurrentPage-1 %>&sort=<%=pageSort %><%=search==""?"":"&search="+search %>" tabindex="-1">Previous</a>
    </li>

     <%for (int i = 1; i <= TotalPage; i++)
            {%>
 <li class="page-item  "><a class="page-link  <%=i==CurrentPage?"bg-ligth  text-success":"bg-dark text-white" %>" href="CrudSPPagingSortingBootstrap.aspx?currentPage=<%=i %>&sort=<%=pageSort %><%=search==""?"":"&search="+search %>"><%=i %></a></li>
            <%} %>
    <li class="page-item  <%=CurrentPage==TotalPage?"disabled":"" %>">
      <a class="page-link bg-dark text-white" href="CrudSPPagingSortingBootstrap.aspx?currentPage=<%=CurrentPage+1 %>&sort=<%=pageSort %><%=search==""?"":"&search="+search %> ">Next</a>
    </li>
  </ul>
         
</nav>

Code behind. The key is to save data of searching and sorting through viewstate.

 //send totalPage currentPage to the page
        public int TotalPage { get; set; }
        public int CurrentPage { get; set; }
        public string url = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString; 
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
              
               int currentPage= Convert.ToInt32(Request["currentPage"]??"1");
                CurrentPage = currentPage;

                //keep the state of currentPage ,sort and search
                ViewState["currentPage"] = currentPage;
                ViewState["sort"] = Request["sort"];
                ViewState["search"] = Request["search"];
                BindData(currentPage, ViewState["sort"] == null ? "CustomerId": ViewState["sort"].ToString(),ViewState["search"]==null?"":ViewState["search"].ToString());
            }
           

        }



        // the method to bind data ,use currentPage and sort as parameter to page and sort
        public void BindData(int CurrentPage,string sort,string search)
        {

            //call sotored procedure
            string sql = "select_page_customer_where";
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, url))
            {
                SqlParameter sqlParameter = new SqlParameter("totalPage", SqlDbType.Int) { Direction = ParameterDirection.Output };
                SqlParameter[] sqlParameters = new SqlParameter[] {
                    new SqlParameter("currentPage",SqlDbType.Int){Value=CurrentPage},
                    sqlParameter,
                    new SqlParameter("pageSize",SqlDbType.Int){Value=5},
                    new SqlParameter("sort",SqlDbType.NVarChar,50){Value=sort},
                    new SqlParameter("custId",SqlDbType.NVarChar,50){Value=search}
                };
                
                
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.AddRange(sqlParameters);
                DataTable table = new DataTable();
                adapter.Fill(table);
                
                ListView1.DataSource = table;
                ListView1.DataBind();
                TotalPage =(int)Math.Ceiling(Convert.ToInt32( sqlParameter.Value)*1.0/10);

            }

        }

        
        protected void ListView1_Sorting(object sender, ListViewSortEventArgs e)
        {
            BindData(1, e.SortExpression, ViewState["search"] == null ? "" : ViewState["search"].ToString());
            ViewState["sort"] = e.SortExpression;
            ViewState["currentPage"] = 1;
        }

     

       
        protected void Button1_Click(object sender, EventArgs e)
        {
            BindData(1, ViewState["sort"] == null ? "CustomerId" : ViewState["sort"].ToString(), TextBox1.Text);
            ViewState["search"] = TextBox1.Text;
        }
    }

The procedure.

go
alter procedure [dbo].[select_page_customer_where]
(
@currentPage int,
@totalPage int out,
@pageSize int,
@sort  varchar(50) ,
@custId varchar(40))
as 
begin

 declare @spageSize varchar(20);
set @spageSize=convert(varchar(20),@pageSize);
 declare @scurrentPage varchar(20);
set @scurrentPage=convert(varchar(20),@currentPage);
--select * from (select * ,rowNum=row_number()  over (order by quotename(@sort))   from customers where customerId like concat('%',@custid,'%'))  as m where m.rowNum between ((@currentPage -1)*@pageSize +1) and @currentPage *@pageSize   order by m.rowNum
   declare @sql varchar(4000);
  -- set @sql='select * from (select * ,rowNum=row_number()  over (order by quotename('''+@sort+''')) from customers where customerId like concat(''%'','''+@custid+''',''%'')) as m where m.rowNum between (('+@scurrentPage+' -1)*'+@spageSize+' +1) and '+@scurrentPage+' *'+@spageSize+'   order by m.rowNum ';
  set @sql='select * from (select * ,rowNum=row_number()  over (order by quotename('+@sort+')) from customers where customerId like concat(''%'','''+@custid+''',''%'')) as m where m.rowNum between (('+@scurrentPage+' -1)*'+@spageSize+' +1) and '+@scurrentPage+' *'+@spageSize+'   order by m.rowNum ';
  -- exec sp_executesql @sql

execute (@sql)
set @totalPage=(select count(*) from customers where customerId like concat('%',@custid,'%'))
end

The result.

I don't understand clearly about paging should look like jquery datatable , here I use bootstrap.

Best regards,

Ackerly Xu

Wednesday, October 31, 2018 6:32 AM

Asked by:

How to Efficiently Paging,Searching,Sorting through data using the ASP.NET

Question

All replies