Pagination of a large number of queries

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 = ""; // 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 MERs" +
"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 MERs" +
"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 MERs", 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 ("MERs ");
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 MERs ";
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 ());
}
}