webpager.cs

商业源码

				pageList.Items.Add("");
				pageList.Enabled = false;
				pageList.SelectedIndex = 0; 
			}
			else // Populate the list
			{
				for(int i=1; i<=TotalPages; i++)
				{
					ListItem item = new ListItem(i.ToString(), (i-1).ToString());
					pageList.Items.Add(item);
				}
				pageList.SelectedIndex = CurrentPageIndex;
			}
			cell.Controls.Add(pageList);
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE BuildNumericPagesUI
		// Generates the HTML markup for the Numeric Pages button bar
		private void BuildNumericPagesUI(TableCell cell)
		{
			// Render a drop-down list  
			DropDownList pageList = new DropDownList();
			pageList.ID = "PageList";
			pageList.AutoPostBack = true;
			pageList.SelectedIndexChanged += new EventHandler(PageList_Click);
			pageList.Font.Name = Font.Name;
			pageList.Font.Size = Font.Size;
			pageList.ForeColor = ForeColor;

			// Embellish the list when there are no pages to list 
			if (TotalPages <=0 || CurrentPageIndex == -1)
			{
				pageList.Items.Add("No pages");
				pageList.Enabled = false;
				pageList.SelectedIndex = 0; 
			}
			else // Populate the list
			{
				for(int i=1; i<=TotalPages; i++)
				{
					ListItem item = new ListItem(i.ToString(), (i-1).ToString());
					pageList.Items.Add(item);
				}
				pageList.SelectedIndex = CurrentPageIndex;
			}
			cell.Controls.Add(pageList);
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE BuildCurrentPage
		// Generates the HTML markup to describe the current page (0-based)
		private void BuildCurrentPage(TableCell cell)
		{
			// Use a standard template: Page X of Y
			if (CurrentPageIndex <0 || CurrentPageIndex >= TotalPages)
				cell.Text = NoPageSelectedText;
			else
				cell.Text = String.Format(CurrentPageText, (CurrentPageIndex+1), TotalPages, TotalRecord, ItemsPerPage);
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE ValidatePageIndex
		// Ensures the CurrentPageIndex is either valid [0,TotalPages) or -1
		private void ValidatePageIndex()
		{
			if (!(CurrentPageIndex >=0 && CurrentPageIndex < TotalPages))
				CurrentPageIndex = -1;
			return;
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE FetchAllData
		// Runs the query for all data to be paged and caches the resulting data
		private void FetchAllData()
		{
			// Looks for data in the ASP.NET Cache
			DataTable data;
			
			data = (DataTable) Page.Cache[CacheKeyName];

			if (data == null)
			{
				// Fix SelectCommand with order-by info
				AdjustSelectCommand(true);

				// If data expired or has never been fetched, go to the database
				OracleDataAdapter adapter = new OracleDataAdapter(SelectCommand, ConnectionString);
				data = new DataTable();
				adapter.Fill(data);
				Page.Cache.Insert(CacheKeyName, data, null, 
					DateTime.Now.AddSeconds(CacheDuration), 
					System.Web.Caching.Cache.NoSlidingExpiration);
			}
		
			// Configures the paged data source component
			if (_dataSource == null)
				_dataSource = new PagedDataSource(); 
			_dataSource.DataSource = data.DefaultView; // must be IEnumerable!
			_dataSource.AllowPaging = true;
			_dataSource.PageSize = ItemsPerPage;
			TotalPages = _dataSource.PageCount; 
			TotalRecord = data.Rows.Count;
			// Ensures the page index is valid 
			ValidatePageIndex();
			if (CurrentPageIndex == -1)
			{
				_dataSource = null;
				return;
			}

			// Selects the page to view
			_dataSource.CurrentPageIndex = CurrentPageIndex;
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE FetchPageData
		// Runs the query to get only the data that fit into the current page
		private void FetchPageData()
		{
			// Need a validated page index to fetch data.
			// Also need the virtual page count to validate the page index
			AdjustSelectCommand(false);
			VirtualRecordCount countInfo = CalculateVirtualRecordCount();
			TotalRecord = countInfo.RecordCount;
			TotalPages = countInfo.PageCount;
			// Validate the page number (ensures CurrentPageIndex is valid or -1)
			ValidatePageIndex();
			if (CurrentPageIndex == -1)
				return;

			// Prepare and run the command
			OracleCommand cmd = PrepareCommand(countInfo);
			if (cmd == null)
				return;
			OracleDataAdapter adapter = new OracleDataAdapter(cmd);
			DataTable data = new DataTable();
			adapter.Fill(data);

			// Configures the paged data source component
			if (_dataSource == null)
				_dataSource = new PagedDataSource(); 
			_dataSource.AllowCustomPaging = true;
			_dataSource.AllowPaging = true;
			_dataSource.CurrentPageIndex = 0;
			//_dataSource.PageSize = ItemsPerPage;
			_dataSource.PageSize = data.Rows.Count;
			_dataSource.VirtualCount = countInfo.RecordCount;
			_dataSource.DataSource = data.DefaultView;	
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE AdjustSelectCommand
		// Strips ORDER-BY clauses from SelectCommand and adds a new one based
		// on SortKeyField
		private void AdjustSelectCommand(bool addCustomSortInfo)
		{
			// Truncate where ORDER BY is found
			string temp = SelectCommand.ToLower();
			int pos = temp.IndexOf("order by"); 
			if (pos > -1)
								SelectCommand = SelectCommand.Substring(0, pos);

				// Add new ORDER BY info if SortKeyField is specified
				if (SortField != "" && addCustomSortInfo)
					SelectCommand += " ORDER BY " + SortField;
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE CalculateVirtualRecordCount
		// Calculates record and page count for the specified query
		private VirtualRecordCount CalculateVirtualRecordCount()
		{
			VirtualRecordCount count = new VirtualRecordCount();

			// Calculate the virtual number of records from the query
			count.RecordCount = GetQueryVirtualCount();
			count.RecordsInLastPage = ItemsPerPage;

			// Calculate the correspondent number of pages
			int lastPage = count.RecordCount/ItemsPerPage;
			int remainder = count.RecordCount % ItemsPerPage;
			if (remainder >0)
				lastPage++;
			count.PageCount = lastPage;
			
			// Calculate the number of items in the last page
			if (remainder >0)
				count.RecordsInLastPage = remainder;
			return count;
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE PrepareCommand
		// Prepares and returns the command object for the reader-based query
		private OracleCommand PrepareCommand(VirtualRecordCount countInfo)
		{
			// No sort field specified: figure it out
						if (SortField == "")
						{
//							// Get metadata for all columns and choose either the primary key
//							// or the 
//							//string text = "SET FMTONLY ON;" + SelectCommand + ";SET FMTONLY OFF;";
//							string text = "SELECT * FROM (" + SelectCommand + ") WHERE 1=2";
//							OracleDataAdapter adapter = new OracleDataAdapter(text, ConnectionString);
//							DataTable t = new DataTable();
//							adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
//							adapter.Fill(t);
//							DataColumn col = null;
//							if (t.PrimaryKey.Length >0)
//								col = t.PrimaryKey[0];
//							else
//								col = t.Columns[0];
//							SortField = col.ColumnName;
						}

			// Determines how many records are to be retrieved.
			// The last page could require less than other pages
			int recsToRetrieve = ItemsPerPage;
			if (CurrentPageIndex == countInfo.PageCount-1)
				recsToRetrieve = countInfo.RecordsInLastPage;
			
			//为Oracle准备排序内容
			if(SortField != "")
			{
				SelectCommand += "order by " + SortField;
			}
			string cmdText = String.Format(QueryPageCommandText, 
				recsToRetrieve,						// {0} --> page size
				ItemsPerPage*(CurrentPageIndex+1),	// {1} --> size * index
				SelectCommand,						// {2} --> base query
				SortField,							// {3} --> key field in the query
				"ASC",								// Default to ascending order
				"DESC",
				ItemsPerPage);

			OracleConnection conn = new OracleConnection(ConnectionString);
			OracleCommand cmd = new OracleCommand(cmdText, conn);
			return cmd;
		}

		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE GetQueryVirtualCount
		// Run a query to get the record count
		private int GetQueryVirtualCount()
		{
			string cmdText = String.Format(QueryCountCommandText, SelectCommand);
			OracleConnection conn = new OracleConnection(ConnectionString);
			OracleCommand cmd = new OracleCommand(cmdText, conn);

			cmd.Connection.Open();
			string rec = cmd.ExecuteScalar().ToString();
			int recCount = Convert.ToInt32(rec);
			//			int recCount = Convert.ToInt32(cmd.ExecuteScalar()); 
			cmd.Connection.Close();

			return recCount;
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE GoToPage
		// Sets the current page index
		private void GoToPage(int pageIndex)
		{
			// Prepares event data
			PageChangedEventArgs e = new PageChangedEventArgs();
			e.OldPageIndex = CurrentPageIndex;
			e.NewPageIndex = pageIndex;

			// Updates the current index
			CurrentPageIndex = pageIndex;

			// Fires the page changed event
			OnPageIndexChanged(e);

			// Binds new data
			DataBind();
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE first_Click
		// Event handler for the << button
		private void first_Click(object sender, EventArgs e)
		{
			GoToPage(0);
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE prev_Click
		// Event handler for the < button
		private void prev_Click(object sender, EventArgs e)
		{
			GoToPage(CurrentPageIndex-1);
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE next_Click
		// Event handler for the > button
		private void next_Click(object sender, EventArgs e)
		{
			GoToPage(CurrentPageIndex+1);
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE last_Click
		// Event handler for the >> button
		private void last_Click(object sender, EventArgs e)
		{
			GoToPage(TotalPages-1);
		}
		// ***********************************************************************

		// ***********************************************************************
		// PRIVATE PageList_Click
		// Event handler for any page selected from the drop-down page list 
		private void PageList_Click(object sender, EventArgs e)
		{
			DropDownList pageList = (DropDownList) sender;
			int pageIndex = Convert.ToInt32(pageList.SelectedItem.Value);
			GoToPage(pageIndex);
		}
		// ***********************************************************************
		#endregion
	}
	#endregion

	#region OleDbPager Control

	[DefaultProperty("SelectCommand")]
	[DefaultEvent("PageIndexChanged")]
	[ToolboxData("<{0}:OleDbPager runat=\"server\" />")]
	public class OleDbPager : 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 = "总记录:<font color=#ff0000><B>{2}</B></font>&nbsp&nbsp页次:<font color=#ff0000><B>{0}</B></font>/<B>{1}</B>页&nbsp&nbsp<B>{3}</B>/页";
		private string NoPageSelectedText = "";
		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 OleDbPager() : base()
		{