ASP .NET MYSQL 的简单分页 并不适用于所有SQL语句

精贴 置顶
5160 0

\\DButils.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
namespace mysql.db
{
public class DButils
{
private string configString = null;
public DButils()
{
configString = System.Configuration.ConfigurationManager.ConnectionStrings["mysql"].ToString();
}

public string getconfigString() {

return this.configString;
}

public MySqlConnection getConnecting()
{
MySqlConnection conn = new MySqlConnection();
conn.ConnectionString = this.configString;
conn.Open();
return conn;
}

public MySqlDataReader getMySqlDataReader(string sql)
{
MySqlConnection conn = this.getConnecting();
MySqlCommand msc = new MySqlCommand(sql, conn);
MySqlDataReader msdr = msc.ExecuteReader();
return msdr;
}

public string getSingleResult(string sql) {
MySqlConnection conn = this.getConnecting();
MySqlCommand msc = new MySqlCommand(sql, conn);
return msc.ExecuteScalar().ToString();
}
public int executeQuery(string sql)
{
MySqlConnection conn = this.getConnecting();
MySqlCommand msc = new MySqlCommand(sql, conn);
return msc.ExecuteNonQuery();
}
}
}

\\myPage.cs

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using mysql.db;
/// <summary>
///Class1 的摘要说明
/// </summary>
namespace db.classdb
{
public class myPage: System.Web.UI.Page
{
private int sumRowNo = 0; //
private int currPage = 0; //
private int maxPage = 0; //
private int singlePageRowSize = 0;//
private int showPageLinkNo = 0;
private string sql = null;
private string sqlTmp = null;
//private string configString = null;
private string otherQueryString = null;
private string pageString=null;
private DButils dbutils = null;
// 页面个数 SQL 连接字串 当前页数 能直接点的连接数
public myPage(int singlePageRowSize, string sql, int currPage, int showPageLinkNo, string otherQueryString, string pageString)
{
if (currPage < 1) {
currPage = 1;
}


this.singlePageRowSize = singlePageRowSize;
this.sql = sql;
this.dbutils=new DButils();
//this.configString = this.dbutils.getconfigString();
this.currPage = currPage;
this.showPageLinkNo = showPageLinkNo;
this.otherQueryString = otherQueryString;
this.pageString = pageString;

int fromNo = this.sql.ToLower().IndexOf("from");

this.sqlTmp = "select count(1) sumresult " + this.sql.Substring(fromNo);
MySqlConnection conn = this.dbutils.getConnecting();
MySqlCommand msc = new MySqlCommand(this.sqlTmp, conn);
this.sumRowNo= int.Parse(msc.ExecuteScalar().ToString());
conn.Close();
this.maxPage = (int)Math.Ceiling((double)((float)this.sumRowNo / this.singlePageRowSize));
if (this.currPage > this.maxPage) {
this.currPage = this.maxPage;
}

this.sql = this.sql + " limit " + (this.currPage - 1) * this.singlePageRowSize + "," + this.singlePageRowSize;

}

public string ShowPage()
{
string text = null;

text+="一共"+this.sumRowNo+"条数据&nbsp;&nbsp;&nbsp;&nbsp;&nbsp";

if(this.currPage>1){
text += "<a href=\"?" + this.otherQueryString + this.pageString + "=1\">|<<</a>" + getSeparates();
text += "<a href=\"?" + this.otherQueryString + this.pageString + "=" + (this.currPage - 1) + "\">|<</a>" + getSeparates();
}else{
text += "<a >|<<</a>" + getSeparates();
text += "<a >|<</a>" + getSeparates();
}
int startNo = 0;
int endNo = 0;

int halfis = (int)Math.Floor((float)this.showPageLinkNo / 2);

startNo = this.currPage - halfis;
endNo = this.currPage + halfis;
if (startNo < 1)
{
endNo = endNo - startNo+1;
startNo = 1;

}
if (endNo > this.maxPage) {
startNo = startNo - (endNo - this.maxPage);
endNo = this.maxPage;

}
if (startNo < 1)
{
startNo = 1;

}


int i=startNo;

while(i<=endNo){
if (i != this.currPage)
{
text += "<a href=\"?" + this.otherQueryString + this.pageString + "=" + i + "\">" + i + "</a>&nbsp;" + getSeparates();
}
else {
text += "<a >[" + i + "]</a>" + getSeparates();
}

i++;
}


if(this.currPage<this.maxPage){
text += " <a href=\"?" + this.otherQueryString + this.pageString + "=" + (this.currPage + 1) + "\">>|</a>" + getSeparates();
text += "<a href=\"?" + this.otherQueryString + this.pageString + "=" + this.maxPage + "\">>>|</a>" + getSeparates();
}else{
text += " <a >>|</a>" + getSeparates();
text += "<a >>>|</a>" + getSeparates();
}


text+="&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;一共有"+this.maxPage+"页";

return text;

}

private string getSeparates()
{
return "&nbsp;&nbsp;";
}

public MySqlDataReader getMySqlDataReader()
{
return this.dbutils.getMySqlDataReader(this.sql);
}


public int get_SumNo()
{
return this.sumRowNo;
}
public int get_MaxPage()
{
return this.maxPage;
}
public int get_currPage()
{
return this.currPage;
}


public string getSql()
{
return this.sql;
}

}
}

\\default.aspx.cs

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;

using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using MySql.Data.MySqlClient;
using db.classdb;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int currPage=0;
int singlePageRowSize = 10;
int showPageLinkNo = 7;
string pageString = "curryPage";
//Math.
if (Request.Params.Get(pageString) == null || Request.Params.Get(pageString).Trim().Equals(""))
{
currPage = 1;
}
else {
currPage = int.Parse(Request.Params.Get(pageString));
}

string[] queryString = Request.QueryString.ToString().Split('&');
int i = 0;
string otherQueryString = null;
while (i < queryString.Length)
{
if (queryString[i] != null && !queryString[i].Equals(""))
{
string[] name = queryString[i].Split('=');
if (!name[0].Equals(pageString))
{
otherQueryString += name[0] + "=" + name[1] + "&";
}

}
i++;

}

string sql = "select * from test";

myPage page = new myPage(singlePageRowSize, sql, currPage, showPageLinkNo, otherQueryString, pageString);

MySqlDataReader msdr = page.getMySqlDataReader();
i = 1;
while (msdr.Read())
{
data.InnerHtml+=( "序号"+i+":"+msdr.GetString(3)+"<br>");
i++;
}

topBar.InnerHtml=page.ShowPage();


}

\\default.aspx

<%@ Page Language="C#" 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 type="text/css" rel="Stylesheet" href="css/css.css"
<style type="text/css">


</style>

</head>
<body>

<form id="form1" runat="server">

<div id="topBar" runat="server" class="page" align="center">

</div>
<div id="data" runat="server">
</div>
</form>

</body>
</html>

  • 没有任何评论
今日天气 ···

···

···

···

热门排行
CSS cursor鼠标样式一览表 2012-06-25
.NET后台写JS代码 2011-11-03
网站地址多出jdfwkey的问题解析及... 2010-08-25
数据库xxx的日志已满,请备份该数... 2013-07-19
百度、google、Yahoo网站地图制作... 2011-01-30
<a>标签的伪类书写顺序问题... 2010-09-04
什么是长尾关键词? 2010-09-24
CEO名言 2010-08-31
Mysql 主从数据库同步 2010-09-12
用ASP实现网页BBS 2010-11-01
博主推荐
本个人博客微信公众平台上线啦~~... 2013-10-13
饼哥网络互联上线啦~~要买域名空... 2013-09-20
网站title标题如何正确修改不会被... 2013-08-26
饼哥通讯录系统上线啦,欢迎大家... 2013-08-24
ASP .NET MYSQL 的简单分页 并不... 2013-08-19
网站有弹窗广告这样的站点,百度... 2013-08-17
做淘宝SEO优化需要注意的8大问题 2013-08-17
淘宝网怎么做SEO优化 2013-08-17
站长们要学习的“苍井空精神” 2013-08-17
苹果公司今日发布了iOS 7第五个开... 2013-08-07
随便看看
站长警惕 十大元凶导致您的网站被... 2010-09-07
一、Web Service简介 2010-09-02
HTML5下插入flash 2013-10-03
FCKeditor 图片上传限制宽高 2014-03-21
随机访问Recordset的一条记录 2010-10-19
SEO优化效果的优势 2010-09-08
适用javaScript技术总结:屏蔽类 2013-06-27
百度指数 2010-09-26
好消息!好消息!各位朋友们看过... 2013-06-25
几个extjs 惯用操作 2013-06-27
RSS新闻
传媒新闻
CSDN
八卦新闻
女性新闻
台湾新闻
互联网
军事-新浪博客
IT-新浪博客
汽车新闻
游戏新闻
国际新闻
国内新闻
体育新闻
我的微博
北京 上海 杭州 深圳 广州 成都