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

精贴置顶

2259 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";

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> " + 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+="     一共有"+this.maxPage+"页";

return text;

}

private string getSeparates()
{
return "  ";
}

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);