Use dapper to receive multiple result sets and implement stored procedure paging

Reprint please indicate the source
http://blog.csdn.net/weijian300/article/details/50463307
This article briefly describes the use of querymultiple, And a querymultiple to implement SQL Server stored procedure paging
first put an online page to find the process, from where not to remember, the original author do not take it amiss

--Paging stored procedure CREATE PROCEDURE [dbo].
    [Pro_system_datapaging] (@Tables VARCHAR (1000),--table name/view name @PrimaryKey VARCHAR (100),--primary key @Sort Varc  HAR = NULL,--the Sort field (without order by) @pageindex int = 1,--The current page number @PageSize int =         10,--the number of records per page @Fields VARCHAR (1000) = N ' * ',--output field @Filter VARCHAR (8000) = NULL, --where filter condition (without where) @Group VARCHAR (1000) = NULL--,--Group statement (without group by)--@DataCount INT O Utput--Total record number) as DECLARE @SortTable VARCHAR (m) DECLARE @SortName VARCHAR (MB) declar E @strSortColumn VARCHAR (d) DECLARE @operator CHAR (2) DECLARE @type VARCHAR (MB) DECLARE @prec INT--Set Sort statement 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 C Harindex ('. ', @strSortColumn) > 0 BEGIN SET @SortTable = SUBSTRING (@strSortColumn, 0, CHA Rindex ('. ', @strSortColumn)) SET @SortName = SUBSTRING (@strSortColumn, CHARINDEX ('. ', @strSortColumn) + 1, LEN     
        (@strSortColumn)) End ELSE BEGIN Set @SortTable = @Tables Set @SortName = @strSortCo
            Lumn End--Set sort field type and precision 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 = @Sor Ttable and c.name = @SortName IF CHARINDEX (' char ', @type) > 0 SET @type = @type + ' ('+ CAST (@prec as VARCHAR) + ') ' DECLARE @strPageSize VARCHAR (a) DECLARE @strStartRow VARCHAR (m) DECLARE Trfilter VARCHAR (1000) DECLARE @strSimpleFilter VARCHAR (1000) DECLARE @strGroup VARCHAR (1000) IF @pageind Ex < 1 set @pageindex = 1 Set @strPageSize = CAST (@PageSize as VARCHAR (50))--Sets the number of start paging records set @strSt Artrow = CAST ((@pageindex-1) * @PageSize + 1) as VARCHAR (50))--Filter and group statements IF @Filter is not NULL and @ Filter!= ' BEGIN SET @strFilter = ' WHERE ' + @Filter + ' Set @strSimpleFil         
            ter = ' and ' + @Filter + ' end ELSE BEGIN SET @strSimpleFilter = ' The Set @strFilter = ' End IF @Group are not NULL and @Group!= ' set @strGr  OUP = ' GROUP by '-Execute query Statement 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 + ')--calculate total number of records, total pages DECLARE @DataCountSql NV Archar (1000) SET @DataCountSql = N ' SELECT COUNT (' + @PrimaryKey + n ') as datacount,ceiling (COUNT (' + @PrimaryKey + n ') /' + CONVERT (VARCHAR (M), @PageSize) + ') as PageCount ' + N ' from ' + @Tables + @strFilter--exec sp_executesql @Dat Acountsql, N ' @DataCount int ', @DataCount OUTPUT EXEC (@DataCountSql)

Table

CREATE TABLE [dbo]. [Testcolumn] (
    [Id] [int] IDENTITY (1,1) not NULL,
    [NAME] [nvarchar] ($) NULL,
    [ModifiedOn] [smalldatetime] null
    )

OK, Next, create a new console project

        Private Const String SqlConnection = @ "Data source=.\mssqlserver2008;initial catalog=dappertest; User Id=sa; password=123456; ";/ /Connect yourself to change public static SqlConnection OpenConnection () {var connection = new SqlConnection (SQLC
            onnection); Connection.
            Open ();
        return connection; ///<summary>///Single result set///</summary>///<returns> Query Results </returns&
        Gt public static ienumerable<testcolumn> Selecttestcolumns () {using (IDbConnection conn = Openconn
                Ection ()) {const string query = ' SELECT * from Testcolumn ORDER by id DESC '; Return Conn.
            query<testcolumn> (query, NULL); }///<summary>///Multiple result sets///</summary>///<param name= "Syspag ing "> Paging information </param>///<returns> query Results </returns> public static IEnUmerable<testcolumn> Selecttestcolumns (out list<syspaging> syspaging) {using (idbconnecti On conn = OpenConnection ()) {const string query = ' EXEC [dbo].[ Pro_system_datapaging] @Tables = n ' testcolumn ', @PrimaryKey = n ' id ', @Sort = n ' id ', @pageindex = 1, @PageSize = ten, @Fields = '
                [Id],[name],[modifiedon] ', @Filter = null, @Group = NULL '; var result = Conn.

                Querymultiple (query); Here must be separately received after the return of 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 ("Time ==>" + Testcolumn.modifiedon + "T"); } Console.Out.WriteLine ("datacount==>" + paging[0].
            Datacount); Console.Out.WriteLine ("pagecount==>" + paging[0].

            PageCount);
        Console.ReadLine (); }

In fact, there is also an introduction to the use of querymultiple in dapper
Official website: http://code.google.com/p/dapper-dot-net/
github:https://github.com/ Stackexchange/dapper-dot-net