DataGridView的VirtualMode,在大容量数据加载时解决卡顿方法

发布于: 2018-10-20    浏览: 2094    作者:Yang

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

        }

 

    }

}

文章内容来自网络,仅用于学习交流,版权归原作者所有,如有侵权请联系管理员删除。

在线客服

售前咨询

售后服务

投诉/建议

服务热线
0731-83091505
18874148081