<!DOCTYPE html>
<head>
<title>无标题页</title>
<script src="javsscript/jquery-1.7.1.js" type="text/javascript"></script>
<script type="text/javascript">
var page = 1;
var pagesize = 5;
var s_name;
$(function() {
//------查询
$("#search").click(function() {
page = 1;
s_name = $("#txtSearch").val();
getAjaxData(s_name, page, pagesize);
});
});
//------封装分页方法开始
function getAjaxData(strwhere, page, pagesize) {
$.ajax({
type: "get",
url: "demo7.ashx",
datatype: "json",
contentType: "application/json;charset=utf-8",
//data:{ page: page, pagesize: pagesize,name: strwhere }, 或：
data: "name=" + strwhere + "&page=" + page + "&pagesize=" + pagesize,
success: function(data) {
var htmlStr = "";
htmlStr += "<table>"
htmlStr += "<thead>"
htmlStr += "<tr><td>编号</td><td>姓名</td><td>年龄</td><td>备注</td></tr>"
htmlStr += "</thead>";
htmlStr += "<tbody>"
for (var i = 0; i < data.userlist.length; i++) {
htmlStr += "<tr>";
htmlStr += "<td>" + data.userlist[i].userid + "</td>"
+ "<td>" + data.userlist[i].username + "</td>"
+ "<td>" + data.userlist[i].userage + "</td>"
+ "<td>" + data.userlist[i].userdesc + "</td>"
htmlStr += "</tr>";
}
//如果查询出得结果总数小于pagesize则用空行填充
if (data.userlist.length < pagesize) {
for (var i = 0; i < pagesize - data.userlist.length; i++)
{
htmlStr += "<tr><td style='height:28px;'></td></tr>";
}
}
htmlStr += "</tbody>";
htmlStr += "<tfoot>";
htmlStr += "<tr>";
htmlStr += "<td colspan='4'>";
htmlStr += "<span>总:" + data.totalcount + "条 当前:" + page + "/<span id='count'>" + (data.totalcount % pagesize == 0 ? parseInt(data.totalcount / pagesize) : parseInt(data.totalcount / pagesize + 1)) + "</span>页" + "</span>";
htmlStr += "<a href='javascript:GoToFirstPage()'id='aFirstPage' >首 页</a>";
htmlStr += "<a href='javascript:GoToPrePage()' id='aPrePage' >上一页</a>";
htmlStr += "<a href='javascript:GoToNextPage()' id='aNextPage'>下一页</a>";
htmlStr += "<a href='javascript:GoToEndPage()' id='aEndPage' >尾 页</a>";
htmlStr += "<input type='text' /><input type='button' value='跳转' onclick='GoToAppointPage(this)' /> ";
htmlStr += "</td>";
htmlStr += "</tr>";
htmlStr += "</tfoot>";
htmlStr += "</table>";
$("#userlist").html(htmlStr);
},
error: function(error) {
alert(error);
}
});
}
//--------封装分页方法结束
//首页
function GoToFirstPage() {
page = 1;
getAjaxData(s_name, page, pagesize);
}
//前一页
function GoToPrePage() {
page = page-1<= 0 ? 1 : page-1;
getAjaxData(s_name, page, pagesize);
}
//后一页
function GoToNextPage() {
if (page + 1 <= parseInt($("#count").text())) {
page=page+1;
}
getAjaxData(s_name, page, pagesize);
}
//尾页
function GoToEndPage() {
page = parseInt($("#count").text());
getAjaxData(s_name, page, pagesize);
}
//跳转
function GoToAppointPage(e) {
var page_goto = $(e).prev().val();
if (isNaN(page_goto)) {
alert("请输入数字!");
}
else {
var tempPageIndex = page;
page = parseInt($(e).prev().val());
if (page < 0 || page >parseInt($("#count").text())) {
page = tempPageIndex;
alert("请输入有效的页面范围!");
}
else {
getAjaxData(s_name, page_goto, pagesize);
}
}
}
</script>
<!--简单样式 -->
<style type="text/css">
#userlist{border:1px solid gray; width:500px; height:215px;}
#userlist table{border-collapse: collapse; width:100%; height:auto;background:CFCFCF;}
#userlist table thead{ margin:0; padding:0; background-color:#CCCCCC;text-align:center; height:30px; line-height:30px;}
#userlist tbody tr{ height:28px; line-height:28px; text-align:center; }
#userlist tfoot tr{ height:30px; background:#CCCCCC;line-height:30px; text-align:center;}
#userlist tfoot tr td a{ text-decoration:none; margin:4px;}
#userlist tfoot tr td a:hover{ text-decoration:underline; color:Red;}
#userlist tfoot tr td input{ height:20px; width:40px; margin:4px;}
#userlist tfoot tr td input[type=text]{ background:white; border:0;}
#userlist tfoot tr td input[type=button]{ border:1px dashed; position:relative;top:0px;top:2px\0; cursor:pointer;}
</style>
</head>
<body>
用户姓名：<input type="text" name="name" id="txtSearch" /><input type="button" id="search"
value="查询" /><br />
<div id="userlist">
</div>
</body>
</html>
---------------后台代码：
<%@ WebHandler Language="C#" Class="demo7" %>
using System;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;
using System.Web.Script.Serialization;
public class demo7 : IHttpHandler {
public void ProcessRequest (HttpContext context) {
//context.Response.ContentType = "text/plain";
context.Response.ContentType = "application/json";
string username = context.Request.Params["name"].ToString();
//string username = context.Request.QueryString["name"].ToString();
int page = Convert.ToInt32(context.Request.Params["page"].ToString());
int pagesize = Convert.ToInt32(context.Request.Params["pagesize"].ToString());
//链接数据库
//string strcon ="server=WIN-B36NXMUXT0K\MSSQL;user ID=user;password=user;database=test";
//或读取webconfing
string strcon = ConfigurationManager.AppSettings["pubsConnectionString"].ToString();
SqlConnection con = new SqlConnection(strcon);
con.Open();
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "proc_searchuser";
SqlParameter[] par = new SqlParameter[] {
new SqlParameter("@username",SqlDbType.VarChar,12),
new SqlParameter("@page",SqlDbType.Int),
new SqlParameter("@pagesize",SqlDbType.Int),
new SqlParameter("@totalcount",SqlDbType.Int)
};
par[0].Value = username;
par[1].Value = page;
par[2].Value = pagesize;
par[3].Direction = ParameterDirection.Output;
com.Parameters.AddRange(par);
//int res = com.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
int totalcount = Convert.ToInt32(par[3].Value.ToString());
com.Dispose();
con.Close();
string json = ObjectToJSON(DataTableToList(ds.Tables[0]));
string resultjson = "{\"totalcount\":"+totalcount+",\"userlist\":"+json+"}";
context.Response.Write(resultjson);
}
//datatable转换为list
public static List<Dictionary<string, object>> DataTableToList(DataTable dt)
{
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow dr in dt.Rows)
{
Dictionary<string, object> dic = new Dictionary<string, object>();
foreach (DataColumn dc in dt.Columns)
{
dic.Add(dc.ColumnName, dr[dc.ColumnName]);
}
list.Add(dic);
}
return list;
}
//系列化json
public static string ObjectToJSON(object obj){
JavaScriptSerializer jss =new JavaScriptSerializer();
try
{
return jss.Serialize(obj);
}
catch(Exception ex)
{
throw new Exception("JSONHelper.ObjectToJSON(): "+ ex.Message);
}
}
public bool IsReusable {
get {
return false;
}
}
}
//----------分页存储过程----------------------
create proc [dbo].[proc_searchuser]
(
@username varchar(12),
@page int=1,
@pagesize int=10,
@totalcount int output
)
as
declare @totalsql nvarchar(200)
declare @sql varchar(4000)
if(ISNULL(@username,'')<>'')
begin
set @totalsql='select @totalcount=COUNT(*) from userinfo where username like ''%'+@username+'%'''
end
else
begin
set @totalsql='select @totalcount=COUNT(*) from userinfo'
end
exec sp_executesql @totalsql,N'@totalcount int output',@totalcount output
-------------分页--------------
if @page<=0 set @page=1
set @sql='select * from (select ROW_NUMBER() over(order by userid)rowNO,* from userinfo where username like ''%'+@username+'%'')U
where U.rowNo BETWEEN '+str((@page-1)*@pagesize+1)+' AND ' +str(@page*@pagesize)
exec (@sql)
