How should i display first 10 records at a time in sql (paging in SQL)?


How should i display 1st 10 records, then next 10 records then next 10 records in sql (paging in SQL)? 

And how should i display the same thing in view using see more link?


Asked by:- SnehalSawant
0
: 391 At:- 5/7/2018 5:58:10 AM
Sql Mvc paging in sql server






3 Answers
profileImage Answered by:- vikas_jk

To fetch rows in Sql server(10/5 rows at a time) to apply pagination you can use OFFSET and FETCH (For SQL server 2012 and above), here is the example, I am using AdventureWorks2012 Database

Fetch First 10 rows of Table Person

USE AdventureWorks2012
GO
SELECT 
  BusinessEntityID
  ,PersonType
 ,FirstName + ' ' + MiddleName + ' ' + LastName 
FROM Person.Person
 ORDER BY BusinessEntityID ASC
  OFFSET 0 ROWS 
  FETCH NEXT 10 ROWS ONLY
GO

Here is the output image

paging-in-sql-server-min.png

Now, if we want to get next page (next 10 rows), you can give OffSet value=10 to start fetching rows from 11th and use Fetch Next 10 to get another 10 rows, here is the query.

USE AdventureWorks2012
GO
SELECT 
  BusinessEntityID
  ,PersonType
 ,FirstName + ' ' + MiddleName + ' ' + LastName 
FROM Person.Person
 ORDER BY BusinessEntityID ASC
  OFFSET 10 ROWS 
  FETCH NEXT 10 ROWS ONLY
GO

Output:

paging-in-sql-server-using-fetch-offset-min.png

You can do this again and again to fetch next 10 rows and so on.

You can create the Stored procedure to use the same above query, here is the stored procedure query code for same

USE AdventureWorks2012
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = 
OBJECT_ID(N'[dbo].[ExampleUsageOfSQLServerPagingFeature]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ExampleUsageOfSQLServerPagingFeature]
GO
CREATE PROCEDURE ExampleUsageOfSQLServerPagingFeature
 (
  @PageNo INT,
 @RowCountPerPage INT
 )
AS
SELECT
  BusinessEntityID
 ,PersonType
 ,FirstName + ' ' + MiddleName + ' ' + LastName 
FROM Person.Person
 ORDER BY BusinessEntityID
  OFFSET (@PageNo - 1) * @RowCountPerPage ROWS
  FETCH NEXT @RowCountPerPage ROWS ONLY
GO

Simply execute the above created stored procedure by passing page number and row count to show

/* Display Records Between 11 AND 20 BusinessEntityID */
EXECUTE ExampleUsageOfSQLServerPagingFeature 2, 10
GO

Output:

output-stored-procedure-paging-sql-server-min.png

For showing this in view, you can just call the stored procedure and get the list of next 10 rows based on page number and show data in view

Refer this article https://qawithexperts.com/article/asp.net/paging-sorting-and-filtering-in-aspnet-mvc-c/31

for more details on how to implement paging in MVC View.

 

1
At:- 5/7/2018 7:28:54 AM Updated at:- 5/7/2018 7:34:00 AM
How should i display it in view? How should i pass sp parameters to controller? 0
By : SnehalSawant - at :- 5/7/2018 10:14:25 AM
How many pages should i show in view and how? 0
By : SnehalSawant - at :- 5/7/2018 12:26:14 PM
How many pages? I think you can count the total number of row, please check my another answer to know how to show data in view using SP & EF 0
By : vikas_jk - at :- 5/7/2018 12:32:41 PM
If i have more than 10000 records, how can i manage next, previous button for this? 0
By : SnehalSawant - at :- 5/8/2018 5:12:57 AM
Use IPagedList.MVC or create your own custom buttons to show only 10 buttons at a time check this https://bootsnipp.com/snippets/oV91g. if this doesn't work for you, you can just a load more button in the bottom of the page and append new data by ajax call to table body. 0
By : neena - at :- 5/8/2018 7:34:33 AM


profileImage Answered by:- vikas_jk

 This is quite basics to call stored procedures using controller C#, if you want to directly call the SP, I have already mentioned it in the above answer you can use the this query

EXECUTE ExampleUsageOfSQLServerPagingFeature 2, 10

Now, Suppose you are using Entity framework(database first approach in my example) in your controller, code can be as below

 public ActionResult ListToShow(int? page)
        {
            var Model = new List<ExampleUsageOfSQLServerPagingFeature_Result>();
            int pageNumber = (page ?? 1);
           
          
            using (var context = new AdventureWorks2012Entities1())
            {
                //show results 10 per page
                var CountToShowPerPage = 10;
                //Execute stored procedure as a function
                Model = context.ExampleUsageOfSQLServerPagingFeature(pageNumber, CountToShowPerPage).ToList();

                //Get Total numbers for row count, for example this example let's say it is 100
                ViewBag.TotalCount = 100;

                //Save Current page number
                ViewBag.CurrentPage = pageNumber;
            }
           return View(Model);
        }

In your View, get the list and display it in table (I am using Custom paging, there are several other options Like IPagesList.MVC for which I have already asked you to refer https://qawithexperts.com/article/asp.net/paging-sorting-and-filtering-in-aspnet-mvc-c/31)

Razor Code

@model IEnumerable<PagingWithStoredProcedure.Models.ExampleUsageOfSQLServerPagingFeature_Result>
@{
    ViewBag.Title = "List To Show";
}

<h2>List To Show</h2>

<table class="table table-condensed">
    <thead>
        <tr>
            <th> Id</th>
            <th>Type</th>
            <th>Full Name</th>
        </tr>

    </thead>
    <tbody id="tBody">
        @foreach (var row in Model)
        {
            <tr>
                <td>
                    @row.BusinessEntityID
                </td>
                <td>
                    @row.PersonType
                </td>
                <td>
                    @row.FullName
                </td>
            </tr>
        }
    </tbody>
</table>
<ul class="pagination">
    @for (var i = 1; i < (ViewBag.TotalCount/10); i++)
    {
        //if current page add active class
        if (i == ViewBag.CurrentPage)
        {
            <li class="active"><a href="javascript:void(0)">@i</a></li>
        }
        else
        {
            <li><a href="/Home/ListToShow?page=@i">@i</a></li>
        }


    }
</ul> 

Now, after testing this locally, in my PC, here is the output

Remember, you would have to change code according to your need.

0
At:- 5/7/2018 12:30:24 PM Updated at:- 5/7/2018 1:05:59 PM


profileImage Answered by:- jaya

In this article https://qawithexperts.com/article/asp-net/bind-data-to-gridview-using-jquery-ajax-webmethod-in-aspnet/125 which is related to Bind Data to Gridview using jQuery Ajax & WebMethod in ASP.NET, above SQL stored procedure paging is explained with custom javascript to render pages in View, you can use this example for reference also, to understand how to implement paging in SQL server stored procedure.

0
At:- 5/16/2018 12:21:29 PM





Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use