<%@ Page Language="C#" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %><html> <title>Custom Pagination - Step 2</title> <style> a {behavior:url(MouseOver.htc);} hr {height:2px;color:black;} .StdText {font-family:verdana;font-size:9pt;font-weight:bold;} .StdTextBox {font-family:verdana;font-size:9pt;border:solid 1px black;filter:progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2, Color='gray', Positive='true')} .Shadow {filter:progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2, Color='gray', Positive='true');} </style> <script runat="server"> public void Page_Load(Object sender, EventArgs e) { // Initialize only the first time... if (!Page.IsPostBack) { lblURL.Text = Request.Url + "<hr>"; SetVirtualItemCount(); ViewState["SortExpression"] = "lastname"; } }SqlDataReader dr; private SqlDataReader CreateDataSource(int nPageIndex) { // nPageIndex is 0-based but we need it to be 1-based nPageIndex ++; // Set up the connection String strConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;"; SqlConnection conn = new SqlConnection(strConn); // Prepare the SQL command StringBuilder sb = new StringBuilder(""); sb.Append("SELECT TOP {0} * FROM "); sb.Append("(SELECT TOP {0} employeeid, firstname, lastname FROM Employees "); sb.Append("WHERE {1} IN "); sb.Append("(SELECT TOP {2} {1} FROM Employees ORDER BY {1}) "); sb.Append("ORDER BY {1} DESC) AS tmp "); sb.Append("ORDER BY {1}"); String strCmd = sb.ToString(); // Set pseudo-parameters: RowsToFetch, SortField and RowsToFetch // {0} - rowstodisplay // {1} - sortfield // {2} - rowstofetch // Adjust the number of rows to display in the last page int nRowsToDisplay = grid.PageSize; int nMod = grid.VirtualItemCount % grid.PageSize; if (nPageIndex == grid.PageCount && nMod >0) nRowsToDisplay = nMod; strCmd = String.Format(strCmd, nRowsToDisplay, ViewState["SortExpression"], grid.PageSize * nPageIndex); SqlCommand cmd = new SqlCommand(strCmd, conn);
// Execute the command conn.Open(); dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return dr; }public void ItemCreated(Object sender, DataGridItemEventArgs e) { ListItemType elemType = e.Item.ItemType; if (elemType == ListItemType.Pager) { // The pager as a whole has the following layout: // // <TR><TD colspan=X> ... links ... </TD></TR> // // Item points to <TR>. The code below moves to <TD>. TableCell pager = (TableCell) e.Item.Controls[0]; // Loop through the pager buttons skipping over blanks // (Blanks are treated as LiteralControl(s) for (int i=0; i<pager.Controls.Count; i+=2) { Object o = pager.Controls[i]; if (o is LinkButton) { LinkButton h = (LinkButton) o; h.Text = "[ " + h.Text + " ]"; } else { Label l = (Label) o; l.Text = "Page " + l.Text; } } } }public void PageIndexChanged(Object sender, DataGridPageChangedEventArgs e) { grid.CurrentPageIndex = e.NewPageIndex; grid.DataSource = CreateDataSource(grid.CurrentPageIndex); grid.DataBind(); dr.Close(); }public void SortCommand(Object sender, DataGridSortCommandEventArgs e) { ViewState["SortExpression"] = e.SortExpression; grid.DataSource = CreateDataSource(grid.CurrentPageIndex); grid.DataBind(); dr.Close(); }public void OnLoadData(Object sender, EventArgs e) { grid.DataSource = CreateDataSource(0); grid.DataBind(); dr.Close(); }public void SetVirtualItemCount() { // Set up the connection String strConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;"; SqlConnection conn = new SqlConnection(strConn); // Set up the command String strCmd = "SELECT COUNT(*) FROM Employees"; SqlCommand cmd = new SqlCommand(strCmd, conn);
using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Text;namespace DataReaderPaging { /// <summary> /// WebForm1 的摘要说明。 /// </summary> public class WebForm1 : System.Web.UI.Page { protected System.Web.UI.WebControls.LinkButton LinkButton1; protected System.Web.UI.WebControls.DataGrid grid;
private void Page_Load(object sender, System.EventArgs e) { if (!IsPostBack) { //计算记录的总数 SetVirtualItemCount(); //设置排序的关键字的字段名 ViewState["SortExpression"] = "employeeid"; //设置grid的分页属性 和 每页的行数 grid.AllowPaging = true; grid.AllowCustomPaging = true; grid.PageSize = 5; } // 在此处放置用户代码以初始化页面 } //计算记录的总数 public void SetVirtualItemCount() { // Set up the connection String strConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;"; SqlConnection conn = new SqlConnection(strConn); // Set up the command String strCmd = "SELECT COUNT(*) FROM Employees"; SqlCommand cmd = new SqlCommand(strCmd, conn);
// Execute the command conn.Open(); int nItemCount = (int) cmd.ExecuteScalar(); conn.Close(); grid.VirtualItemCount = nItemCount; return; } SqlDataReader dr; public SqlDataReader CreateDataSource(int nPageIndex) { nPageIndex ++;
String sConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;"; // Prepare the SQL command StringBuilder sb = new StringBuilder(""); sb.Append("SELECT TOP {0} * FROM "); sb.Append("(SELECT TOP {0} employeeid, firstname, lastname FROM Employees "); sb.Append("WHERE {1} IN "); sb.Append("(SELECT TOP {2} {1} FROM Employees ORDER BY {1}) "); sb.Append("ORDER BY {1} DESC) AS tmp "); sb.Append("ORDER BY {1}"); String strCmd = sb.ToString(); // Set pseudo-parameters: RowsToFetch, SortField and RowsToFetch // {0} - rowstodisplay 显示的记录条数 // {1} - sortfield 排序关键字段名 // {2} - rowstofetch 从后台取得记录数 int nRowsToDisplay = grid.PageSize; int nMod = grid.VirtualItemCount % grid.PageSize; if (nPageIndex == grid.PageCount && nMod >0) nRowsToDisplay = nMod; strCmd = String.Format(strCmd, nRowsToDisplay, ViewState["SortExpression"], grid.PageSize * nPageIndex); SqlConnection conn = new SqlConnection(sConn); conn.Open();
可能是好的解决方法，有没有例子或者网站介绍这方面的情况？感谢yeefly
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %><html>
<title>Custom Pagination - Step 2</title>
<style>
a {behavior:url(MouseOver.htc);}
hr {height:2px;color:black;}
.StdText {font-family:verdana;font-size:9pt;font-weight:bold;}
.StdTextBox {font-family:verdana;font-size:9pt;border:solid 1px black;filter:progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2, Color='gray', Positive='true')}
.Shadow {filter:progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2, Color='gray', Positive='true');}
</style>
<script runat="server">
public void Page_Load(Object sender, EventArgs e)
{
// Initialize only the first time...
if (!Page.IsPostBack)
{
lblURL.Text = Request.Url + "<hr>";
SetVirtualItemCount();
ViewState["SortExpression"] = "lastname";
}
}SqlDataReader dr;
private SqlDataReader CreateDataSource(int nPageIndex)
{
// nPageIndex is 0-based but we need it to be 1-based
nPageIndex ++; // Set up the connection
String strConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;";
SqlConnection conn = new SqlConnection(strConn); // Prepare the SQL command
StringBuilder sb = new StringBuilder("");
sb.Append("SELECT TOP {0} * FROM ");
sb.Append("(SELECT TOP {0} employeeid, firstname, lastname FROM Employees ");
sb.Append("WHERE {1} IN ");
sb.Append("(SELECT TOP {2} {1} FROM Employees ORDER BY {1}) ");
sb.Append("ORDER BY {1} DESC) AS tmp ");
sb.Append("ORDER BY {1}");
String strCmd = sb.ToString(); // Set pseudo-parameters: RowsToFetch, SortField and RowsToFetch
// {0} - rowstodisplay
// {1} - sortfield
// {2} - rowstofetch // Adjust the number of rows to display in the last page
int nRowsToDisplay = grid.PageSize;
int nMod = grid.VirtualItemCount % grid.PageSize;
if (nPageIndex == grid.PageCount && nMod >0)
nRowsToDisplay = nMod; strCmd = String.Format(strCmd,
nRowsToDisplay,
ViewState["SortExpression"],
grid.PageSize * nPageIndex);
SqlCommand cmd = new SqlCommand(strCmd, conn);
// Execute the command
conn.Open();
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}public void ItemCreated(Object sender, DataGridItemEventArgs e)
{
ListItemType elemType = e.Item.ItemType;
if (elemType == ListItemType.Pager)
{
// The pager as a whole has the following layout:
//
// <TR><TD colspan=X> ... links ... </TD></TR>
//
// Item points to <TR>. The code below moves to <TD>.
TableCell pager = (TableCell) e.Item.Controls[0]; // Loop through the pager buttons skipping over blanks
// (Blanks are treated as LiteralControl(s)
for (int i=0; i<pager.Controls.Count; i+=2)
{
Object o = pager.Controls[i];
if (o is LinkButton)
{
LinkButton h = (LinkButton) o;
h.Text = "[ " + h.Text + " ]";
}
else
{
Label l = (Label) o;
l.Text = "Page " + l.Text;
}
}
}
}public void PageIndexChanged(Object sender, DataGridPageChangedEventArgs e)
{
grid.CurrentPageIndex = e.NewPageIndex;
grid.DataSource = CreateDataSource(grid.CurrentPageIndex);
grid.DataBind();
dr.Close();
}public void SortCommand(Object sender, DataGridSortCommandEventArgs e)
{
ViewState["SortExpression"] = e.SortExpression;
grid.DataSource = CreateDataSource(grid.CurrentPageIndex);
grid.DataBind();
dr.Close();
}public void OnLoadData(Object sender, EventArgs e)
{
grid.DataSource = CreateDataSource(0);
grid.DataBind();
dr.Close();
}public void SetVirtualItemCount()
{
// Set up the connection
String strConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;";
SqlConnection conn = new SqlConnection(strConn); // Set up the command
String strCmd = "SELECT COUNT(*) FROM Employees";
SqlCommand cmd = new SqlCommand(strCmd, conn);
// Execute the command
conn.Open();
int nItemCount = (int) cmd.ExecuteScalar();
conn.Close(); grid.VirtualItemCount = nItemCount;
return;
}
</script>
<body bgcolor="ivory" style="font-family:arial;font-size:xsmall"><!-- ASP.NET topbar -->
<h2>Custom Pagination Using Generic SQL </h2>
<asp:Label runat="server" cssclass="StdText" font-bold="true">Current path: </asp:label>
<asp:Label runat="server" id="lblURL" cssclass="StdText" style="color:blue"></asp:label><form runat="server"><!-- Query -->
<asp:label runat="server" cssclass="stdtext" Text="Query" />
<asp:textbox runat="server" Enabled="false" cssclass="stdtextbox" width="400px"
text="SELECT employeeid, firstname, lastname FROM Employees" />
<asp:linkbutton runat="server" cssclass="stdtext" OnClick="OnLoadData" Text="Go get data..." />
<hr><!-- Show the information -->
<asp:datagrid runat="server" id="grid"
Font-Size="Smaller" Font-Names="Verdana"
CellPadding="2" CellSpacing="0" GridLines="vertical"
CssClass="Shadow" BackColor="White"
BorderStyle="solid" BorderColor="black" BorderWidth="1"
PageSize="2"
AllowSorting="true"
OnSortCommand="SortCommand"
AllowPaging="True"
AllowCustomPaging="True"
OnItemCreated="ItemCreated"
OnPageIndexChanged="PageIndexChanged"> <PagerStyle Font-Bold="true" Mode="NumericPages" BackColor="palegreen" />
<AlternatingItemStyle BackColor="#eeeeee" />
<ItemStyle BackColor="White" />
<HeaderStyle Font-Bold="True" ForeColor="White" BackColor="Navy" />
</asp:datagrid></form>
</body></html>
再用dataset和adapter.fill(开始记录,结尾记录,"表名")
这样就可以了，每次fill的只是你指定的记录数，而不是全数记数，在分页显示有多少页那里其实是显示的datareader读取回来的数据处理后的内容。datagrid太难使用了，而且不能自定样式，不方便。
快来结帐吧我今天看到一个不错的代码，你看看应该有用的
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Text;namespace DataReaderPaging
{
/// <summary>
/// WebForm1 的摘要说明。
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.LinkButton LinkButton1;
protected System.Web.UI.WebControls.DataGrid grid;
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
//计算记录的总数
SetVirtualItemCount();
//设置排序的关键字的字段名
ViewState["SortExpression"] = "employeeid"; //设置grid的分页属性 和 每页的行数
grid.AllowPaging = true;
grid.AllowCustomPaging = true;
grid.PageSize = 5; }
// 在此处放置用户代码以初始化页面
}
//计算记录的总数
public void SetVirtualItemCount()
{
// Set up the connection
String strConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;";
SqlConnection conn = new SqlConnection(strConn); // Set up the command
String strCmd = "SELECT COUNT(*) FROM Employees";
SqlCommand cmd = new SqlCommand(strCmd, conn);
// Execute the command
conn.Open();
int nItemCount = (int) cmd.ExecuteScalar();
conn.Close(); grid.VirtualItemCount = nItemCount;
return;
}
SqlDataReader dr;
public SqlDataReader CreateDataSource(int nPageIndex)
{
nPageIndex ++;
String sConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;";
// Prepare the SQL command
StringBuilder sb = new StringBuilder("");
sb.Append("SELECT TOP {0} * FROM ");
sb.Append("(SELECT TOP {0} employeeid, firstname, lastname FROM Employees ");
sb.Append("WHERE {1} IN ");
sb.Append("(SELECT TOP {2} {1} FROM Employees ORDER BY {1}) ");
sb.Append("ORDER BY {1} DESC) AS tmp ");
sb.Append("ORDER BY {1}");
String strCmd = sb.ToString(); // Set pseudo-parameters: RowsToFetch, SortField and RowsToFetch
// {0} - rowstodisplay 显示的记录条数
// {1} - sortfield 排序关键字段名
// {2} - rowstofetch 从后台取得记录数 int nRowsToDisplay = grid.PageSize;
int nMod = grid.VirtualItemCount % grid.PageSize;
if (nPageIndex == grid.PageCount && nMod >0)
nRowsToDisplay = nMod; strCmd = String.Format(strCmd,
nRowsToDisplay,
ViewState["SortExpression"],
grid.PageSize * nPageIndex);
SqlConnection conn = new SqlConnection(sConn);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = strCmd;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return dr; } #region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.grid.PageIndexChanged += new System.Web.UI.WebControls.DataGridPageChangedEventHandler(this.grid_PageIndexChanged);
this.LinkButton1.Click += new System.EventHandler(this.LinkButton1_Click);
this.Load += new System.EventHandler(this.Page_Load); }
#endregion private void grid_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
grid.CurrentPageIndex = e.NewPageIndex;
grid.DataSource = CreateDataSource(grid.CurrentPageIndex);
grid.DataBind();
dr.Close();
} private void LinkButton1_Click(object sender, System.EventArgs e)
{
grid.DataSource = CreateDataSource(0);
grid.DataBind();
}
}
}可以试试，别忘了把"DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;";
的密码加上你自己sql server的密码