Large number of query paging show Microsoft Solutions _ Application Tips

Microsoft's Solution
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 = ""; The determine location for move previous.
static string Lastvisiblecustomer = ""; Last customer in 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 to 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 ();

The 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 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 the 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 ());
}
}