DataGridView的VirtualMode，在大容量数据加载时特别有用

DataGridView的虚拟模式，允许我们在需要时再检索数据。达到实时加载的目的。这避免了DataGridView由于大数据量而刷新延迟，也避免了数据量过大造成网络超时，更可以节约本地内存。总之，它的好处多多，但是要实现这种方式，需要做的事情可不是那么简单，所以需要权衡项目中是否有这样的大数据。

基本思路就是：

1，本地设立数据的缓存。比如装载100条数据。

2，如果用户查阅的数据超过了这个数，就去取下100条数据，并可以考虑丢掉前面的一些缓存，以保证本地内存不会太大。

3，如何判断是否应该去取数据，DataGridView已经提供了相应的一些事件，如：CellValueNeeded，一般以XXXNeeded的事件就是告诉你需要取数据了。当然，一个重要的前提是要开启DataGridView的虚拟模式，这些事件才会有效。this.dataGridView1.VirtualMode = true;在这些事件里，我们可以对DataGridView的单元格赋值（就是直接取缓存的数据来显示，缓存里如果没有，会自动去取下100条数据）。

4，取下100条数据，可能要涉及到SQL的分页。比如用top 100 等就可以简单实现。

下面的例子就是一个缓存的例子，照着MSDN做的，可以做个参考。即使不用于DataGridView，这个缓存的思想也是非常值得借鉴的。

View Code

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调用

View Code

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);
        }

    }
}

我测试的是100万条数据，真的和直接加载一个100万条数据的DataTable有天壤之别。附一个图：