getpagerforsql.ascx.cs

sql server分页存储过程 ASP.net 控件,网上着的

namespace doHope
{
	using System;
	using System.Data;
	using System.Drawing;
	using System.Web;
	using System.Web.UI.WebControls;
	using System.Web.UI.HtmlControls;
	using System.Data.SqlClient;

	/// <summary>
	///	配合存储过程分页自定义控件(Sql Server)
	///	By cherish58
	/// </summary>
	public class GetPagerForSql : System.Web.UI.UserControl
	{
		protected System.Web.UI.WebControls.Label Label1;
		protected System.Web.UI.WebControls.Label Label9;
		protected System.Web.UI.WebControls.Label lbl_PageCnt;
		protected System.Web.UI.WebControls.Label Label6;
		protected System.Web.UI.WebControls.LinkButton lkbLast;
		protected System.Web.UI.WebControls.LinkButton lkbNext;
		protected System.Web.UI.WebControls.LinkButton lkbPre;
		protected System.Web.UI.WebControls.LinkButton lkbFirst;
		protected System.Web.UI.WebControls.Label Label3;
		protected System.Web.UI.WebControls.Label lbl_RecordCnt;
		protected System.Web.UI.WebControls.Label Label2;
		protected System.Web.UI.WebControls.Label Label4;
		protected System.Web.UI.WebControls.Label Label5;
		protected System.Web.UI.WebControls.Label Label7;
		protected System.Web.UI.WebControls.Label Label8;
		protected System.Web.UI.WebControls.TextBox txt_CurrentPage;

		#region 全局变量

		/// <summary>
		/// 获得数据库连接字符
		/// </summary>
		protected string strconn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString() ;

		/// <summary>
		/// 初始登陆时是否绑定数据（是为true，否为false），默认为false
		/// </summary>
		public bool InitBindData = false ;

		#endregion

		#region 属性

		/// <summary>
		/// 表名，必须赋初值
		/// </summary>
		public string TableName
		{
			get{return ViewState["TableName"].ToString();}
			set{ViewState["TableName"] = value;}
		}
		/// <summary>
		/// 返回的列名，默认为全部
		/// </summary>
		public string RetColumns
		{
			get{return ViewState["RetColumns"].ToString();}
			set{ViewState["RetColumns"] = value;}
		}
		/// <summary>
		/// 查询条件字符串，默认为空
		/// </summary>
		public string SqlWhere
		{
			get{return ViewState["SqlWhere"].ToString();}
			set{ViewState["SqlWhere"] = value;}
		}
		/// <summary>
		/// 排序字段，必须赋初值
		/// </summary>
		public string OrderField
		{
			get{return ViewState["OrderField"].ToString();}
			set{ViewState["OrderField"] = value;}
		}
		/// <summary>
		/// 排序类型（升序为asc，降序为desc），默认为升序
		/// </summary>
		public string OrderType
		{
			get{return ViewState["OrderType"].ToString();}
			set{ViewState["OrderType"] = value;}
		}
		/// <summary>
		/// 每页显示记录数，默认为10条
		/// </summary>
		public int PageSize
		{
			get{return int.Parse(ViewState["PageSize"].ToString());}
			set{ViewState["PageSize"] = value;}
		}
		/// <summary>
		/// 初始显示为第几页，默认为第1页
		/// </summary>
		public int CurrentPage
		{
			get{return int.Parse(ViewState["CurrentPage"].ToString());}
			set{ViewState["CurrentPage"] = value;}
		}
		/// <summary>
		/// 数据列表控件名称，必须赋初值
		/// </summary>
		public string DataControlName
		{
			get{return ViewState["DataControlName"].ToString();}
			set{ViewState["DataControlName"] = value;}
		}

		#endregion

		#region Page_Load

		private void Page_Load(object sender, System.EventArgs e)
		{
			if(!IsPostBack)
			{
				if(this.InitBindData)
				{
					//默认显示为第几页
					ViewState["CurrentPage"] = ViewState["CurrentPage"] == null || ViewState["CurrentPage"].ToString() == "" ? "1" : ViewState["CurrentPage"].ToString() ;
					//每页显示记录总数
					ViewState["PageSize"] = ViewState["PageSize"] == null || ViewState["PageSize"].ToString() == "" ? 10 : int.Parse(ViewState["PageSize"].ToString()) ;

					this.BindGridData() ;
				}
			}
		}

		#endregion

		#region Web 窗体设计器生成的代码
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		///		设计器支持所需的方法 - 不要使用代码编辑器
		///		修改此方法的内容。
		/// </summary>
		private void InitializeComponent()
		{
			this.lkbFirst.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
			this.lkbPre.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
			this.lkbNext.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
			this.lkbLast.Command += new System.Web.UI.WebControls.CommandEventHandler(this.ChangePage);
			this.txt_CurrentPage.TextChanged += new System.EventHandler(this.txt_CurrentPage_TextChanged);
			this.Load += new System.EventHandler(this.Page_Load);

		}
		#endregion

		#region 分页 ChangePage

		private void ChangePage(object sender, System.Web.UI.WebControls.CommandEventArgs e)
		{
			int PageCount = this.GetPageCount() ;   
			int CurrentPage = int.Parse(ViewState["CurrentPage"].ToString()) ;
   
			string Change = e.CommandArgument.ToString() ;
			if(Change == "Pre") //上一页   
			{         
				if(CurrentPage <= 1)   
				{   
					ViewState["CurrentPage"] = 1;    
				}   
				else   
				{   
					ViewState["CurrentPage"] = CurrentPage - 1 ;     
				}   
			}   
			else if(Change == "Next") //下一页  
			{         
				if(CurrentPage >= PageCount)   
				{   
					ViewState["CurrentPage"] = PageCount ;     
				}   
				else   
				{   
					ViewState["CurrentPage"] = CurrentPage + 1 ;    
				}      
			}   
			else if(Change == "First") //首页
			{   
				ViewState["CurrentPage"] = 1 ;       
			}   
			else //末页
			{   
				ViewState["CurrentPage"] = PageCount ;   
			}
   
			//显示当前页
			this.txt_CurrentPage.Text = ViewState["CurrentPage"].ToString() ;  
 
			this.ProData() ;   
		}

		#endregion

		#region 绑定数据

		/// <summary>
		/// 设置分页相关的参数
		/// </summary>
		private void BindGridData()
		{
			//记录总数
			this.lbl_RecordCnt.Text = this.GetRecordCount().ToString() ; 
			//总页数
			this.lbl_PageCnt.Text = this.GetPageCount().ToString() ;
			if(this.lbl_PageCnt.Text != "0")
			{
				//当前页
				this.txt_CurrentPage.Text = ViewState["CurrentPage"].ToString() ; 
			}
			else
				this.txt_CurrentPage.Text = "0" ; 

			//避免翻页后再查询出现列表没记录的情况
			if(int.Parse(this.lbl_RecordCnt.Text) <= int.Parse(ViewState["PageSize"].ToString()))
			{
				ViewState["CurrentPage"] = 1 ;
				this.txt_CurrentPage.Text = "1" ;
			}

			//绑定数据
			this.ProData() ;
		}

		#endregion

		#region 处理数据集

		/// <summary>
	    /// 处理数据集
	    /// </summary>
	    /// <returns></returns>
		private void ProData()
		{   
			SqlConnection conn = new SqlConnection(strconn);   
			SqlCommand cmd = new SqlCommand("GetRecordFromPage",conn);   
			conn.Open() ; 
 
			cmd.CommandType = CommandType.StoredProcedure ;   
			cmd.Parameters.Add("@tblName",""+ViewState["TableName"].ToString()+"") ;
			string retcolumns = ViewState["RetColumns"] == null || ViewState["RetColumns"].ToString() == "" ? "*" : ViewState["RetColumns"].ToString() ;
			cmd.Parameters.Add("@RetColumns",retcolumns) ;   
			string sqlwhere = ViewState["SqlWhere"] == null || ViewState["SqlWhere"].ToString() == "" ? "" : ViewState["SqlWhere"].ToString() ;
			cmd.Parameters.Add("@strWhere",sqlwhere) ;   
			cmd.Parameters.Add("@Orderfld",""+ViewState["OrderField"].ToString()+"") ;
			cmd.Parameters.Add("@PageIndex",int.Parse(ViewState["CurrentPage"].ToString())) ; 
			cmd.Parameters.Add("@PageSize",""+int.Parse(ViewState["PageSize"].ToString())+"") ;
			string ordertype = ViewState["OrderType"] == null || ViewState["OrderType"].ToString() == "" ? "asc" : ViewState["OrderType"].ToString() ;
			cmd.Parameters.Add("@OrderType",ordertype) ;
    
			SqlDataAdapter da = new SqlDataAdapter() ;   
			da.SelectCommand = cmd ;   
    
			DataSet ds = new DataSet() ;   
			da.Fill(ds) ;

			//找到父页面控件并绑定（这里只对DataGrid控件绑定）
			DataGrid dg = (DataGrid)this.Page.FindControl(""+ViewState["DataControlName"].ToString()+"") ;
			dg.DataSource = ds ;
			dg.DataBind() ;

			da.Dispose() ;
			cmd.Dispose() ;
			conn.Close() ;

			//控制分页按扭状态
			this.StatsLinkButton() ;
		}

		#endregion

		#region 控制分页按扭状态

		private void StatsLinkButton()
		{
			int CurrentPage = int.Parse(ViewState["CurrentPage"].ToString()) ;   
			int PageCount = this.GetPageCount() ; 
			if(PageCount > 0)
				this.txt_CurrentPage.Enabled = true ;
			else
				this.txt_CurrentPage.Enabled = false ;

			//若当前页为第一页
			if(CurrentPage <=1 )   
			{         
				this.lkbFirst.Enabled = false ;   
				this.lkbPre.Enabled = false ;       
			}
			else
			{
				this.lkbFirst.Enabled = true ;   
				this.lkbPre.Enabled = true ;       
			}
			//若当前页为最后页 
			if(CurrentPage >= PageCount)  
			{         
				this.lkbLast.Enabled = false ;   
				this.lkbNext.Enabled = false ;   
			}
			else
			{
				this.lkbLast.Enabled = true ;   
				this.lkbNext.Enabled = true ;   
			}
		}

		#endregion

		#region 得到记录总数、总页数

		//记录总数
		private int GetRecordCount()   
		{   
			int RecordCount = 0 ;
   
			string sql = "select count(*) from "+ViewState["TableName"].ToString()+" where 1=1" ;
			if(ViewState["SqlWhere"] != null && ViewState["SqlWhere"].ToString() != "")
				sql = sql + " and "+ViewState["SqlWhere"].ToString()+"" ;

			SqlConnection conn = new SqlConnection(strconn) ;   
			SqlCommand cmd = new SqlCommand(sql,conn) ;   
			conn.Open() ;
			RecordCount = int.Parse(cmd.ExecuteScalar().ToString()) ;
			cmd.Dispose() ;
			conn.Close() ;
     
			return RecordCount ;       
		} 

		//总页数
		private int GetPageCount()   
		{   
			int RecordCount = 0 ;   
			int YeShu = 0 ;
			int psize = int.Parse(ViewState["PageSize"].ToString()) ;

			string sql = "select count(*) from "+ViewState["TableName"].ToString()+" where 1=1" ;
			if(ViewState["SqlWhere"] != null && ViewState["SqlWhere"].ToString() != "")
				sql = sql + " and "+ViewState["SqlWhere"].ToString()+"" ;

			SqlConnection conn = new SqlConnection(strconn) ;   
			SqlCommand cmd = new SqlCommand(sql,conn) ;   
			conn.Open() ;
			RecordCount = int.Parse(cmd.ExecuteScalar().ToString()) ;
			cmd.Dispose() ;
			conn.Close() ;
     
			YeShu = RecordCount % psize ;   
    
			if(YeShu == 0)   
			{   
				return RecordCount/psize ;       
			}   
			else   
			{   
				return RecordCount/psize + 1 ;   
			}    
		} 
  
		#endregion

		#region 跳转

		private void txt_CurrentPage_TextChanged(object sender, System.EventArgs e)
		{
			try
			{
				int num = Convert.ToInt32(this.txt_CurrentPage.Text) ;
				if(num > this.GetPageCount())
				{
					Page.RegisterStartupScript("","<script>alert('输入的页数已超出总页数，请重新输入！')</script>") ;
					return ;
				}

				ViewState["CurrentPage"] = num ;  
 
				this.ProData() ;   
			}
			catch(Exception ee)
			{
				Page.RegisterStartupScript("","<script>alert('请输入正确的页数！')</script>") ;
				return ;
			}		
		}

		#endregion

		#region 传值后再绑定，用于有条件查询（前台调用）

		/// <summary>
		/// 传值后再绑定，用于有条件查询
		/// </summary>
		public void GetDataByCond()
		{
			//默认显示为第1页
			ViewState["CurrentPage"] = ViewState["CurrentPage"] == null || ViewState["CurrentPage"].ToString() == "" ? "1" : ViewState["CurrentPage"].ToString() ;
			//每页显示记录总数
			ViewState["PageSize"] = ViewState["PageSize"] == null || ViewState["PageSize"].ToString() == "" ? 10 : int.Parse(ViewState["PageSize"].ToString()) ;

			this.BindGridData() ;
		}

		#endregion
	}
}