Pagination calculation like start index and end index in MVC

Question

_{Monday, January 4, 2016 2:06 PM}

this way i am calculation Pagination calculation like start index and end index. the below code is working

            if (oSVm.page == 0)
                oSVm.page = 1;

            StartIndex = ((oSVm.page * oSVm.PageSize) - oSVm.PageSize) + 1;
            EndIndex = (oSVm.page * oSVm.PageSize);
            CurrentPage = StartIndex;

i am showing 5 data at a time. so first time data look like

ID First Name Last Name

1 Arnab saha

2 Arun Sen

first time data is sorted on id column as ascending order and in table there is 50 records. when i click again on id column then data should look like

ID First Name Last Name

50 Wariya saha

49 Sukumar Sen

but in my case data looks like

ID First Name Last Name

5 Chanu saha

4 Biplab Sen

the issue 50 as a id should be displaying as first rec when sort id by descending order. i guess there is problem in my pagination calculation. so tell me what logic i should use to make it right. thanks

All replies (6)

_{Tuesday, January 5, 2016 2:47 AM}

Hi,sudip_inn

You could refer to the following code.

                /// <summary>
        /// Initializes a new instance of the <see cref="PagedList{T}"/> class that divides the supplied superset into subsets the size of the supplied pageSize. The instance then only containes the objects contained in the subset specified by index.
        /// </summary>
        /// <param name="superset">The collection of objects to be divided into subsets. If the collection implements <see cref="IQueryable{T}"/>, it will be treated as such.</param>
        /// <param name="pageNumber">The one-based index of the subset of objects to be contained by this instance.</param>
        /// <param name="pageSize">The maximum size of any individual subset.</param>
        /// <exception cref="ArgumentOutOfRangeException">The specified index cannot be less than zero.</exception>
        /// <exception cref="ArgumentOutOfRangeException">The specified page size cannot be less than one.</exception>
        public PagedList(IQueryable<T> superset, int pageNumber, int pageSize)
        {
            if (pageNumber < 1)
                throw new ArgumentOutOfRangeException("pageNumber", "PageNumber cannot be below 1.");
            if (pageSize < 1)
                throw new ArgumentOutOfRangeException("pageSize", "PageSize cannot be less than 1.");

            // set source to blank list if superset is null to prevent exceptions
            TotalItemCount = superset == null ? 0 : superset.Count();
            PageSize = pageSize;
            PageCount = TotalItemCount > 0
                        ? (int)Math.Ceiling(TotalItemCount / (double)PageSize)
                        : 0;
            PageNumber = (pageNumber == int.MaxValue) ? PageCount : pageNumber;
            HasPreviousPage = PageNumber > 1;
            HasNextPage = PageNumber < PageCount;
            IsFirstPage = PageNumber == 1;
            IsLastPage = PageNumber >= PageCount;
            FirstItemOnPage = (PageNumber - 1) * PageSize + 1;
            var numberOfLastItemOnPage = FirstItemOnPage + PageSize - 1;
            LastItemOnPage = numberOfLastItemOnPage > TotalItemCount
                            ? TotalItemCount
                            : numberOfLastItemOnPage;

            // add items to internal list
            if (superset != null && TotalItemCount > 0)
                Subset.AddRange(pageNumber == 1
                    ? superset.Take(pageSize).ToList()
                    : superset.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList()
                );
        }

**Sample

Or install PagedList.Mvc,Asp.Net MVC HtmlHelper method for generating paging control for use with PagedList library.

Best regards,

Chris Zhao

_{Tuesday, January 5, 2016 5:22 AM}

This issue may not be related to pagination. Are you using any grid control to present the information. Also are you doing server pagination?

For me this issue is related to sorting the entire data and not just the data shown on the current page. Please provide more details on how you are presenting the data to the user.

_{Tuesday, January 5, 2016 8:40 AM}

@pprasannak sure i will post my full code along with view model, model, repository, controller and view html code.

i am working with webgrid in mvc. thanks

_{Tuesday, January 5, 2016 8:41 AM}

@Chris Zhao i am not using EF rather using ado.net to fetch data from database.

_{Tuesday, January 5, 2016 9:03 AM}

Here is my full code

Base repository

public abstract class AdoRepository<T> where T : class
    {
        private SqlConnection _connection;
        public virtual void Status(bool IsError, string strErrMsg)
        {

        }

        public AdoRepository(string connectionString)
        {
            _connection = new SqlConnection(connectionString);
        }

        public virtual T PopulateRecord(SqlDataReader reader)
        {
            return null;
        }

        public virtual void GetDataCount(int count)
        {
           
        }

        protected IEnumerable<T> GetRecords(SqlCommand command)
        {
            var reader = (SqlDataReader) null;
            var list = new List<T>();
            try
            {
                command.Connection = _connection;
                _connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(PopulateRecord(reader));
                }

                reader.NextResult();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        GetDataCount(Convert.ToInt32(reader["Count"].ToString()));
                    }
                }
                Status(false, "");
            }
            catch (Exception ex)
            {
                Status(true, ex.Message);
            }
            finally
            {
                // Always call Close when done reading.
                reader.Close();
                _connection.Close();
                _connection.Dispose();
            }

            return list;
        }

        protected T GetRecord(SqlCommand command)
        {
            var reader = (SqlDataReader)null;
            T record = null;

            try
            {
                command.Connection = _connection;
                _connection.Open();

                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    record = PopulateRecord(reader);
                    Status(false, "");
                    break;
                }
            }
            catch (Exception ex)
            {
                Status(true, ex.Message);
            }
            finally
            {
                reader.Close();
                _connection.Close();
                _connection.Dispose();
            }
            return record;
        }

        protected IEnumerable<T> ExecuteStoredProc(SqlCommand command)
        {
            var reader = (SqlDataReader)null;
            var list = new List<T>();
            try
            {
                command.Connection = _connection;
                command.CommandType = CommandType.StoredProcedure;
                _connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    var record = PopulateRecord(reader);
                    if (record != null) list.Add(record);
                }
            }
            finally
            {
                // Always call Close when done reading.
                reader.Close();
                _connection.Close();
                _connection.Dispose();
            }
            return list;
        }
    }

StudentRepository which extend base AdoRepository

    public class StudentRepository : AdoRepository<Student>
        {
            public int DataCounter { get; set; }
            public bool hasError { get; set; }
            public string ErrorMessage { get; set; }
    
            public StudentRepository(string connectionString)
                : base(connectionString)
            {
            }
    
            public IEnumerable<Student> GetAll()
            {
                // DBAs across the country are having strokes
                //  over this next command!
                using (var command = new SqlCommand("SELECT ID, FirstName,LastName,IsActive,StateName,CityName FROM vwListStudents"))
                {
                    return GetRecords(command);
                }
            }
            public Student GetById(string id)
            {
                // PARAMETERIZED QUERIES!
                using (var command = new SqlCommand("SELECT ID, FirstName,LastName,IsActive,StateName,CityName FROM vwListStudents WHERE Id = @id"))
                {
                    command.Parameters.Add(new ObjectParameter("id", id));
                    return GetRecord(command);
                }
            }
    
            public IEnumerable<Student> GetStudents(int StartIndex, int EndIndex, string sortCol, string sortOrder)
            {
                string strSQL = "SELECT * FROM vwListStudents WHERE ID >=" + StartIndex + " AND ID <=" + EndIndex;
                strSQL += " ORDER BY " + sortCol + " " + sortOrder;
                strSQL += ";SELECT COUNT(*) AS Count FROM vwListStudents";
                var command = new SqlCommand(strSQL);
                return GetRecords(command);
            }
    
            public override Student PopulateRecord(SqlDataReader reader)
            {
                return new Student
                {
                    ID = Convert.ToInt32(reader["ID"].ToString()),
                    FirstName = reader["FirstName"].ToString(),
                    LastName = reader["LastName"].ToString(),
                    IsActive = Convert.ToBoolean(reader["IsActive"]),
                    StateID = Convert.ToInt32(reader["StateID"].ToString()),
                    StateName = reader["StateName"].ToString(),
                    CityID = Convert.ToInt32(reader["CityID"].ToString()),
                    CityName = reader["CityName"].ToString()
                };
            }
    
            public override void GetDataCount(int count)
            {
                DataCounter = count;
            }
    
            public override void Status(bool IsError, string strErrMsg)
            {
                hasError = IsError;
                ErrorMessage = strErrMsg;
            }
    }

calling StudentRepository from mvc controller like below way

 public class StudentController : Controller
    {
        private StudentRepository _data;

        public StudentController()
        {
            _data = new StudentRepository(System.Configuration.ConfigurationManager.ConnectionStrings["StudentDBContext"].ConnectionString);
        }

        // GET: Stuent
        public ActionResult List(StudentListViewModel oSVm)
        {
            
            StudentListViewModel SVm = new StudentListViewModel();
            SVm.SetUpParams(oSVm);
            SVm.Students = _data.GetStudents(SVm.StartIndex, SVm.EndIndex, SVm.sort, oSVm.sortdir).ToList();
            SVm.RowCount = _data.DataCounter;

            return View("ListStudents",SVm);
        }
    }

Now my view code

@grid.GetHtml(
        tableStyle: "webgrid-table",
        headerStyle: "webgrid-header",
        footerStyle: "webgrid-footer",
        alternatingRowStyle: "webgrid-alternating-row",
        selectedRowStyle: "webgrid-selected-row",
        rowStyle: "webgrid-row-style",
        mode: WebGridPagerModes.All,
        firstText: "<<",
        previousText: "<",
        nextText: ">",
        lastText: ">>",
        numericLinksCount: 5,
        columns:
            grid.Columns
            (
            grid.Column(columnName: "ID", header: "ID", format: @<text>@item.ID <input type="hidden" name="HiddenID" value="@item.ID" id="HiddenID"  /></text>,style:"SmallCols"),
            grid.Column(columnName: "FirstName", header: "First Name", format: @<text><span class="display-mode">@item.FirstName</span><input type="text" id="txtFirstName" value="@item.FirstName" class="edit-mode" /></text>,style:"NameColWidth" ),
            grid.Column(columnName: "LastName", header: "Last Name", format: @<text><span class="display-mode">@item.LastName</span><input type="text" id="txtLastName" value="@item.LastName" class="edit-mode" /></text>,style:"NameColWidth"),

            grid.Column(columnName: "StateName", header: "State Name", format: @<text><input type="hidden" name="HiddenID" value="@item.StateID" id="HiddenStateID" /><span class="display-mode">@item.StateName</span>@Html.DropDownListFor(x => x.SelectedStateId, new SelectList(Model.States, "ID", "Name", item.StateID), "-- Select States--", new { id = "cboState", @class = "edit-mode" })</text>,style:"NameColWidth"),
            grid.Column(columnName: "CityName", header: "City Name", format: @<text><input type="hidden" name="HiddenID" value="@item.CityID" id="HiddenCityID" /><span class="display-mode">@item.CityName</span>@Html.DropDownListFor(x => x.SelectedCityId, new SelectList(Model.Cities, "ID", "Name", item.CityID), "-- Select City--", new { id = "cboCity", @class = "edit-mode" })
</text>,style:"NameColWidth"),
            grid.Column(header: "IsActive",
            format: @<text><input id="select" class="box" name="select"
                                  type="checkbox" @(item.IsActive ? "checked='checked'" : "") value="@item.IsActive" /></text>
                    , style: "text-center checkbox-width SmallCols"),

             grid.Column("Action", format: @<text>
                        <button class="edit-user display-mode btnGreen">Edit</button>
                        <button class="edit-user display-mode btnRed">Delete</button>
                        <button class="save-user edit-mode btnGreen">Save</button>
                        <button class="cancel-user edit-mode btnSky">Cancel</button>
            </text>, style: "ActionCol", canSort: false)

                        ))

Student view model code

public class StudentListViewModel
{
    public int StartIndex { get; set; }
    public int EndIndex { get; set; }
    public int page { get; set; }
    public int RowCount { get; set; }
    public int PageSize { get; set; }
    public int CurrentPage { get; set; }

    public string sort { get; set; }
    public string sortdir { get; set; }

    public IList<Student> Students { get; set; }

    public int SelectedStateId { set; get; }
    public IList<State> States { get; set; }

    public int SelectedCityId { set; get; }
    public IList<City> Cities { get; set; }

    public StudentListViewModel()
    {
        PageSize = 5;
        sort = "ID";
        sortdir = "ASC";
        CurrentPage = 1;
    }

    public void SetUpParams(StudentListViewModel oSVm)
    {
        if (oSVm.page == 0)
            oSVm.page = 1;

        StartIndex = ((oSVm.page * oSVm.PageSize) - oSVm.PageSize) + 1;
        EndIndex = (oSVm.page * oSVm.PageSize);
        CurrentPage = StartIndex;

        if (string.IsNullOrEmpty(oSVm.sort))
            oSVm.sort = "ID";

        if (string.IsNullOrEmpty(oSVm.sortdir))
            oSVm.sortdir = "ASC";
    }
}

Model code

public class Student
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public bool IsActive { get; set; }
    public int StateID { get; set; }
    public string StateName { get; set; }
    public int CityID { get; set; }
    public string CityName { get; set; }
}

public class State
{
    public int ID { get; set; }
    public string Name { get; set; }
}

public class City
{
    public int ID { get; set; }
    public string Name { get; set; }
}

please see my code and come with your suggestion for calculating pagination and data sorting logic. you can post rectified area too here if you think any area need to change. thanks

_{Tuesday, January 5, 2016 11:41 AM}

try below code

public ActionResult Index(string sortOrder, string CurrentSort, int? page)
{
    int pageSize = 10;
    int pageIndex = 1;
    pageIndex = page.HasValue ? Convert.ToInt32(page) : 1;
            
    ViewBag.CurrentSort = sortOrder;

    sortOrder = String.IsNullOrEmpty(sortOrder) ? "ProductID" : sortOrder;

    IPagedList<Product> products = null;
            
    switch (sortOrder)
    {
        case "ProductID":
            if(sortOrder.Equals(CurrentSort))  
                products = _db.Products.OrderByDescending
                        (m => m.ProductID).ToPagedList(pageIndex, pageSize);   
            else
                products = _db.Products.OrderBy
                        (m => m.ProductID).ToPagedList(pageIndex, pageSize);   
            break;
        case "ProductName":
            if (sortOrder.Equals(CurrentSort))  
                products = _db.Products.OrderByDescending
                        (m => m.ProductName).ToPagedList(pageIndex, pageSize);
            else
                products = _db.Products.OrderBy
                        (m => m.ProductName).ToPagedList(pageIndex, pageSize);
            break;
                
        // Add sorting statements for other columns
                
        case "Default":
            products = _db.Products.OrderBy
                    (m => m.ProductID).ToPagedList(pageIndex, pageSize);
            break; 
    }
    return View(products);
    }
}

for more details paging and sorting in mvc

Last updated on 2016-01-04

Share via

Pagination calculation like start index and end index in MVC

Question

All replies (6)

Additional resources