在 Windows 窗体 DataGridView 控件中实现实时数据加载的虚拟模式

 

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=clientue;" +
         "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";

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

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

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

}

public interface IDataPageRetriever
{
     DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage);
}

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.
         if (columnToSortBy == null)
         {
             columnToSortBy = this.Columns[0].ColumnName;
         }

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

}

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值