November 15, 2014 0:28 AM #1
ModeratorModerator

Posts: 47
Points: 405
Joined: January 2010
Pagination for older SQL Server.

ASP.NET Video Kit and all sub products by default support SQL SERVER 2012 or later versions. If you are using older version of SQL SERVER e.g SQL SERVER 2008, you need to adjust pagination script.

i: Open models/utility/pagination_process.cs file
adjust code in function Prepare_SQLSERVER2005_Pagination

public static string Prepare_SQLSERVER2005_Pagination(string query, string order,int pagenumber, int pagesize, string distinct = "")
{
      // SQL SERVER 2012 OR NEWER
      int OFFSET = (pagenumber - 1) * pagesize;
      return "SELECT " + distinct + " " + query + " ORDER BY " + order + " OFFSET " + OFFSET + " ROWS FETCH NEXT " + pagesize + " ROWS ONLY";
      // SQL SERVER 2012 or LOWER
      // fetch and return only batch of data starting from star posting to lastbound
      // int startindex = (pagenumber - 1) * pagesize + 1;
      // int lastbound = (startindex - 1) + pagesize;
      // return "SELECT" + distinct + " TOP " + pagesize + " * FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + order + ") AS RowNumber," + query + ") 
      AS Results WHERE RowNumber >=" + startindex + " AND RowNumber <=" + lastbound;
}