存储过程分页

--创建存储过程

create proc sp_Show
(
@index int, --当前页
@size int, --每页大小
@totalcount int out, --总数据数
@pagecount int out --总页数
)
as
begin
--计算总数据数
select @totalcount=COUNT(*) from Goods --(where name like '%'+ @name +'%')
--计算总页数
set @pagecount=CEILING(@totalcount*1.0/@size)

if(@index<=0)
set @index=1
if(@index>=@pagecount)
set @index=@pagecount

--分页查询
select * from
(select ROW_NUMBER() over(order by GId) rn,*from Goods) tb1 where ----(where name like '%'+ @name +'%')
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 总页数

 

 

///api调用存储过程

ShopDB db = new ShopDB();

//分页显示商品
[HttpGet]
public PageDate GetGoods(int index, int size)
{
PageDate page = new PageDate();
var list = db.Goods.ToList();
var count = list.Count();
page.List = list.OrderBy(x => x.GId).Skip((index - 1) * size).Take(size).ToList();
page.PageCount = count / size + (count % size == 0 ? 0 : 1);
return page;
}
//分页存储过程显示
[HttpGet]
public PageDate GetGoods2(int index, int size)
{
//实例化参数
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@index",index),
new SqlParameter("@size",size),
new SqlParameter("@totalcount",SqlDbType.Int), //总数据数
new SqlParameter("@pagecount",SqlDbType.Int), //总页数
};
//指定输出参数
parameters[2].Direction = ParameterDirection.Output;
parameters[3].Direction = ParameterDirection.Output;

//存储过程查询
var list = db.Database.SqlQuery<Goods>("exec sp_Show @index,@size,@totalcount out,@pagecount out", parameters).ToList();

PageDate page = new PageDate();
page.List = list;
page.PageCount = int.Parse(parameters[3].Value.ToString());
return page;
}

 

 

///MVc视图

<table class="table table-bordered">
<thead>
<tr>
<td>商品名称</td>
<td>商品图片</td>
<td>商品价格</td>
<td>加入购物车</td>
</tr>
</thead>
<tbody id="tb"></tbody>
</table>

<table class="table table-bordered">
<tr>
<td><input id="Button1" type="button" value="首页" onclick="first()" /></td>
<td><input id="Button1" type="button" value="上一页" onclick="prve()" /></td>
<td><input id="Button1" type="button" value="下一页" onclick="next()" /></td>
<td><input id="Button1" type="button" value="尾页" onclick="last()" /></td>
</tr>
</table>

<script>
var index1 = 0;
var pagecount = 0;
function load(index) {
index1 = index;
$.ajax({
url: "http://localhost:50825/api/Shopping/GetGoods2",
data: { index: index1, size: 2 },
type: "get",
dataType: "json",
success:
function (d) {
$("#tb").empty();
$(d.List).each(function () {
$("#tb").append(
'<tr>' +
'<td>' + this.Name + '</td>' +
'<td><img src="http://localhost:50825' + this.Img + '" width="80" height="60" /></td>' +
'<td>' + this.Price + '</td>' +
'<td><input id="Button1" type="button" value="加入购物车" onclick="addcar()" /></td>' +
'</tr>'
)
})
pagecount = d.PageCount;
}
})
}
load(1);

function first() {
index1 = 1;
load(index1);
}
function prve() {
index1--;
if (index1 == 0) {
index1 = 1;
}
load(index1);
}
function next() {
index1++;
if (index1 > pagecount) {
index1 = pagecount;
}
load(index1);
}
function last() {
load(pagecount);
}

//cookie零时存购物车
function addcar() {
//判断cookie是否有值
if (getCookie("shopcar") == null) {
//数组类型
setCookie("shopcar", "[]");
}
//这里举个例子，将值写死
var obj = {
Name: "球鞋",
Price: "1200"
};

//获取值，此时为字符串类型
var liststr = getCookie('shopcar');
//类型转换
var list = JSON.parse(liststr);
//追加值
list.push(obj);
//保存到cookie中
setCookie("shopcar", JSON.stringify(list));
location.href = "/Default/ShopCar";
}

//取值
function setCookie(name, value) {
if (value) {
var days = 1; //定义一天
var exp = new Date();
exp.setTime(exp.getTime() + days * 24 * 60 * 60 * 1000);
// 写入Cookie, toGMTString将时间转换成字符串
document.cookie = name + "=" + escape(value) + ";expires=" + exp.toGMTString;
}
};
//存值
function getCookie(name) {
var arr, reg = new RegExp("(^| )" + name + "=([^;]*)(;|$)"); //匹配字段
if (arr = document.cookie.match(reg)) {
return unescape(arr[2]);
} else {
return null;
}
};

 

 

//其他页面

//取值
function setCookie(name, value) {
if (value) {
var days = 1; //定义一天
var exp = new Date();
exp.setTime(exp.getTime() + days * 24 * 60 * 60 * 1000);
// 写入Cookie, toGMTString将时间转换成字符串
document.cookie = name + "=" + escape(value) + ";expires=" + exp.toGMTString;
}
};
//存值
function getCookie(name) {
var arr, reg = new RegExp("(^| )" + name + "=([^;]*)(;|$)"); //匹配字段
if (arr = document.cookie.match(reg)) {
return unescape(arr[2]);
} else {
return null;
}
};