CSLA.NET Web 專案整合 SQL Server 2005 分頁查詢

背景與動機

在 Web 專案使用 CSLA.NET 時，發現其預設範本側重主從表單（如採購單含主表與明細），適合 WinForms，但 Web 多為單表編輯，使用上略感不順。本文分享如何在 CSLA 模型中優雅整合 SQL Server 2005 的 ROW_NUMBER() 分頁功能。

四步驟實作分頁

第一步：建立 PagingCriteria 傳遞分頁參數

[Serializable]
public class PagingCriteria<T> : CriteriaBase
{
    public IDictionary ParamDic { get; private set; }
    public string SortExpression { get; private set; }
    public string DefaultOrder { get; private set; }
    public int StartRowIndex { get; private set; }
    public int MaximumRows { get; private set; }

    protected PagingCriteria() { }

    public PagingCriteria(IDictionary paramDic, int startRowIndex, int maximumRows, 
                          string sortExpression, string defaultOrder)
        : base(typeof(T))
    {
        ParamDic = paramDic;
        SortExpression = sortExpression;
        DefaultOrder = defaultOrder;
        StartRowIndex = startRowIndex;
        MaximumRows = maximumRows;
    }
}

第二步：實作 PagingWrapper 將 SQL 包裝為可分頁語句

public static class PagingWrapper
{
    private const string SQL_TEMPLATE = 
        @"WITH TargetTable AS (
            SELECT ROW_NUMBER() OVER (ORDER BY Temp.{1}) AS RowNumber, Temp.* 
            FROM ({0}) AS Temp
          )
          SELECT * FROM TargetTable WHERE RowNumber BETWEEN {2} AND {3};
          SELECT COUNT(*) FROM ({0}) Temp";

    public static string Wrap(string sqlQuery, string sortExpression, 
                              int startRowIndex, int maximumRows)
    {
        if (string.IsNullOrEmpty(sortExpression))
            throw new Exception("未指定排序欄位。");
        return string.Format(SQL_TEMPLATE, sqlQuery, sortExpression, 
                             startRowIndex + 1, startRowIndex + maximumRows);
    }

    public static string Wrap<T>(string sqlQuery, PagingCriteria<T> criteria)
    {
        string order = string.IsNullOrEmpty(criteria.SortExpression) 
                       ? criteria.DefaultOrder 
                       : criteria.SortExpression;
        return Wrap(sqlQuery, order, criteria.StartRowIndex, criteria.MaximumRows);
    }
}

第三步：集合物件中呼叫（以 RoleList 為例）

using (SqlCommand cm = cn.CreateCommand())
{
    cm.CommandType = CommandType.Text;
    string sql = "SELECT * FROM Roles";
    cm.CommandText = PagingWrapper.Wrap(sql, criteria);
    using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
    {
        while (dr.Read())
            Add(Role.FillRole(dr));
        dr.NextResult(); // 移至第二個結果集（總筆數）
        dr.Read();
        TotalRowCount = dr.GetInt32(0);
    }
}

TotalRowCount 實作 Csla.Core.IReportTotalRowCount 介面，供資料源控制項取得總筆數。

第四步：頁面資料源控制項綁定 GridView 實現分頁

private int totalCount = 0;

protected void CslaDs_SelectObject(object sender, Csla.Web.SelectObjectArgs e)
{
    BLL.Security.RoleList list = BLL.Security.RoleList.PagingRoleList(
        null, e.SortExpression, e.StartRowIndex, e.MaximumRows);
    e.BusinessObject = list;
    totalCount = list.TotalRowCount;
}

protected void gvlist_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.Pager)
    {
        ((Label)e.Row.FindControl("lblTotal")).Text = totalCount.ToString();
    }
}

public static RoleList PagingRoleList(IDictionary paramDic, string sortExpression, 
                                      int startRowIndex, int maximumRows)
{
    return DataPortal.Fetch<RoleList>(
        new PagingCriteria<RoleList>(paramDic, startRowIndex, maximumRows, sortExpression, "ID"));
}

GridView PagerTemplate 完整範例

<PagerTemplate>
    <br />
    共<asp:Label ID="lblTotal" runat="server"></asp:Label>筆
    <asp:Label ID="lblPage" runat="server" 
        Text='<%# "第" + (((GridView)Container.NamingContainer).PageIndex + 1) + "頁/共" + (((GridView)Container.NamingContainer).PageCount) + "頁" %>'>
    </asp:Label>
    <asp:LinkButton ID="lbnFirst" runat="Server" Text="首頁" 
        Enabled='<%# ((GridView)Container.NamingContainer).PageIndex != 0 %>'
        CommandName="Page" CommandArgument="First"></asp:LinkButton>
    <asp:LinkButton ID="lbnPrev" runat="server" Text="上一頁" 
        Enabled='<%# ((GridView)Container.NamingContainer).PageIndex != 0 %>'
        CommandName="Page" CommandArgument="Prev"></asp:LinkButton>
    <asp:LinkButton ID="lbnNext" runat="Server" Text="下一頁" 
        Enabled='<%# ((GridView)Container.NamingContainer).PageIndex != (((GridView)Container.NamingContainer).PageCount - 1) %>'
        CommandName="Page" CommandArgument="Next"></asp:LinkButton>
    <asp:LinkButton ID="lbnLast" runat="Server" Text="尾頁" 
        Enabled='<%# ((GridView)Container.NamingContainer).PageIndex != (((GridView)Container.NamingContainer).PageCount - 1) %>'
        CommandName="Page" CommandArgument="Last"></asp:LinkButton>
    <br />
</PagerTemplate>

關鍵說明

GridView 內部將總筆數封裝於私有 PagedDataSource 中，外部無法直接取得（反射也不行）。最簡單做法：在 SelectObject 事件中暫存 totalCount，再於 RowDataBound 事件寫入 PagerTemplate 的 Label。若求完美，可自行封裝繼承 GridView 並暴露 TotalRowCount 屬性。