大数量查询分页显示 微软的解决办法
点击次数：20 次 发布日期：2008-11-26 14:26:17 作者：源代码网
|
源代码网推荐 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()); 源代码网推荐 } 源代码网推荐 } 源代码网推荐 源代码网推荐 源代码网推荐 做人要厚道，请注明转自酷网动力(www.ASPCOOL.COM)。 源代码网推荐 源代码网供稿. |
