Jquery+Ajax+Json+存储过程实现高效分页
程序员文章站
2023-12-10 23:32:34
之前在做分页时,很多朋友都是用jquery分页插件,之前我就用的jquery.paper,有需要的朋友可以联系我,接下来小编给大家分享用jquery+ajax+json+存...
之前在做分页时,很多朋友都是用jquery分页插件,之前我就用的jquery.paper,有需要的朋友可以联系我,接下来小编给大家分享用jquery+ajax+json+存储过程实现高效分页。
实现此功能用分页存储过程,pagination,js样式,废话不多了,具体请看下面代码
分页存储过程:pagination
create procedure [dbo].[pagination] @feilds varchar(),--要显示的字段 @page_index int,--当前页码 @page_size int,--页面大小 @ordertype bit,--当为时 则为 desc 当为 时 asc @andwhere varchar()='',--where语句 不用加where @orderfeild varchar(), --排序的字段 @tablename varchar() --查询的表明 as declare @execsql varchar() declare @orderstr varchar() declare @orderby varchar() begin set nocount on if @ordertype = begin set @orderstr = ' > ( select max(['+@orderfeild+'])' set @orderby = 'order by '+@orderfeild+' asc' end else begin set @orderstr = ' < ( select min(['+@orderfeild+'])' set @orderby = 'order by '+@orderfeild+' desc' end if @page_index = --当页码是第一页时直接运行,提高速度 begin if @andwhere='' set @execsql = 'select top '+str(@page_size)+' '+@feilds+' from '+@tablename+' '+@orderby else set @execsql = 'select top '+str(@page_size)+' '+@feilds+' from '+@tablename+' where '+@andwhere+' '+ @orderby end else begin if @andwhere='' begin --以子查询结果当做新表时 要给表名别名才能用 set @execsql = 'select top'+str(@page_size)+' '+@feilds+' from '+@tablename+' where '+@orderfeild+ @orderstr+' from (select top '+str(@page_size*(@page_index-))+' '+@orderfeild+ ' from '+@tablename+' '+@orderby+') as temp) '+ @orderby end else begin set @execsql = 'select top'+str(@page_size)+' '+@feilds+' from '+@tablename+' where '+@orderfeild+ @orderstr+' from (select top '+ str(@page_size*(@page_index-))+' '+@orderfeild+ ' from '+@tablename+' where '+@andwhere+' '+@orderby+') as temp) and '+@andwhere+' '+ @orderby end end exec (@execsql)--这里要加括号 end
分页样式:
<style type="text/css"> a,area { -moz-outline-style: none; blr:expression(this.onfocus=this.blur()); text-decoration:none} div.badoo { padding:px; text-align:center; } div.badoo a { border:px solid #ededed; padding:px px; color:#; border-radius:px; margin-right:px;} div.badoo a:hover {border:px solid #ffa; color: #ffa; } div.badoo a:active {border:px solid #ffa; margin-right:px;} div.badoo span { border:px solid #ededed; padding:px px; color:#f;font-weight:bold; background:#fafafa; border-radius:px; margin-right:px;} div.badoo span.disabled { border:px solid #ededed; padding:px px; color:#; margin-right:px; font-weight:;} </style>
首先创建一般处理程序,读取数据库中内容,得到返回值.
创建文件,getdata.ashx.
我这里是用的存储过程,存储过程会再下面粘出来,至于数据只是实例,你们可根据需求自行读取数据
using system; using system.web; using system.data.sqlclient; using system.data; using system.collections.generic; using system.web.script.serialization; using model; context.response.contenttype = "text/plain"; var pageindex = context.request["pageindex"]; //判断当前索引存不存在,如果不存在则获取记录的总数。 if (string.isnullorempty(pageindex)) { //获取查询记录总数的sql语句 int count = ; int.tryparse(new bll.t_profit().selectallnum(), out count); context.response.write(count); context.response.end(); } //当根据索引获取数据 else { int currentpageindex = ; int.tryparse(pageindex, out currentpageindex); sqlparameter[] parms = new sqlparameter[] { new sqlparameter("@feilds",sqldbtype.nvarchar,), new sqlparameter("@page_index",sqldbtype.int,), new sqlparameter("@page_size",sqldbtype.int,), new sqlparameter("@ordertype",sqldbtype.int,), new sqlparameter("@andwhere",sqldbtype.varchar,), new sqlparameter("@orderfeild",sqldbtype.varchar,) }; parms[].value = "id,name,sex,tel";//获取所有的字段 parms[].value = pageindex;//当前页面索引 parms[].value = ;//页面大小 parms[].value = ;//升序排列 parms[].value = "";//条件语句 parms[].value = "id";//排序字段 list<book> list = new list<book>(); using (sqldatareader sdr = yoodor.dal.sqlhelper.executereader(commandtype.storedprocedure, "pagination", parms)) { while (sdr.read()) { list.add(new book { id = sdr[].tostring(), name = sdr[].tostring(), sex = sdr[].tostring(), tel = sdr[].tostring() }); } } context.response.write(new javascriptserializer().serialize(list).tostring());//转为json格式 } public string id { get; set; } public string name { get; set; } public string sex { get; set; } public string tel { get; set; }
样式代码:
<style type="text/css"> a,area { -moz-outline-style: none; blr:expression(this.onfocus=this.blur()); text-decoration:none} div.badoo { padding:px; text-align:center; } div.badoo a { border:px solid #ededed; padding:px px; color:#; border-radius:px; margin-right:px;} div.badoo a:hover {border:px solid #ffa; color: #ffa; } div.badoo a:active {border:px solid #ffa; margin-right:px;} div.badoo span { border:px solid #ededed; padding:px px; color:#f;font-weight:bold; background:#fafafa; border-radius:px; margin-right:px;} div.badoo span.disabled { border:px solid #ededed; padding:px px; color:#; margin-right:px; font-weight:;} </style>
js代码
<script type="text/javascript"> $(function () { $.post("getdata.ashx", null, function (data) { var total = data; pageclick(, total, ); }); pageclick = function (pageindex, total, spaninterval) { $.ajax({ url: "getdata.ashx", data: { "pageindex": pageindex }, type: "post", datatype: "json", success: function (data) { //索引从开始 //将当前页索引转为int类型 var intpageindex = parseint(pageindex); //获取显示数据的表格 var table = $("#content"); //清楚表格中内容 $("#content tr").remove(); //向表格中添加内容 for (var i = ; i < data.length; i++) { table.append( $("<tr><td>" + data[i].id + "</td><td>" + data[i].name + "</td><td>" + data[i].sex + "</td><td>" + data[i].tel + "</td></tr>") ); } //for //创建分页 //将总记录数结果 得到 总页码数 var pages = total if (pages % == ) pages = pages / ; else pages = parseint(total / ) + ; var $pager = $("#pager"); //清楚分页div中的内容 $("#pager span").remove(); $("#pager a").remove(); //添加第一页 if (intpageindex == ) { // $pager.append("<span class='disabled'>第一页</span>"); } else { // var first = $("<a href='javascript:void()' first='" + + "'>第一页</a>").click(function () { // pageclick($(this).attr('first'), total, spaninterval); // return false; // }); // $pager.append(first); } //添加上一页 if (intpageindex == ) $pager.append("<span class='disabled'>上一页</span>"); else { var pre = $("<a href='javascript:void()' pre='" + (intpageindex - ) + "'>上一页</a>").click(function () { pageclick($(this).attr('pre'), total, spaninterval); return false; }); $pager.append(pre); } //设置分页的格式 这里可以根据需求完成自己想要的结果 var interval = parseint(spaninterval); //设置间隔 var start = math.max(, intpageindex - interval); //设置起始页 var end = math.min(intpageindex + interval, pages)//设置末页 if (intpageindex < interval + ) { end = ( * interval + ) > pages ? pages : ( * interval + ); } if ((intpageindex + interval) > pages) { start = (pages - * interval) < ? : (pages - * interval); } //生成页码 for (var j = start; j < end + ; j++) { if (j == intpageindex) { var spanselectd = $("<span class='current'>" + j + "</span>"); $pager.append(spanselectd); } //if else { var a = $("<a href='javascript:void()'>" + j + "</a>").click(function () { pageclick($(this).text(), total, spaninterval); return false; }); $pager.append(a); } //else } //for //上一页 if (intpageindex == math.ceil(total / )) { $pager.append("<span class='disabled'>下一页</span>"); } else { var next = $("<a href='javascript:void()' next='" + (intpageindex + ) + "'>下一页</a>").click(function () { pageclick($(this).attr("next"), total, spaninterval); return false; }); $pager.append(next); } //最后一页 if (intpageindex == pages) { // $pager.append("<span class='disabled'>最后一页</span>"); } else { // var last = $("<a href='javascript:void()' last='" + pages + "'>最后一页</a>").click(function () { // pageclick($(this).attr("last"), total, spaninterval); // return false; // }); // $pager.append(last); } } //sucess }); //ajax }; //function }); //ready </script> <table id="content"> </table> <div id="pager" class="badoo"></div> /// <summary> /// execute a sqlcommand that returns a resultset against the database specified in the connection string /// using the provided parameters. /// </summary> /// <param name="connectionstring">一个有效的数据库连接字符串</param> /// <param name="cmdtype">sqlcommand命令类型 (存储过程, t-sql语句, 等等。)</param> /// <param name="cmdtext">存储过程的名字或者 t-sql 语句</param> /// <param name="commandparameters">以数组形式提供sqlcommand命令中用到的参数列表</param> /// <returns>a sqldatareader containing the results</returns> public static sqldatareader executereader( commandtype cmdtype, string cmdtext, params sqlparameter[] commandparameters) { sqlcommand cmd = new sqlcommand(); sqlconnection conn = new sqlconnection(connectionstring); // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandbehaviour.closeconnection will not work try { preparecommand(cmd, conn, null, cmdtype, cmdtext, commandparameters); sqldatareader rdr = cmd.executereader(commandbehavior.closeconnection); cmd.parameters.clear(); return rdr; } catch (exception ex) { conn.close(); throw new exception(ex.message); } }
以上就是本文使用jquery+ajax+json+存储过程实现高效分页的全部内容,希望大家喜欢。