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 System.Data.SqlClient;
using System.ComponentModel;
using System.Text.RegularExpressions;
using System.Web.UI.MobileControls;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using System.Collections.Specialized;namespace MyPager
{
public enum EConnectionType
{
SqlSourceID,
ConnStr
}
public partial class VeryPage : System.Web.UI.UserControl
{
#region 必须设置的属性 /// <summary>
/// 连接方式。
/// </summary>
public EConnectionType ConnectionType { get; set; }
/// <summary>
/// 连接字符串,该字符串指的是web.config中的connectionStrings节点。当不使用SqlSourceID的时候则必须指定,同时将引发事件传递DataSet。
/// </summary>
public string ConnStr { get; set; }
/// <summary>
/// 指定ConnectionType为ConnStr时,引发该事件,传递DataSet。
/// </summary>
public event ForConnStr ForConnStrEvent; /// <summary>
/// 数据源绑定控件。
/// </summary>
public string SqlSourceID { get; set; }
/// <summary>
/// 要查询的表名。
/// </summary>
public string TableName { get; set; }
/// <summary>
/// 翻页时的跳转页。
/// </summary>
public string PageName { get; set; }
/// <summary>
/// 排序字段。
/// </summary>
public string OrderField { get; set; } #endregion #region 一些属性 /// <summary>
/// 需要移除的参数,默认将移除page
/// </summary>
public string[] RemoveKeys { get; set; }
/// <summary>
/// 每页显示数据量,默认为10。
/// </summary>
public int PageSize { get; set; }
/// <summary>
/// 总数。
/// </summary>
int Count { get; set; }
/// <summary>
/// 一共需要分多少页。
/// </summary>
int PageCount { get; set; }
/// <summary>
/// 当前页。
/// </summary>
int NowPage { get; set; }
/// <summary>
/// 需要查询的字段名,默认为“*”。
/// </summary>
public string FieldName { get; set; }
/// <summary>
/// 条件语句。
/// </summary>
public string Where { get; set; }
/// <summary>
/// 设置在只有一页的情况下是否显示分页。
/// </summary>
public bool OnePageVisible { get; set; }
/// <summary>
/// 设置排序。
/// </summary>
public string Order { get; set; }
/// <summary>
/// 指定ConnectionType为ConnStr时,将引发事件传递。
/// </summary>
DataSet ds; public delegate void ForConnStr(DataSet ds); #endregion public VeryPage()
{
this.PreRender += new EventHandler(VeryPage_PreRender);
}
protected void Page_Load(object sender, EventArgs e)
{ }
void VeryPage_PreRender(object sender, EventArgs e)
{
if (!IsPostBack)
{
PageSize = PageSize == 0 ? 10 : PageSize;
FieldName = FieldName != null ? FieldName : "*";
Order = Order != null ? Order : "desc";
Where = Where != null ? Where : "";
if (RemoveKeys == null)
RemoveKeys = new string[] { "page" };
//OnePageVisible = OnePageVisible != null ? OnePageVisible : false;
SqlConnection con;
SqlDataSource SqlSource = null;
if (ConnectionType == EConnectionType.SqlSourceID)
{
if (SqlSourceID == null)
{
pager.InnerHtml = "Error:使用的是SqlSourceID连接方式,但并没有指定SqlSourceID。";
return;
}
else
{
SqlSource = (SqlDataSource)Page.FindControl(SqlSourceID);
if (SqlSource != null)
{
con = new SqlConnection(SqlSource.ConnectionString);
}
else
{
pager.InnerHtml = "Error:使用的是SqlSourceID连接方式,但并没有找到SqlSourceID。";
return;
}
}
}
else
{
if (ConnStr == null)
{
pager.InnerHtml = "Error:使用的是ConnStr连接方式,但并没有指定ConnStr。";
return;
}
else
{
ConnStr = ConfigurationManager.ConnectionStrings[ConnStr].ConnectionString;
con = new SqlConnection(ConnStr);
}
}
try
{
con.Open();
}
catch
{
pager.InnerHtml = "Error:连接打开失败。";
return;
}
Count = GetCount(con, TableName, Where);
PageCount = GetPageCount(Count, PageSize);
NowPage = GetNowPage();
CreatePageLink(NowPage, PageCount, OnePageVisible);
//if (ConnectionType == EConnectionType.SqlSourceID)
//{
// SetSelectCommand(SqlSource, TableName, FieldName, Where, Order);
//}
//else
//{
// SetSelectCommand(con, TableName, FieldName, Where, Order,OrderField);
// ForConnStrEvent(ds);
//}
SetSelectCommand(con, TableName, FieldName, Where, Order, OrderField);
ForConnStrEvent(ds);
}
}
/// <summary>
/// 返回一共有多少条数据。
/// </summary>
int GetCount(SqlConnection con, string TableName, string Where)
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = string.Format("select count(*) from {0} {1}", TableName, Where);
int tempcount = (int)cmd.ExecuteScalar();
con.Close();
return tempcount;
}
/// <summary>
/// 返回一共需要分多少页。
/// </summary>
int GetPageCount(int Count, int PageSize)
{
if (Count != 0)
PageSize = (int)Math.Ceiling((double)Count / PageSize);
else
PageSize = 1;
return PageSize;
}
/// <summary>
/// 返回当前页。
/// </summary>
int GetNowPage()
{
string a = Request.QueryString["page"];
int tempNowPage;
if (Request.QueryString["page"] != null &&
Request.QueryString["page"] != "" &&
Regex.IsMatch(Request.QueryString["page"], @"^[+-]?\d*$") &&
int.Parse(Request.QueryString["page"]) <= PageCount)
tempNowPage = int.Parse(Request.QueryString["page"]);
else
tempNowPage = 1;
return tempNowPage;
}
/// <summary>
/// 创建分页连接。
/// </summary>
void CreatePageLink(int NowPage, int PageCount, bool OnePageVisible)
{
if (PageCount != 1 || (PageCount == 1 && OnePageVisible == true))
{
//重组参数
NameValueCollection NewQueryString = new NameValueCollection(Request.QueryString);//将参数导出到一个新的容器
//移除不需要的参数
foreach (string key in RemoveKeys)
{
NewQueryString.Remove(key);
}
StringBuilder QS = new StringBuilder("");
foreach (string key in NewQueryString.AllKeys)
{
QS.Append("&" + key + "=" + NewQueryString[key]);
}
//
StringBuilder sb = new StringBuilder("");
sb.Append("<a href=\"" + PageName + "?page=1" + QS.ToString() + "\">最前页</a>\r\n");
int minpage = 1;
if (PageCount >= 10 && NowPage >= PageCount - 5)
{
minpage = NowPage - (4 + (NowPage - (PageCount - 5)));
}
else if (PageCount >= 10 && NowPage >= 5)
{
minpage = NowPage - 4;
}
if (PageCount >= 10)
PageCount = 10;
for (int i = 0; i < PageCount; i++)
{
if (minpage != NowPage)
{
sb.Append("<a href=\"" + PageName + "?page=" + minpage.ToString() + QS.ToString() + "\">" + minpage.ToString() + "</a>\r\n");
}
else
{
sb.Append("<a>" + NowPage.ToString() + "</a>\r\n");
}
minpage++;
}
sb.Append("<a href=\"" + PageName + "?page=" + PageCount + QS.ToString() + "\">最后页</a> 共有:" + Count + "条\r\n");
pager.InnerHtml = sb.ToString();
}
}
解决方案 »
- 如何在子页面获取母板页里Testbox里的值
- 这段代码就是跟我有仇。前台页面怎么就不调用这个方法呢。
- RegisterStartupScript
- asp.net2.0中,成员管理和角色管理同时用会导致重复用户
- Page.Cache["Key"]为什么不能保存SqlDataReader对象?
- 怎样动态在Dbgrid中增加按钮列???
- 想换程序学习的大侠进来看看。
- 怎么能在DATAGRID中将一个特定的值显示为红色呢?
- *********************请教一个xslt的问题**********************
- ListView FindControl SelectedItemTemplate
- 有谁知道怎么用Ajax无刷新实现图书分页吗?
- 我发现一个可以读出相片光圈快门的网站后台,这个功能如何实现?
void SetSelectCommand(SqlConnection con, string TableName, string FieldName, string Where, string Order, string OrderField)
{
string command = "";
if (Order == "asc")
{
//升序
command = string.Format("select top {0} * from (select top {1} {2} from {3} {4} order by {5} desc) as temptable order by {5} asc",
PageSize.ToString(),
Count - (NowPage - 1) * PageSize,
FieldName,
TableName,
Where,
OrderField);
//command = "exec('select top'+ @PageSize+' * from (select top'+ @PageSizeFZ + @FieldName+' from '+@TableName + @Where +'order by '+@OrderField'+ desc) as temptable order by '+@OrderField+' asc')";
}
else
{
//降序
command = string.Format("select top {0} * from (select top {1} {2} from {3} {4} order by {5} asc) as temptable order by {5} desc",
PageSize.ToString(),
Count - (NowPage - 1) * PageSize,
FieldName,
TableName,
Where,
OrderField);
//command = "exec('select top'+ @PageSize+' * from (select top'+ @PageSizeFZ + @FieldName+' from '+@TableName + @Where +'order by '+@OrderField'+ asc) as temptable order by '+@OrderField+' desc')"; } SqlDataAdapter da = new SqlDataAdapter(command, con);
ds = new DataSet();
da.Fill(ds);
}
}
}
页面:
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="VeryPage.ascx.cs"
Inherits="MyPager.VeryPage" %><div runat="server" id="pager"></div>封装成用户控件。
//<summary>
//分页获取数据列表
//</summary>
public DataSet GetList(List<string> list)
{
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.VarChar, 255), // -- 表名
new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000), // '*', -- 需要返回的列
new SqlParameter("@fldName", SqlDbType.VarChar, 255), //-- 排序的字段名
new SqlParameter("@PageSize", SqlDbType.Int), //-- 页尺寸
new SqlParameter("@PageIndex", SqlDbType.Int), // -- 页码
new SqlParameter("@doCount", SqlDbType.Bit), //-- 返回记录总数, 非 0 值则返回
new SqlParameter("@OrderType", SqlDbType.Bit), //-- 设置排序类型, 非 0 值则降序
new SqlParameter("@strWhere", SqlDbType.VarChar,1500), // -- 查询条件 (注意: 不要加 where)
};
parameters[0].Value = list[0].ToString();
parameters[1].Value = list[1].ToString();
parameters[2].Value = list[2].ToString();
parameters[3].Value = int.Parse(list[3].ToString());
parameters[4].Value = int.Parse(list[4].ToString());
parameters[5].Value = int.Parse(list[5].ToString()); ;
parameters[6].Value = int.Parse(list[6].ToString());
parameters[7].Value = list[7].ToString();
return DbHelperSQL.RunProcedure("Pagination", parameters, "ds"); //执行存储过程 } /// <summary>
/// 近回行数
/// </summary>
/// <param name="strWhere">条件</param>
/// <param name="tbName">表名</param>
/// <returns></returns>
public string rowcount(string strWhere, string tbName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(*) ");
strSql.Append(" FROM " + tbName);
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return DbHelperSQL.GetSingle(strSql.ToString()).ToString();
}
页面直接调用:
public void DataBind()
{
DataSet ds = new DataSet();
string strWhere = "1=1";
//用于分页
List<string> list = new List<string>();
list.Add("tb_table"); // -- 表名
list.Add("*"); // '*', -- 需要返回的列
list.Add(ViewState["SortOrder"].ToString()); //-- 排序的字段名
list.Add(AspNetPager1.PageSize.ToString()); //-- 页尺寸
list.Add(AspNetPager1.CurrentPageIndex.ToString()); // -- 页码
list.Add("0"); //-- 返回记录总数, 非 0 值则返回
list.Add(ViewState["OrderDire"].ToString()); //-- 设置排序类型, 非 0 值则降序
list.Add(strWhere); // -- 查询条件 (注意: 不要加 where)
ds = PageBase.GetList(list); AspNetPager1.RecordCount = int.Parse(PageBase.rowcount(strWhere, "tb_table"));//返会总行数
if (AspNetPager1.RecordCount == 0)
{
AspNetPager1.Visible = false;
}
else
{
AspNetPager1.Visible = true;
} gridview1.DataSource = ds.Tables[0].DefaultView;
gridview1.DataBind();
}
我也给大家分享一个,这个用起来挺好的
就像楼上有说的 你做的通用点 封装成DLL 更好
不知比起AspNetPager这个控件是否好用?
private void getData(int row,int page)
{
string str = "select * from student";
int nPage = 0;
int previousPage = 0;
int nextPage = 0;
SqlDataAdapter da = new SqlDataAdapter(str, conn);
DataSet ds = new DataSet();
da.Fill(ds);
//DataRow Row = ds.Tables[0].Rows;
string strShow = "<table border='0' cellspacing='0' style='text-align:center;width:350px;'><tr style='background-color:#45708F;color:#ffffff'>";
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
strShow += "<td>" + ds.Tables[0].Columns[j].ColumnName + "</td>";
}
strShow += "</tr>"; int count = ds.Tables[0].Rows.Count;
if (count % row != 0)
nPage = count / row + 1;
else
nPage = count / row;
int start = (page - 1) * row;
int endpage = (page - 1) * row + row;
int colorStyleNum = 0;
//string test = ds.Tables[0].Rows[0][0].ToString();
for (int i = start; i < count && i < endpage; i++)
{
DataRow Row = ds.Tables[0].Rows[i];
if (colorStyleNum == 0)
{
strShow += "<tr style='background-color:#B0BCBF;'><td>" + Row["sno"].ToString() + "</td><td>" + Row["sname"].ToString() + "</td><td>";
strShow += Row["ssex"].ToString() + "</td><td>" + Row["sclass"].ToString() + "</td></tr>";
colorStyleNum++;
}
else
{
strShow += "<tr style='background-color:#D6DCDE;'><td>" + Row["sno"].ToString() + "</td><td>" + Row["sname"].ToString() + "</td><td>";
strShow += Row["ssex"].ToString() + "</td><td>" + Row["sclass"].ToString() + "</td></tr>";
colorStyleNum--;
}
}
if (page > 1)
{
previousPage = page;
previousPage--;
strShow += "<tr><td colspan='4' style='text-align:right;'><a href=Default.aspx?page=";
strShow += previousPage + "&row=" + row + "><<</a>";
}
else strShow += "<tr><td colspan='4' style='text-align:right;'>";
int startCount = 0;
int endCount = 0;
if (page > nPage) page = nPage;
startCount = (page + 5) > nPage ? nPage-4: page-2;//中间页起始序号
endCount = page <= startCount + 5 ? startCount + 5 : page + 3;//中间页终止序号
if (startCount < 1)
{
startCount = 1;
}
if (nPage < endCount)
{
endCount = nPage;
}
for (int k = startCount; k < endCount; k++)
{
//if (page % 5 == 0) left = left + 5;
//getData(row,page);
if (k == page)
{
strShow += "<a class='current' style='margin-left:3px;' href=Default.aspx?page=" + k + "&row=" + row + ">" + "<b>" + k + "</b>" + "</a>";
}
else
{
strShow += "<a style='margin-left:3px;' href=Default.aspx?page=" + k + "&row=" + row + ">" + k + "</a>";
}
} //if (nPage - page > 5)
//{
// int cpage = left + 5;
// strShow += "<a style='margin-left:3px;' href=Default.aspx?page=" + cpage + "&row=" + row + ">...</a>";
//} if (page < nPage)
{
nextPage = page;
nextPage++;
strShow += "<a style='margin-left:3px;' href=Default.aspx?page=" + nextPage + "&row=" + row + ">>></a>";
}
strShow += "<a style='margin-left:3px;' href=Default.aspx?page=" + nPage + "&row=" + row + ">>|</a>";
strShow += "</td></tr></table>";
lblShow.Text = strShow;
}
拖上去直接用,一般设置一些属性就可以了,例如:
<uc1:VeryPage ID="VeryPage" runat="server" TableName="表名" PageName="当前需要分页的页面名如123.aspx"
PageSize="一页显示多少条数据" ConnStr="数据源连接字符串,该字符串在web.config的<connectionStrings>节点定义,设置其NAME属性就可以了" FieldName="需要显示的字段,可以不设置,默认为*" OrderField="排序字段"
Order="asc或desc" Where="条件" />如果使用ConnStr的方式,则需要在代码中设置:
VeryPage.ConnectionType = MyPager.EConnectionType.ConnStr;还需要注册ForConnStrEvent事件,该事件传递一个查询后的DATASET,例如:VeryPage.ForConnStrEvent += new MyPager.VeryPage.ForConnStr(VeryPage_ForConnStrEvent); void VeryPage_ForConnStrEvent(DataSet ds)
{
_Repeater.DataSource = ds;
_Repeater.DataBind();
}
ConnStr改成SqlSourceID="数据源ID例如SqlDataSource1"
第页,共页 首页 上一页 (中间一直是20个页码1 2 3...,当然如果不够20则显示实际数) 下一页 末页
本来也想拿出来让大家指导指导,但是本人比较懒,在前台直接用“<% %>”这种判断的...所以不拿出来的献丑,呵呵!!
lz这个帖子应该早点出现,那样我也不用自己写了...