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

   
  public class Sample
  {
   static void Main()
   {
   Application.Run(new PagingSample());
   }
  }
  [VB.NET]
  
  Imports System
  Imports System.Data
  Imports System.Data.SqlClient
  Imports System.Drawing
  Imports System.Windows.Forms
  
  Public Class PagingSample
   Inherits Form
  
   ' Form controls.
   Dim prevBtn As Button = New Button()
   Dim nextBtn As Button = New Button()
  
   Shared myGrid As DataGrid = New DataGrid()
   Shared pageLbl As Label = New Label()
  
   ' Paging variables.
   Shared pageSize As Integer = 10 ' Size of viewed page.
   Shared totalPages As Integer = 0 ' Total pages.
   Shared currentPage As Integer = 0 ' Current page.
   Shared firstVisibleCustomer As String = "" ' First customer on page to determine location for move previous.
   Shared lastVisibleCustomer As String = "" ' Last customer on page to determine location for move next.
  
   ' DataSet to bind to DataGrid.
   Shared custTable As DataTable
  
   ' Initialize connection to database and DataAdapter.
   Shared nwindConn As SqlConnection = New SqlConnection("Data Source=.;Integrated Security=SSPI;Initial Catalog=northwind")
   Shared custDA As SqlDataAdapter = New SqlDataAdapter("", nwindConn)
   Shared selCmd As SqlCommand = custDA.SelectCommand()
  
  
   Public Shared Sub GetData(direction As String)
  
   ' Create SQL statement to return a page of records.
   selCmd.Parameters.Clear()
  
   Select Case direction
   Case "Next"
   selCmd.CommandText = "SELECT TOP " & pageSize & " CustomerID, CompanyName FROM Customers " & _
   "WHERE CustomerID > @CustomerId ORDER BY CustomerID"
   selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = lastVisibleCustomer
   Case "Previous"
   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
   Case Else
   selCmd.CommandText = "SELECT TOP " & pageSize & " CustomerID, CompanyName FROM Customers ORDER BY CustomerID"
  
   ' Determine total pages.
   Dim totCMD As SqlCommand = New SqlCommand("SELECT Count(*) FROM Customers", nwindConn)
   nwindConn.Open()
   Dim totalRecords As Integer = CInt(totCMD.ExecuteScalar())
   nwindConn.Close()
   totalPages = CInt(Math.Ceiling(CDbl(totalRecords) / pageSize))
   End Select
  
   ' Fill a temporary table with query results.
   Dim tmpTable As DataTable = New DataTable("Customers")
   Dim recordsAffected As Integer = custDA.Fill(tmpTable)
  
   ' If table does not exist, create it.
   If custTable Is Nothing Then custTable = tmpTable.Clone()
  
   ' Refresh table if at least one record returned.
   If recordsAffected > 0 Then
   Select Case direction
   Case "Next"
   currentPage += 1
   Case "Previous"
   currentPage += -1
   Case Else
   currentPage = 1
   End Select
  
   pageLbl.Text = "Page " & currentPage & " of " & totalPages
  
   ' Clear rows and add New results.
   custTable.Rows.Clear()
  
   Dim myRow As DataRow
   For Each myRow In tmpTable.Rows
   custTable.ImportRow(myRow)
   Next
  
   ' Preserve first and last primary key values.
   Dim ordRows() As DataRow = custTable.Select("", "CustomerID ASC")
   firstVisibleCustomer = ordRows(0)(0).ToString()
   lastVisibleCustomer = ordRows(custTable.Rows.Count - 1)(0).ToString()
   End If
   End Sub
  
  
   Public Sub New()
   MyBase.New
  
   ' Initialize controls and add to form.
   Me.ClientSize = New Size(360, 274)
   Me.Text = "NorthWind Data"
  
   myGrid.Location = New Point(10,10)
   myGrid.Size = New Size(340, 220)
   myGrid.AllowSorting = true
   myGrid.CaptionText = "NorthWind Customers"
   myGrid.ReadOnly = true
   myGrid.AllowNavigation = false
   myGrid.PreferredColumnWidth = 150
  
   prevBtn.Text = "<<"
   prevBtn.Size = New Size(48, 24)
   prevBtn.Location = New Point(92, 240)
   AddHandler prevBtn.Click, New EventHandler(AddressOf Prev_OnClick)
  
   nextBtn.Text = ">>"
   nextBtn.Size = New Size(48, 24)
   nextBtn.Location = New Point(160, 240)
  
   pageLbl.Text = "No Records Returned."
   pageLbl.Size = New Size(130, 16)
   pageLbl.Location = New Point(218, 244)
  
   Me.Controls.Add(myGrid)
   Me.Controls.Add(prevBtn)
   Me.Controls.Add(nextBtn)
   Me.Controls.Add(pageLbl)
   AddHandler nextBtn.Click, New EventHandler(AddressOf Next_OnClick)
  
  
   ' Populate DataSet with first page of records and bind to grid.
   GetData("Default")
   Dim custDV As DataView = New DataView(custTable, "", "CustomerID", DataViewRowState.CurrentRows)
   myGrid.SetDataBinding(custDV, "")
   End Sub
  
  
  
   Public Shared Sub Prev_OnClick(sender As Object, args As EventArgs)
   GetData("Previous")
   End Sub
  
   Public Shared Sub Next_OnClick(sender As Object, args As EventArgs)
   GetData("Next")
   End Sub
  End Class
  
  
  Public Class Sample
   Shared Sub Main()
   Application.Run(New PagingSample())
   End Sub
  End Class

查看本文来源