本文介绍了如何在DataGridView中使用虚拟模式处理大量数据，通过只在需要时加载数据，显著提高性能，减少内存占用。通过实例展示了如何利用IDataPageRetriever接口与SQLite数据库配合，以及Cache类的使用来实现动态数据加载和缓存管理。
首先我们解释下什么是虚拟模式实时数据加载
虚拟模式是一种使 DataGridView 以更有效的方式处理大量数据的技术。当在虚拟模式下使用 DataGridView
时，数据并不直接加载到控件中，而是在需要显示的时候才加载，这样可以大大减少内存的占用，提高性能。
例如，如果你正在处理远程数据库中的一个非常大的表，你可能希望通过仅检索显示所需的数据，并仅在用户将新行滚动到视图中时检索其他数据，从而避免启动延迟。 如果运行应用程序的客户端计算机可用于存储数据的内存量有限，则你可能还希望在从数据库中检索新值时放弃未使用的数据。
优点说明：
性能优化： 虚拟模式允许在需要时加载和显示数据，而不是一次性加载整个数据集。这显著减少了内存占用，提高了性能，尤其是在处理大量数据时。
快速响应： 由于只加载可见区域的数据，虚拟模式使得用户界面更加响应迅速。用户可以更快地滚动、过滤或搜索数据，而不会感受到明显的延迟。
减少资源占用： 通过按需加载数据，虚拟模式降低了应用程序的内存消耗。这对于处理大型数据集或在资源受限的环境中运行的应用程序尤为重要。
适应大型数据集： 虚拟模式使得 DataGridView 能够更好地处理大型数据集，而不会因为数据量过大而导致性能下降或内存不足的问题。
动态数据更新： 当数据源动态变化时，虚拟模式允许在不中断整个应用程序的情况下更新显示。这对于实时数据、动态配置等场景非常有用。
灵活性： 虚拟模式提供了更大的灵活性，允许开发人员根据实际需求定制数据加载和显示的逻辑。这使得应用程序可以更好地适应不同的业务场景。
总结下，就是这是一种思想，不仅可以用于Winfrom控件，任何系统加载数据都可以通过这种思想来实现
实现项目：
我们数据库是本地一个sqlite库，表里有100w条数据
DataRetriever 类
该类实现 IDataPageRetriever 接口以从服务器检索数据页。
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace WindowsVirtualMode
{
public class DataRetriever : IDataPageRetriever
{
private string tableName;
private SQLiteCommand command;
public DataRetriever(string connectionString, string tableName)
{
SQLiteConnection connection = new SQLiteConnection(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 = Convert.ToInt32(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;
SQLiteDataAdapter adapter = new SQLiteDataAdapter();
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 SQLiteDataAdapter adapter = new SQLiteDataAdapter();
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.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;
//command.CommandText = "Select " +
// CommaSeparatedListOfColumnNames + " From " + tableName +
// " WHERE " + columnToSortBy + " NOT IN (SELECT " +
// columnToSortBy + " From " +
// tableName + " Order By " + columnToSortBy + " LIMIT " + lowerPageBoundary +
// ") Order By " + columnToSortBy + " LIMIT " + rowsPerPage;
command.CommandText = "Select " +
CommaSeparatedListOfColumnNames + " From " + tableName +
" WHERE " + columnToSortBy + " NOT IN (SELECT " +
columnToSortBy + " From " +
tableName + " LIMIT " + lowerPageBoundary +
") " + " LIMIT " + rowsPerPage;
adapter.SelectCommand = command;
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.Fill(table);
return table;
}
}
}
Cache 类，该类管理通过 IDataPageRetriever 实现填充的两页数据。 Cache 类定义了内部 DataPage 结构，该结构包含一个 DataTable，用于将值存储在单个缓存页面中，并计算表示页面上下边界的行索引。
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace WindowsVirtualMode
{
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].HighestIndex &&
rowIndex >= cachePages[pageNumber].LowestIndex;
}
}
}
主题代码：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace WindowsVirtualMode
{
public partial class Form1 : Form
{
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 = "Data source=" + AppDomain.CurrentDomain.BaseDirectory + "\\data.db";
private string table = "test2";
public Form1()
{
InitializeComponent();
this.DoubleBuffered = true;//设置本窗体
SetStyle(ControlStyles.UserPaint, true);
SetStyle(ControlStyles.AllPaintingInWmPaint, true); // 禁止擦除背景.
SetStyle(ControlStyles.DoubleBuffer, true); // 双缓冲
SetDoubleBuffered(dataGridView1, true); //设置双缓冲
SetOnLoad();
}
protected override CreateParams CreateParams
{
get
{
CreateParams cp = base.CreateParams;
cp.ExStyle |= 0x02000000;
return cp;
}
}
/// <summary>
/// 将给定的DataGridView设置双缓冲
/// </summary>
/// <param name="datagrid">给定的DataGridView</param>
/// <param name="opened">设置为ture即打开双缓冲</param>
public void SetDoubleBuffered(DataGridView datagrid, bool opened)
{
var dgvType = datagrid.GetType();
var properInfo = dgvType.GetProperty("DoubleBuffered", BindingFlags.Instance | BindingFlags.NonPublic);
properInfo.SetValue(datagrid, opened, null);
}
public void SetOnLoad()
{
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, 160);
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);
}
private void dataGridView1_CellValueNeeded(object sender, DataGridViewCellValueEventArgs e)
{
var showInternet = this.BeginInvoke((MethodInvoker)delegate
{
e.Value = memoryCache.RetrieveElement(e.RowIndex, e.ColumnIndex);
});
this.EndInvoke(showInternet);
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Multiselect = true; //该值确定是否可以选择多个文件
dialog.Title = "请选择文件"; //弹窗的标题
//dialog.InitialDirectory = "C:\\"; //默认打开的文件夹的位置
dialog.Filter = "MicroSoft Excel文件(*.xlsx)|*.xlsx|所有文件(*.*)|*.*"; //筛选文件
dialog.ShowHelp = true; //是否显示“帮助”按钮
if (dialog.ShowDialog() == DialogResult.OK)
{
var dateStart = DateTime.Now;
string path = dialog.FileName;
var data = CSVReaderParallel(path);
string times = "花费时间" + (DateTime.Now - dateStart).TotalMilliseconds + "ms";
//label2.Text = times;
if (data != null)
{
dataGridView1.DataSource = data;
}
}
}
/// <summary>
/// 多线程读取
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public DataTable CSVReaderParallel(string path)
{
string dataText = File.ReadAllText(path);
var list = dataText.Split('\n');
DataTable dt = new DataTable();
if (true)
{
string[] arr = list[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string str in arr)
{
dt.Columns.Add(str);
}
}
else
{
string[] arr = list[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
int index = 0;
foreach (string str in arr)
{
dt.Columns.Add("column" + index);
index++;
}
}
int threadCount = 4;
//大于1w的数据采用多线程,采用4个线程
int listCount = list.Count() - 1;
if (listCount > 10000)
{
Dictionary<int, List<string[]>> dtList = new Dictionary<int, List<string[]>>();
int count = listCount / threadCount;
Parallel.For(0, threadCount, (tIndex) =>
{
int startIndex = 0;
int endIndex = 0;
if (tIndex == 0)
{
startIndex = 1;
endIndex = count;
}
else if (tIndex > 0 && tIndex < count - 1)
{
startIndex = tIndex * count;
endIndex = startIndex + count;
}
else
{
startIndex = tIndex * count;
endIndex = listCount;
}
List<string[]> lists = new List<string[]>();
for (int i = startIndex; i < endIndex; i++)
{
string[] arr = list[i].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
lists.Add(arr);
}
dtList.Add(tIndex, lists);
});
for (int i = 0; i < dtList.Count(); i++)
{
List<string[]> strings = dtList[i];
for (int j = 0; j < strings.Count; j++)
{
string[] arr = strings[j];
DataRow dr = dt.NewRow();
for (int m = 0; m < dt.Columns.Count; m++)
{
dr[m] = m < arr.Length ? arr[m] : "";
}
dt.Rows.Add(dr);
}
}
}
else
{
DataRow dr = dt.NewRow();
for (int t = 1; t < list.Count(); t++)
{
string[] arr = list[t].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < dt.Columns.Count; i++)
{
dr[i] = i < arr.Length ? arr[i] : "";
}
}
dt.Rows.Add(dr);
}
return dt;
}
}
}
在资源绑定中下载博主编写好的示例工程
扫一扫
768
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
