在 DataGridView 控件中实现实时数据加载的虚拟模式 |
您所在的位置：网站首页 › datagridview自动刷新 › 在 DataGridView 控件中实现实时数据加载的虚拟模式 |
|
在 Windows 窗体 DataGridView 控件中实现实时数据加载的虚拟模式
项目
05/04/2023
在 DataGridView 控件中实现虚拟模式的一个原因是可以仅在需要时检索数据。 这称为“实时数据加载”。 例如，如果你正在处理远程数据库中的一个非常大的表，你可能希望通过仅检索显示所需的数据，并仅在用户将新行滚动到视图中时检索其他数据，从而避免启动延迟。 如果运行应用程序的客户端计算机可用于存储数据的内存量有限，则你可能还希望在从数据库中检索新值时放弃未使用的数据。 以下部分介绍了如何将 DataGridView 控件与实时缓存结合使用。 若要将本主题中的代码复制为单个列表，请参阅如何：在 Windows 窗体 DataGridView 控件中实现实时数据加载的虚拟模式。 窗体以下代码示例定义了一个包含只读 DataGridView 控件的窗体，该控件通过 CellValueNeeded 事件处理程序与 Cache 对象交互。 Cache 对象管理本地存储的值，并使用 DataRetriever 对象从示例 Northwind 数据库的 Orders 表中检索值。 DataRetriever 对象实现了 Cache 类所需的 IDataPageRetriever 接口，还可用于初始化 DataGridView 控件行和列。 本主题后面部分将介绍 IDataPageRetriever、DataRetriever 和 Cache 类型。 注意 将敏感信息（如密码）存储在连接字符串中可能会影响应用程序的安全性。 若要控制对数据库的访问，一种较为安全的方法是使用 Windows 身份验证（也称为集成安全性）。 有关详细信息，请参阅保护连接信息。 using System; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Windows.Forms; 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()); } } Imports System.Data Imports System.Data.SqlClient Imports System.Drawing Imports System.Windows.Forms Public Class VirtualJustInTimeDemo Inherits System.Windows.Forms.Form Private WithEvents dataGridView1 As New DataGridView() Private memoryCache As Cache ' 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 connectionString As String = _ "Initial Catalog=NorthWind;Data Source=localhost;" & _ "Integrated Security=SSPI;Persist Security Info=False" Private table As String = "Orders" Private Sub VirtualJustInTimeDemo_Load( _ ByVal sender As Object, ByVal e As EventArgs) _ Handles Me.Load ' Initialize the form. With Me .AutoSize = True .Controls.Add(Me.dataGridView1) .Text = "DataGridView virtual-mode just-in-time demo" End With ' Complete the initialization of the DataGridView. With Me.dataGridView1 .Size = New Size(800, 250) .Dock = DockStyle.Fill .VirtualMode = True .ReadOnly = True .AllowUserToAddRows = False .AllowUserToOrderColumns = False .SelectionMode = DataGridViewSelectionMode.FullRowSelect End With ' Create a DataRetriever and use it to create a Cache object ' and to initialize the DataGridView columns and rows. Try Dim retriever As New DataRetriever(connectionString, table) memoryCache = New Cache(retriever, 16) For Each column As DataColumn In retriever.Columns dataGridView1.Columns.Add( _ column.ColumnName, column.ColumnName) Next Me.dataGridView1.RowCount = retriever.RowCount Catch ex As SqlException MessageBox.Show("Connection could not be established. " & _ "Verify that the connection string is valid.") Application.Exit() End Try ' Adjust the column widths based on the displayed values. Me.dataGridView1.AutoResizeColumns( _ DataGridViewAutoSizeColumnsMode.DisplayedCells) End Sub Private Sub dataGridView1_CellValueNeeded( _ ByVal sender As Object, ByVal e As DataGridViewCellValueEventArgs) _ Handles dataGridView1.CellValueNeeded e.Value = memoryCache.RetrieveElement(e.RowIndex, e.ColumnIndex) End Sub _ Public Shared Sub Main() Application.Run(New VirtualJustInTimeDemo()) End Sub End Class IDataPageRetriever 接口以下代码示例定义了由 DataRetriever 类实现的 IDataPageRetriever 接口。 此接口中声明的唯一方法是 SupplyPageOfData 方法，该方法需要初始行索引和单页数据中的行数计数。 实现者使用这些值从数据源检索数据子集。 Cache 对象在构造期间使用此接口的实现来加载两个初始数据页。 每当需要未缓存的值时，缓存都会放弃其中一个页面并请求包含来自 IDataPageRetriever 的值的新页面。 public interface IDataPageRetriever { DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage); } Public Interface IDataPageRetriever Function SupplyPageOfData( _ ByVal lowerPageBoundary As Integer, ByVal rowsPerPage As Integer) _ As DataTable End Interface DataRetriever 类以下代码示例定义 DataRetriever 类，该类实现 IDataPageRetriever 接口以从服务器检索数据页。 DataRetriever 类还提供 Columns 和 RowCount 属性，DataGridView 控件将使用这些属性来创建必要的列并将相应数量的空行添加到 Rows 集合。 添加空行是必要的，这样可以使控件表现为好像包含表中的所有数据一样。 这意味着滚动条中的滚动框将具有适当的大小，用户将能够访问表中的任何行。 仅当行滚动到视图中时，CellValueNeeded 事件处理程序才会填充这些行。 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. columnToSortBy ??= this.Columns[0].ColumnName; if (!this.Columns[columnToSortBy].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; } } Public Class DataRetriever Implements IDataPageRetriever Private tableName As String Private command As SqlCommand Public Sub New( _ ByVal connectionString As String, ByVal tableName As String) Dim connection As New SqlConnection(connectionString) connection.Open() command = connection.CreateCommand() Me.tableName = tableName End Sub Private rowCountValue As Integer = -1 Public ReadOnly Property RowCount() As Integer Get ' Return the existing value if it has already been determined. If Not rowCountValue = -1 Then Return rowCountValue End If ' Retrieve the row count from the database. command.CommandText = "SELECT COUNT(*) FROM " & tableName rowCountValue = CInt(command.ExecuteScalar()) Return rowCountValue End Get End Property Private columnsValue As DataColumnCollection Public ReadOnly Property Columns() As DataColumnCollection Get ' Return the existing value if it has already been determined. If columnsValue IsNot Nothing Then Return columnsValue End If ' Retrieve the column information from the database. command.CommandText = "SELECT * FROM " & tableName Dim adapter As New SqlDataAdapter() adapter.SelectCommand = command Dim table As New DataTable() table.Locale = System.Globalization.CultureInfo.InvariantCulture adapter.FillSchema(table, SchemaType.Source) columnsValue = table.Columns Return columnsValue End Get End Property Private commaSeparatedListOfColumnNamesValue As String = Nothing Private ReadOnly Property CommaSeparatedListOfColumnNames() As String Get ' Return the existing value if it has already been determined. If commaSeparatedListOfColumnNamesValue IsNot Nothing Then Return commaSeparatedListOfColumnNamesValue End If ' Store a list of column names for use in the ' SupplyPageOfData method. Dim commaSeparatedColumnNames As New System.Text.StringBuilder() Dim firstColumn As Boolean = True For Each column As DataColumn In Columns If Not firstColumn Then commaSeparatedColumnNames.Append(", ") End If commaSeparatedColumnNames.Append(column.ColumnName) firstColumn = False Next commaSeparatedListOfColumnNamesValue = _ commaSeparatedColumnNames.ToString() Return commaSeparatedListOfColumnNamesValue End Get End Property ' Declare variables to be reused by the SupplyPageOfData method. Private columnToSortBy As String Private adapter As New SqlDataAdapter() Public Function SupplyPageOfData( _ ByVal lowerPageBoundary As Integer, ByVal rowsPerPage As Integer) _ As DataTable Implements IDataPageRetriever.SupplyPageOfData ' Store the name of the ID column. This column must contain unique ' values so the SQL below will work properly. If columnToSortBy Is Nothing Then columnToSortBy = Me.Columns(0).ColumnName End If If Not Me.Columns(columnToSortBy).Unique Then Throw New InvalidOperationException(String.Format( _ "Column {0} must contain unique values.", columnToSortBy)) End If ' 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 Dim table As New DataTable() table.Locale = System.Globalization.CultureInfo.InvariantCulture adapter.Fill(table) Return table End Function End Class Cache 类以下代码示例定义 Cache 类，该类管理通过 IDataPageRetriever 实现填充的两页数据。 Cache 类定义了内部 DataPage 结构，该结构包含一个 DataTable，用于将值存储在单个缓存页面中，并计算表示页面上下边界的行索引。 Cache 类在构造时加载两页数据。 每当 CellValueNeeded 事件请求值时，Cache 对象都会确定该值是否在其两个页面之一中可用，如果是，则返回该页面。 如果该值在本地不可用，则 Cache 对象确定其两个页面中的哪一个与当前显示的行最远，并将该页面替换为包含请求值的新页面，然后返回该页面。 假设一个数据页中的行数与屏幕上一次可以显示的行数相同，则该模型允许用户对表进行分页，以高效地返回到最近查看的页面。 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 = cachePages[0].table .Rows[rowIndex % RowsPerPage][columnIndex].ToString(); return true; } else if (IsRowCachedInPage(1, rowIndex)) { element = cachePages[1].table .Rows[rowIndex % RowsPerPage][columnIndex].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. cachePages[GetIndexToUnusedPage(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) { if (rowIndex > cachePages[0].HighestIndex && rowIndex > cachePages[1].HighestIndex) { int offsetFromPage0 = rowIndex - cachePages[0].HighestIndex; int offsetFromPage1 = rowIndex - cachePages[1].HighestIndex; if (offsetFromPage0 < offsetFromPage1) { return 1; } return 0; } else { int offsetFromPage0 = cachePages[0].LowestIndex - rowIndex; int offsetFromPage1 = cachePages[1].LowestIndex - rowIndex; if (offsetFromPage0 < offsetFromPage1) { return 1; } return 0; } } // Returns a value indicating whether the given row index is contained // in the given DataPage. private bool IsRowCachedInPage(int pageNumber, int rowIndex) { return rowIndex = cachePages[pageNumber].LowestIndex; } } Public Class Cache Private Shared RowsPerPage As Integer ' Represents one page of data. Public Structure DataPage Public table As DataTable Private lowestIndexValue As Integer Private highestIndexValue As Integer Public Sub New(ByVal table As DataTable, ByVal rowIndex As Integer) Me.table = table lowestIndexValue = MapToLowerBoundary(rowIndex) highestIndexValue = MapToUpperBoundary(rowIndex) System.Diagnostics.Debug.Assert(lowestIndexValue >= 0) System.Diagnostics.Debug.Assert(highestIndexValue >= 0) End Sub Public ReadOnly Property LowestIndex() As Integer Get Return lowestIndexValue End Get End Property Public ReadOnly Property HighestIndex() As Integer Get Return highestIndexValue End Get End Property Public Shared Function MapToLowerBoundary( _ ByVal rowIndex As Integer) As Integer ' Return the lowest index of a page containing the given index. Return (rowIndex \ RowsPerPage) * RowsPerPage End Function Private Shared Function MapToUpperBoundary( _ ByVal rowIndex As Integer) As Integer ' Return the highest index of a page containing the given index. Return MapToLowerBoundary(rowIndex) + RowsPerPage - 1 End Function End Structure Private cachePages As DataPage() Private dataSupply As IDataPageRetriever Public Sub New(ByVal dataSupplier As IDataPageRetriever, _ ByVal rowsPerPage As Integer) dataSupply = dataSupplier Cache.RowsPerPage = rowsPerPage LoadFirstTwoPages() End Sub ' Sets the value of the element parameter if the value is in the cache. Private Function IfPageCached_ThenSetElement(ByVal rowIndex As Integer, _ ByVal columnIndex As Integer, ByRef element As String) As Boolean If IsRowCachedInPage(0, rowIndex) Then element = cachePages(0).table.Rows(rowIndex Mod RowsPerPage) _ .Item(columnIndex).ToString() Return True ElseIf IsRowCachedInPage(1, rowIndex) Then element = cachePages(1).table.Rows(rowIndex Mod RowsPerPage) _ .Item(columnIndex).ToString() Return True End If Return False End Function Public Function RetrieveElement(ByVal rowIndex As Integer, _ ByVal columnIndex As Integer) As String Dim element As String = Nothing If IfPageCached_ThenSetElement(rowIndex, columnIndex, element) Then Return element Else Return RetrieveData_CacheIt_ThenReturnElement( _ rowIndex, columnIndex) End If End Function Private Sub LoadFirstTwoPages() cachePages = New DataPage() { _ New DataPage(dataSupply.SupplyPageOfData( _ DataPage.MapToLowerBoundary(0), RowsPerPage), 0), _ New DataPage(dataSupply.SupplyPageOfData( _ DataPage.MapToLowerBoundary(RowsPerPage), _ RowsPerPage), RowsPerPage) _ } End Sub Private Function RetrieveData_CacheIt_ThenReturnElement( _ ByVal rowIndex As Integer, ByVal columnIndex As Integer) As String ' Retrieve a page worth of data containing the requested value. Dim table As DataTable = dataSupply.SupplyPageOfData( _ DataPage.MapToLowerBoundary(rowIndex), RowsPerPage) ' Replace the cached page furthest from the requested cell ' with a new page containing the newly retrieved data. cachePages(GetIndexToUnusedPage(rowIndex)) = _ New DataPage(table, rowIndex) Return RetrieveElement(rowIndex, columnIndex) End Function ' Returns the index of the cached page most distant from the given index ' and therefore least likely to be reused. Private Function GetIndexToUnusedPage(ByVal rowIndex As Integer) _ As Integer If rowIndex > cachePages(0).HighestIndex AndAlso _ rowIndex > cachePages(1).HighestIndex Then Dim offsetFromPage0 As Integer = _ rowIndex - cachePages(0).HighestIndex Dim offsetFromPage1 As Integer = _ rowIndex - cachePages(1).HighestIndex If offsetFromPage0 < offsetFromPage1 Then Return 1 End If Return 0 Else Dim offsetFromPage0 As Integer = _ cachePages(0).LowestIndex - rowIndex Dim offsetFromPage1 As Integer = _ cachePages(1).LowestIndex - rowIndex If offsetFromPage0 < offsetFromPage1 Then Return 1 End If Return 0 End If End Function ' Returns a value indicating whether the given row index is contained ' in the given DataPage. Private Function IsRowCachedInPage( _ ByVal pageNumber As Integer, ByVal rowIndex As Integer) As Boolean Return rowIndex = cachePages(pageNumber).LowestIndex End Function End Class 其他注意事项前面提供的代码示例是实时数据加载的演示。 你将需要根据自己的需要修改代码以实现最大效率。 至少，需要为缓存中每页数据的行数选择一个合适的值。 此值将传递到 Cache 构造函数。 每页的行数应不少于可以在 DataGridView 控件中同时显示的行数。 为获得最佳结果，需要进行性能测试和可用性测试，以确定系统和用户的要求。 需要考虑的几个因素包括运行应用程序的客户端计算机中的内存量、所用网络连接的可用带宽以及所用服务器的延迟。 应在高峰使用期间确定带宽和延迟。 若要提高应用程序的滚动性能，可以增加本地存储的数据量。 但是，为了缩短启动时间，必须避免在最初加载过多的数据。 你可能想要修改 Cache 类以增加其可以存储的数据页数。 使用更多数据页可以提高滚动效率，但需要根据可用带宽和服务器延迟确定数据页中的理想行数。 对于较小的页面，将更频繁地访问服务器，但返回请求的数据所需的时间更少。 如果延迟比带宽更重要，则可能需要使用更大的数据页。 另请参阅 DataGridView VirtualMode Windows 窗体 DataGridView 控件中的性能优化 缩放 Windows 窗体 DataGridView 控件的最佳做法 Windows 窗体 DataGridView 控件中的虚拟模式 演练：在 Windows 窗体 DataGridView 控件中实现虚拟模式 如何：在 Windows 窗体 DataGridView 控件中实现实时数据加载的虚拟模式 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |