locked
Paginate Search Results or Infinite Scrolling RRS feed

  • Question

  • User-371148474 posted

    I read Mikes article on how to page results efficiently without using the webgrid. It was really helpful, 

    I was able to paginate my Default.cshtml because it just retrieves all items from the Product table. I have been trying to implement same pagination script for Search results, I think the algorithm is tweeky.

    Find below two code blocks, one for my Default.cshtml and the other for my search result.

    var pageSize = 8; 
        var totalPages = 0; 
        var count = 0;
        var page = UrlData[0].AsInt(1);
        var offset = ((page -1)* pageSize)+1;
        var offset1 = (page)* pageSize;
        
        var db = Database.Open("hon05");
        var sql = "Select Count(*) From Products"; 
        count = (int)db.QueryValue(sql);
        totalPages = count/pageSize;        
        if(count % pageSize > 0){
            totalPages += 1;
        }
        sql = @"
            Select * From(Select *,ROW_NUMBER() OVER (ORDER BY productID DESC) AS Home From Products
            )AS Num
            WHERE Home BETWEEN @0 AND @1  ORDER BY productID DESC";
        var product = db.Query(sql, offset, offset1);

    The above code works well for my Default page


    The next code block is for the Search.cshtml, it returns only exact results instead of similar results with search input, two it doesnt paginate itself


        var sqlSelect = "Select Count(*) From Products"; 
     var searchWords = "%" + Request["searchTerm"] + "%";
        count = (int)db.QueryValue(sqlSelect);
        totalPages = count/pageSize;        
        if(count % pageSize > 0){
            totalPages += 1;
        }
     
         sqlSelect = @"
            Select * From(Select *,ROW_NUMBER() OVER (ORDER BY productID DESC) AS Home From Products
            )AS Num WHERE Home BETWEEN @0 AND @1 AND Name LIKE '%'+@2+'%' OR Author LIKE '%'+@2+'%' OR ISBN LIKE '%'+@2+'%' ORDER BY productID DESC";
        var product = db.Query(sqlSelect, offset, offset1, searchWords);
    Sunday, June 29, 2014 4:59 AM

Answers

  • User895691971 posted

    First you need to create the basic logic behind those things. 

    Pagination 

    This feature enables you to create different (virtual) pages for the result that you're showing to the user. And then you use the link or the page code to show the results that should/would be in that page. Its basically required to Split the pages into different segments and show them in the page, if they exceed. Actually this feature is used to minimize the Network traffic amount. It would cut down the resources needed to load the page, and user won't get irritated instead he would be glad, that his search query met 10 pages.

    The basic Algorithm behind that is, to chop down the query result to a small amount of the results only and shown them. Something like this

    var results = db.Query("SELECT TOP 10 * FROM table_name WHERE column_name = @0", valueToCheck);

    Once this is returned only the TOP 10 results would be shown to the user. You can further count the total results too and create a set of 10 results on each page. Showing the page links like, page 2, page 3 and so on.

    Infinite Scroll 

    Actually, this feature is the same as the Pagination, as it also gets the result for the user query. But the only difference is, that here the event that would trigger command to get next results wouldn't be the Load_Next_Page, instead it would be the Page Scroll, it would be used. Once the user is at the end of the page. He would be able to see the next results.

    I used to code just like you, by just writing the values and writing the hardcoded format of the ASP.NET Web Pages code and then getting stuck at a point, going to a friend or coming to this ASP.NET Forum to get help in my Logic which was a good thing but a time consuming one too. I would advise you as a Beginner in ASP.NET, to always create the logic as an algorithm, or a pseudo-code in simple English (if you're native English speaker) or any other language (if you've a language apart from English). That would really help you, while coding as it would tell you what you were to write and what you wrote and what you would have written there. 

    I think, Pagination is better method. Why?

    Some of the users still use old P4 computer systems or dual cores, which are not fast and stable systems. If you load ~15th pages or next to this value page the user's system would have to do alot of work to process it. I had to face this problem on "Tumblr." sites and sometimes on "Facebook" too, now I have i5 so I don't face it. But I still keep in mind, that there would be some other Afzaal somewhere, who might get irritated by my Infinite scroll feature as it would make his Browser crash. You should pay attention to UI and UX more than the site features. 

    Good luck!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 30, 2014 10:26 AM

All replies

  • User-352524747 posted

    For infinite scrolling you mean something like this: http://forums.asp.net/post/5335041.aspx

    Monday, June 30, 2014 2:53 AM
  • User-1454326058 posted

    Hi hardweezy,

    For scrolling, please refer to dow7’s reply.

    To paging the data, you should add the addition conditions in the inner SQL statement.

     

    sqlSelect = @"
            Select * From(Select *,ROW_NUMBER() OVER (ORDER BY productID DESC) AS Home From Products
            where Name LIKE '%'+@2+'%' OR Author LIKE '%'+@2+'%' OR ISBN LIKE '%'+@2+'%')AS Num WHERE Home BETWEEN @0 AND @1";
        var product = db.Query(sqlSelect, offset, offset1, searchWords);

    Thanks

    Best Regards

    Monday, June 30, 2014 4:27 AM
  • User895691971 posted

    First you need to create the basic logic behind those things. 

    Pagination 

    This feature enables you to create different (virtual) pages for the result that you're showing to the user. And then you use the link or the page code to show the results that should/would be in that page. Its basically required to Split the pages into different segments and show them in the page, if they exceed. Actually this feature is used to minimize the Network traffic amount. It would cut down the resources needed to load the page, and user won't get irritated instead he would be glad, that his search query met 10 pages.

    The basic Algorithm behind that is, to chop down the query result to a small amount of the results only and shown them. Something like this

    var results = db.Query("SELECT TOP 10 * FROM table_name WHERE column_name = @0", valueToCheck);

    Once this is returned only the TOP 10 results would be shown to the user. You can further count the total results too and create a set of 10 results on each page. Showing the page links like, page 2, page 3 and so on.

    Infinite Scroll 

    Actually, this feature is the same as the Pagination, as it also gets the result for the user query. But the only difference is, that here the event that would trigger command to get next results wouldn't be the Load_Next_Page, instead it would be the Page Scroll, it would be used. Once the user is at the end of the page. He would be able to see the next results.

    I used to code just like you, by just writing the values and writing the hardcoded format of the ASP.NET Web Pages code and then getting stuck at a point, going to a friend or coming to this ASP.NET Forum to get help in my Logic which was a good thing but a time consuming one too. I would advise you as a Beginner in ASP.NET, to always create the logic as an algorithm, or a pseudo-code in simple English (if you're native English speaker) or any other language (if you've a language apart from English). That would really help you, while coding as it would tell you what you were to write and what you wrote and what you would have written there. 

    I think, Pagination is better method. Why?

    Some of the users still use old P4 computer systems or dual cores, which are not fast and stable systems. If you load ~15th pages or next to this value page the user's system would have to do alot of work to process it. I had to face this problem on "Tumblr." sites and sometimes on "Facebook" too, now I have i5 so I don't face it. But I still keep in mind, that there would be some other Afzaal somewhere, who might get irritated by my Infinite scroll feature as it would make his Browser crash. You should pay attention to UI and UX more than the site features. 

    Good luck!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 30, 2014 10:26 AM