dapper mysql 分页_Dapper存储过程分页

create database Month6

use Month6

--用户表

create table UserInfo

(

UId int primary key identity,

UName varchar(max), --登录名

UPwd varchar(max) --密码

)

--商品表

create table Goods

(

GId int primary key identity,

GName varchar(max),

GImg varchar(max),

GColor varchar(max),

GSize varchar(max),

GPrice int

)

select * from Goods

--购物车表

create table ShopCar

(

CId int primary key identity,

BuyCount int,

UId int foreign key references UserInfo(UId), --用户外键

GId int foreign key references Goods(GId) --商品外键

)

--订单表

create table OrderInfo

(

OId int primary key identity,

OrderNum varchar(max),

CreateTime datetime,

Count int,

UId int foreign key references UserInfo(UId), --用户外键

GId int foreign key references Goods(GId) --商品外键

)

--分页显示存储过程

create proc sp_Show

(

@index int,

@size int,

@totalcount int out, --总数据数

@pagecount int out --总页数

)

as

begin

--如果当前页数小于一

if(@index<1)

begin

set @index=1

end

--计算总数据数

select @totalcount=count(*) from Goods

--计算总页数

set @pagecount=CEILING(@totalcount*1.0/@size)

--分页查询

select * from

(select *,ROW_NUMBER() over (order by GId) rn from Goods) tb1 where rn between (@index-1)*@size+1 and @index*@size

end

declare @x int,@y int

exec sp_Show 1,2,@x out,@y out

select @x,@y