分页存储过程

--创建分页存储过程
create proc Pages
@pageIndex int,
@pageSize int,
@totalCount int output
as
begin
select @totalCount=COUNT(*) from students

select * from (select *,ROW_NUMBER()over(order by sid) as num from students) as tb 
where num between (((@pageIndex-1)*@pageSize)+1) and @pageIndex*@pageSize
end

----删除存储过程
drop proc Pages

--测试分页存储过程
declare @totalCount int
exec Pages 2,3,@totalCount output 
print @totalCount

-----前台jquery处理

$(function () {
//加载分页的事件
Pages();
//隐藏div弹出层
$('#Add').css("display", "none");
//获取所有的用户信息
GetAllUsers();
//单机页面新增 按钮获得的事件
$('#btnadd').click(function () {
$('#Add').css("display", "block");
AddEvent();
})

//弹出层设置
function LayerDiv() {
$.layer({
type: 1,
title: false,
area: ['420px', '260px'],
shade: true,
page: {
html: $('#Add').html()
}, success: function () {
layer.shift('left'); //左边动画弹出
$('#Add').css("display", "block");

}
});
}
})

function AddEvent() {
//新增的按钮事件
$('#btn').click(function () {
var par = {
ID: $("#txtID").val(),
UserName: $("#txtName").val(),
Email: $("#txtEmail").val(),
Age: $("#txtAge").val()
};
$.post("Test.ashx?_=" + Math.random, par, function (data) {
var strings = data.split(':');
if (strings[0] == "ok") {
$('#msg').html(strings[1]);
GetAllUsers();
} else {
$('#msg').html(strings[1]);
}
});
})
}

//数据分页的实现
function Pages() {
$('#firstPage').click(function () {
$('#pageIndex').val('1');
GetAllUsers();
})
$('#prevPage').click(function () {
var ChangePageIndex = parseInt($('#pageIndex').attr('value'));
var ChangePageCount = parseInt($('#pagecount').attr('value'));
ChangePageIndex = ChangePageIndex < 2 ? 1 : ChangePageIndex - 1;
$('#pageIndex').attr('value', ChangePageIndex);
//加载数据
GetAllUsers();
})
$('#nextPage').click(function () {
var ChangePageIndex = parseInt($('#pageIndex').attr('value'));
var ChangePageCount = parseInt($('#pagecount').attr('value'));
ChangePageIndex = ChangePageIndex > ChangePageCount - 1 ? ChangePageCount : ChangePageIndex + 1;
$('#pageIndex').attr('value', ChangePageIndex);
//加载数据
GetAllUsers();
})
$('#lastPage').click(function () {
//alert('尾页');
var ChangePageCount = parseInt($('#pagecount').attr('value'));
$('#pageIndex').attr('value', ChangePageCount);
GetAllUsers();
})
}

 

//加载所有的用户信息
function GetAllUsers() {
var pageIndex = $('#pageIndex').val();
$.post("GetAllUser.ashx", { pageIndex: pageIndex }, function (data) {
//接过来的字符串转换成json数组 即可获取里面的数据 eval转换有问题
var datas = JSON.parse(data);
//alert(datas.pageindex);
//alert(datas.pagecount);
var GetHtml = "<table id='tb' border='1'><tr><td>用户id</td><td>用户名</td><td>年龄</td><td>邮箱</td><td>操作</td></tr>";
$.each(datas.list, function (i, ReturnJson) {
var html = "<tr><td>" + ReturnJson.SID + "</td><td>" + ReturnJson.SName + "</td><td>" + ReturnJson.SAge + "</td><td>" + ReturnJson.SEmail + "</td><td><a href='javascript:void(0)' class='del' stuid='" + ReturnJson.SID + "'>删除</a> <a href='javascript:void(0)'>更新</a></td></tr>";
GetHtml = GetHtml + html;
})
GetHtml += "</table>";
$('#allUsers').html(GetHtml);
//从服务端结果pageindex写到浏览器端的隐藏域
$('#pageIndex').attr('value', datas.pageindex);
//alert($('#pageIndex').attr('value'));
//从服务端结果pagecount写到浏览器端的隐藏域
$('#pagecount').attr('value', datas.pagecount);
//alert($('#pagecount').attr('value'));
//加载删除事件
DeleteUser();
});
};
//删除用户
function DeleteUser() {
$('.del').click(function () {
var id = $(this).attr('stuid');
alert(id);
//把id传给后台让后台接收并且把memcache中的相应数据清除掉
//根据id获取数据
$.post("Del.ashx", { id: id }, function (r) {
if (r == "ok") {
GetAllUsers();
layer.alert("删除成功!");

} else {
layer.alert("删除失败！");
//删除失败
}
})

})
}

 

 

 

前台html代码

<div id="Add">
用户id:<input type="text" name="" id="txtID" value="" /><br />
用户名：<input type="text" name="txtName" id="txtName" value="" /><br />
用户邮箱：<input type="text" name="txtEmail" id="txtEmail" value="" /><br />
用户年龄：<input type="text" name="txtAge" id="txtAge" value="" /><br />
<input type="submit" name="name" id="btn" value="提交" /><span id="msg"></span>
<br />
</div>
<input type="button" name="add" value="新增" id="btnadd" />

<hr />
<!---显示所有的用户---->
<div id="allUsers">
</div>
<!--页码条--->
<a href="javascript:void(0)" id="firstPage">首页</a>
<a href="javascript:void(0)" id="prevPage">上一页</a>
<a href="javascript:void(0)" id="nextPage">下一页</a>
<a href="javascript:void(0)" id="lastPage">尾页</a>
<input type="hidden" name="pageIndex" id="pageIndex" value="1" />
<input type="hidden" name="pagecount" id="pagecount" value="5" />
<hr />
<div id="layers">
</div>

 

 

 

ashx处理程序

context.Response.ContentType = "text/plain";
////context.Response.Write("Hello World");
GetDb db = new GetDb();
ModelPar par = new ModelPar();
int pageIndex;
if (!int.TryParse(context.Request["pageIndex"], out pageIndex))
{
pageIndex = 1;
}
par.pageIndex = pageIndex;
par.pageSize = 2;
int totalCount;
List<Model> list = db.PageData(par, out totalCount);
int pagecount = Convert.ToInt32((Math.Ceiling((double)totalCount / par.pageSize)));
var data = new { list = list, pageindex = pageIndex, pagecount = pagecount };
JavaScriptSerializer js = new JavaScriptSerializer();
string json = js.Serialize(data);
context.Response.Write(json);

 

-----简单的数据库获取部分  脱离了三层

//获取分页存储过程
public List<Model> PageData(ModelPar par, out int totalCount)
{
List<Model> list = new List<Model>();

string ProcName = "Pages";
SqlParameter[] psm = new SqlParameter[] {
new SqlParameter("@pageIndex",par.pageIndex),
new SqlParameter("@pageSize",par.pageSize),
//获取输出参数的值
new SqlParameter("@totalCount",SqlDbType.Int){Direction= ParameterDirection.Output}
};
DataTable dt = SqlHelper.ExeProcDataTable(ProcName, psm);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
Model m = RowToObject(dr);
list.Add(m);
}
}
//记录数据库中的总数量
totalCount = Convert.ToInt32(psm[2].Value);
return list;
}