Compartir a través de

Paginar a través de un resultado de consulta

La paginación a través del resultado de una consulta es un proceso que consiste en devolver los resultados de una consulta en subconjuntos menores de datos, o páginas. Se trata de una práctica frecuente para presentar los resultados a un usuario en fragmentos pequeños y fáciles de administrar.

DataAdapter permite devolver únicamente una página de datos mediante sobrecargas del método Fill. Sin embargo, quizás no sea la mejor opción para paginar a través de resultados de consultas grandes ya que, aunque DataAdapter rellena la DataTable o el DataSet de destino sólo con los registros solicitados, se siguen utilizando los recursos para devolver toda la consulta. Para devolver una página de datos a partir de un origen de datos sin utilizar los recursos necesarios con el fin de devolver toda la consulta, hay que especificar otros criterios adicionales para la consulta que reduzcan las filas devueltas a las filas únicamente necesarias.

Para utilizar el método Fill con el fin de devolver una página de datos, hay que especificar un valor startRecord que represente el primer registro de la página de datos y un valor maxRecords que represente el número de registros de la página de datos.

En el siguiente ejemplo de código se muestra cómo utilizar el método Fill para devolver la primera página del resultado de una consulta cuando el tamaño de la página es de cinco registros.

Dim currentIndex As Integer = 0
Dim pageSize As Integer = 5

Dim 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");

En el ejemplo anterior, el DataSet sólo se rellena con cinco registros pero se devuelve toda la tabla Pedidos. Para rellenar el DataSet con esos mismos cinco registros, pero devolver únicamente cinco registros, hay que utilizar las cláusulas TOP y WHERE en la instrucción SQL, como en el ejemplo de código siguiente.

Dim pageSize As Integer = 5

Dim 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");

Hay que tener en cuenta que, al paginar a través del resultado de una consulta de esta forma, hay que conservar el identificador único por el que están ordenadas las filas con el fin de pasar el Id. único al comando con el fin de devolver la siguiente página de registros, tal y como se muestra en el ejemplo de código siguiente.

Dim lastRecord As String = myDS.Tables("Orders").Rows(pageSize - 1)("OrderID").ToString()
[C#]
string lastRecord = myDS.Tables["Orders"].Rows[pageSize - 1]["OrderID"].ToString();

Para devolver la siguiente página de registros utilizando la sobrecarga del método Fill que toma los parámetros startRecord y maxRecords, hay que incrementar el índice del registro actual en el tamaño de página y rellenar la tabla. Recuerde que el servidor de base de datos devuelve todos los resultados de la consulta aunque sólo se agregue una página de registros al DataSet. En el siguiente ejemplo de código se vacía el contenido de las filas de la tabla antes de rellenarse con la siguiente página de datos. Quizás se desee conservar una cierta cantidad de filas devueltas en una caché local para reducir los recorridos hasta el servidor de base de datos.

currentIndex = currentIndex + pageSize

myDS.Tables("Orders").Rows.Clear()

myDA.Fill(myDS, currentIndex, pageSize, "Orders")
[C#]
currentIndex += pageSize;

myDS.Tables["Orders"].Rows.Clear();

myDA.Fill(myDS, currentIndex, pageSize, "Orders");

Para devolver la siguiente página de registros sin que el servidor de base de datos tenga que devolver toda la consulta, hay que especificar criterios restrictivos en la instrucción SELECT de SQL. Como el ejemplo anterior conservaba el último registro devuelto, es posible utilizarlo en la cláusula WHERE con el fin de especificar un punto de partida para la consulta, como se muestra en el ejemplo de código siguiente.

orderSQL = "SELECT TOP " & pageSize & " * FROM Orders WHERE OrderID > " & lastRecord & " ORDER BY OrderID"
myDA.SelectCommand.CommandText = orderSQL

myDS.Tables("Orders").Rows.Clear()

myDA.Fill(myDS, "Orders")
[C#]
orderSQL = "SELECT TOP " + pageSize + " * FROM Orders WHERE OrderID > " + lastRecord + " ORDER BY OrderID";
myDA.SelectCommand.CommandText = orderSQL;

myDS.Tables["Orders"].Rows.Clear();

myDA.Fill(myDS, "Orders");

A continuación se muestra un ejemplo de paginación a través del resultado de una consulta en el que se especifican criterios en la instrucción SQL para devolver sólo una página de registros cada vez desde la base de datos.

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=localhost;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
[C#]
using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;

public class PagingSample: Form
{
  // Form controls.
  Button prevBtn = new Button();
  Button nextBtn = new Button();

  static DataGrid myGrid = new DataGrid();
  static Label pageLbl = new Label();

  // Paging variables.
  static int pageSize = 10;      // Size of viewed page.
  static int totalPages = 0;      // Total pages.
  static int currentPage = 0;      // Current page.
  static string firstVisibleCustomer = "";  // First customer on page to determine location for move previous.
  static string lastVisibleCustomer = "";    // Last customer on page to determine location for move next.

  // DataSet to bind to DataGrid.
  static DataTable custTable;

  // Initialize connection to database and DataAdapter.
  static SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
  static SqlDataAdapter custDA = new SqlDataAdapter("", nwindConn);
  static SqlCommand selCmd = custDA.SelectCommand;

  public static void GetData(string direction)
  {
    // Create SQL statement to return a page of records.
    selCmd.Parameters.Clear();

    switch (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;
        break;
      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;
        break;
      default:
        selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers ORDER BY CustomerID";
        
        // Determine total pages.
        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;
    }

    // Fill a temporary table with query results.
    DataTable tmpTable = new DataTable("Customers");
    int recordsAffected = custDA.Fill(tmpTable);

    // If table does not exist, create it.
    if (custTable == null)
      custTable = tmpTable.Clone();

    // Refresh table if at least one record returned.
    if (recordsAffected > 0)
    {
      switch (direction)
      {
        case "Next":
          currentPage++;
          break;
        case "Previous":
          currentPage--;
          break;
        default:
          currentPage = 1;
          break;
      }

      pageLbl.Text = "Page " + currentPage + " of " + totalPages;

      // Clear rows and add new results.
      custTable.Rows.Clear();

      foreach (DataRow myRow in tmpTable.Rows)
        custTable.ImportRow(myRow);

      // Preserve first and last primary key values.
      DataRow[] ordRows = custTable.Select("", "CustomerID ASC");
      firstVisibleCustomer = ordRows[0][0].ToString();
      lastVisibleCustomer = ordRows[custTable.Rows.Count - 1][0].ToString();
    }
  }



  public PagingSample()
  {
    // Initialize controls and add to form.
    this.ClientSize = new Size(360, 274);
    this.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);
    prevBtn.Click += new EventHandler(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);

    this.Controls.Add(myGrid);
    this.Controls.Add(prevBtn);
    this.Controls.Add(nextBtn);
    this.Controls.Add(pageLbl);
    nextBtn.Click += new EventHandler(Next_OnClick);


    // Populate DataSet with first page of records and bind to grid.
    GetData("Default");
    DataView custDV = new DataView(custTable, "", "CustomerID", DataViewRowState.CurrentRows);
    myGrid.SetDataBinding(custDV, "");
  }



  public static void Prev_OnClick(object sender, EventArgs args)
  {
    GetData("Previous");
  }

  public static void Next_OnClick(object sender, EventArgs args)
  {
    GetData("Next");
  }
}



public class Sample
{
  static void Main()
  {
    Application.Run(new PagingSample());
  }
}

Vea también

Ejemplo de escenarios de ADO.NET | Acceso a datos con ADO.NET | Utilizar proveedores de datos de .NET Framework para obtener acceso a datos | Crear y utilizar DataSets | Crear y utilizar DataTables

  • Last updated on