sqlpager.cs

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

			bool canMoveForward = (CurrentPageIndex<TotalPages-1);

			// Render the << button
			LinkButton first = new LinkButton();
			first.ID = "First";
			first.Click += new EventHandler(first_Click);
			first.Font.Name = "webdings";
			first.Font.Size = FontUnit.Medium;
			first.ForeColor = ForeColor;
			first.ToolTip = "First page";
			first.Text = "7";	
			first.Enabled = isValidPage && canMoveBack;
			cell.Controls.Add(first);

			// Add a separator
			cell.Controls.Add(new LiteralControl("&nbsp;"));

			// Render the < button
			LinkButton prev = new LinkButton();
			prev.ID = "Prev";
			prev.Click += new EventHandler(prev_Click);
			prev.Font.Name = "webdings";
			prev.Font.Size = FontUnit.Medium;
			prev.ForeColor = ForeColor;
			prev.ToolTip = "Previous page";
			prev.Text = "3";	
			prev.Enabled = isValidPage && canMoveBack;
			cell.Controls.Add(prev);

			// Add a separator
			cell.Controls.Add(new LiteralControl("&nbsp;"));

			// Render the > button
			LinkButton next = new LinkButton();
			next.ID = "Next";
			next.Click += new EventHandler(next_Click);
			next.Font.Name = "webdings";
			next.Font.Size = FontUnit.Medium;
			next.ForeColor = ForeColor;
			next.ToolTip = "Next page";
			next.Text = "4";	
			next.Enabled = isValidPage && canMoveForward;
			cell.Controls.Add(next);

			// Add a separator
			cell.Controls.Add(new LiteralControl("&nbsp;"));

			// Render the >> button
			LinkButton last = new LinkButton();
			last.ID = "Last";
			last.Click += new EventHandler(last_Click);
			last.Font.Name = "webdings";
			last.Font.Size = FontUnit.Medium;
			last.ForeColor = ForeColor;
			last.ToolTip = "Last page";
			last.Text = "8";
			last.Enabled = isValidPage && canMoveForward;
			cell.Controls.Add(last);
		}
		// ***********************************************************************

		// ***********************************************************************
		// 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);
		}
		// ***********************************************************************

		// ***********************************************************************
		// 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
				SqlDataAdapter adapter = new SqlDataAdapter(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; 

			// 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();
			TotalPages = countInfo.PageCount;
			this.ViewState["RecordCount"]=countInfo.RecordCount;
			// Validate the page number (ensures CurrentPageIndex is valid or -1)
			ValidatePageIndex();
			if (CurrentPageIndex == -1)
				return;

			// Prepare and run the command
			SqlCommand cmd = PrepareCommand(countInfo);
			if (cmd == null)
				return;
			SqlDataAdapter adapter = new SqlDataAdapter(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.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 SqlCommand 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;";
				SqlDataAdapter adapter = new SqlDataAdapter(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;

			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
				"DESC",								// Default to ascending order
				"ASC");

			SqlConnection conn = new SqlConnection(ConnectionString);
			SqlCommand cmd = new SqlCommand(cmdText, conn);
			return cmd;
		}

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

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

			cmd.Connection.Open();
			int recCount = (int) 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
}