如何利用Dapper實(shí)現(xiàn)分頁效果

如何利用Dapper實(shí)現(xiàn)分頁效果？這個(gè)問題可能是我們?nèi)粘W(xué)習(xí)或工作經(jīng)常見到的。希望通過這個(gè)問題能讓你收獲頗深。下面是小編給大家?guī)淼膮⒖純?nèi)容，讓我們一起來看看吧！

簡介

之前事先搜索了下博客園上關(guān)于Dapper分頁的實(shí)現(xiàn)，有是有，但要么是基于存儲(chǔ)過程，要么支持分頁，而不支持排序，或者搜索條件不是那么容易維護(hù)。

方法定義

以下是我的一個(gè)分頁的實(shí)現(xiàn)，雖然不是泛型（因?yàn)榭紤]到where條件以及sql語句的搭配），但是應(yīng)該可以算是比較通用的了，方法定義如下：

public Tuple<IEnumerable<Log>, int> Find(LogSearchCriteria criteria
      , int pageIndex
      , int pageSize
      , string[] asc
      , string[] desc);

以上函數(shù)定義是一個(gè)查詢Log的示例，返回結(jié)果中，Tuple的第一個(gè)值是結(jié)果集，第二個(gè)值是總行數(shù)（例如，總共有100條記錄，每頁10條，當(dāng)前第一頁，那么第一個(gè)值是10條記錄，第二個(gè)值是100）

在示例項(xiàng)目中，我用兩種方法實(shí)現(xiàn)了分頁：

1. 第一種是基于2此查詢，第一次得到總數(shù)，第二次查詢得到結(jié)果集。

2. 第二種是基于1此查詢，用了SqlServer 的Offest/Fetch，所以只支持Sql Server 2012+，所以大家根據(jù)自己用的Sql Server版本選擇不同的實(shí)現(xiàn)，這里當(dāng)然是第二種實(shí)現(xiàn)效率更高一點(diǎn)。

運(yùn)行示例

1. 將Github的Repo下載或者Clone到本地以后，到Database目錄下，解壓縮Database.7z

2. Attach到Sql Server上。默認(rèn)我使用Sql Server LocalDB，連接字符串是 Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DapperPagingSample;integrated security=True;   如果你用的不是LocalDB，請(qǐng)酌情修改App.Config的連接字符串。

3. Ctrl+F5運(yùn)行程序，示例項(xiàng)目里，我用了一個(gè)簡單的WinForm程序，但應(yīng)該可以比較好的演示分頁效果。

多表支持

增加了示例，支持多表查詢，例如有兩個(gè)Log表，Level表，Log的LevelId字段引用Level的Id字段，通過以下的查詢，可以實(shí)現(xiàn)多表查詢的分頁，排序，過濾：

首先是通過兩次查詢的示例（基本支持所有版本Sql Server）：

public Tuple<IEnumerable<Log>, int> Find(LogSearchCriteria criteria
      , int pageIndex
      , int pageSize
      , string[] asc
      , string[] desc)
    {
      using (IDbConnection connection = base.OpenConnection())
      {
        const string countQuery = @"SELECT COUNT(1)
                      FROM   [Log] l
                      INNER JOIN [Level] lv ON l.LevelId = lv.Id
                      /**where**/";

        const string selectQuery = @" SELECT *
              FROM  ( SELECT  ROW_NUMBER() OVER ( /**orderby**/ ) AS RowNum, l.*, lv.Name as [Level]
                   FROM   [Log] l
                   INNER JOIN [Level] lv ON l.LevelId = lv.Id
                   /**where**/
                  ) AS RowConstrainedResult
              WHERE  RowNum >= (@PageIndex * @PageSize + 1 )
                AND RowNum <= (@PageIndex + 1) * @PageSize
              ORDER BY RowNum";

        SqlBuilder builder = new SqlBuilder();

        var count = builder.AddTemplate(countQuery);
        var selector = builder.AddTemplate(selectQuery, new { PageIndex = pageIndex, PageSize = pageSize });

        if (!string.IsNullOrEmpty(criteria.Level))
          builder.Where("lv.Name= @Level", new { Level = criteria.Level });

        if (!string.IsNullOrEmpty(criteria.Message))
        {
          var msg = "%" + criteria.Message + "%";
          builder.Where("l.Message Like @Message", new { Message = msg });
        }

        foreach (var a in asc)
        {
          if(!string.IsNullOrWhiteSpace(a))
            builder.OrderBy(a);
        }

        foreach (var d in desc)
        {
          if (!string.IsNullOrWhiteSpace(d))
            builder.OrderBy(d + " desc");
        }

        var totalCount = connection.Query<int>(count.RawSql, count.Parameters).Single();
        var rows = connection.Query<Log>(selector.RawSql, selector.Parameters);

        return new Tuple<IEnumerable<Log>, int>(rows, totalCount);
      }
    }

第二個(gè)示例是通過Offset/Fetch查詢（支持Sql Server 2012+）

public Tuple<IEnumerable<Log>, int> FindWithOffsetFetch(LogSearchCriteria criteria
                        , int pageIndex
                        , int pageSize
                        , string[] asc
                        , string[] desc)
    {
      using (IDbConnection connection = base.OpenConnection())
      {
        
        const string selectQuery = @" ;WITH _data AS (
                      SELECT l.*, lv.Name AS [Level]
                      FROM   [Log] l
                      INNER JOIN [Level] lv ON l.LevelId = lv.Id
                      /**where**/
                    ),
                      _count AS (
                        SELECT COUNT(1) AS TotalCount FROM _data
                    )
                    SELECT * FROM _data CROSS APPLY _count /**orderby**/ OFFSET @PageIndex * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY";

        SqlBuilder builder = new SqlBuilder();
        
        var selector = builder.AddTemplate(selectQuery, new { PageIndex = pageIndex, PageSize = pageSize });

        if (!string.IsNullOrEmpty(criteria.Level))
          builder.Where("lv.Name = @Level", new { Level = criteria.Level });

        if (!string.IsNullOrEmpty(criteria.Message))
        {
          var msg = "%" + criteria.Message + "%";
          builder.Where("l.Message Like @Message", new { Message = msg });
        }
        
        foreach (var a in asc)
        {
          if (!string.IsNullOrWhiteSpace(a))
            builder.OrderBy(a);
        }

        foreach (var d in desc)
        {
          if (!string.IsNullOrWhiteSpace(d))
            builder.OrderBy(d + " desc");
        }
        
        var rows = connection.Query<Log>(selector.RawSql, selector.Parameters).ToList();

        if(rows.Count == 0)
          return new Tuple<IEnumerable<Log>, int>(rows, 0);
        

        return new Tuple<IEnumerable<Log>, int>(rows, rows[0].TotalCount);
        
      }
    }

感謝各位的閱讀！看完上述內(nèi)容，你們對(duì)如何利用Dapper實(shí)現(xiàn)分頁效果大概了解了嗎？希望文章內(nèi)容對(duì)大家有所幫助。如果想了解更多相關(guān)文章內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道。