PageRenderTime 26ms CodeModel.GetById 12ms app.highlight 10ms RepoModel.GetById 1ms app.codeStats 0ms

/Visual Studio 2008/VBWinFormDataGridView/JustInTimeDataLoading/Cache.vb

#
Visual Basic | 249 lines | 176 code | 39 blank | 34 comment | 0 complexity | 6bc7b4c9f9cc5525d9b44b81122b6696 MD5 | raw file
  1'************************************* Module Header **************************************\
  2' Module Name:  JustInTimeDataLoading.Cache 
  3' Project:      VBWinFormDataGridView
  4' Copyright (c) Microsoft Corporation.
  5' 
  6' This sample demonstrates how to use virtual mode in the DataGridView control 
  7' with a data cache that loads data from a server only when it is needed. 
  8' This kind of data loading is called "Just-in-time data loading". 
  9' 
 10' This source is subject to the Microsoft Public License.
 11' See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
 12' All other rights reserved.
 13'
 14' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
 15' EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
 16' WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
 17'**********************************************************************************/
 18
 19
 20Imports System.Data.SqlClient
 21
 22Namespace VBWinFormDataGridView.JustInTimeDataLoading
 23
 24#Region "Cache Class"
 25    Public Class Cache
 26        Private Shared RowsPerPage As Integer
 27
 28        Public Structure DataPage
 29            Public table As DataTable
 30            Private lowestIndexValue As Integer
 31            Private highestIndexValue As Integer
 32
 33            Public Sub New(ByVal table As DataTable, ByVal rowIndex As Integer)
 34                Me.table = table
 35                lowestIndexValue = MapToLowerBoundary(rowIndex)
 36                highestIndexValue = MapToUpperBoundary(rowIndex)
 37                System.Diagnostics.Debug.Assert(lowestIndexValue >= 0)
 38                System.Diagnostics.Debug.Assert(highestIndexValue >= 0)
 39            End Sub
 40
 41            Public ReadOnly Property LowestIndex() As Integer
 42                Get
 43                    Return lowestIndexValue
 44                End Get
 45            End Property
 46
 47            Public ReadOnly Property HighestIndex() As Integer
 48                Get
 49                    Return highestIndexValue
 50                End Get
 51            End Property
 52
 53            Public Shared Function MapToLowerBoundary(ByVal rowIndex As Integer) As Integer
 54                ' Return the lowest index of a page containing the given index.
 55                Return (rowIndex / RowsPerPage) * RowsPerPage
 56            End Function
 57
 58            Private Shared Function MapToUpperBoundary(ByVal rowIndex) As Integer
 59                ' Return the highest index of a page containing the given index.
 60                Return MapToLowerBoundary(rowIndex) + RowsPerPage - 1
 61            End Function
 62        End Structure
 63
 64        Private cachePages As DataPage()
 65        Private dataSupply As IDataPageRetriever
 66
 67        Public Sub New(ByVal dataSupplier As IDataPageRetriever, ByVal rowsPerPage As Integer)
 68            dataSupply = dataSupplier
 69            Cache.RowsPerPage = rowsPerPage
 70            LoadFirstTwoPages()
 71        End Sub
 72
 73        Private Function IfPageCached_ThenSetElement(ByVal rowIndex As Integer, ByVal columnIndex As Integer, ByRef element As String) As Boolean
 74            If IsRowCachedInPage(0, rowIndex) Then
 75                element = cachePages(0).table.Rows(rowIndex Mod RowsPerPage)(columnIndex).ToString()
 76                Return True
 77            ElseIf IsRowCachedInPage(1, rowIndex) Then
 78                element = cachePages(1).table.Rows(rowIndex Mod RowsPerPage)(columnIndex).ToString()
 79                Return True
 80            End If
 81            Return False
 82        End Function
 83
 84        Public Function RetrieveElement(ByVal rowIndex As Integer, ByVal columnIndex As Integer) As String
 85            Dim element As String = Nothing
 86            If IfPageCached_ThenSetElement(rowIndex, columnIndex, element) Then
 87                Return element
 88            Else
 89                Return RetrieveData_CacheIt_ThenReturnElement(rowIndex, columnIndex)
 90            End If
 91        End Function
 92
 93        Private Sub LoadFirstTwoPages()
 94            cachePages = New DataPage() {New DataPage(dataSupply.SupplyPageOfData(DataPage.MapToLowerBoundary(0), RowsPerPage), 0), _
 95                                         New DataPage(dataSupply.SupplyPageOfData(DataPage.MapToLowerBoundary(RowsPerPage), _
 96                                         RowsPerPage), RowsPerPage)}
 97        End Sub
 98
 99        Private Function RetrieveData_CacheIt_ThenReturnElement(ByVal rowIndex As Integer, ByVal columnIndex As Integer)
100            ' Retrieve a page worth of data containing the requested value.
101            Dim table As DataTable = dataSupply.SupplyPageOfData(DataPage.MapToLowerBoundary(rowIndex), RowsPerPage)
102            ' Replace the cached page furthest from the requested cell
103            ' with a new page containing the newly retrieved data.
104            cachePages(GetIndexToUnusedPage(rowIndex)) = New DataPage(table, rowIndex)
105            Return RetrieveElement(rowIndex, columnIndex)
106        End Function
107
108        Private Function GetIndexToUnusedPage(ByVal rowIndex As Integer) As Integer
109            If rowIndex > cachePages(0).HighestIndex AndAlso rowIndex > cachePages(1).HighestIndex Then
110                Dim offsetFromPage0 As Integer = rowIndex - cachePages(0).HighestIndex
111                Dim offsetFromPage1 As Integer = rowIndex - cachePages(1).HighestIndex
112                If offsetFromPage0 < offsetFromPage1 Then
113                    Return 1
114                End If
115                Return 0
116            Else
117                Dim offsetFromPage0 As Integer = cachePages(0).LowestIndex - rowIndex
118                Dim offsetFromPage1 As Integer = cachePages(1).LowestIndex - rowIndex
119                If offsetFromPage0 < offsetFromPage1 Then
120                    Return 1
121                End If
122                Return 0
123            End If
124        End Function
125
126        Private Function IsRowCachedInPage(ByVal pageNumber As Integer, ByVal rowIndex As Integer) As Boolean
127            Return rowIndex <= cachePages(pageNumber).HighestIndex AndAlso rowIndex >= cachePages(pageNumber).LowestIndex
128        End Function
129    End Class
130#End Region
131
132#Region "IDataPageRetriever Interface"
133    Public Interface IDataPageRetriever
134        Function SupplyPageOfData(ByVal lowerPageBoundary As Integer, ByVal rowsPerPage As Integer) As DataTable
135    End Interface
136#End Region
137
138#Region "DataRetriever Class"
139    Public Class DataRetriever
140        Implements IDataPageRetriever
141
142        Private tableName As String
143        Private command As SqlCommand
144
145        Public Sub New(ByVal connectionString As String, ByVal tableName As String)
146            Dim connection As SqlConnection = New SqlConnection(connectionString)
147            connection.Open()
148            command = connection.CreateCommand()
149            Me.tableName = tableName
150        End Sub
151
152        Private rowCountValue As Integer = -1
153
154        Public ReadOnly Property RowCount() As Integer
155            Get
156                ' Return the existing value if it has already been determined.
157                If rowCountValue <> -1 Then
158                    Return rowCountValue
159                End If
160                ' Retrieve the row count from the database.
161                command.CommandText = "SELECT COUNT(*) FROM " + tableName
162                rowCountValue = CType(command.ExecuteScalar(), Integer)
163                Return rowCountValue
164            End Get
165        End Property
166
167        Private columnsValue As DataColumnCollection
168
169        Public ReadOnly Property Columns() As DataColumnCollection
170            Get
171                ' Return the existing value if it has already been determined.
172                If columnsValue IsNot Nothing Then
173                    Return columnsValue
174                End If
175
176                ' Retrieve the column information from the database.
177                command.CommandText = "SELECT * FROM " + tableName
178                Dim adapter As SqlDataAdapter = New SqlDataAdapter()
179                adapter.SelectCommand = command
180                Dim table As DataTable = New DataTable()
181                table.Locale = System.Globalization.CultureInfo.InvariantCulture
182                adapter.FillSchema(table, SchemaType.Source)
183                columnsValue = table.Columns
184                Return columnsValue
185            End Get
186        End Property
187
188        Private commaSeparatedListOfColumnNamesValue As String = Nothing
189
190        Private ReadOnly Property CommaSeparatedListOfColumnNames() As String
191            Get
192                ' Return the existing value if it has already been determined.
193
194                If commaSeparatedListOfColumnNamesValue IsNot Nothing Then
195                    Return commaSeparatedListOfColumnNamesValue
196                End If
197
198                ' Store a list of column names for use in the
199                ' SupplyPageOfData method.
200                Dim commaSeparatedColumnNames As System.Text.StringBuilder = New System.Text.StringBuilder()
201                Dim firstColumn As Boolean = True
202                For Each column As DataColumn In Columns
203                    If Not firstColumn Then
204                        commaSeparatedColumnNames.Append(", ")
205                    End If
206                    commaSeparatedColumnNames.Append(column.ColumnName)
207                    firstColumn = False
208                Next
209
210                commaSeparatedListOfColumnNamesValue = commaSeparatedColumnNames.ToString()
211                Return commaSeparatedListOfColumnNamesValue
212            End Get
213        End Property
214
215        ' Declare variables to be reused by the SupplyPageOfData method.
216        Private columnToSortBy As String
217        Private adapter As SqlDataAdapter = New SqlDataAdapter()
218
219        Public Function SupplyPageOfData(ByVal lowerPageBoundary As Integer, ByVal rowsPerPage As Integer) As System.Data.DataTable Implements IDataPageRetriever.SupplyPageOfData
220            ' Store the name of the ID column. This column must contain unique
221            ' values so the SQL below will work properly.
222            If columnToSortBy Is Nothing Then
223                columnToSortBy = Me.Columns(0).ColumnName
224            End If
225
226            If Not Me.Columns(columnToSortBy).Unique Then
227                Throw New InvalidOperationException(String.Format("Column {0} must contain unique values.", columnToSortBy))
228            End If
229
230            ' Retrieve the specified number of rows from the database, starting
231            ' with the row specified by the lowerPageBoundary parameter.
232            command.CommandText = "Select Top " + rowsPerPage + " " + _
233            CommaSeparatedListOfColumnNames + " From " + tableName + _
234                " WHERE " + columnToSortBy + " NOT IN (SELECT TOP " + _
235                lowerPageBoundary + " " + columnToSortBy + " From " + _
236                tableName + " Order By " + columnToSortBy + _
237                ") Order By " + columnToSortBy
238
239            adapter.SelectCommand = command
240
241            Dim table As DataTable = New DataTable()
242            table.Locale = System.Globalization.CultureInfo.InvariantCulture
243            adapter.Fill(table)
244            Return table
245        End Function
246    End Class
247#End Region
248
249End Namespace