分页用户控件

最新推荐文章于 2022-08-22 18:59:03 发布

原创最新推荐文章于 2022-08-22 18:59:03 发布 · 511 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#string #sql #server #exception #dataset #javascript

asp.net 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种基于SQL查询的分页用户控件实现方法，该控件能够为ASP.NET Web应用程序提供灵活的分页功能。通过设置SQL查询语句、数据库连接字符串等属性，用户可以轻松地在页面中实现数据的分页展示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前一阵子不是很忙，写了个分页的用户控件，也是第一次写。感觉还能用，就贴上来了。

效果如下图，呵呵 ~~ 长的不是很好看，但基本功能都实现了。

分页原理：传入sql查询语句，为sql查询到的结果集分配行号，然后以行号为条件获取结果集。

使用方式：

1.新建一个Page页面，代码如下：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="MyPage._Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <%@ Register Src="~/TaoPage.ascx" TagName="TaoPage" TagPrefix="tao" %> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>无标题页</title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" ></asp:GridView> <tao:TaoPage ID="tao" runat="server"/> </div> </form> </body> </html>

2.Page页面的后台代码：因为代码都有注释，就不做过多的说明

protected void Page_Load(object sender, EventArgs e) { //可选属性 this.tao.UserNumIndex = true; //使用序号索引 this.tao.NumIndexSize = 10; //序号索引大小为10 this.tao.PageSize = 10; //每页10条记录 this.tao.ShowPageCountInfo = true; //显示也统计信息 this.tao.ShowPageGoInfo = true; //显示跳转到第几页 this.tao.OrderByColumnStr = "RecId desc"; //RecId的降序排列 //必填属性 this.tao.SqlStr = "select * from AlertData"; //传入sql字符串 this.tao.SqlConnStr = "server=ouyangtao;database=GoodsAgentDB;uid=sa;pwd=sasa;max pool size=500;"; //数据库连接字符串 this.GridView1.DataSource = this.tao.GetDataSource(); //获取数据源 this.GridView1.DataBind(); //数据绑定 }

下面是用户控件的代码

后台代码

public partial class TaoPage : System.Web.UI.UserControl { #region Field private int _pageSize = 10; //默认为10 /// <summary> /// 设置或获取页大小 /// </summary> public int PageSize { get { return _pageSize; } set { if (value > 0) _pageSize = value; } } private int _pageIndex = 1; //默认为1 /// <summary> /// 设置或获取页索引 /// </summary> public int PageIndex { get { return _pageIndex; } set { if (value > 0) _pageIndex = value; } } private string _sqlStr; /// <summary> /// sql语句 /// </summary> public string SqlStr { get { return _sqlStr; } set { _sqlStr = value; } } private string _sqlConnStr; /// <summary> /// 设置或获取数据库连接字符串 /// </summary> public string SqlConnStr { get { return _sqlConnStr; } set { _sqlConnStr = value; } } private int _totalPage; /// <summary> /// 获取总页数 /// </summary> public int TotalPage { get { return _totalPage; } } private int _totalCount; /// <summary> /// 获取总记录数 /// </summary> public int TotalCount { get { return _totalCount; } } private bool _useNumIndex; /// <summary> /// 是否使用页索引 /// </summary> public bool UserNumIndex { get { return _useNumIndex; } set { _useNumIndex = value; } } private int _numIndexSize = 10; //默认为10 /// <summary> /// 设置或获取页索引大小 /// </summary> public int NumIndexSize { get { return _numIndexSize; } set { if (value > 0) _numIndexSize = value; } } private string _orderByColumnStr; /// <summary> /// 排序列(多个列使用逗号隔开) 例：Id,name desc /// </summary> public string OrderByColumnStr { get { return _orderByColumnStr; } set { _orderByColumnStr = value; } } private bool _showPageCountInfo; /// <summary> /// 显示页统计信息 /// </summary> public bool ShowPageCountInfo { get { return _showPageCountInfo; } set { _showPageCountInfo = value; } } private bool _showPageGoInfo; /// <summary> /// 显示PageGo信息 /// </summary> public bool ShowPageGoInfo { get { return _showPageGoInfo; } set { _showPageGoInfo = value; } } //页面Url string pageUrl; #endregion //控件加载事件 protected void Page_Load(object sender, EventArgs e) { } /// <summary> /// 获取数据源 /// </summary> /// <returns>DataTable</returns> public DataTable GetDataSource() { this.LoadData(); return this.GetDataSource(this.CreateSql()); } /// <summary> /// 加载数据 /// </summary> private void LoadData() { if (!this.CheckInfo()) return; //获取和处理页面Url pageUrl = Page.Request.Url.ToString(); if (pageUrl.IndexOf("pageIndex") < 0) { if (pageUrl.IndexOf("?") < 0) pageUrl += "?pageIndex="; else pageUrl += "&pageIndex="; } else { try { this.PageIndex = Convert.ToInt32(pageUrl.Substring(pageUrl.LastIndexOf("pageIndex=") + 9 + 1)); } catch { AlertInfo("页索引必须为正整数！输入错误，跳转到首页！"); this.PageIndex = 1; } pageUrl = pageUrl.Remove(pageUrl.LastIndexOf('=') + 1); } //获取记录总数 this._totalCount = this.GetTotalCount(); //根据记录总数和每页的记录数统计页总数 if (this._totalCount % this.PageSize > 0) this._totalPage = (this._totalCount / this.PageSize) + 1; else this._totalPage = this._totalCount / this.PageSize; //处理按钮的Url链接 this.DisposeButtonUrl(); //是否使用索引序号 if (this.UserNumIndex) { this.pageIndexContainer.Visible = true; this.CreateIndexNum(); } else { this.pageIndexContainer.Visible = false; } //是否显示页统计信息 if (this.ShowPageCountInfo) { this.lblPageAndCount.Visible = true; this.ShowPageAndCount(); } else { this.lblPageAndCount.Visible = false; } //是否显示PageGo信息 if (this.ShowPageGoInfo) { this.ShowPageGo(); } } /// <summary> /// 检查信息是否完整 /// </summary> /// <returns></returns> private bool CheckInfo() { if (string.IsNullOrEmpty(this.SqlConnStr)) { AlertInfo("请指定数据库连接字符串！"); return false; } if (string.IsNullOrEmpty(this.SqlStr)) { AlertInfo("请指定SQL语句！"); return false; } return true; } /// <summary> /// 生成索引序号 /// </summary> private void CreateIndexNum() { int startIndex = 0; int endIndex = 0; if (this.NumIndexSize >= this.TotalPage) //分组页数大于总页数 { startIndex = 1; endIndex = this.TotalPage; } else { if (this.PageIndex <= (this.NumIndexSize / 2))//是头组 { startIndex = 1; endIndex = this.NumIndexSize; } else if ((this.PageIndex + (this.NumIndexSize / 2)) >= this.TotalPage)//是尾组 { startIndex = this.TotalPage - this.NumIndexSize + 1; endIndex = this.TotalPage; } else //既不是头也不是尾 { startIndex = this.PageIndex - this.NumIndexSize / 2; if (this.NumIndexSize % 2 == 0) //如果不能均匀分组，则前面部分比后面部分多分一页 { endIndex = this.PageIndex + this.NumIndexSize / 2 - 1; } else { endIndex = this.PageIndex + this.NumIndexSize / 2; } } } //根据序号创建数字索引控件 this.CreateNumIndexControl(startIndex, endIndex); } /// <summary> /// 处理按钮的url /// </summary> private void DisposeButtonUrl() { if (this.TotalCount > 0) { if (this.PageIndex == 1) { this.firstPage.HRef = ""; this.upPage.HRef = ""; this.nextPage.HRef = pageUrl + (this.PageIndex + 1); this.lastPage.HRef = pageUrl + this.TotalPage; } else if (this.PageIndex == this.TotalPage) { this.firstPage.HRef = pageUrl + 1; this.upPage.HRef = pageUrl + (this.PageIndex - 1); this.nextPage.HRef = ""; this.lastPage.HRef = ""; } else { this.firstPage.HRef = pageUrl + 1; this.upPage.HRef = pageUrl + (this.PageIndex - 1); this.nextPage.HRef = pageUrl + (this.PageIndex + 1); this.lastPage.HRef = pageUrl + this.TotalPage; } } } /// <summary> /// 创建数字索引控件 /// </summary> /// <param name="startIndex">开始序号</param> /// <param name="endIndex">结束序号</param> private void CreateNumIndexControl(int startIndex, int endIndex) { this.pageIndexContainer.Controls.Clear(); if (endIndex > startIndex) { for (int i = startIndex; i <= endIndex; i++) { HtmlAnchor a = new HtmlAnchor(); a.InnerText = i.ToString(); a.ID = "Index" + i.ToString() + "Page"; if (i == this.PageIndex) //当前页不添加链接 { a.HRef = ""; } else { a.HRef = pageUrl + i; } //为了使序号看起来不那么紧凑，左右两边都添加5px的间距 a.Attributes.Add("style", "margin-left:5px;margin-right:5px;"); this.pageIndexContainer.Controls.Add(a); } } } /// <summary> /// 显示统计页信息 /// </summary> private void ShowPageAndCount() { this.lblPageAndCount.InnerHtml = "第" + this.PageIndex + "页/共" + this.TotalPage + "页，共" + this.TotalCount + "条"; } /// <summary> /// 显示PageGO /// </summary> private void ShowPageGo() { Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "", "<mce:script type='text/javascript' language='javascript'></mce:script>"); this.pageGoContrainer.Text = @" 跳至第<input type='text' id='txtPageIndex' onkeydown='if((event.keyCode<47||event.keyCode>59)&&event.keyCode!=8)return false;' onmouseOver='this.select()' size='3'>页<input type='button' id='btnGo' onclick='f_Page_Go(" + '"' + pageUrl + '"' + ")' value='GO'> "; } /// <summary> /// 获取数据源 /// </summary> /// <param name="sql">sql语句</param> /// <returns>数据源</returns> private DataTable GetDataSource(string sql) { //根据sql语句获取数据源 DataTable dt = new DataTable(); try { dt = this.GetResultData(sql); } catch (Exception ex) { this.AlertInfo("分页获取数据源失败！", ex.Message); } return dt; } /// <summary> /// 获取总记录数 /// </summary> /// <returns>总记录数</returns> private int GetTotalCount() { string sql = string.Empty; sql = " select count(*) from ( " + this.SqlStr + " ) as TempTaoPage"; int count = 0; try { count = Convert.ToInt32(this.GetScalarData(sql)); } catch (Exception ex) { this.AlertInfo("分页获取记录总数失败!", ex.Message); } return count; } /// <summary> /// 构建最终sql语句 /// </summary> private string CreateSql() { StringBuilder lastSql = new StringBuilder(); //因为使用row_number()必须给定一个排序列。如果没有的话这里就用getdate() if (string.IsNullOrEmpty(this.OrderByColumnStr)) { this.OrderByColumnStr = "getdate()"; } lastSql.Append(" with tempTable as "); lastSql.Append(" (select row_number() over "); lastSql.Append(" (order by "); lastSql.Append(this.OrderByColumnStr); lastSql.Append(")"); lastSql.Append(" as rowid ,* from "); lastSql.Append(" ( "); lastSql.Append(this.SqlStr); lastSql.Append(" ) TempPageA) /n "); lastSql.Append(" select * from tempTable "); lastSql.Append(" where rowid "); lastSql.Append(" between "); lastSql.Append((this.PageIndex - 1) * this.PageSize + 1); lastSql.Append(" and "); lastSql.Append(this.PageIndex * this.PageSize); return lastSql.ToString(); } /// <summary> /// 输出信息 /// </summary> /// <param name="info"></param> private void AlertInfo(string tipInfo) { if (!string.IsNullOrEmpty(tipInfo)) { Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "", "<mce:script type='text/javascript' language='javascript'></mce:script>"); } } /// <summary> /// 输出信息,带有错误信息 /// </summary> /// <param name="tipInfo">提示信息</param> /// <param name="errorInfo">错误详细信息</param> private void AlertInfo(string tipInfo, string errorInfo) { if (!string.IsNullOrEmpty(tipInfo) && !string.IsNullOrEmpty(errorInfo)) { errorInfo = this.DisposeStr(errorInfo); //处理字符串 Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "", "<mce:script type='text/javascript' language='javascript'></mce:script>"); } } /// <summary> /// 处理字符串(首先将字符串中的"/"替换为"/"，将/r和/n都替换为空，然后将双引号或单引号替换为带有转义符的双引号或单引号) /// 如果是空串，将不做处理 /// </summary> /// <param name="str">原始字符串</param> /// <returns>处理后的字符串</returns> private string DisposeStr(string str) { string returnStr = string.Empty; if (!string.IsNullOrEmpty(str)) { if (str.Trim().Length == 0) //如果只包含空格就返回原始字符串 returnStr = str; else //首先将'/'替换为'/',然后除了单引号和双引号 returnStr = str.Replace("//", "/").Replace("/r", "").Replace("/n", "").Replace("'", "//'").Replace("/"", "///""); } return returnStr; } /// <summary> /// 根据最终sql语句获取结果集 /// </summary> /// <param name="sql">最终sql语句</param> /// <returns>返回结果集</returns> private DataTable GetResultData(string sql) { using (SqlConnection conn = new SqlConnection(this.SqlConnStr)) { using (SqlDataAdapter sda = new SqlDataAdapter(sql, conn)) { DataSet ds = new DataSet(); try { sda.Fill(ds); } catch (Exception ex) { throw ex; } return ds.Tables[0]; } } } /// <summary> /// 根据sql语句获取结果集的第一行第一列 /// </summary> /// <param name="sql">sql语句</param> /// <returns>结果集的第一行第一列</returns> private object GetScalarData(string sql) { object result = null; SqlConnection conn = new SqlConnection(this.SqlConnStr); SqlCommand comm = new SqlCommand(sql, conn); try { conn.Open(); result = comm.ExecuteScalar(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } return result; } }

页面代码

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="TaoPage.ascx.cs" Inherits="MyPage.TaoPage" %> <span> <a id="firstPage" runat="server" >首页</a> <a id="upPage" runat="server" >上一页</a> <span id="pageIndexContainer" runat="server" ></span> <a id="nextPage" runat="server" >下一页</a> <a id="lastPage" runat="server" >尾页</a> <asp:Literal ID="pageGoContrainer" runat="server"></asp:Literal> <label id="lblPageAndCount" runat="server" ></label> </span>

诶好累~~ 这个在线编辑器用的不是很习惯。

控件可能有很多不完善的地方，不好的地方还望指出。