PageRenderTime 58ms CodeModel.GetById 18ms app.highlight 30ms RepoModel.GetById 1ms app.codeStats 0ms

/Visual Studio 2008/CSWinFormDataGridView/JustInTimeDataLoading/Cache.cs

#
C# | 345 lines | 239 code | 49 blank | 57 comment | 17 complexity | 606819fae7e48bf41b4624fc1fd44620 MD5 | raw file
  1/************************************* Module Header **************************************\
  2* Module Name:	JustInTimeDataLoading.Cache 
  3* Project:		CSWinFormDataGridView
  4* Copyright (c) Microsoft Corporation.
  5* 
  6* 
  7* This sample demonstrates how to use virtual mode in the DataGridView control 
  8* with a data cache that loads data from a server only when it is needed. 
  9* This kind of data loading is called "Just-in-time data loading". 
 10* 
 11* This source is subject to the Microsoft Public License.
 12* See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
 13* All other rights reserved.
 14* 
 15* History:
 16* * 6/10/2009 3:00 PM Zhi-Xin Ye Created
 17\******************************************************************************************/
 18
 19using System;
 20using System.Collections.Generic;
 21using System.Linq;
 22using System.Text;
 23using System.Data;
 24using System.Data.SqlClient;
 25
 26namespace CSWinFormDataGridView.JustInTimeDataLoading
 27{
 28
 29    #region Cache Class
 30
 31    public class Cache
 32    {
 33        private static int RowsPerPage;
 34
 35        /// <summary>
 36        /// Represents one page of data.
 37        /// </summary>
 38        public struct DataPage
 39        {
 40            public DataTable table;
 41            private int lowestIndexValue;
 42            private int highestIndexValue;
 43
 44            public DataPage(DataTable table, int rowIndex)
 45            {
 46                this.table = table;
 47                lowestIndexValue = MapToLowerBoundary(rowIndex);
 48                highestIndexValue = MapToUpperBoundary(rowIndex);
 49                System.Diagnostics.Debug.Assert(lowestIndexValue >= 0);
 50                System.Diagnostics.Debug.Assert(highestIndexValue >= 0);
 51            }
 52
 53            public int LowestIndex
 54            {
 55                get
 56                {
 57                    return lowestIndexValue;
 58                }
 59            }
 60
 61            public int HighestIndex
 62            {
 63                get
 64                {
 65                    return highestIndexValue;
 66                }
 67            }
 68
 69            public static int MapToLowerBoundary(int rowIndex)
 70            {
 71                // Return the lowest index of a page containing the given index.
 72                return (rowIndex / RowsPerPage) * RowsPerPage;
 73            }
 74
 75            private static int MapToUpperBoundary(int rowIndex)
 76            {
 77                // Return the highest index of a page containing the given index.
 78                return MapToLowerBoundary(rowIndex) + RowsPerPage - 1;
 79            }
 80        }
 81
 82        private DataPage[] cachePages;
 83        private IDataPageRetriever dataSupply;
 84
 85        public Cache(IDataPageRetriever dataSupplier, int rowsPerPage)
 86        {
 87            dataSupply = dataSupplier;
 88            Cache.RowsPerPage = rowsPerPage;
 89            LoadFirstTwoPages();
 90        }
 91
 92        /// <summary>
 93        /// Sets the value of the element parameter if the value is in the cache.
 94        /// </summary>
 95        /// <param name="rowIndex"></param>
 96        /// <param name="columnIndex"></param>
 97        /// <param name="element"></param>
 98        /// <returns></returns>
 99        private bool IfPageCached_ThenSetElement(int rowIndex,
100            int columnIndex, ref string element)
101        {
102            if (IsRowCachedInPage(0, rowIndex))
103            {
104                element = cachePages[0].table
105                    .Rows[rowIndex % RowsPerPage][columnIndex].ToString();
106                return true;
107            }
108            else if (IsRowCachedInPage(1, rowIndex))
109            {
110                element = cachePages[1].table
111                    .Rows[rowIndex % RowsPerPage][columnIndex].ToString();
112                return true;
113            }
114
115            return false;
116        }
117
118        public string RetrieveElement(int rowIndex, int columnIndex)
119        {
120            string element = null;
121
122            if (IfPageCached_ThenSetElement(rowIndex, columnIndex, ref element))
123            {
124                return element;
125            }
126            else
127            {
128                return RetrieveData_CacheIt_ThenReturnElement(
129                    rowIndex, columnIndex);
130            }
131        }
132
133        private void LoadFirstTwoPages()
134        {
135            cachePages = new DataPage[]{
136            new DataPage(dataSupply.SupplyPageOfData(
137                DataPage.MapToLowerBoundary(0), RowsPerPage), 0), 
138            new DataPage(dataSupply.SupplyPageOfData(
139                DataPage.MapToLowerBoundary(RowsPerPage), 
140                RowsPerPage), RowsPerPage)};
141        }
142
143        private string RetrieveData_CacheIt_ThenReturnElement(
144            int rowIndex, int columnIndex)
145        {
146            // Retrieve a page worth of data containing the requested value.
147            DataTable table = dataSupply.SupplyPageOfData(
148                DataPage.MapToLowerBoundary(rowIndex), RowsPerPage);
149
150            // Replace the cached page furthest from the requested cell
151            // with a new page containing the newly retrieved data.
152            cachePages[GetIndexToUnusedPage(rowIndex)] = new DataPage(table, rowIndex);
153
154            return RetrieveElement(rowIndex, columnIndex);
155        }
156
157        /// <summary>
158        /// Returns the index of the cached page most distant from the given index
159        /// and therefore least likely to be reused.
160        /// </summary>
161        /// <param name="rowIndex"></param>
162        /// <returns></returns>
163        private int GetIndexToUnusedPage(int rowIndex)
164        {
165            if (rowIndex > cachePages[0].HighestIndex &&
166                rowIndex > cachePages[1].HighestIndex)
167            {
168                int offsetFromPage0 = rowIndex - cachePages[0].HighestIndex;
169                int offsetFromPage1 = rowIndex - cachePages[1].HighestIndex;
170                if (offsetFromPage0 < offsetFromPage1)
171                {
172                    return 1;
173                }
174                return 0;
175            }
176            else
177            {
178                int offsetFromPage0 = cachePages[0].LowestIndex - rowIndex;
179                int offsetFromPage1 = cachePages[1].LowestIndex - rowIndex;
180                if (offsetFromPage0 < offsetFromPage1)
181                {
182                    return 1;
183                }
184                return 0;
185            }
186
187        }
188
189        /// <summary>
190        /// Returns a value indicating whether the given row index is contained
191        /// in the given DataPage. 
192        /// </summary>
193        /// <param name="pageNumber"></param>
194        /// <param name="rowIndex"></param>
195        /// <returns></returns>
196        private bool IsRowCachedInPage(int pageNumber, int rowIndex)
197        {
198            return rowIndex <= cachePages[pageNumber].HighestIndex &&
199                rowIndex >= cachePages[pageNumber].LowestIndex;
200        }
201    }
202
203    #endregion 
204
205
206    #region IDataPageRetriever Interface
207
208    public interface IDataPageRetriever
209    {
210        DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage);
211    }
212
213    #endregion
214
215
216    #region DataRetriever Class
217
218    public class DataRetriever : IDataPageRetriever
219    {
220        private string tableName;
221        private SqlCommand command;
222
223        public DataRetriever(string connectionString, string tableName)
224        {
225            SqlConnection connection = new SqlConnection(connectionString);
226            connection.Open();
227            command = connection.CreateCommand();
228            this.tableName = tableName;
229        }
230
231        private int rowCountValue = -1;
232
233        public int RowCount
234        {
235            get
236            {
237                // Return the existing value if it has already been determined.
238                if (rowCountValue != -1)
239                {
240                    return rowCountValue;
241                }
242
243                // Retrieve the row count from the database.
244                command.CommandText = "SELECT COUNT(*) FROM " + tableName;
245                rowCountValue = (int)command.ExecuteScalar();
246                return rowCountValue;
247            }
248        }
249
250        private DataColumnCollection columnsValue;
251
252        public DataColumnCollection Columns
253        {
254            get
255            {
256                // Return the existing value if it has already been determined.
257                if (columnsValue != null)
258                {
259                    return columnsValue;
260                }
261
262                // Retrieve the column information from the database.
263                command.CommandText = "SELECT * FROM " + tableName;
264                SqlDataAdapter adapter = new SqlDataAdapter();
265                adapter.SelectCommand = command;
266                DataTable table = new DataTable();
267                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
268                adapter.FillSchema(table, SchemaType.Source);
269                columnsValue = table.Columns;
270                return columnsValue;
271            }
272        }
273
274        private string commaSeparatedListOfColumnNamesValue = null;
275
276        private string CommaSeparatedListOfColumnNames
277        {
278            get
279            {
280                // Return the existing value if it has already been determined.
281                if (commaSeparatedListOfColumnNamesValue != null)
282                {
283                    return commaSeparatedListOfColumnNamesValue;
284                }
285
286                // Store a list of column names for use in the
287                // SupplyPageOfData method.
288                System.Text.StringBuilder commaSeparatedColumnNames =
289                    new System.Text.StringBuilder();
290                bool firstColumn = true;
291                foreach (DataColumn column in Columns)
292                {
293                    if (!firstColumn)
294                    {
295                        commaSeparatedColumnNames.Append(", ");
296                    }
297                    commaSeparatedColumnNames.Append(column.ColumnName);
298                    firstColumn = false;
299                }
300
301                commaSeparatedListOfColumnNamesValue =
302                    commaSeparatedColumnNames.ToString();
303                return commaSeparatedListOfColumnNamesValue;
304            }
305        }
306
307        // Declare variables to be reused by the SupplyPageOfData method.
308        private string columnToSortBy;
309        private SqlDataAdapter adapter = new SqlDataAdapter();
310
311        public DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage)
312        {
313            // Store the name of the ID column. This column must contain unique 
314            // values so the SQL below will work properly.
315            if (columnToSortBy == null)
316            {
317                columnToSortBy = this.Columns[0].ColumnName;
318            }
319
320            if (!this.Columns[columnToSortBy].Unique)
321            {
322                throw new InvalidOperationException(String.Format(
323                    "Column {0} must contain unique values.", columnToSortBy));
324            }
325
326            // Retrieve the specified number of rows from the database, starting
327            // with the row specified by the lowerPageBoundary parameter.
328            command.CommandText = "Select Top " + rowsPerPage + " " +
329                CommaSeparatedListOfColumnNames + " From " + tableName +
330                " WHERE " + columnToSortBy + " NOT IN (SELECT TOP " +
331                lowerPageBoundary + " " + columnToSortBy + " From " +
332                tableName + " Order By " + columnToSortBy +
333                ") Order By " + columnToSortBy;
334            adapter.SelectCommand = command;
335
336            DataTable table = new DataTable();
337            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
338            adapter.Fill(table);
339            return table;
340        }
341
342    }
343
344    #endregion 
345}