jpager.js：
View Code
$(function () {
$.post("/GetData.aspx", null, function (data) {
var total = data;
PageClick(1, total, 3);
});
PageClick = function (pageIndex, total, spanInterval) {
$.ajax({
url: "/GetData.aspx",
data: "pageindex=" + pageIndex,
type: "POST",
cache: false,
dataType: "json",
success: function (data) {
//索引从1开始
//将当前页索引转为int类型
var intPageIndex = parseInt(pageIndex);
//获取显示数据的表格
var table = $("#content");
//清楚表格中内容
$("#content tr").remove();
//向表格中添加内容
for (var i = 0; i < data.length - 1; i++) {
table.append(
$("<tr><td>" +
data[i].id
+ "</td><td>" +
data[i].uname
+ "</td><td>" +
data[i].pwd
+ "</td><td>" +
data[i].cname
+ "</td></tr>")
);
}
//创建分页
//将总记录数结果 得到 总页码数
JsonHelper.cs：
View Code
using System;
using System.Collections.Generic;
using System.Web;
using System.Collections;
using System.Text;
/// <summary>
///JsonHelper 的摘要说明
/// </summary>
public class JsonHelper
{
//对应JSON的singleInfo成员
public string singleInfo = string.Empty;
protected string _error = string.Empty;
protected bool _success = true;
protected long _totalCount = 0;
protected System.Collections.ArrayList arrData = new ArrayList();
protected System.Collections.ArrayList arrDataItem = new ArrayList();
public JsonHelper()
{
}
//public static string ToJSON(object obj)
//{
// JavaScriptSerializer serializer = new JavaScriptSerializer();
// return serializer.Serialize(obj);
//}
//public static string ToJSON(object obj, int recursionDepth)
//{
// JavaScriptSerializer serializer = new JavaScriptSerializer();
// serializer.RecursionLimit = recursionDepth;
// return serializer.Serialize(obj);
//}
//对应于JSON的success成员
public bool success
{
get
{
return _success;
}
set
{
//如设置为true则清空error
if (success) _error = string.Empty;
_success = value;
}
}
//对应于JSON的error成员
public string error
{
get
{
return _error;
}
set
{
//如设置error，则自动设置success为false
if (value != "") _success = false;
_error = value;
}
}
public long totlalCount
{
get { return _totalCount; }
set { _totalCount = value; }
}
//重置，每次新生成一个json对象时必须执行该方法
public void Reset()
{
_success = true;
_error = string.Empty;
singleInfo = string.Empty;
arrData.Clear();
arrDataItem.Clear();
}
public void AddItem(string name, string value)
{
arrData.Add("\"" + name + "\":" + "\"" + value + "\"");
}
public void ItemOk()
{
arrData.Add("<BR>");
totlalCount++;
}
//序列化JSON对象，得到返回的JSON代码
public string ToString(int count)
{
StringBuilder sb = new StringBuilder();
//sb.Append("{");
//sb.Append("totalCount:" + totlalCount.ToString() + ",");
//sb.Append("success:" + _success.ToString().ToLower() + ",");
//sb.Append("error:\"" + _error.Replace("\"", "\\\"") + "\",");
//sb.Append("singleInfo:\"" + singleInfo.Replace("\"", "\\\"") + "\",");
sb.Append("[{");
int index = 0;
//sb.Append("{");
if (arrData.Count <= 0)
{
sb.Append("]");
}
else
{
foreach (string val in arrData)
{
index++;
if (val != "<BR>")
{
sb.Append(val + ",");
}
else
{
sb = sb.Replace(",", "", sb.Length - 1, 1);
sb.Append("},");
if (index < arrData.Count)
{
sb.Append("{");
}
}
}
sb = sb.Replace(",", "", sb.Length - 1, 1);
// sb.Append("]");
}
sb.Append(",{\"total\":" + count + "}");
sb.Append("]");
return sb.ToString();
}
}
SqlHelper.cs：
View Code
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
public class SqlHelper
{
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
try
{
return ExecuteNonQuery(ConnectionString, cmdType, cmdText, commandParameters);
}
catch
{
throw;
}
}
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
try
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
catch
{
throw;
}
}
public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
try
{
return ExecuteReader(ConnectionString, cmdType, cmdText, commandParameters);
}
catch
{
throw;
}
}
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
try
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
throw;
}
}
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader();
cmd.Parameters.Clear();
return rdr;
}
catch
{
throw;
}
}
public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
try
{
return ExecuteScalar(ConnectionString, cmdType, cmdText, commandParameters);
}
catch
{
throw;
}
}
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
try
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
catch
{
throw;
}
}
public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
catch
{
throw;
}
}
public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
catch
{
throw;
}
}
public static DataTable ExecuteTable(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
try
{
return ExecuteTable(ConnectionString, cmdType, cmdText, commandParameters);
}
catch
{
throw;
}
}
public static DataTable ExecuteTable(CommandType cmdType, string cmdText, int pageindex,int pagesize,params SqlParameter[] commandParameters)
{
try
{
return ExecuteTable(ConnectionString, cmdType, cmdText, pageindex,pagesize,commandParameters);
}
catch
{
throw;
}
}
public static DataTable ExecuteTable(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
try
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
SqlDataAdapter ap = new SqlDataAdapter();
ap.SelectCommand = cmd;
DataSet st = new DataSet();
ap.Fill(st, "Result");
cmd.Parameters.Clear();
return st.Tables["Result"];
}
}
catch
{
throw;
}
}
public static DataTable ExecuteTable(string connectionString, CommandType cmdType, string cmdText,int pageindex,int pagesize, params SqlParameter[] commandParameters)
{
try
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
SqlDataAdapter ap = new SqlDataAdapter();
ap.SelectCommand = cmd;
DataSet st = new DataSet();
ap.Fill(st,pageindex-1,pagesize,"Result");
cmd.Parameters.Clear();
return st.Tables["Result"];
}
}
catch
{
throw;
}
}
public static DataTable ExecuteTable(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
SqlDataAdapter ap = new SqlDataAdapter();
ap.SelectCommand = cmd;
DataSet st = new DataSet();
ap.Fill(st, "Result");
cmd.Parameters.Clear();
return st.Tables["Result"];
}
catch
{
throw;
}
}
public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
{
try
{
parmCache[cacheKey] = commandParameters;
}
catch
{
throw;
}
}
public static SqlParameter[] GetCachedParameters(string cacheKey)
{
try
{
SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
if (cachedParms == null)
return null;
SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
return clonedParms;
}
catch
{
throw;
}
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
try
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
catch
{
throw;
}
}
public static readonly string ConnectionString = "Data Source=.;Database=duyia;Uid=sa;Pwd=sa";
}
default.css
View Code
.tbcss{ width:100%; text-align:center; border: solid 1px #eee; border-collapse:collapse;}
.tbcss td{ height:24px; line-height:24px; border: solid 1px #eee;}
DIV.yahoo {
PADDING-RIGHT: 3px; PADDING-LEFT: 3px; FONT-SIZE: 0.85em; PADDING-BOTTOM: 3px; MARGIN: 3px; PADDING-TOP: 3px; FONT-FAMILY: Tahoma,Helvetica,sans-serif; TEXT-ALIGN: center
}
DIV.yahoo A {
BORDER-RIGHT: #ccdbe4 1px solid; PADDING-RIGHT: 8px; BACKGROUND-POSITION: 50% bottom; BORDER-TOP: #ccdbe4 1px solid; PADDING-LEFT: 8px; PADDING-BOTTOM: 2px; BORDER-LEFT: #ccdbe4 1px solid; COLOR: #0061de; MARGIN-RIGHT: 3px; PADDING-TOP: 2px; BORDER-BOTTOM: #ccdbe4 1px solid; TEXT-DECORATION: none
}
DIV.yahoo A:hover {
BORDER-RIGHT: #2b55af 1px solid; BORDER-TOP: #2b55af 1px solid; BACKGROUND-IMAGE: none; BORDER-LEFT: #2b55af 1px solid; COLOR: #fff; BORDER-BOTTOM: #2b55af 1px solid; BACKGROUND-COLOR: #3666d4
}
DIV.yahoo A:active {
BORDER-RIGHT: #2b55af 1px solid; BORDER-TOP: #2b55af 1px solid; BACKGROUND-IMAGE: none; BORDER-LEFT: #2b55af 1px solid; COLOR: #fff; BORDER-BOTTOM: #2b55af 1px solid; BACKGROUND-COLOR: #3666d4
}
DIV.yahoo SPAN.current {
PADDING-RIGHT: 6px; PADDING-LEFT: 6px; FONT-WEIGHT: bold; PADDING-BOTTOM: 2px; COLOR: #000; MARGIN-RIGHT: 3px; PADDING-TOP: 2px
}
DIV.yahoo SPAN.disabled {
BORDER-RIGHT: #ccdbe4 1px solid; PADDING-RIGHT: 8px; BACKGROUND-POSITION: 50% bottom; BORDER-TOP: #ccdbe4 1px solid; PADDING-LEFT: 8px; PADDING-BOTTOM: 2px; BORDER-LEFT: #ccdbe4 1px solid; COLOR: #0061de; MARGIN-RIGHT: 3px; PADDING-TOP: 2px; BORDER-BOTTOM: #ccdbe4 1px solid; TEXT-DECORATION: none
}
DIV.yahoo A.next {
BORDER-RIGHT: #ccdbe4 2px solid; BORDER-TOP: #ccdbe4 2px solid; MARGIN: 0px 0px 0px 10px; BORDER-LEFT: #ccdbe4 2px solid; BORDER-BOTTOM: #ccdbe4 2px solid
}
DIV.yahoo A.next:hover {
BORDER-RIGHT: #2b55af 2px solid; BORDER-TOP: #2b55af 2px solid; BORDER-LEFT: #2b55af 2px solid; BORDER-BOTTOM: #2b55af 2px solid
}
DIV.yahoo A.prev {
BORDER-RIGHT: #ccdbe4 2px solid; BORDER-TOP: #ccdbe4 2px solid; MARGIN: 0px 10px 0px 0px; BORDER-LEFT: #ccdbe4 2px solid; BORDER-BOTTOM: #ccdbe4 2px solid
}
DIV.yahoo A.prev:hover {
BORDER-RIGHT: #2b55af 2px solid; BORDER-TOP: #2b55af 2px solid; BORDER-LEFT: #2b55af 2px solid; BORDER-BOTTOM: #2b55af 2px solid
}
GetData.aspx.cs
View Code
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;
public partial class GetData : System.Web.UI.Page
{
private string table = "user_info";
private int pagesize = 10;
private string files = "id,uname,pwd,cname";
private string orderstr = "id asc";
protected void Page_Load(object sender, EventArgs e)
{
Response.ContentType = "text/plain";
string pageindex = "1";
try
{
pageindex=Request.Params["pageindex"].ToString();
}
catch
{
}
StringBuilder sb = new StringBuilder();
SqlParameter[] parm ={
new SqlParameter("@TableName",table),
new SqlParameter("@Fields",files),
new SqlParameter("@OrderField",orderstr),
new SqlParameter("@sqlWhere",sb.ToString()),
new SqlParameter("@pageSize",pagesize),
new SqlParameter("@pageIndex",pageindex),
new SqlParameter("@parm","0")
};
DataTable dt = SqlHelper.ExecuteTable(CommandType.StoredProcedure, "proc_pager", parm);
parm[6].Value = "1";
int totalRecord = Convert.ToInt32(SqlHelper.ExecuteTable(CommandType.StoredProcedure, "proc_pager", parm).Rows[0][0]);
int pagecount = Convert.ToInt32(Math.Ceiling((totalRecord + 0.0) / pagesize));
JsonHelper json = new JsonHelper();
foreach (DataRow dr in dt.Rows)
{
json.AddItem("id", dr["id"].ToString());//对于前台的输出
json.AddItem("uname", dr["uname"].ToString());//对于前台的输出
json.AddItem("pwd", dr["pwd"].ToString());//对于前台的输出
json.AddItem("cname", dr["cname"].ToString());//对于前台的输出
json.ItemOk();
}
Response.Write(json.ToString(pagecount));
}
}
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="/css/default.css" rel="stylesheet" type="text/css" />
<script src="/js/jquery-1.4.1.min.js" type="text/javascript"></script>
<script src="/js/jpager.js" type="text/javascript"></script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table id="content" class="tbcss"></table>
<div id="pager" class="yahoo"></div>
</div>
</form>
</body>
</html>