ASP.NET+三层架构+SqlServer Ajax无刷新分页
1.首先数据表如下(IT_ExamPaper);
2.确定查询内容(比如查询:ExamName,AddTime,ExamTimelimit),那么这里本人通过存储过程来查。
存储如下:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[ExamPaper_GetPages]
@pageIndex int,--下标
@pageSize int,--大小
@count int output--总数量
as
declare @n int
select @count=COUNT(*) from IT_Exampaper
select * from
(select *,ROW_NUMBER() over(order by ExamId asc) as num from IT_Exampaper) as t
where num between (@pageIndex-1)*@pageSize + 1 and @pageIndex*@pageSize
order by ExamId asc
2.1然后为了测试存储是否正确可以调用执行一下
因为这里带了输出参数:@Count(输出总数量的!) 其中ROW_NUMBER()是一个sql函数用于排序生成流水号的,针对于ID加密什么的可以用,当然这里看个人喜好了!
declare @qq int
exec [dbo].[ExamPaper_GetPages] 1, 10, @qq out
3.那么就是调用存储过程了!
IExam(接口层):
代码: DataTable GetaAllExamPaper(int pageIndex, int pageSize, out int count);
Dal(数据访问层):
/// <summary>
/// 获得所有试卷再分页
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public DataTable GetaAllExamPaper(int pageIndex, int pageSize, out int count)
{
SqlParameter[] val = {
new SqlParameter("@pageIndex",SqlDbType.Int),
new SqlParameter("@pageSize",SqlDbType.Int),
new SqlParameter("@count",SqlDbType.Int)
};
//设置输入参数
val[0].Value = pageIndex;
val[1].Value = pageSize;
val[2].Direction = ParameterDirection.Output;
DataTable dt = DBResHelper.ExexProcQuery("ExamPaper_GetPages", val);
int.TryParse(val[2].Value.ToString(), out count);
return dt;
}
ADO.NET:
这里有一个ADO.NET辅助方法ExexProcQuery(“存储过程名”,参数列表)
读取存储过程方法代码如下:
/// <summary>
/// 读取存贮过程
/// </summary>
/// <param name="ProcName"></param>
/// <param name="Pars"></param>
/// <returns></returns>
public static DataTable ExexProcQuery(string ProcName, SqlParameter[] Pars)
{
SqlConnection con = new SqlConnection(ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter(ProcName, con);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < Pars.Length; i++)
{
sda.SelectCommand.Parameters.Add(Pars[i]);
}
//这里写成
DataTable dt = new DataTable();
try
{
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
//LogTool.WriteLog(ex.Message.ToString());
return dt;
}
finally
{
con.Close();
}
}
BLL:业务逻辑层
public DataTable GetaAllExamPaper(int pageIndex, int pageSize, out int count)
{
return ie.GetaAllExamPaper(pageIndex, pageSize, out count);
}
这里ie来自简单工厂模式中的公共副类对象
好了到了这一步如果没有其他问题,数据是取出来了,那么接下路就是在UI层通过Aajx进行处理了!
UI(Exampaper.Aspx):
<div class="pracl_dalist">
<div class="paper_tit">
<div class="pt_left"> <a href="#">首页</a><em>></em><a href="#">课程列表</a></div>
<div class="labnum"> 一共:<asp:Label CssClass="lab" ID="labNum" runat="server" Text=""></asp:Label>条记录。</div>
</div>
<div class="examList_data clear" id="divtable">
</div>
</div>
<div class="page_nav">
<ul class="pages">
<li id="page-first" class="pgNext"style=" cursor:pointer;">首页</li>
<li id="page-pre" class="pgNext" style=" cursor:pointer;">上一页</li>
<li id="page-next" class="pgNext" style=" cursor:pointer;">下一页</li>
<li id="page-last" class="pgNext" style=" cursor:pointer;">末页</li>
<li class="pgNext" style=" width:150px;">转到第<input id="txtIndex" type="text" />页</li>
<li class="pgNext go" id="pageTo">确定</li>
</ul>
</div>
然后再写Ajax:在此之前要求先把JQUERY包引入进来,网上有的下载!
在写Ajax之前得先建一个一般处理程序(ExamPageCut.ashx),建好之后那么就可以开始写Ajax了
页面上代码如下:
<script type="text/javascript" language="javascript">
var pagesize = 10;
var pageindex;
var lastindex;
$(function () {
loaddata();
aaa();
$("#page-first").click(function () {
if (pageindex == 1) {
alert('已经是第一页了!');
}
else {
pageindex = 1;
loaddata();
}
});
$("#page-pre").click(function () {
if (pageindex == 1) {
alert('这是第一页!');
}
else {
if (pageindex > 1) {
pageindex--;
loaddata();
}
}
});
$("#page-next").click(function () {
if (pageindex == lastindex) {
alert('已经是最后一页了!');
}
else {
if (pageindex < lastindex) {
pageindex++;
loaddata();
}
}
});
$("#page-last").click(function () {
if (pageindex == lastindex) {
alert('已经是最后一页了!');
}
else {
pageindex = lastindex;
loaddata();
}
})
$("#pageTo").click(function (event) {
var txtval = $("#txtIndex").val();
pageindex = txtval;
loaddata();
});
});
//..
function loaddata() {
pageindex = !pageindex ? 1 : pageindex;
$.ajax({
url: "WebServeice/PageForExamPaper.ashx?action=GetExamForPage", //调用后台方法发送请求
type: "get",
contentType: "application/json",
dataType: "json",
data: { pageindex: pageindex, pagesize: pagesize },
success: function (result) {
if (result) {
var count = result.PageCount;
if (lastindex) {
lastindex = Math.floor(count / pagesize);
if (count % pagesize > 0) {
lastindex += 1;
}
}
var data = result.data;
var biaoqian = "<table style='width:100%;'>";//动态添加数据,
biaoqian += "<tr style='text-align:center;'><td>课程名称</td><td>所属级别</td><td>添加时间</td></tr>";
$.each(data, function (name, obj) {
biaoqian += "<tr style='text-align:left;'>";
biaoqian += "<td>" + obj["Id"] + "</td>";//rowNumber中的Id,就是存储过程中的流水号!
biaoqian += "<td>" + obj["ExamName"] + "</td>";
biaoqian += "<td>" + obj["ExamTimeLimit"] + "</td>";
biaoqian += "<td>" + formatDateTime(obj["AddTime"]) + "</td>";
biaoqian += "</tr>";
});
biaoqian += "</table>";
$('#divtable').html(biaoqian);
}
},
error: function (err) {
//alert(err.status);
}
});
}
function aaa() {
$.ajax({
type: "get",
contentType: "application/json",
url: "./WebServeice/PageForExamPaper.ashx",
//data: "{pagesize:" + pagesize + "}",
data: {
action: "GetExamPageForClassPageindex",
pagesize: pagesize,
ts: new Date().getTime()
},
success: function (result) {
//alert(result);
lastindex = result;
},
error: function (err) {
alert(err.status);
}
});
}
function go(ipage) {
loaddata();
}
//这个方法是转换时间格式,因为JSON取到的是时间戳,那么要转换为时间格式。
function formatDateTime(dateTime, format) {
var dtime = new Date(dateTime);
var str = dtime.getFullYear().toString() + "-"
+ (dtime.getMonth() + 1).toString() + "-"
+ dtime.getDate().toString();
switch (format) {
case "yyyy-MM-dd HH:mm:ss":
str += " " + dtime.getHours().toString() + ":"
+ dtime.getMinutes().toString() + ":"
+ dtime.getSeconds().toString();
break;
}
return str;
}
</script>
这一块就是AJAX了
处理程序中代码:
public class PageForExamPaper : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
string action = context.Request["action"];
string result = string.Empty;
if (!string.IsNullOrEmpty(action))
{
switch (action)
{
case "GetExamPageForClassPageindex":
result = GetExamPageForClassPageindex(int.Parse(context.Request["pagesize"])).ToString();
break;
case "GetExamForPage":
result = GetExamForPage(int.Parse(context.Request["pageindex"]), int.Parse(context.Request["pagesize"]));
break;
}
}
context.Response.Write(result);
context.Response.End();
}
public string GetExamForPage(int pageindex, int pagesize)
{
int pageCount = 0;
Content.CExam cce = new Content.CExam();
DataTable dt = cce.GetaAllExamPaper(pageindex, pagesize, out pageCount);
List<Modle.exampaperModle> list = new List<Modle.exampaperModle>();
int id = 0;//获取存储过程中NUM
string examname = "";
string timelimit = "";
DateTime Addtime;
for (int i = 0; i < dt.Rows.Count; i++)
{
id = int.Parse(dt.Rows[i]["num"].ToString());//添加流水号从1开始递增
examname = dt.Rows[i]["ExamName"].ToString();
timelimit = dt.Rows[i]["ExamTimeLimit"].ToString();
Addtime = Convert.ToDateTime(dt.Rows[i]["AddTime"]);
Modle.exampaperModle ctl = new Modle.exampaperModle();
{
ctl.Id = id;
ctl.ExamName = examname;
ctl.ExamTimeLimit = int.Parse(timelimit.ToString());
ctl.AddTime = Addtime;
};
list.Add(ctl);
}
var obj = new
{
PageCount = pageCount,
data = list
};
Newtonsoft.Json.Converters.IsoDateTimeConverter timeFormat = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
timeFormat.DateTimeFormat = "yyyy-MM-dd";
string result = Newtonsoft.Json.JsonConvert.SerializeObject(obj);
return result;
}
[WebMethod]
public int GetExamPageForClassPageindex(int pagesize)
{
Content.CExam cce = new Content.CExam();
int totalcount = cce.GetExamCount();//这个方法是获取一个总数量的方法,用来查看共有多少页,一共有多少条数据!
if (totalcount % pagesize == 0)
{
return totalcount / pagesize;
}
else
{
return totalcount / pagesize + 1;
}
}
public bool IsReusable
{
get
{
return false;
}
}
}
好了,这一个模块的功能就全部完成了,希望对大家有帮助,有什么好的指点或者意见,留言一起讨论吧,谢谢!
2.确定查询内容(比如查询:ExamName,AddTime,ExamTimelimit),那么这里本人通过存储过程来查。
存储如下:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[ExamPaper_GetPages]
@pageIndex int,--下标
@pageSize int,--大小
@count int output--总数量
as
declare @n int
select @count=COUNT(*) from IT_Exampaper
select * from
(select *,ROW_NUMBER() over(order by ExamId asc) as num from IT_Exampaper) as t
where num between (@pageIndex-1)*@pageSize + 1 and @pageIndex*@pageSize
order by ExamId asc
2.1然后为了测试存储是否正确可以调用执行一下
因为这里带了输出参数:@Count(输出总数量的!) 其中ROW_NUMBER()是一个sql函数用于排序生成流水号的,针对于ID加密什么的可以用,当然这里看个人喜好了!
declare @qq int
exec [dbo].[ExamPaper_GetPages] 1, 10, @qq out
3.那么就是调用存储过程了!
IExam(接口层):
代码: DataTable GetaAllExamPaper(int pageIndex, int pageSize, out int count);
Dal(数据访问层):
/// <summary>
/// 获得所有试卷再分页
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public DataTable GetaAllExamPaper(int pageIndex, int pageSize, out int count)
{
SqlParameter[] val = {
new SqlParameter("@pageIndex",SqlDbType.Int),
new SqlParameter("@pageSize",SqlDbType.Int),
new SqlParameter("@count",SqlDbType.Int)
};
//设置输入参数
val[0].Value = pageIndex;
val[1].Value = pageSize;
val[2].Direction = ParameterDirection.Output;
DataTable dt = DBResHelper.ExexProcQuery("ExamPaper_GetPages", val);
int.TryParse(val[2].Value.ToString(), out count);
return dt;
}
ADO.NET:
这里有一个ADO.NET辅助方法ExexProcQuery(“存储过程名”,参数列表)
读取存储过程方法代码如下:
/// <summary>
/// 读取存贮过程
/// </summary>
/// <param name="ProcName"></param>
/// <param name="Pars"></param>
/// <returns></returns>
public static DataTable ExexProcQuery(string ProcName, SqlParameter[] Pars)
{
SqlConnection con = new SqlConnection(ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter(ProcName, con);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < Pars.Length; i++)
{
sda.SelectCommand.Parameters.Add(Pars[i]);
}
//这里写成
DataTable dt = new DataTable();
try
{
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
//LogTool.WriteLog(ex.Message.ToString());
return dt;
}
finally
{
con.Close();
}
}
BLL:业务逻辑层
public DataTable GetaAllExamPaper(int pageIndex, int pageSize, out int count)
{
return ie.GetaAllExamPaper(pageIndex, pageSize, out count);
}
这里ie来自简单工厂模式中的公共副类对象
好了到了这一步如果没有其他问题,数据是取出来了,那么接下路就是在UI层通过Aajx进行处理了!
UI(Exampaper.Aspx):
<div class="pracl_dalist">
<div class="paper_tit">
<div class="pt_left"> <a href="#">首页</a><em>></em><a href="#">课程列表</a></div>
<div class="labnum"> 一共:<asp:Label CssClass="lab" ID="labNum" runat="server" Text=""></asp:Label>条记录。</div>
</div>
<div class="examList_data clear" id="divtable">
</div>
</div>
<div class="page_nav">
<ul class="pages">
<li id="page-first" class="pgNext"style=" cursor:pointer;">首页</li>
<li id="page-pre" class="pgNext" style=" cursor:pointer;">上一页</li>
<li id="page-next" class="pgNext" style=" cursor:pointer;">下一页</li>
<li id="page-last" class="pgNext" style=" cursor:pointer;">末页</li>
<li class="pgNext" style=" width:150px;">转到第<input id="txtIndex" type="text" />页</li>
<li class="pgNext go" id="pageTo">确定</li>
</ul>
</div>
然后再写Ajax:在此之前要求先把JQUERY包引入进来,网上有的下载!
在写Ajax之前得先建一个一般处理程序(ExamPageCut.ashx),建好之后那么就可以开始写Ajax了
页面上代码如下:
<script type="text/javascript" language="javascript">
var pagesize = 10;
var pageindex;
var lastindex;
$(function () {
loaddata();
aaa();
$("#page-first").click(function () {
if (pageindex == 1) {
alert('已经是第一页了!');
}
else {
pageindex = 1;
loaddata();
}
});
$("#page-pre").click(function () {
if (pageindex == 1) {
alert('这是第一页!');
}
else {
if (pageindex > 1) {
pageindex--;
loaddata();
}
}
});
$("#page-next").click(function () {
if (pageindex == lastindex) {
alert('已经是最后一页了!');
}
else {
if (pageindex < lastindex) {
pageindex++;
loaddata();
}
}
});
$("#page-last").click(function () {
if (pageindex == lastindex) {
alert('已经是最后一页了!');
}
else {
pageindex = lastindex;
loaddata();
}
})
$("#pageTo").click(function (event) {
var txtval = $("#txtIndex").val();
pageindex = txtval;
loaddata();
});
});
//..
function loaddata() {
pageindex = !pageindex ? 1 : pageindex;
$.ajax({
url: "WebServeice/PageForExamPaper.ashx?action=GetExamForPage", //调用后台方法发送请求
type: "get",
contentType: "application/json",
dataType: "json",
data: { pageindex: pageindex, pagesize: pagesize },
success: function (result) {
if (result) {
var count = result.PageCount;
if (lastindex) {
lastindex = Math.floor(count / pagesize);
if (count % pagesize > 0) {
lastindex += 1;
}
}
var data = result.data;
var biaoqian = "<table style='width:100%;'>";//动态添加数据,
biaoqian += "<tr style='text-align:center;'><td>课程名称</td><td>所属级别</td><td>添加时间</td></tr>";
$.each(data, function (name, obj) {
biaoqian += "<tr style='text-align:left;'>";
biaoqian += "<td>" + obj["Id"] + "</td>";//rowNumber中的Id,就是存储过程中的流水号!
biaoqian += "<td>" + obj["ExamName"] + "</td>";
biaoqian += "<td>" + obj["ExamTimeLimit"] + "</td>";
biaoqian += "<td>" + formatDateTime(obj["AddTime"]) + "</td>";
biaoqian += "</tr>";
});
biaoqian += "</table>";
$('#divtable').html(biaoqian);
}
},
error: function (err) {
//alert(err.status);
}
});
}
function aaa() {
$.ajax({
type: "get",
contentType: "application/json",
url: "./WebServeice/PageForExamPaper.ashx",
//data: "{pagesize:" + pagesize + "}",
data: {
action: "GetExamPageForClassPageindex",
pagesize: pagesize,
ts: new Date().getTime()
},
success: function (result) {
//alert(result);
lastindex = result;
},
error: function (err) {
alert(err.status);
}
});
}
function go(ipage) {
loaddata();
}
//这个方法是转换时间格式,因为JSON取到的是时间戳,那么要转换为时间格式。
function formatDateTime(dateTime, format) {
var dtime = new Date(dateTime);
var str = dtime.getFullYear().toString() + "-"
+ (dtime.getMonth() + 1).toString() + "-"
+ dtime.getDate().toString();
switch (format) {
case "yyyy-MM-dd HH:mm:ss":
str += " " + dtime.getHours().toString() + ":"
+ dtime.getMinutes().toString() + ":"
+ dtime.getSeconds().toString();
break;
}
return str;
}
</script>
这一块就是AJAX了
处理程序中代码:
public class PageForExamPaper : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
string action = context.Request["action"];
string result = string.Empty;
if (!string.IsNullOrEmpty(action))
{
switch (action)
{
case "GetExamPageForClassPageindex":
result = GetExamPageForClassPageindex(int.Parse(context.Request["pagesize"])).ToString();
break;
case "GetExamForPage":
result = GetExamForPage(int.Parse(context.Request["pageindex"]), int.Parse(context.Request["pagesize"]));
break;
}
}
context.Response.Write(result);
context.Response.End();
}
public string GetExamForPage(int pageindex, int pagesize)
{
int pageCount = 0;
Content.CExam cce = new Content.CExam();
DataTable dt = cce.GetaAllExamPaper(pageindex, pagesize, out pageCount);
List<Modle.exampaperModle> list = new List<Modle.exampaperModle>();
int id = 0;//获取存储过程中NUM
string examname = "";
string timelimit = "";
DateTime Addtime;
for (int i = 0; i < dt.Rows.Count; i++)
{
id = int.Parse(dt.Rows[i]["num"].ToString());//添加流水号从1开始递增
examname = dt.Rows[i]["ExamName"].ToString();
timelimit = dt.Rows[i]["ExamTimeLimit"].ToString();
Addtime = Convert.ToDateTime(dt.Rows[i]["AddTime"]);
Modle.exampaperModle ctl = new Modle.exampaperModle();
{
ctl.Id = id;
ctl.ExamName = examname;
ctl.ExamTimeLimit = int.Parse(timelimit.ToString());
ctl.AddTime = Addtime;
};
list.Add(ctl);
}
var obj = new
{
PageCount = pageCount,
data = list
};
Newtonsoft.Json.Converters.IsoDateTimeConverter timeFormat = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
timeFormat.DateTimeFormat = "yyyy-MM-dd";
string result = Newtonsoft.Json.JsonConvert.SerializeObject(obj);
return result;
}
[WebMethod]
public int GetExamPageForClassPageindex(int pagesize)
{
Content.CExam cce = new Content.CExam();
int totalcount = cce.GetExamCount();//这个方法是获取一个总数量的方法,用来查看共有多少页,一共有多少条数据!
if (totalcount % pagesize == 0)
{
return totalcount / pagesize;
}
else
{
return totalcount / pagesize + 1;
}
}
public bool IsReusable
{
get
{
return false;
}
}
}
好了,这一个模块的功能就全部完成了,希望对大家有帮助,有什么好的指点或者意见,留言一起讨论吧,谢谢!