sqlpager.cs

1、用SQL查询器打开install目录下的dooogo.sql运行之后创建数据库dooogo。 2、然后打开web.config修改 <DbProvider type="Club.Fram

using System;
using System.IO;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.ComponentModel;

namespace Club.Common
{
	#region PagingMode enum
	public enum PagingMode
	{
		Cached,
		NonCached
	}
	#endregion
	
	#region PagerStyle enum
	public enum PagerStyle
	{
		NextPrev,
		NumericPages
	}
	#endregion

	#region VirtualRecordCount class
	public class VirtualRecordCount
	{
		public int RecordCount;
		public int PageCount;
		public int RecordsInLastPage;
	}
	#endregion

	#region PageChangedEventArgs class
	public class PageChangedEventArgs : EventArgs 
	{
		public int OldPageIndex;
		public int NewPageIndex;
	}
	#endregion 

	#region SqlPager Control

	[DefaultProperty("SelectCommand")]
	[DefaultEvent("PageIndexChanged")]
	[ToolboxData("<{0}:SqlPager runat=\"server\" />")]
	public class SqlPager : WebControl, INamingContainer
	{
		#region  PRIVATE DATA MEMBERS 
		// ***********************************************************************
		// PRIVATE members
		private PagedDataSource _dataSource;
		private Control _controlToPaginate;
		private string CacheKeyName 
		{
			get {return Page.Request.FilePath + "_" + UniqueID + "_Data";}
		}
		private string CurrentPageText = "<b>Page</b> {0} <b>of</b> {1}";
		private string NoPageSelectedText = "No page selected.";
		private string QueryPageCommandText = "SELECT * FROM " + 
			"(SELECT TOP {0} * FROM " + 
			"(SELECT TOP {1} * FROM ({2}) AS t0 ORDER BY {3} {4}) AS t1 " + 
			"ORDER BY {3} {5}) AS t2 " + 
			"ORDER BY {3}";
		private string QueryCountCommandText = "SELECT COUNT(*) FROM ({0}) AS t0";
		// ***********************************************************************
		#endregion

		#region CTOR(s) 
		// ***********************************************************************
		// Ctor
		public SqlPager() : base()
		{
			_dataSource = null;
			_controlToPaginate = null;

			Font.Name = "verdana";
			Font.Size = FontUnit.Point(8);
			BackColor = Color.Gainsboro; 
			ForeColor = Color.Black;
			BorderStyle = BorderStyle.Outset;
			BorderWidth = Unit.Parse("1px"); 
			PagingMode = PagingMode.Cached;
			PagerStyle = PagerStyle.NextPrev;
			CurrentPageIndex = 0;
			SelectCommand = "";
			ConnectionString = "";
			ItemsPerPage = 10;
			TotalPages = -1;
			CacheDuration = 60;
		}
		// ***********************************************************************
		#endregion

		#region PUBLIC PROGRAMMING INTERFACE 
		// ***********************************************************************
		// METHOD ClearCache
		// Removes any data cached for paging
		public void ClearCache()
		{
			if (PagingMode == PagingMode.Cached)
				Page.Cache.Remove(CacheKeyName); 
		}
		// ***********************************************************************

		// ***********************************************************************
		// EVENT PageIndexChanged
		// Fires when the pager is about to switch to a new page
		public delegate void PageChangedEventHandler(object sender, PageChangedEventArgs e);
		public event PageChangedEventHandler PageIndexChanged;
		protected virtual void OnPageIndexChanged(PageChangedEventArgs e)
		{
			if (PageIndexChanged != null)
				PageIndexChanged(this, e);
		}
		// ***********************************************************************

		// ***********************************************************************
		// PROPERTY CacheDuration
		[Description("Gets and sets for how many seconds the data should stay in the cache")]
		public int CacheDuration
		{
			get {return Convert.ToInt32(ViewState["CacheDuration"]);}
			set {ViewState["CacheDuration"] = value;}
		}
		// ***********************************************************************

		// ***********************************************************************
		// PROPERTY PagingMode
		[Description("Indicates whether the data are retrieved page by page or can be cached")]
		public PagingMode PagingMode
		{
			get {return (PagingMode) ViewState["PagingMode"];}
			set {ViewState["PagingMode"] = value;}
		}
		// ***********************************************************************

		// ***********************************************************************
		// PROPERTY PagerStyle
		[Description("Indicates the style of the pager's navigation bar")]
		public PagerStyle PagerStyle
		{
			get {return (PagerStyle) ViewState["PagerStyle"];}
			set {ViewState["PagerStyle"] = value;}
		}
		// ***********************************************************************

		// ***********************************************************************
		// PROPERTY ControlToPaginate
		[Description("Gets and sets the name of the control to paginate")]
		public string ControlToPaginate
		{
			get {return Convert.ToString(ViewState["ControlToPaginate"]);}
			set {ViewState["ControlToPaginate"] = value;}
		}
		// ***********************************************************************

		// ***********************************************************************
		// PROPERTY ItemsPerPage
		[Description("Gets and sets the number of items to display per page")]
		public int ItemsPerPage
		{
			get {return Convert.ToInt32(ViewState["ItemsPerPage"]);}
			set {ViewState["ItemsPerPage"] = value;}
		}
		// ***********************************************************************

		// ***********************************************************************
		// PROPERTY CurrentPageIndex
		[Description("Gets and sets the index of the currently displayed page")]
		public int CurrentPageIndex
		{
			get {return Convert.ToInt32(ViewState["CurrentPageIndex"]);}
			set {ViewState["CurrentPageIndex"] = value;}
		}
		[Description("总记录数")]
		public int RecordCount
		{
			get {return Convert.ToInt32(ViewState["RecordCount"]);}
		}
		// ***********************************************************************

		// ***********************************************************************
		// PROPERTY ConnectionString
		[Description("Gets and sets the connection string to access the database")]
		public string ConnectionString
		{
			get {return Convert.ToString(ViewState["ConnectionString"]);}
			set {ViewState["ConnectionString"] = value;}
		}
		// ***********************************************************************

		// ***********************************************************************
		// PROPERTY SelectCommand
		[Description("Gets and sets the SQL query to get data")]
		public string SelectCommand
		{
			get {return Convert.ToString(ViewState["SelectCommand"]);}
			set {ViewState["SelectCommand"] = value;}
		}
		// ***********************************************************************

		// ***********************************************************************
		// PROPERTY SortField
		[Description("Gets and sets the sort-by field. It is mandatory in NonCached mode.)")]
		public string SortField
		{
			get {return Convert.ToString(ViewState["SortKeyField"]);}
			set {ViewState["SortKeyField"] = value;}
		}
		// ***********************************************************************

		// ***********************************************************************
		// PROPERTY PageCount
		// Gets the number of displayable pages 
		[Browsable(false)]
		public int PageCount
		{
			get {return TotalPages;}
		}
		// ***********************************************************************

		// ***********************************************************************
		// PROPERTY TotalPages
		// Gets and sets the number of pages to display 
		protected int TotalPages
		{
			get {return Convert.ToInt32(ViewState["TotalPages"]);}
			set {ViewState["TotalPages"] = value;}
		}
		// ***********************************************************************

		// ***********************************************************************
		// OVERRIDE DataBind
		// Fetches and stores the data
		public override void DataBind()
		{
			// Fires the data binding event
			base.DataBind();

			// Controls must be recreated after data binding 
			ChildControlsCreated = false;

			// Ensures the control exists and is a list control
			if (ControlToPaginate == "")
				return;
			_controlToPaginate = Page.FindControl(ControlToPaginate);
			if (_controlToPaginate == null)
				return;
			if (!(_controlToPaginate is BaseDataList || _controlToPaginate is ListControl || _controlToPaginate is Repeater))
				return;

			// Ensures enough info to connect and query is specified
			if (ConnectionString == "" || SelectCommand == "")
				return;

			// Fetch data
			if (PagingMode == PagingMode.Cached)
				FetchAllData();
			else
			{
				//if (SortField == "")
				//	return;
				FetchPageData();
			}

			// Bind data to the buddy control
			BaseDataList baseDataListControl = null;
			ListControl listControl = null;
			Repeater baseRepeater=null;
			if (_controlToPaginate is BaseDataList)
			{
				baseDataListControl = (BaseDataList) _controlToPaginate;
				baseDataListControl.DataSource = _dataSource; 
				baseDataListControl.DataBind();
				return;
			}
			if (_controlToPaginate is ListControl)
			{
				listControl = (ListControl) _controlToPaginate;
				listControl.Items.Clear();
				listControl.DataSource = _dataSource; 
				listControl.DataBind();
				return;
			}
			if(_controlToPaginate is Repeater)
			{
				baseRepeater = (Repeater) _controlToPaginate;
				baseRepeater.DataSource = _dataSource; 
				baseRepeater.DataBind();
				return;
			}
		}
		// ***********************************************************************

		// ***********************************************************************
		// OVERRIDE Render
		// Writes the content to be rendered on the client
		protected override void Render(HtmlTextWriter output)
		{
			// If in design-mode ensure that child controls have been created.
			// Child controls are not created at this time in design-mode because
			// there's no pre-render stage. Do so for composite controls like this 
			if (Site != null && Site.DesignMode) 
				CreateChildControls();

			base.Render(output);
		}
		// ***********************************************************************

		// ***********************************************************************
		// OVERRIDE CreateChildControls
		// Outputs the HTML markup for the control
		protected override void CreateChildControls()
		{
			Controls.Clear();
			ClearChildViewState();

			BuildControlHierarchy();
		}
		// ***********************************************************************
		#endregion

		#region PRIVATE HELPER METHODS 
		// ***********************************************************************
		// PRIVATE BuildControlHierarchy
		// Control the building of the control's hierarchy
		private void BuildControlHierarchy()
		{
			// Build the surrounding table (one row, two cells)
			Table t = new Table();
			t.Font.Name = Font.Name;
			t.Font.Size = Font.Size;
			t.BorderStyle = BorderStyle;
			t.BorderWidth = BorderWidth;
			t.BorderColor = BorderColor;
			t.Width = Width;
			t.Height = Height;
			t.BackColor = BackColor;
			t.ForeColor = ForeColor;

			// Build the table row
			TableRow row = new TableRow();
			t.Rows.Add(row);

			// Build the cell with navigation bar
			TableCell cellNavBar = new TableCell();
			if (PagerStyle == PagerStyle.NextPrev)
				BuildNextPrevUI(cellNavBar);
			else
				BuildNumericPagesUI(cellNavBar);
			row.Cells.Add(cellNavBar);

			// Build the cell with the page index
			TableCell cellPageDesc = new TableCell();
			cellPageDesc.HorizontalAlign = HorizontalAlign.Right;
			BuildCurrentPage(cellPageDesc);
			row.Cells.Add(cellPageDesc);

			// Add the table to the control tree
			Controls.Add(t);
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE BuildNextPrevUI
		// Generates the HTML markup for the Next/Prev navigation bar
		private void BuildNextPrevUI(TableCell cell)
		{
			bool isValidPage = (CurrentPageIndex >=0 && CurrentPageIndex <= TotalPages-1);
			bool canMoveBack = (CurrentPageIndex>0);