实现数据高效分页
首先，是对于数据操作的底层类，命名为DAL.cs ，代码如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using 自写无刷新高效分页.Model;
using System.Configuration;
using System.Data;
namespace 自写无刷新高效分页
{
public class DAL
{
private int totalpages;
//创建数据库连接
private SqlConnection CreateConn()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DALDataConnection"].ConnectionString);
return conn;
}
//设置每页显示的数据条数
public int pageSize { get;set ;}
//获得总页数
public int totalPages
{
get { return totalpages; }
}
//获取总数据条数，并且为totalPages属性赋值
public int GetTotalCount()
{
using (SqlConnection conn = CreateConn())
{
using (SqlCommand cmd = new SqlCommand("select Count(学校编号) from 考研学校信息",conn))
{
conn.Open();
int count=Convert.ToInt32(cmd.ExecuteScalar());
if (pageSize > 0)
{
totalpages = (count / pageSize) + 1;
}
return count;
}
}
}
//获取指定的分页数据并存数实体数组
public SchoolInfo[] GetPagedData(int start)
{
string queryStr = "select * from(select 学校编号,学校名称,学校简介,招生院系,研究方向,考试科目,备注,复习参考书目,Row_Number()over(order by 学校编号)rownum From 考研学校信息)t where t.rownum>@startIndex and t.rownum<=@startIndex+@maxIndexRows";
SchoolInfo[] schoolInfoArray;
using (SqlConnection conn =CreateConn())
{
using (SqlCommand cmd = new SqlCommand(queryStr))
{
//添加sql查询参数startIndex、maxIndexRows
SqlParameter startIndex = new SqlParameter("startIndex", SqlDbType.Int);
SqlParameter maxIndexRows = new SqlParameter("maxIndexRows", SqlDbType.Int);
startIndex.Value = start;
maxIndexRows.Value = pageSize;
cmd.Parameters.Add(startIndex);
cmd.Parameters.Add(maxIndexRows);
cmd.Connection = conn;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds, "dt");
//将数据映射到实体中
int count=ds.Tables["dt"].Rows.Count;
schoolInfoArray = new SchoolInfo[count];
for (int i = 0; i < count; i++)
{
DataTable dt=ds.Tables["dt"];
SchoolInfo newSchoolInfo = new SchoolInfo(dt.Rows[i][0].ToString(), dt.Rows[i][1].ToString(), dt.Rows[i][2].ToString(), dt.Rows[i][3].ToString(), dt.Rows[i][4].ToString(), dt.Rows[i][5].ToString(), dt.Rows[i][6].ToString(), dt.Rows[i][7].ToString());
schoolInfoArray[i] = newSchoolInfo;
}
}
}
return schoolInfoArray;
}
}
}
第二部分是用于展示数据的后台代码：文件名为PageShow.aspx.cs ；注意这里要先为pageSize属性赋值
View Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using 自写无刷新高效分页.Model;
namespace 自写无刷新高效分页
{
public partial class PageShow : System.Web.UI.Page
{
public int TotalCounts;
public int TotalPages;
protected void Page_Load(object sender, EventArgs e)
{
DAL a = new DAL();
//这里必须先定义pageSize属性值，在调用GetTotalCount方法
a.pageSize = 5;
TotalCounts= a.GetTotalCount();
TotalPages = a.totalPages;
object pageId =Request.QueryString["pageid"];
if(pageId==null)
{
SchoolInfo[] schoolInfoArray = a.GetPagedData(0);
for (int i = 0; i < schoolInfoArray.Length; i++)
{
Response.Write(
schoolInfoArray[i].Id + "<br/>" +
schoolInfoArray[i].Name + "<br/>" +
schoolInfoArray[i].Introduce + "<br/>" +
schoolInfoArray[i].Department + "<br/>" +
schoolInfoArray[i].Direction + "<br/>" +
schoolInfoArray[i].Examitems + "<br/>" +
schoolInfoArray[i].Remark + "<br/>" +
schoolInfoArray[i].Reference + "<br/>" + "<hr/>"
);
}
}
else
{
int start = (Convert.ToInt32(pageId) - 1) * a.pageSize;
SchoolInfo[] schoolInfoArray = a.GetPagedData(start);
for (int i = 0; i < schoolInfoArray.Length; i++)
{
Response.Write(
schoolInfoArray[i].Id + "<br/>" +
schoolInfoArray[i].Name + "<br/>" +
schoolInfoArray[i].Introduce + "<br/>" +
schoolInfoArray[i].Department + "<br/>" +
schoolInfoArray[i].Direction + "<br/>" +
schoolInfoArray[i].Examitems + "<br/>" +
schoolInfoArray[i].Remark + "<br/>" +
schoolInfoArray[i].Reference + "<br/>" + "<hr/>"
);
}
}
}
}
}
第三部分是前台页面，用JS输出数据总条数，以及页数：
View Code
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="PageShow.aspx.cs" Inherits="自写无刷新高效分页.PageShow" %>
<!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>
<script src="Scripts/jquery-1.4.1.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">
$(function () {
var totalCount=<%=TotalCounts %>;
var totalPages=<%=TotalPages %>;
$("#pagedDiv").append("<label>总共有"+totalCount+"条记录 ");
for(var i=1;i<=totalPages;i++){
$("#pagedDiv").append("<a href='PageShow.aspx?pageid="+i+"'>"+i+"</a> ");
}
})
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<div id="pagedDiv"></div>
</div>
</form>
</body>
</html>
