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

  • Question

  • User-1499457942 posted

    Hi

      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