AJAX+JQUERRY实现分页
============== Windows Phone 7手机开发、.Net培训、期待与您交流！ =============
JQERRY代码如下：
var where = ""; var ajaxurl = ""; $().ready(function () { var indexs = 1; if ($("#hdwhere").val() == null) { } else { where = $("#hdwhere").val(); } ajaxurl = "ashx/Pages.ashx"; Init(indexs); $(".UpPage").click(function () { if (indexs > 1) { indexs-- } Init(indexs); }); $(".DownPage").click(function () { if (indexs < $("#lbPages").text()) {//判断小于总页数 indexs++; } Init(indexs); }); $(".FirstPage").click(function () { Init(1); }); $(".LastPage").click(function () { Init($("#lbPages").text()); }); }); function Init(ind) { $.ajax({ type: "GET", dataType: "json", url: ajaxurl, //目标地址(页面代码会在下面呈上) data: { "pageIndex": ind, "where": where }, //要发送的数据 beforeSend: function () { // alert("准备发送"); }, success: function (json) { var result = json.ShowData; var tbody = ""; // $(".block_topic_content").html(""); var $str; $(".block_topic_content").remove(); $.each(result, function (i, n) { alert(i); $str = $("<tr class='block_topic_content'><td class='persontablerow'>" + n.EmployeeID + "</td><td class='persontablerow'>" + n.FName + "</td><td class='persontablerow'>" + n.FDepartName + "</td><td class='persontablerow'>" + n.FPosition + "</td><td class='persontablerow'>" + n.FPhoneNum + "</td><td class='persontablerow'><a href='#'>查看</a> | <a href='#'>修改</a> | <a href='#'>删除</a></td></tr>"); $(".block_topic_title").after($str); }); $("#lbNowPage").text(ind); }, complete: function (data, textStatus) { //HideLoading(); }, error: function (data, textStatus) { //请求出错处理 } }); }
HTML页面：
<table width="96%" border="0" cellspacing="0" cellpadding="0"> <tr class="block_topic_title"> <td width="15%" class="persontable">员工编号</td> <td width="15%" class="persontable">姓名</td> <td width="15%" class="persontable">部门</td> <td width="15%" class="persontable">职务</td> <td width="20%" class="persontable">联系方式</td> <td width="20%" class="persontable">操作</td> </tr> <tr> <td class="persontablebottom" colspan="6><div style="float:left;margin-left:20px;"><a href="javascript:void(0);" class="FirstPage">首页</a></div><div style="float:left;margin-left:20px;"><a href="javascript:void(0);" class="UpPage">上一页</a></div><div style="float:left;margin-left:20px;"><a href="javascript:void(0);" class="DownPage">下一页</a></div><div style="float:left;margin-left:20px;"><a href="javascript:void(0);" class="LastPage">尾页</a></div></td> </tr> </table>
ashx页面代码：
private string constr = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["OADBConnectionString"].ConnectionString;//数据库连接字符串 string where = ""; public void ProcessRequest(HttpContext context) { //去掉页面缓存 context.Response.Buffer = true; context.Response.ExpiresAbsolute = DateTime.Now.AddDays(-1); context.Response.AddHeader("pragma", "no-cache"); context.Response.AddHeader("cache-control", ""); context.Response.CacheControl = "no-cache"; context.Response.ContentType = "text/plain"; context.Response.Charset = "UTF-8"; where = context.Request.Params["where"].ToString(); int pageindex = string.IsNullOrEmpty(context.Request.Params["pageIndex"].ToString()) ? 1 : Convert.ToInt32(context.Request.Params["pageIndex"].ToString()); DataSet ds = GetList(pageindex, where); string jsonData = DataTableToJSON(ds.Tables[0], "ShowData"); //输入json格式数据 context.Response.Write(jsonData); } public bool IsReusable { get { return false; } } /// <summary> /// 分页获取数据列表 /// </summary> private DataSet GetList(int pageindex, string where) { //创建数据库连接池 SqlConnection co = new SqlConnection(constr); //打开连接池 co.Open(); SqlCommand commands; commands = new SqlCommand("select count(*) from T_Persons", co); //数据总记录数 int totalcounts = Convert.ToInt32(commands.ExecuteScalar()); //自定义每页大小为5条数据 int pagesize = Convert.ToInt32(ConfigurationManager.AppSettings["pagesize"].ToString()); ; //总记录数 int totalpages; if (totalcounts % pagesize > 0) totalpages = totalcounts / pagesize + 1; else totalpages = totalcounts / pagesize; if (pageindex > totalpages) pageindex = totalpages; DataSet ds = new DataSet(); commands = new SqlCommand("P_Page " + pageindex + ", " + pagesize + ", 'FDepartName=" + where + "','T_Persons', true,'EmployeeID'", co); SqlDataAdapter adapter = new SqlDataAdapter(commands); adapter.Fill(ds, "ds"); co.Dispose(); return ds; } /// <summary> /// 数据表转换成JSON字符 /// </summary> /// <param name="dt">数据表对象</param> /// <param name="dtName">数据表名称</param> public static string DataTableToJSON(DataTable dt, string dtName) { StringBuilder sb = new StringBuilder(); StringWriter sw = new StringWriter(sb); using (JsonWriter jw = new JsonTextWriter(sw)) { JsonSerializer ser = new JsonSerializer(); jw.WriteStartObject(); jw.WritePropertyName(dtName); jw.WriteStartArray(); if (dt != null) { foreach (DataRow dr in dt.Rows) { jw.WriteStartObject(); foreach (DataColumn dc in dt.Columns) { jw.WritePropertyName(dc.ColumnName); ser.Serialize(jw, dr[dc].ToString()); } jw.WriteEndObject(); } } jw.WriteEndArray(); jw.WriteEndObject(); sw.Close(); jw.Close(); } return sb.ToString(); }
注意JSON数据处理引用的是：Newtonsoft.Json.dll。
分页存储过程：
ALTER PROCEDURE [dbo].[P_Page] ( @startIndex INT, --当前页码 @pageSize INT, --每页多少条数据 @strSql varchar(5000), ---查询条件不用加where,例：id>10 @TableName varchar(50), --表名 @DoCount AS bit=1, -- 0值返回记录总数, 非 0 值则返回记录 @keyword varchar(50) --排序字段 ) AS begin tran IF @DoCount=0 Goto GetCount Else Goto GetSearch GetCount: --返回记录总数 DECLARE @SearchSql AS Nvarchar(4000) SET @SearchSql= 'SELECT Count(*) AS Total FROM '+@TableName+' WHERE ' +@strsql exec sp_executesql @SearchSql --print @SearchSql COMMIT TRAN return GetSearch: --返回记录 DECLARE @SqlQuery varchar(4000) SET @SqlQuery='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY O.'+@keyword+' ) Row, * from '+@TableName+' O Where '+@strsql+') as temp WHERE Row BETWEEN '+cast((@startIndex-1)*@pageSize+1 as varchar) +' and '+cast(@startIndex*@pageSize as varchar)+' and '+ @strsql +' order by '+@keyword + ' desc' --print @SqlQuery execute(@SqlQuery) COMMIT TRAN
=============== Windows Phone 7手机开发、.Net培训、期待与您交流！ ==================
