Only show related data in list table

Question

0.00/5 (No votes)

See more:

i am using table that use auto paging, when i get data from database it show all record from DB and show in table using list. If record are 10,000 it take time. I want to use such trick that not show all record it just show 10 record and when click on page two it get 11-20 record from database.

How can it possible, and this technique is best or not

Can i use store procedure for this purpose

What I have tried:

function loadData() {
       var Year = $("#year").val();
       var Month = $("#month").val();
       var DataObjectForAjaxCall = { strYear: Year, strMonth: Month };
       if (Year != "" && Month != "") {
           $("#modalCenter").click();
           $.ajax({
               url: "@Url.Action("loadData", "Home")",
               method: "GET",
               data: DataObjectForAjaxCall,
               async: false,
               success: function (Result) {
                   $('.modal-body').html(Result);
               }
           });
           $("#hideLoader").click();
       } else {
           Alerts("Select Year and Month First");
       }

   }

Posted 4-Jul-17 23:15pm

Member 10028394

Updated 5-Jul-17 0:13am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Answer 1 · 2017-07-05T00:13:00

A stored procedure is perfect for this.

The technique you're looking for is called "Paging".

You will have to extend your DataObjectForAjaxCall object to include page number and maybe a page size (but you might want to assume 10).

JavaScript

function loadData(pageNum) {

       if(pageNum == undefined)
          pageNum = 1;

       var Year = $("#year").val();
       var Month = $("#month").val();
       var DataObjectForAjaxCall = { strYear: Year, strMonth: Month, iPageNum: pageNum, iPageSize: 10 };
// ...

change the loadData action to use these new parameters to call the procedure.

In the procedure, you will need an index column:

SQL

SELECT *,
          ROW_NUMBER() OVER (ORDER BY @sort DESC) AS [Index]
      FROM myResults

Index will number the rows from 1 to n. Use this in the WHERE clause:

SQL

WHERE [Index] BETWEEN (@pageNum-1)*@pageSize AND (@pageNum)*@pageSize

There are loads of examples of Paging online. here's a simple example:
sql - Pagination with the stored procedure - Stack Overflow[^]