seePaging Through a Query Result
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconpagingthroughqueryresult.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconpagingthroughqueryresult.asp
Dim pageSize As Integer = 5Dim orderSQL As String = "SELECT * FROM Orders ORDER BY OrderID"
Dim myDA As SqlDataAdapter = New SqlDataAdapter(orderSQL, nwindConn)Dim myDS As DataSet = New DataSet()
myDA.Fill(myDS, currentIndex, pageSize, "Orders")[C#]int currentIndex = 0;
int pageSize = 5;string orderSQL = "SELECT * FROM Orders ORDER BY OrderID";
SqlDataAdapter myDA = new SqlDataAdapter(orderSQL, nwindConn);DataSet myDS = new DataSet();
myDA.Fill(myDS, currentIndex, pageSize, "Orders");在上面的例子中,DataSet只填充了5条记录,但返回的仍是整个Orders表。如果要达到填充几条返回几天的目的,在SQL语句中使用TOP和WHERE从句即可。例如:[VB.NET]Dim pageSize As Integer = 5Dim orderSQL As String = "SELECT TOP " & pageSize & " * FROM Orders ORDER BY OrderID"
Dim myDA As SqlDataAdapter = New SqlDataAdapter(orderSQL, nwindConn)Dim myDS As DataSet = New DataSet()
myDA.Fill(myDS, "Orders") [C#]int pageSize = 5;string orderSQL = "SELECT TOP " + pageSize + " * FROM Orders ORDER BY OrderID";
SqlDataAdapter myDA = new SqlDataAdapter(orderSQL, nwindConn);DataSet myDS = new DataSet();
myDA.Fill(myDS, "Orders");此时需要注意的是:用这种方法进行的分页,必须自己维护记录排序的唯一标识,为了向下一页请求传递唯一的ID,我们必须象下面那样:[VB.NET]
Dim lastRecord As String = myDS.Tables("Orders").Rows(pageSize - 1)("OrderID").ToString()
[C#]
string lastRecord = myDS.Tables["Orders"].Rows[pageSize - 1]["OrderID"].ToString();下面的代码在Table填充之前进行了清空:[VB.NET]
currentIndex = currentIndex + pageSizemyDS.Tables("Orders").Rows.Clear()myDA.Fill(myDS, currentIndex, pageSize, "Orders")
[C#]
currentIndex += pageSize;myDS.Tables["Orders"].Rows.Clear();myDA.Fill(myDS, currentIndex, pageSize, "Orders");下面是完整的代码: [C#]using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;public class PagingSample: Form
{
// Form 控件.
Button prevBtn = new Button();
Button nextBtn = new Button(); static DataGrid myGrid = new DataGrid();
static Label pageLbl = new Label(); // 分页变量
static int pageSize = 10; // 要显示的页数
static int totalPages = 0; // 总页数
static int currentPage = 0; // 当前页
static string firstVisibleCustomer = ""; // 当前页的第一条记录,用来进行移动“前一页”的定位。
static string lastVisibleCustomer = ""; //当前页的最后条记录,用来进行移动“下一页”的定位。 // DataSet用来绑定到DataGrid.
static DataTable custTable; //初始化连接和DataAdapter.
static SqlConnection nwindConn = new SqlConnection("Data Source=.;Integrated Security=SSPI;Initial Catalog=northwind");
static SqlDataAdapter custDA = new SqlDataAdapter("", nwindConn);
static SqlCommand selCmd = custDA.SelectCommand; public static void GetData(string direction)
{
// 创建返回一页记录的SQL语句
selCmd.Parameters.Clear(); switch (direction)
{
case "下一页":
selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " +
"WHERE CustomerID > @CustomerId ORDER BY CustomerID";
selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = lastVisibleCustomer;
break;
case "前一页":
selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " +
"WHERE CustomerID < @CustomerId ORDER BY CustomerID DESC";
selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = firstVisibleCustomer;
break;
default:
selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers ORDER BY CustomerID";
// 计算总页数
SqlCommand totCMD = new SqlCommand("SELECT Count(*) FROM Customers", nwindConn);
nwindConn.Open();
int totalRecords = (int)totCMD.ExecuteScalar();
nwindConn.Close();
totalPages = (int)Math.Ceiling((double)totalRecords / pageSize); break;
} // 用查询结果填充临时表
DataTable tmpTable = new DataTable("Customers");
int recordsAffected = custDA.Fill(tmpTable); // 如果表不存在,就创建
if (custTable == null)
custTable = tmpTable.Clone(); // 如果有记录返回,就刷新表
if (recordsAffected > 0)
{
switch (direction)
{
case "下一页":
currentPage++;
break;
case "上一页":
currentPage--;
break;
default:
currentPage = 1;
break;
} pageLbl.Text = "第" + currentPage + "/ " + totalPages + "页"; // 清除行集,添加新记录
custTable.Rows.Clear(); foreach (DataRow myRow in tmpTable.Rows)
custTable.ImportRow(myRow); // 保存first 和 last 关键值
DataRow[] ordRows = custTable.Select("", "CustomerID ASC");
firstVisibleCustomer = ordRows[0][0].ToString();
lastVisibleCustomer = ordRows[custTable.Rows.Count - 1][0].ToString();
}
} public PagingSample()
{
// 初始化控件并添加到Form
this.ClientSize = new Size(360, 274);
this.Text = "NorthWind 数据表"; myGrid.Location = new Point(10,10);
myGrid.Size = new Size(340, 220);
myGrid.AllowSorting = true;
myGrid.CaptionText = "NorthWind 客户信息";
myGrid.ReadOnly = true;
myGrid.AllowNavigation = false;
myGrid.PreferredColumnWidth = 150; prevBtn.Text = "前一页";
prevBtn.Size = new Size(60, 24);
prevBtn.Location = new Point(50, 240);
prevBtn.Click += new EventHandler(Prev_OnClick); nextBtn.Text = "下一页";
nextBtn.Size = new Size(60, 24);
nextBtn.Location = new Point(120, 240); pageLbl.Text = "没有记录返回";
pageLbl.Size = new Size(130, 16);
pageLbl.Location = new Point(200, 244); this.Controls.Add(myGrid);
this.Controls.Add(prevBtn);
this.Controls.Add(nextBtn);
this.Controls.Add(pageLbl);
nextBtn.Click += new EventHandler(Next_OnClick);
// 计算默认的第一页,并进行绑定
GetData("Default");
DataView custDV = new DataView(custTable, "", "CustomerID", DataViewRowState.CurrentRows);
myGrid.SetDataBinding(custDV, "");
} public static void Prev_OnClick(object sender, EventArgs args)
{
GetData("前一页");
} public static void Next_OnClick(object sender, EventArgs args)
{
GetData("下一页");
}
}public class Sample
{
static void Main()
{
Application.Run(new PagingSample());
}
}