DataGridView的虚拟模式,允许我们在需要时再检索数据。达到实时加载的目的。这避免了DataGridView由于大数据量而刷新延迟,也避免了数据量过大造成网络超时,更可以节约本地内存。总之,它的好处多多,但是要实现这种方式,需要做的事情可不是那么简单,所以需要权衡项目中是否有这样的大数据。
基本思路就是:
1,本地设立数据的缓存。比如装载100条数据。
2,如果用户查阅的数据超过了这个数,就去取下100条数据,并可以考虑丢掉前面的一些缓存,以保证本地内存不会太大。
3,如何判断是否应该去取数据,DataGridView已经提供了相应的一些事件,如:CellValueNeeded,一般以XXXNeeded的事件就是告诉你需要取数据了。当然,一个重要的前提是要开启DataGridView的虚拟模式,这些事件才会有效。this.dataGridView1.VirtualMode = true;在这些事件里,我们可以对DataGridView的单元格赋值(就是直接取缓存的数据来显示,缓存里如果没有,会自动去取下100条数据)。
4,取下100条数据,可能要涉及到SQL的分页。比如用top 100 等就可以简单实现。
下面的例子就是一个缓存的例子,照着MSDN做的,可以做个参考。即使不用于DataGridView,这个缓存的思想也是非常值得借鉴的。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DataGridViewVirtualModeTest
{
interface IDataPageRetriever
{
DataTable ApplyPageOfData(int lowPageBoundary, int rowsPerPage);
}
internal class DataRetriever : IDataPageRetriever
{
private const String Conn_Str =
@"Data Source=SK-WZ\SQLEXPRESS;Initial Catalog=TestUse;Persist Security Info=True;User ID=wang;password=wang";
private string tableName;
private SqlCommand command;
public DataRetriever(string tableName)
{
this.tableName = tableName;
SqlConnection conn = new SqlConnection(Conn_Str);
conn.Open();
command = conn.CreateCommand();
}
private int rowCount = -1;
public int RowCount
{
get
{
if (rowCount != -1)
return RowCount;
command.CommandText = "select count(*) from " + tableName;
rowCount = (int)command.ExecuteScalar();
return rowCount;
}
}
private DataColumnCollection columns;
public DataColumnCollection Columns
{
get
{
if (columns != null)
return columns;
command.CommandText = "select * from " + tableName;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.CurrentCulture;
adapter.FillSchema(table, SchemaType.Source);
columns = table.Columns;
return columns;
}
}
private string commaSeparatedColumnNames;
public string CommaSeparatedColumnNames
{
get
{
if (commaSeparatedColumnNames != null)
return commaSeparatedColumnNames;
StringBuilder builder = new StringBuilder();
bool isFirstColumn = true;
foreach (DataColumn column in Columns)
{
if (!isFirstColumn)
{
builder.Append(",");
}
isFirstColumn = false;
builder.Append(column.ColumnName);
}
commaSeparatedColumnNames = builder.ToString();
return commaSeparatedColumnNames;
}
}
#region IDataPageRetriver
private string keyColumnName;
public DataTable ApplyPageOfData(int lowPageBoundary, int rowsPerPage)
{
keyColumnName = Columns[0].ColumnName;
StringBuilder builder = new StringBuilder();
builder.Append(" select top " + rowsPerPage + " " + CommaSeparatedColumnNames + " from ");
builder.Append(tableName);
builder.Append(" where " + keyColumnName + " not in (");
builder.Append(" select top " + lowPageBoundary + " " + keyColumnName);
builder.Append(" from " + tableName + " order by " + keyColumnName + ") ");
builder.Append(" order by " + keyColumnName);
command.CommandText = builder.ToString();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
DataTable table = new DataTable();
adapter.Fill(table);
return table;
}
#endregion
}
internal class Cache
{
private struct DataPage
{
public DataTable table;
private int lowIndex;
private int highIndex;
public DataPage(DataTable table, int rowIndex)
{
this.table = table;
this.lowIndex = MapLowerBoundary(rowIndex);
this.highIndex = MapUpperBoundary(rowIndex);
}
public int LowIndex
{
get { return this.lowIndex; }
}
public int HighIndex
{
get { return this.highIndex; }
}
public static int MapLowerBoundary(int rowIndex)
{
return (rowIndex / RowPerPage) * RowPerPage;
}
public static int MapUpperBoundary(int rowIndex)
{
return MapLowerBoundary(rowIndex) + RowPerPage - 1;
}
}
IDataPageRetriever dataSupply;
static int RowPerPage;
DataPage[] catchPages = new DataPage[2];
public Cache(IDataPageRetriever dataSupplier, int rowsPerPage)
{
this.dataSupply = dataSupplier;
RowPerPage = rowsPerPage;
PreLoadDataPages();
}
private void PreLoadDataPages()
{
catchPages[0] = new DataPage(dataSupply.ApplyPageOfData(0, RowPerPage), 0);
catchPages[1] = new DataPage(dataSupply.ApplyPageOfData(RowPerPage, RowPerPage), RowPerPage);
}
public string RetrieveElement(int rowIndex, int colIndex)
{
string element = "";
if (IfPageCatched_TheSetElement(rowIndex, colIndex, ref element))
{
return element;
}
else
{
element = RetrieveData_CatchIt_ReturnElement(rowIndex, colIndex);
}
return element;
}
private bool IfPageCatched_TheSetElement(int rowIndex, int colIndex, ref string element)
{
if (IsRowCatchedInPage(0, rowIndex))
{
element = catchPages[0].table.Rows[rowIndex % RowPerPage][colIndex].ToString();
return true;
}
else if (IsRowCatchedInPage(1, rowIndex))
{
element = catchPages[1].table.Rows[rowIndex % RowPerPage][colIndex].ToString();
return true;
}
return false;
}
private string RetrieveData_CatchIt_ReturnElement(int rowIndex, int colIndex)
{
DataPage newPage = new DataPage(dataSupply.ApplyPageOfData(DataPage.MapLowerBoundary(rowIndex), RowPerPage), rowIndex);
//which old datapage should be replaced?
catchPages[GetIndexOfReplacedPage(rowIndex)] = newPage;
return RetrieveElement(rowIndex, colIndex);
}
private bool IsRowCatchedInPage(int pageNum, int rowIndex)
{
return catchPages[pageNum].LowIndex <= rowIndex &&
catchPages[pageNum].HighIndex >= rowIndex;
}
private int GetIndexOfReplacedPage(int rowIndex)
{
if (catchPages[0].HighIndex < rowIndex && catchPages[1].HighIndex < rowIndex)
{
int offsetFromPage0 = rowIndex - catchPages[0].HighIndex;
int offsetFromPage1 = rowIndex - catchPages[1].HighIndex;
if (offsetFromPage0 < offsetFromPage1)
return 1;
else
return 0;
}
else
{
int offsetFromPage0 = catchPages[0].LowIndex - rowIndex;
int offsetFromPage1 = catchPages[1].LowIndex - rowIndex;
if (offsetFromPage0 < offsetFromPage1)
return 1;
return 0;
}
}
}
}
DataGridView调用
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace DataGridViewVirtualModeTest
{
public partial class Form1 : Form
{
Cache memoryCache;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
DataRetriever dataSupply = new DataRetriever("t_temp");
memoryCache = new Cache(dataSupply, 50);
foreach (DataColumn column in dataSupply.Columns)
{
this.dataGridView1.Columns.Add(column.ColumnName, column.ColumnName);
}
this.dataGridView1.RowCount = dataSupply.RowCount;
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);
this.dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
}
void dataGridView1_CellValueNeeded(object sender, DataGridViewCellValueEventArgs e)
{
e.Value = memoryCache.RetrieveElement(e.RowIndex, e.ColumnIndex);
}
}
}
文章内容来自网络,仅用于学习交流,版权归原作者所有,如有侵权请联系管理员删除。