Page handling a large amount of data Microsoft solution

Using system; using system.data.sqlclient; using system.drawing; using system.windows.form;

Public class paningsample: 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 dataatable cofrestable

// 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 ();

Moreach (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 (next); 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, evenetargs args) {getData ("next");}}

Public class sample {static void main () {Application.run (new pagingsample ());}}