今天用了下那个分页插件，问题来了，要是SortField不是唯一的话，那sqlserver排序会不知道怎么排，都乱了，导致按下一页出现的东西还是一样的。
仔细研究了下，终于改过来了，要是SortField不是唯一的话，则新增属性IsKey="false"（用字符串），同时指定一个KeyField（不指定的话默认为id字段），这样，排序的要求就变为2个字段的叠加，按SortField＋keyField排序，这样排出来就不会出错了。原来只能降序排，新加了个属性OrderType，取值为"asc"和"desc"，通过这个就可以改变排序方式了．
把代码粘出来，大家研究下，使用说明可参照我以前写的那篇文章，就是多了几个属性，都有默认值，这几个属性是IsKey，KeyField，OrderType，也不多做说明了，编译下就可以自己去试，代码如下：
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 RecordPager
{
#region PagingMode enum
public enum PagingMode
{
Cached,
NonCached
}
#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 RecordCountText = "合计<b><font color=red>{0}</font></b>个 |";
private string CurrentPageText = "页次：<b><font color=red>{0}</font>/{1}</b> <b>{2}</b>个/页";
private string NoPageSelectedText = "No page selected.";
private string NotKeyQueryPageCommandText = "SELECT * FROM " +
"(SELECT TOP {0} * FROM " +
"(SELECT TOP {1} * FROM ({2}) AS t0 ORDER BY {3}+CAST({6} AS varchar(12)) {5}) AS t1 " +
"ORDER BY {3}+CAST({6} AS varchar(12)) {4}) AS t2 " +
"ORDER BY {3}+CAST({6} AS varchar(12)) {5}";
private string QueryPageCommandText = "SELECT * FROM " +
"(SELECT TOP {0} * FROM " +
"(SELECT TOP {1} * FROM ({2}) AS t0 ORDER BY {3} {5}) AS t1 " +
"ORDER BY {3} {4}) AS t2 " +
"ORDER BY {3} {5}";
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;
CurrentPageIndex = 0;
SelectCommand = "";
ConnectionString = "";
ItemsPerPage = 10;
TotalPages = -1;
CacheDuration = 60;
SortField="";
KeyField="id";
IsKey="true";
OrderType="desc";
}
// ***********************************************************************
#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 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;}
}
// ***********************************************************************
// ***********************************************************************
public string OrderType
{
get {return Convert.ToString(ViewState["OrderType"]);}
set
{
ViewState["OrderType"] = value;
if (value.ToLower()=="asc")
OpOrderType="desc";
}
}
private string OpOrderType
{
get {return Convert.ToString(ViewState["OpOrderType"]);}
set {ViewState["OpOrderType"] = value;}
}
public string KeyField
{
get {return Convert.ToString(ViewState["KeyField"]);}
set {ViewState["KeyField"] = value;}
}
public string IsKey
{
get {return Convert.ToString(ViewState["IsKey"]);}
set {ViewState["IsKey"] = value;}
}
// 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))
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;
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;
}
}
// ***********************************************************************
// ***********************************************************************
// 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();
cellNavBar.HorizontalAlign = HorizontalAlign.Right;
cellNavBar.Controls.Add(new LiteralControl(BuildRecordCount(cellNavBar)));
cellNavBar.Controls.Add(new LiteralControl(" "));
BuildNextPrevUI(cellNavBar);
cellNavBar.Controls.Add(new LiteralControl(" "));
cellNavBar.Controls.Add(new LiteralControl(BuildCurrentPage(cellNavBar)));
cellNavBar.Controls.Add(new LiteralControl(" "));
cellNavBar.Controls.Add(new LiteralControl("转到："));
BuildNumericPagesUI(cellNavBar);
cellNavBar.Controls.Add(new LiteralControl(" "));
row.Cells.Add(cellNavBar);
// 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);
bool canMoveForward = (CurrentPageIndex<TotalPages-1);
// Render the << button
LinkButton first = new LinkButton();
first.ID = "First";
first.Click += new EventHandler(first_Click);
first.Font.Name = "verdana";
first.Font.Size = FontUnit.Point(9);
first.ForeColor = ForeColor;
first.ToolTip = "首页";
first.Text = "首页";
first.Enabled = isValidPage && canMoveBack;
cell.Controls.Add(first);
// Add a separator
cell.Controls.Add(new LiteralControl(" "));
// Render the < button
LinkButton prev = new LinkButton();
prev.ID = "Prev";
prev.Click += new EventHandler(prev_Click);
prev.Font.Name = "verdana";
prev.Font.Size = FontUnit.Point(9);
prev.ForeColor = ForeColor;
prev.ToolTip = "上一页";
prev.Text = "上一页";
prev.Enabled = isValidPage && canMoveBack;
cell.Controls.Add(prev);
// Add a separator
cell.Controls.Add(new LiteralControl(" "));
// Render the > button
LinkButton next = new LinkButton();
next.ID = "Next";
next.Click += new EventHandler(next_Click);
next.Font.Name = "verdana";
next.Font.Size = FontUnit.Point(9);
next.ForeColor = ForeColor;
next.ToolTip = "下一页";
next.Text = "下一页";
next.Enabled = isValidPage && canMoveForward;
cell.Controls.Add(next);
// Add a separator
cell.Controls.Add(new LiteralControl(" "));
// Render the >> button
LinkButton last = new LinkButton();
last.ID = "Last";
last.Click += new EventHandler(last_Click);
last.Font.Name = "verdana";
last.Font.Size = FontUnit.Point(9);
last.ForeColor = ForeColor;
last.ToolTip = "尾页";
last.Text = "尾页";
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 string BuildCurrentPage(TableCell cell)
{
// Use a standard template: Page X of Y
if (CurrentPageIndex <0 || CurrentPageIndex >= TotalPages)
return(NoPageSelectedText);
else
return(String.Format(CurrentPageText, (CurrentPageIndex+1), TotalPages,ItemsPerPage));
}
// ***********************************************************************
// PRIVATE BuildCurrentPage
// Generates the HTML markup to describe the current page (0-based)
private string BuildRecordCount(TableCell cell)
{
// Use a standard template: Page X of Y
return(String.Format(RecordCountText, GetQueryVirtualCount()));
}
// ***********************************************************************
// ***********************************************************************
// 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;
// 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 =SelectCommand+" ORDER BY " + SortField + " DESC";
}
// ***********************************************************************
// ***********************************************************************
// 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;
}
//AdjustSelectCommand(true);
// 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=null;
if (IsKey.ToLower()=="true")
{
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
OpOrderType, // Default to ascending order
OrderType);
}
if (IsKey.ToLower()=="false")
{
cmdText = String.Format(NotKeyQueryPageCommandText,
recsToRetrieve, // {0} --> page size
ItemsPerPage*(CurrentPageIndex+1), // {1} --> size * index
SelectCommand, // {2} --> base query
SortField, // {3} --> key field in the query
OpOrderType, // Default to ascending order
OrderType,
KeyField);
}
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
}
闪人~~