在Windows窗体DataGridView控件中实现实时数据加载的虚拟模式.doc

在 DataGridView 控件中实现虚拟模式的一个原因是为了只在需要时才检索数据。这称为“实时数据加载”。 例如，如果您正在使用远程数据库中的一个非常大的表，您可能希望只检索显示所需的数据，而且只在用户将新行滚动到视图中时才检索额外的数据，从而避免启动延迟。如果运行您的应用程序的客户端计算机只有少量内存可供存储数据使用，则您可能还希望在从数据库中检索新值时丢弃无用的数据。下面的部分描述如何配合使用 DataGridView 控件与实时缓存。 若要将本主题中的代码作为一个单独的列表进行复制，请参见如何：在 Windows 窗体 DataGridView 控件中实现实时数据加载的虚拟模式。窗体下面的代码示例定义了一个包含只读 DataGridView 控件的窗体，该控件通过 CellValueNeeded 事件处理程序与 Cache 对象进行交互。Cache 对象管理本地存储的值，并使用 DataRetriever 对象从 Northwind 示例数据库的 Orders 表中检索值。DataRetriever 对象（实现 Cache 类所需要的 IDataPageRetriever 接口）还用于初始化 DataGridView 控件的行和列。 IDataPageRetriever、DataRetriever 和 Cache 类型在本主题的稍后部分予以介绍。注意：将敏感信息（如密码）存储在连接字符串中可能会影响您的应用程序的安全性。若要控制对数据库的访问，一种较为安全的方法是使用 Windows 身份验证（也称为集成安全性）。有关更多信息，请参见保护连接信息 (ADO.NET)。C#复制代码public class VirtualJustInTimeDemo : System.Windows.Forms.Form private DataGridView dataGridView1 = new DataGridView(); private Cache memoryCache; / Specify a connection string. Replace the given value with a / valid connection string for a Northwind SQL Server sample / database accessible to your system. private string connectionString = Initial Catalog=NorthWind;Data Source=localhost; + Integrated Security=SSPI;Persist Security Info=False; private string table = Orders; protected override void OnLoad(EventArgs e) / Initialize the form. this.AutoSize = true; this.Controls.Add(this.dataGridView1); this.Text = DataGridView virtual-mode just-in-time demo; / Complete the initialization of the DataGridView. this.dataGridView1.Size = new Size(800, 250); this.dataGridView1.Dock = DockStyle.Fill; this.dataGridView1.VirtualMode = true; this.dataGridView1.ReadOnly = true; this.dataGridView1.AllowUserToAddRows = false; this.dataGridView1.AllowUserToOrderColumns = false; this.dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; this.dataGridView1.CellValueNeeded += new DataGridViewCellValueEventHandler(dataGridView1_CellValueNeeded); / Create a DataRetriever and use it to create a Cache object / and to initialize the DataGridView columns and rows. try DataRetriever retriever = new DataRetriever(connectionString, table); memoryCache = new Cache(retriever, 16); foreach (DataColumn column in retriever.Columns) dataGridView1.Columns.Add( column.ColumnName, column.ColumnName); this.dataGridView1.RowCount = retriever.RowCount; catch (SqlException) MessageBox.Show(Connection could not be established. + Verify that the connection string is valid.); Application.Exit(); / Adjust the column widths based on the displayed values. this.dataGridView1.AutoResizeColumns( DataGridViewAutoSizeColumnsMode.DisplayedCells); base.OnLoad(e); private void dataGridView1_CellValueNeeded(object sender, DataGridViewCellValueEventArgs e) e.Value = memoryCache.RetrieveElement(e.RowIndex, e.ColumnIndex); STAThreadAttribute() public static void Main() Application.Run(new VirtualJustInTimeDemo(); IDataPageRetriever 接口下面的代码示例定义 IDataPageRetriever 接口，该接口由 DataRetriever 类实现。此接口中唯一声明的方法是 SupplyPageOfData 方法，它需要提供一个初始行索引以及单个数据页中的行数。实施者使用这些值来从数据源中检索一个数据子集。Cache 对象在构造期间使用此接口的实现来加载两页初始数据。每当需要未缓存的值时，该缓存将丢弃这两页中的一页，然后从 IDataPageRetriever 那里请求一个包含值的新页。C#复制代码public interface IDataPageRetriever DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage);DataRetriever 类下面的代码示例定义了 DataRetriever 类，该类实现了从服务器检索数据页的 IDataPageRetriever 接口。DataRetriever 类还提供了 Columns 和 RowCount 属性，DataGridView 控件使用这两个属性来创建必要的列，以及将适当数量的空行添加到 Rows 集合。添加空行是必要的，这样该控件就看似包含了表中的所有数据。这意味着滚动条中的滚动框将具有适当的大小，并且用户将能够访问表中的任何行。只有将这些行滚动到视图中时，它们才会由 CellValueNeeded 事件处理程序填充。 C#复制代码public class DataRetriever : IDataPageRetriever private string tableName; private SqlCommand command; public DataRetriever(string connectionString, string tableName) SqlConnection connection = new SqlConnection(connectionString); connection.Open(); command = connection.CreateCommand(); this.tableName = tableName; private int rowCountValue = -1; public int RowCount get / Return the existing value if it has already been determined. if (rowCountValue != -1) return rowCountValue; / Retrieve the row count from the database. command.CommandText = SELECT COUNT(*) FROM + tableName; rowCountValue = (int)command.ExecuteScalar(); return rowCountValue; private DataColumnCollection columnsValue; public DataColumnCollection Columns get / Return the existing value if it has already been determined. if (columnsValue != null) return columnsValue; / Retrieve the column information from the database. command.CommandText = SELECT * FROM + tableName; SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = command; DataTable table = new DataTable(); table.Locale = System.Globalization.CultureInfo.InvariantCulture; adapter.FillSchema(table, SchemaType.Source); columnsValue = table.Columns; return columnsValue; private string commaSeparatedListOfColumnNamesValue = null; private string CommaSeparatedListOfColumnNames get / Return the existing value if it has already been determined. if (commaSeparatedListOfColumnNamesValue != null) return commaSeparatedListOfColumnNamesValue; / Store a list of column names for use in the / SupplyPageOfData method. System.Text.StringBuilder commaSeparatedColumnNames = new System.Text.StringBuilder(); bool firstColumn = true; foreach (DataColumn column in Columns) if (!firstColumn) commaSeparatedColumnNames.Append(, ); commaSeparatedColumnNames.Append(column.ColumnName); firstColumn = false; commaSeparatedListOfColumnNamesValue = commaSeparatedColumnNames.ToString(); return commaSeparatedListOfColumnNamesValue; / Declare variables to be reused by the SupplyPageOfData method. private string columnToSortBy; private SqlDataAdapter adapter = new SqlDataAdapter(); public DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage) / Store the name of the ID column. This column must contain unique / values so the SQL below will work properly. if (columnToSortBy = null) columnToSortBy = this.Columns0.ColumnName; if (!this.ColumnscolumnToSortBy.Unique) throw new InvalidOperationException(String.Format( Column 0 must contain unique values., columnToSortBy); / Retrieve the specified number of rows from the database, starting / with the row specified by the lowerPageBoundary parameter. command.CommandText = Select Top + rowsPerPage + + CommaSeparatedListOfColumnNames + From + tableName + WHERE + columnToSortBy + NOT IN (SELECT TOP + lowerPageBoundary + + columnToSortBy + From + tableName + Order By + columnToSortBy + ) Order By + columnToSortBy; adapter.SelectCommand = command; DataTable table = new DataTable(); table.Locale = System.Globalization.CultureInfo.InvariantCulture; adapter.Fill(table); return table; Cache 类下面的代码示例定义 Cache 类，它管理通过 IDataPageRetriever 实现填充的两个数据页。Cache 类定义了一个 DataPage 内部结构，该结构包含一个 DataTable 以存储单个缓存页中的值，并且计算表示页的上限和下限的行索引。 Cache 类在构造时加载两个数据页。每当 CellValueNeeded 事件请求值时，Cache 对象确定该值是否存在于其两个数据页的其中一页中，如果存在，则返回该值。如果本地不存在该值，则 Cache 对象确定它的两个数据页中哪一页距离当前显示的行最远，然后用包含请求值的新页替换该页，随后返回该请求值。 假如数据页中的行数与屏幕一次可以显示的行数相同，则此模型可以使对表进行分页的用户有效地返回到最近查看过的页。 C#复制代码public class Cache private static int RowsPerPage; / Represents one page of data. public struct DataPage public DataTable table; private int lowestIndexValue; private int highestIndexValue; public DataPage(DataTable table, int rowIndex) this.table = table; lowestIndexValue = MapToLowerBoundary(rowIndex); highestIndexValue = MapToUpperBoundary(rowIndex); System.Diagnostics.Debug.Assert(lowestIndexValue = 0); System.Diagnostics.Debug.Assert(highestIndexValue = 0); public int LowestIndex get return lowestIndexValue; public int HighestIndex get return highestIndexValue; public static int MapToLowerBoundary(int rowIndex) / Return the lowest index of a page containing the given index. return (rowIndex / RowsPerPage) * RowsPerPage; private static int MapToUpperBoundary(int rowIndex) / Return the highest index of a page containing the given index. return MapToLowerBoundary(rowIndex) + RowsPerPage - 1; private DataPage cachePages; private IDataPageRetriever dataSupply; public Cache(IDataPageRetriever dataSupplier, int rowsPerPage) dataSupply = dataSupplier; Cache.RowsPerPage = rowsPerPage; LoadFirstTwoPages(); / Sets the value of the element parameter if the value is in the cache. private bool IfPageCached_ThenSetElement(int rowIndex, int columnIndex, ref string element) if (IsRowCachedInPage(0, rowIndex) element = cachePages0.table .RowsrowIndex % RowsPerPagecolumnIndex.ToString(); return true; else if (IsRowCachedInPage(1, rowIndex) element = cachePages1.table .RowsrowIndex % RowsPerPagecolumnIndex.ToString(); return true; return false; public string RetrieveElement(int rowIndex, int columnIndex) string element = null; if (IfPageCached_ThenSetElement(rowIndex, columnIndex, ref element) return element; else return RetrieveData_CacheIt_ThenReturnElement( rowIndex, columnIndex); private void LoadFirstTwoPages() cachePages = new DataPage new DataPage(dataSupply.SupplyPageOfData( DataPage.MapToLowerBoundary(0), RowsPerPage), 0), new DataPage(dataSupply.SupplyPageOfData( DataPage.MapToLowerBoundary(RowsPerPage), RowsPerPage), RowsPerPage); private string RetrieveData_CacheIt_ThenReturnElement( int rowIndex, int columnIndex) / Retrieve a page worth of data containing the requested value. DataTable table = dataSupply.SupplyPageOfData( DataPage.MapToLowerBoundary(rowIndex), RowsPerPage); / Replace the cached page furthest from the requested cell / with a new page containing the newly retrieved data. cachePagesGetIndexToUnusedPage(rowIndex) = new DataPage(table, rowIndex); return RetrieveElement(rowIndex, columnIndex); / Returns the index of the cached page most distant from the given index / and therefore least likely to be reused. private int GetIndexToUnusedPage(int rowIndex)