元哥的筆記

問題：筆者工作上遇到部分系統去呼叫API資料直接撈一整批回來，再透過AP去處理分頁，造成效能問題，在此先記錄一下資料分頁寫法，做為系統調整優化和系統改造備案。

會記錄這一篇還有一個很大目的，筆者最近開始進行著手，系統移轉工程，進行梳理需求和盤整遺留系統邏輯，發現資料表沒有訂P-KEY，筆者後來知道系統的歷史來源，以20～30年前的系統，關聯資料庫沒那麼普及與強大，以前開發的人是從檔案結構移轉到資料庫，沒有這概念。

採用Dapper原因，作為備案原因如下：

1. 因為Entity Framework遇到早期系統Table沒有定義PKEY，天曉得你怎知道老系統裡面有沒有其它的地方在調用。
2. 想繼續沿用舊的資料表，在外部來說開始進行改造，畢竟裡面已經有歷史的資料，運行已久。
3. 像遇到Entity Framework Edmx的時候，萬一別的系統追加欄位，你還要同步更新，是一件相當麻煩的事情。

最後筆者發現到關於網路上的分頁Dapper寫法並不是我要的解決方案，外加只介紹SQL的分頁，於是把SQL分頁跟API和MVC的方式結合起來，寫這一篇做紀錄。

  

分頁邏輯算法

Page 1 = ((1-1) *  4 + 1)  AND (1 * 4) = 0  AND 4
Page 2 = ((2-1) *  4 + 1)  AND (2 * 4) = 5  AND 8
Page 3 = ((3-1) *  4 + 1)  AND (3 * 4) = 9  AND 12
 

--TSQL寫法
Declare @PageNo INT
Declare @PageSize INT
Set @PageNo=1
Set @PageSize=4

--寫法一
Select * From (Select ROW_NUMBER() Over (Order by ProductId Desc) AS 'RowNum',* 
From Products
)t Where t.RowNum Between
((@PageNo-1)*@PageSize +1) AND (@PageNo*@PageSize)


--寫法二
Select * From Products
Order By ProductID  
Offset @PageSize * (@PageNo - 1) Rows
Fetch Next @PageSize Rows Only

FETCH 寫法效能比 ROW_NUMBER() 快很多

底下是自己一個sample Demo來做參考，以便日後應付這樣舊系統改寫改善處理。

API Model部分

    public class Customer
    {
        public string CustomerID { get; set; }

        public string CompanyName { get; set; }

        public string ContactName { get; set; }

        public string ContactTitle { get; set; }
    }

定義PageResult

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace ApiDemo.Models
{
    /// <summary>
    /// Helper class for returning paged results
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class PagedResults<T>
    {
        public PagedResults(int totalItems,
            int pageNumber = 1,
            int pageSize = 10,
            int maxNavigationPages = 5)
        {
            // Calculate total pages
            var totalPages = (int)Math.Ceiling(totalItems / (decimal)pageSize);

            // Ensure actual page isn't out of range
            if (pageNumber < 1)
            {
                pageNumber = 1;
            }
            else if (pageNumber > totalPages)
            {
                pageNumber = totalPages;
            }

            int startPage;
            int endPage;
            if (totalPages <= maxNavigationPages)
            {
                startPage = 1;
                endPage = totalPages;
            }
            else
            {
                var maxPagesBeforeActualPage = (int)Math.Floor(maxNavigationPages / (decimal)2);
                var maxPagesAfterActualPage = (int)Math.Ceiling(maxNavigationPages / (decimal)2) - 1;
                if (pageNumber <= maxPagesBeforeActualPage)
                {
                    // Page at the start
                    startPage = 1;
                    endPage = maxNavigationPages;
                }
                else if (pageNumber + maxPagesAfterActualPage >= totalPages)
                {
                    // Page at the end
                    startPage = totalPages - maxNavigationPages + 1;
                    endPage = totalPages;
                }
                else
                {
                    // Page in the middle
                    startPage = pageNumber - maxPagesBeforeActualPage;
                    endPage = pageNumber + maxPagesAfterActualPage;
                }
            }

            // Create list of Page numbers
            var pageNumbers = Enumerable.Range(startPage, (endPage + 1) - startPage).ToList();

            StartPage = startPage;
            EndPage = endPage;
            PageNumber = pageNumber;
            PageNumbers = pageNumbers;
            PageSize = pageSize;
            TotalItems = totalItems;
            TotalPages = totalPages;
        }

        //public IEnumerable<T> Items { get; set; }
        public List<T> Items { get; set; }
        /// <summary>
        /// Total number of items to be paged
        /// </summary>
        public int TotalItems { get; set; }

        /// <summary>
        /// Maximum number of page navigation links to display, default is 5
        /// </summary>
        public int MaxNavigationPages { get;  set; } = 5;

        /// <summary>
        /// Current active page
        /// </summary>
        public int PageNumber { get;  set; } = 1;

        /// <summary>
        /// Number of items per page, default is 10
        /// </summary>
        public int PageSize { get;  set; } = 10;

        public int TotalPages { get;  set; }

        /// <summary>
        /// Start Page number
        /// </summary>        
        public int StartPage { get;  set; }

        /// <summary>
        /// End Page number
        /// </summary>        
        public int EndPage { get;  set; }

        /// <summary>
        /// List of page numbers that we can loop
        /// </summary>
        public List<int> PageNumbers { get;  set; }
    }
}

新增一個專案API簡單寫一下撈資料邏輯，以北風資料庫為例在API的Repository Folder底下新增CustomerRepository

    public class CustomerRepository
    {
        public PagedResults<Customer> GetCustomers(string searchString = "", int pageNumber = 1, int pageSize = 10)
        {
            using (var conn = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Trusted_Connection=True;"))
            {
                conn.Open();
                // Set first query
                //var whereStatement = string.IsNullOrWhiteSpace(searchString) ? "" : $"WHERE [FirstName] LIKE '{searchString}'";
                //第一種寫法
                var queries = " SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CustomerID DESC) AS 'RowNum',* ";
                queries += " FROM Customers";
                queries += " )t WHERE t.RowNum BETWEEN		";
                queries += " ((@PageNumber-1)*@PageSize +1) AND (@PageNumber*@PageSize) 		";	
                //第二種寫法
                //var queries = "SELECT  * FROM [dbo].[Customers] (NOLOCK) ";
                //queries += whereStatement;
                //queries += "ORDER BY [CustomerID]  OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;";

                // Set second query, separated with semi-colon
                queries += "SELECT COUNT(*) AS TotalItems FROM [dbo].[Customers] (NOLOCK);";
               
               
                var multi = conn.QueryMultiple(queries,
                    new
                    {
                        PageNumber = pageNumber,
                        PageSize = pageSize
                    });
                
                var items = multi.Read<Customer>().ToList();
                var totalItems =  multi.ReadFirst<int>();
                var result = new PagedResults<Customer>(totalItems, pageNumber, pageSize)
                {
                    Items = items
                };
                return result;
            }
        }
    }

設定API Controller部分

using ApiDemo.Models;
using ApiDemo.Repository;

namespace ApiDemo.Controllers
{
    public class CustomerController : ApiController
    {
        public PagedResults<Customer> Get() 
        {
            return new CustomerRepository().GetCustomers();
        }
        public PagedResults<Customer> Get(int page) 
        {
            return new CustomerRepository().GetCustomers("",page);
        }
    }
}

設定一下回傳JSON

        public static void Register(HttpConfiguration config)
        {
            // Web API 設定和服務

            // Web API 路由
            config.MapHttpAttributeRoutes();

            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );
             
            var appXmlType = config.Formatters.XmlFormatter.SupportedMediaTypes.FirstOrDefault(t => t.MediaType == "application/xml");
            config.Formatters.XmlFormatter.SupportedMediaTypes.Remove(appXmlType);
        }

接者輪到MVC這一塊去呼叫API。

using ApiDemo.Models;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Web;
using System.Web.Mvc;

namespace WebDemo.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index(int pageNumber = 1)
        {
            HttpClient clinet = new HttpClient();
            clinet.BaseAddress = new Uri("http://localhost:52770/");
            clinet.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
            HttpResponseMessage response = clinet.GetAsync("api/Customer?page=" + pageNumber).Result;
            var pagedResults = new PagedResults<Customer>(0);
            if (response.IsSuccessStatusCode)
            {
                var body = response.Content.ReadAsStringAsync().Result;
                pagedResults = JsonConvert.DeserializeObject<PagedResults<Customer>>(body);
            }
          
            return View(pagedResults);
        }

    }
}

接者加入檢視

@using ApiDemo.Models
@model ApiDemo.Models.PagedResults<Customer>
@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<table class="table table-sm table-striped table-bordered">
    <thead>
        <tr>
            <th scope="col">CustomerID</th>
            <th scope="col">CompanyName</th>
            <th scope="col">ContactName</th>
            <th scope="col">ContactTitle</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var customer in Model.Items)
        {
            <tr>
                <th scope="row">@customer.CustomerID</th>
                <td>@customer.CompanyName</td>
                <td>@customer.ContactName</td>
                <td>@customer.ContactTitle</td>
            </tr>
        }
    </tbody>
</table>
@if (Model.PageNumbers.Any())
{
    <nav class="table-responsive">
        <ul class="pagination justify-content-center d-flex flex-wrap">
            <li class="page-item @(Model.PageNumber > 1 ? "enabled" : "disabled")">
                <a class="page-link" href="/">First</a>
            </li>
            <li class="page-item @(Model.PageNumber > 1 ? "enabled" : "disabled")">
                <a class="page-link" href="/?pageNumber=@(Model.PageNumber - 1)">Prev</a>
            </li>

            @foreach (var pageNumber in Model.PageNumbers)
            {
                <li class="page-item @(pageNumber == Model.PageNumber ? "active" : "")">
                    <a class="page-link" href="/?pageNumber=@pageNumber">@pageNumber</a>
                </li>
            }

            <li class="page-item @(Model.PageNumber < Model.TotalPages ? "enabled" : "disabled")">
                <a class="page-link" href="/?pageNumber=@(Model.PageNumber + 1)">Next</a>
            </li>
            <li class="page-item @(Model.PageNumber < Model.TotalPages ? "enabled" : "disabled")">
                <a class="page-link" href="/?pageNumber=@(Model.TotalPages)">Last</a>
            </li>
        </ul>
    </nav>
}

結果如下

元哥的筆記