使用Dapper接收多個結果集,並實現預存程序分頁

轉載請註明出處
http://blog.csdn.net/weijian300/article/details/50463307
本文簡單介紹一下QueryMultiple的用法,並給出用QueryMultiple來實現SQL Server預存程序分頁
先放一個網上找的分頁過程,出自哪裡不記得了,原文作者別見怪

--分頁預存程序  CREATE PROCEDURE [dbo].[Pro_System_DataPaging]    (      @Tables VARCHAR(1000) ,                --表名/視圖名      @PrimaryKey VARCHAR(100) ,             --主鍵      @Sort VARCHAR(200) = NULL ,            --排序欄位(不帶order by)      @pageindex INT = 1 ,                    --當前頁碼      @PageSize INT = 10 ,                    --每頁記錄數      @Fields VARCHAR(1000) = N'*' ,         --輸出欄位      @Filter VARCHAR(8000) = NULL ,         --where過濾條件(不帶where)      @Group VARCHAR(1000) = NULL --,          --Group語句(不帶Group By)      --@DataCount INT OUTPUT                 --總記錄數    )AS     DECLARE @SortTable VARCHAR(100)     DECLARE @SortName VARCHAR(100)     DECLARE @strSortColumn VARCHAR(200)     DECLARE @operator CHAR(2)     DECLARE @type VARCHAR(100)     DECLARE @prec INT --設定排序語句    IF @Sort IS NULL        OR @Sort = ''         SET @Sort = @PrimaryKey          IF CHARINDEX('DESC', @Sort) > 0         BEGIN                     SET @strSortColumn = REPLACE(@Sort, 'DESC', '')                     SET @operator = '<='             END     ELSE         BEGIN                            SET @strSortColumn = REPLACE(@Sort, 'ASC', '')                            SET @operator = '>='             END     IF CHARINDEX('.', @strSortColumn) > 0         BEGIN                     SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.', @strSortColumn))            SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.', @strSortColumn) + 1, LEN(@strSortColumn))             END     ELSE         BEGIN                     SET @SortTable = @Tables                     SET @SortName = @strSortColumn          END --設定排序欄位類型和精度     SELECT  @type = t.name ,            @prec = c.prec    FROM    sysobjects o            JOIN syscolumns c ON o.id = c.id            JOIN systypes t ON c.xusertype = t.xusertype    WHERE   o.name = @SortTable            AND c.name = @SortName    IF CHARINDEX('char', @type) > 0         SET @type = @type + '(' + CAST(@prec AS VARCHAR) + ')'    DECLARE @strPageSize VARCHAR(50)     DECLARE @strStartRow VARCHAR(50)     DECLARE @strFilter VARCHAR(1000)     DECLARE @strSimpleFilter VARCHAR(1000)     DECLARE @strGroup VARCHAR(1000)      IF @pageindex < 1         SET @pageindex = 1      SET @strPageSize = CAST(@PageSize AS VARCHAR(50)) --設定開始分頁記錄數     SET @strStartRow = CAST(( ( @pageindex - 1 ) * @PageSize + 1 ) AS VARCHAR(50))  --篩選以及分組語句    IF @Filter IS NOT NULL        AND @Filter != ''         BEGIN                     SET @strFilter = ' WHERE ' + @Filter + ' '             SET @strSimpleFilter = ' AND ' + @Filter + ' '         END     ELSE         BEGIN                     SET @strSimpleFilter = ''                     SET @strFilter = ''             END     IF @Group IS NOT NULL        AND @Group != ''         SET @strGroup = ' GROUP BY ' --執行查詢語句        EXEC(    '    DECLARE @SortColumn ' + @type + '    SET ROWCOUNT ' + @strStartRow + '    SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '    SET ROWCOUNT ' + @strPageSize + '    SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '    '    )       --計算總記錄數,總頁數    DECLARE @DataCountSql NVARCHAR(1000)    SET @DataCountSql = N'SELECT COUNT(' + @PrimaryKey + N') AS DataCount,CEILING(COUNT(' + @PrimaryKey + N')/' + CONVERT(VARCHAR(10),@PageSize) + '.000) AS PageCount'+ N' FROM ' + @Tables + @strFilter    --EXEC sp_executesql @DataCountSql, N'@DataCount int', @DataCount OUTPUT    EXEC(@DataCountSql)

表

CREATE TABLE [dbo].[TestColumn](    [Id] [int] IDENTITY(1,1) NOT NULL,    [NAME] [nvarchar](150) NULL,    [ModifiedOn] [smalldatetime] NULL    )

OK,接下來建立個控制台項目

        private const string Sqlconnection = @"Data Source=.\MSSQLSERVER2008;Initial Catalog=DapperTest;User Id=sa;Password=123456;";//串連自己改        public static SqlConnection OpenConnection()        {            var connection = new SqlConnection(Sqlconnection);            connection.Open();            return connection;        }        /// <summary>        /// 單個結果集        /// </summary>        /// <returns>查詢結果</returns>        public static IEnumerable<TestColumn> SelectTestColumns()        {            using (IDbConnection conn = OpenConnection())            {                const string query = "select * from TestColumn order by id desc";                return conn.Query<TestColumn>(query, null);            }        }        /// <summary>        /// 多個結果集        /// </summary>        /// <param name="sysPaging">分頁資訊</param>        /// <returns>查詢結果</returns>        public static IEnumerable<TestColumn> SelectTestColumns(out List<SysPaging> sysPaging)        {            using (IDbConnection conn = OpenConnection())            {                const string query = "EXEC [dbo].[Pro_System_DataPaging] @Tables = N'TestColumn',@PrimaryKey = N'ID',@Sort = N'ID',@pageindex = 1,@PageSize = 10,@Fields = '[Id],[NAME],[ModifiedOn]',@Filter = NULL,@Group = NULL";                var result = conn.QueryMultiple(query);                //這裡一定要分別單獨接收之後,再進行return                var resultGird = result.Read<TestColumn>().ToList();                var paging = result.Read<SysPaging>().ToList();                sysPaging = paging.ToList<SysPaging>();                return resultGird;            }        }        static void Main(string[] args)        {            var paging = new List<SysPaging>();            var allTestColumns = SelectTestColumns(out paging).ToList<TestColumn>();            foreach (var testColumn in allTestColumns.Where(c => c.Parentid == 0))            {                Console.Out.WriteLine("Id==>" + testColumn.Id + "\t");                Console.Out.WriteLine("Name==>" + testColumn.Name + "\t");                Console.Out.WriteLine("時間==>" + testColumn.ModifiedOn + "\t");            }            Console.Out.WriteLine("DataCount==>" + paging[0].DataCount);            Console.Out.WriteLine("PageCount==>" + paging[0].PageCount);            Console.ReadLine();        }

其實在Dapper中也有介紹QueryMultiple的用法
官網:http://code.google.com/p/dapper-dot-net/
GitHub:https://github.com/StackExchange/dapper-dot-net