Paging del risultato di una query
Il paging del risultato di una query corrisponde al processo di restituzione dei risultati di una query in sottoinsiemi di dati di dimensioni inferiori o pagine. Si tratta di una tecnica comunemente utilizzata per la visualizzazione di risultati a un utente in blocchi di dimensioni ridotte e di facile gestione.
In DataAdapter è disponibile una funzionalità che consente la restituzione di un'unica pagina di dati, tramite gli overload del metodo Fill. Questa soluzione potrebbe non rivelarsi ottimale per il paging di una quantità elevata di risultati di query, poiché, mentre DataAdapter riempie la DataTable o il DataSet di destinazione utilizzando solo i record richiesti, le risorse per la restituzione dell'intera query sono ancora in uso. Per restituire una pagina di dati da un'origine dati senza utilizzare le risorse necessarie per la restituzione dell'intera query, specificare dei criteri aggiuntivi per la query, in modo che vengano restituite solo le righe necessarie.
Per utilizzare il metodo Fill per la restituzione di una pagina di dati, specificare startRecord, indicando il primo record della pagina di dati, e maxRecords, indicando il numero di record della pagina di dati.
Nell'esempio di codice seguente viene mostrato come utilizzare il metodo Fill per restituire la prima pagina di un risultato di una query, dove la dimensione della pagina corrisponde a cinque record.
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");
Nell'esempio precedente il DataSet viene riempito solo con cinque record, ma viene restituita l'intera tabella Orders. Per riempire il DataSet con gli stessi cinque record, ma restituire solo cinque record, utilizzare le clausole TOP e WHERE nell'istruzione SQL, come illustrato nell'esempio seguente.
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");
Si noti che quando si esegue in questo modo il paging dei risultati della query, è necessario conservare l'identificatore univoco in base a cui sono ordinate le righe, in modo da passare l'ID univoco al comando per restituire la pagina successiva di record, come illustrato nell'esempio seguente.
Dim lastRecord As String = myDS.Tables("Orders").Rows(pageSize - 1)("OrderID").ToString()
[C#]
string lastRecord = myDS.Tables["Orders"].Rows[pageSize - 1]["OrderID"].ToString();
Per restituire la pagina successiva di record utilizzando l'overload del metodo Fill che accetta i parametri startRecord e maxRecords, incrementare l'indice di record corrente sulla base della dimensione della pagina e riempire la tabella. Si ricordi che il server del database restituisce tutti i risultati della query, anche se a DataSet viene aggiunta solo una pagina di record. Nell'esempio di codice seguente i contenuti delle tabelle vengono cancellati prima che tali tabelle siano riempite con la pagina successiva di dati. Per ridurre i percorsi al server del database, è possibile memorizzare una determinata quantità di righe restituite in una cache locale.
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");
Per restituire la pagina successiva di record senza che il server del database restituisca l'intera query, specificare dei criteri restrittivi per l'istruzione SELECT di SQL. Poiché nell'esempio precedente l'ultimo record restituito viene conservato, è possibile utilizzare tale record nella clausola WHERE per specificare un punto di partenza per la query, come mostrato nel seguente esempio di codice.
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");
Di seguito viene riportato un esempio di paging dei risultati di una query tramite la specifica di criteri in un'istruzione SQL, in modo che dal database venga restituita una sola pagina di record alla volta.
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());
}
}
Vedere anche
Scenari ADO.NET di esempio | Accesso ai dati tramite ADO.NET | Utilizzo di provider di dati .NET Framework per accedere ai dati | Creazione e utilizzo di DataSet | Creazione e utilizzo di DataTable