使用Dapper接收多个结果集,并实现存储过程分页

来源：互联网发布：美工设计培训班编辑：程序博客网时间：2024/05/18 15:52

本文简单介绍一下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

0 0