Solution to Simple Paging Logic. ASP.NET MVC3

Question

I would like to add simple paging procedure in my project. Though far I've created a Stored Procedure as:

CREATE PROCEDURE getStudent_Paging
(   
    @PageIndex INT,
    @PageSize INT,  
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @minIndex int,
        @maxIndex int

SET @minIndex = (@PageIndex * @PageSize) + 1;
SET @maxIndex = (@PageIndex +1) * @PageSize;

SELECT ROW_NUMBER() OVER (ORDER BY ID ASC)AS RowNumber, * INTO #Results
FROM dbStudent;

SELECT @TotalRows  = COUNT(*) FROM #Results;

SELECT * FROM #Results WHERE RowNumber BETWEEN' + 
@minIndex + 'AND' + @maxIndex + ';  

DROP TABLE #Results

END

Ive also shown first 5 records. But what should be done in Navigation buttons to navigate to another page.

My controller logic:

public ActionResult Index()
{
    DatabaseConnString db = new DatabaseConnString();
    SqlConnection conn = db.ConnectDB();

    List<StudentClass> list = new List<StudentClass>();

    SqlCommand myCommand = new SqlCommand("getStudent_Paging", conn); 
    myCommand.CommandType = CommandType.StoredProcedure;    
    myCommand.Parameters.AddWithValue("@PageIndex", pageIndex);    
    myCommand.Parameters.AddWithValue("@PageSize", pageSize);    

    SqlDataReader reader = myCommand.ExecuteReader();    
    while (reader.Read())
    {
        int ID = Int32.Parse(reader["ID"].ToString());
        string Name = reader["Name"].ToString();
        string Address = reader["Address"].ToString();
        string PhoneNumber = reader["PhoneNumber"].ToString();

        StudentClass model = new StudentClass
        {
            ID = ID,
            Name = Name,
            Address = Address,
            PhoneNumber = PhoneNumber
        };

        list.Add(model);
    }

    return View(list);
}

You could try keeping a list of records on the page, and when the user clicks next, making another call out to the stored procedure and appending the results to the list. — Zach, Dec 12 '13 at 20:39

Dmitry Sikorsky · Answer 1 · 2013-12-12T21:26:25.220

0

Create your base page (for example Index.cshtml) with placeholder for table data:

<div id="tableData"></div>

Create controller action GetData(int skip, int take) to your controller with url /items. It should return partial view like this:

@foreach (var item in this.Model.rows)
{
    <div>@item.property</div>
}
<div onclick="loadPage(@Model.pageIndex)">Next page</div>

After Index.cshtml loads call loadPage(0) to load the first page into placeholder #tableData. Click 'Next page' item to load the next page. 'loadPage' javascript function may look like:

function loadPage(pageIndex) {
    $("#tableData").load("/items?skip=" + (pageIndex * 10) + "&take=10");
}

If you are familiar with javascript then you of course should not add 'Next page' item to partial view and update it every time via http but just modify on the client side. But I think it is enough info for starting point. Good luck!

edited Dec 12 '13 at 21:26

answered Dec 12 '13 at 21:12

Dmitry Sikorsky

1,374
14
23

it helped but it is not my answer. i've finished it. but i would like to insert sorting method as well so if you could help me there it would be appreciated – user3064407 Dec 15 '13 at 10:36
just add another one parameter to controller's action. like 'orderBy'. it's very simple. – Dmitry Sikorsky Dec 15 '13 at 14:52
i know its simple like addind one parameter but i dont know what should be done so could please post the whole code if you can.. – user3064407 Dec 16 '13 at 05:09

Solution to Simple Paging Logic. ASP.NET MVC3

1 Answers1