C#通过查询结果进行分页 2

  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("下一页");
   }
  } 
   
  

查看本文来源