PageRenderTime 41ms CodeModel.GetById 17ms RepoModel.GetById 0ms 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. Imports System.Data.SqlClient
  19. Namespace VBWinFormDataGridView.JustInTimeDataLoading
  20. #Region "Cache Class"
  21. Public Class Cache
  22. Private Shared RowsPerPage As Integer
  23. Public Structure DataPage
  24. Public table As DataTable
  25. Private lowestIndexValue As Integer
  26. Private highestIndexValue As Integer
  27. Public Sub New(ByVal table As DataTable, ByVal rowIndex As Integer)
  28. Me.table = table
  29. lowestIndexValue = MapToLowerBoundary(rowIndex)
  30. highestIndexValue = MapToUpperBoundary(rowIndex)
  31. System.Diagnostics.Debug.Assert(lowestIndexValue >= 0)
  32. System.Diagnostics.Debug.Assert(highestIndexValue >= 0)
  33. End Sub
  34. Public ReadOnly Property LowestIndex() As Integer
  35. Get
  36. Return lowestIndexValue
  37. End Get
  38. End Property
  39. Public ReadOnly Property HighestIndex() As Integer
  40. Get
  41. Return highestIndexValue
  42. End Get
  43. End Property
  44. Public Shared Function MapToLowerBoundary(ByVal rowIndex As Integer) As Integer
  45. ' Return the lowest index of a page containing the given index.
  46. Return (rowIndex / RowsPerPage) * RowsPerPage
  47. End Function
  48. Private Shared Function MapToUpperBoundary(ByVal rowIndex) As Integer
  49. ' Return the highest index of a page containing the given index.
  50. Return MapToLowerBoundary(rowIndex) + RowsPerPage - 1
  51. End Function
  52. End Structure
  53. Private cachePages As DataPage()
  54. Private dataSupply As IDataPageRetriever
  55. Public Sub New(ByVal dataSupplier As IDataPageRetriever, ByVal rowsPerPage As Integer)
  56. dataSupply = dataSupplier
  57. Cache.RowsPerPage = rowsPerPage
  58. LoadFirstTwoPages()
  59. End Sub
  60. Private Function IfPageCached_ThenSetElement(ByVal rowIndex As Integer, ByVal columnIndex As Integer, ByRef element As String) As Boolean
  61. If IsRowCachedInPage(0, rowIndex) Then
  62. element = cachePages(0).table.Rows(rowIndex Mod RowsPerPage)(columnIndex).ToString()
  63. Return True
  64. ElseIf IsRowCachedInPage(1, rowIndex) Then
  65. element = cachePages(1).table.Rows(rowIndex Mod RowsPerPage)(columnIndex).ToString()
  66. Return True
  67. End If
  68. Return False
  69. End Function
  70. Public Function RetrieveElement(ByVal rowIndex As Integer, ByVal columnIndex As Integer) As String
  71. Dim element As String = Nothing
  72. If IfPageCached_ThenSetElement(rowIndex, columnIndex, element) Then
  73. Return element
  74. Else
  75. Return RetrieveData_CacheIt_ThenReturnElement(rowIndex, columnIndex)
  76. End If
  77. End Function
  78. Private Sub LoadFirstTwoPages()
  79. cachePages = New DataPage() {New DataPage(dataSupply.SupplyPageOfData(DataPage.MapToLowerBoundary(0), RowsPerPage), 0), _
  80. New DataPage(dataSupply.SupplyPageOfData(DataPage.MapToLowerBoundary(RowsPerPage), _
  81. RowsPerPage), RowsPerPage)}
  82. End Sub
  83. Private Function RetrieveData_CacheIt_ThenReturnElement(ByVal rowIndex As Integer, ByVal columnIndex As Integer)
  84. ' Retrieve a page worth of data containing the requested value.
  85. Dim table As DataTable = dataSupply.SupplyPageOfData(DataPage.MapToLowerBoundary(rowIndex), RowsPerPage)
  86. ' Replace the cached page furthest from the requested cell
  87. ' with a new page containing the newly retrieved data.
  88. cachePages(GetIndexToUnusedPage(rowIndex)) = New DataPage(table, rowIndex)
  89. Return RetrieveElement(rowIndex, columnIndex)
  90. End Function
  91. Private Function GetIndexToUnusedPage(ByVal rowIndex As Integer) As Integer
  92. If rowIndex > cachePages(0).HighestIndex AndAlso rowIndex > cachePages(1).HighestIndex Then
  93. Dim offsetFromPage0 As Integer = rowIndex - cachePages(0).HighestIndex
  94. Dim offsetFromPage1 As Integer = rowIndex - cachePages(1).HighestIndex
  95. If offsetFromPage0 < offsetFromPage1 Then
  96. Return 1
  97. End If
  98. Return 0
  99. Else
  100. Dim offsetFromPage0 As Integer = cachePages(0).LowestIndex - rowIndex
  101. Dim offsetFromPage1 As Integer = cachePages(1).LowestIndex - rowIndex
  102. If offsetFromPage0 < offsetFromPage1 Then
  103. Return 1
  104. End If
  105. Return 0
  106. End If
  107. End Function
  108. Private Function IsRowCachedInPage(ByVal pageNumber As Integer, ByVal rowIndex As Integer) As Boolean
  109. Return rowIndex <= cachePages(pageNumber).HighestIndex AndAlso rowIndex >= cachePages(pageNumber).LowestIndex
  110. End Function
  111. End Class
  112. #End Region
  113. #Region "IDataPageRetriever Interface"
  114. Public Interface IDataPageRetriever
  115. Function SupplyPageOfData(ByVal lowerPageBoundary As Integer, ByVal rowsPerPage As Integer) As DataTable
  116. End Interface
  117. #End Region
  118. #Region "DataRetriever Class"
  119. Public Class DataRetriever
  120. Implements IDataPageRetriever
  121. Private tableName As String
  122. Private command As SqlCommand
  123. Public Sub New(ByVal connectionString As String, ByVal tableName As String)
  124. Dim connection As SqlConnection = New SqlConnection(connectionString)
  125. connection.Open()
  126. command = connection.CreateCommand()
  127. Me.tableName = tableName
  128. End Sub
  129. Private rowCountValue As Integer = -1
  130. Public ReadOnly Property RowCount() As Integer
  131. Get
  132. ' Return the existing value if it has already been determined.
  133. If rowCountValue <> -1 Then
  134. Return rowCountValue
  135. End If
  136. ' Retrieve the row count from the database.
  137. command.CommandText = "SELECT COUNT(*) FROM " + tableName
  138. rowCountValue = CType(command.ExecuteScalar(), Integer)
  139. Return rowCountValue
  140. End Get
  141. End Property
  142. Private columnsValue As DataColumnCollection
  143. Public ReadOnly Property Columns() As DataColumnCollection
  144. Get
  145. ' Return the existing value if it has already been determined.
  146. If columnsValue IsNot Nothing Then
  147. Return columnsValue
  148. End If
  149. ' Retrieve the column information from the database.
  150. command.CommandText = "SELECT * FROM " + tableName
  151. Dim adapter As SqlDataAdapter = New SqlDataAdapter()
  152. adapter.SelectCommand = command
  153. Dim table As DataTable = New DataTable()
  154. table.Locale = System.Globalization.CultureInfo.InvariantCulture
  155. adapter.FillSchema(table, SchemaType.Source)
  156. columnsValue = table.Columns
  157. Return columnsValue
  158. End Get
  159. End Property
  160. Private commaSeparatedListOfColumnNamesValue As String = Nothing
  161. Private ReadOnly Property CommaSeparatedListOfColumnNames() As String
  162. Get
  163. ' Return the existing value if it has already been determined.
  164. If commaSeparatedListOfColumnNamesValue IsNot Nothing Then
  165. Return commaSeparatedListOfColumnNamesValue
  166. End If
  167. ' Store a list of column names for use in the
  168. ' SupplyPageOfData method.
  169. Dim commaSeparatedColumnNames As System.Text.StringBuilder = New System.Text.StringBuilder()
  170. Dim firstColumn As Boolean = True
  171. For Each column As DataColumn In Columns
  172. If Not firstColumn Then
  173. commaSeparatedColumnNames.Append(", ")
  174. End If
  175. commaSeparatedColumnNames.Append(column.ColumnName)
  176. firstColumn = False
  177. Next
  178. commaSeparatedListOfColumnNamesValue = commaSeparatedColumnNames.ToString()
  179. Return commaSeparatedListOfColumnNamesValue
  180. End Get
  181. End Property
  182. ' Declare variables to be reused by the SupplyPageOfData method.
  183. Private columnToSortBy As String
  184. Private adapter As SqlDataAdapter = New SqlDataAdapter()
  185. Public Function SupplyPageOfData(ByVal lowerPageBoundary As Integer, ByVal rowsPerPage As Integer) As System.Data.DataTable Implements IDataPageRetriever.SupplyPageOfData
  186. ' Store the name of the ID column. This column must contain unique
  187. ' values so the SQL below will work properly.
  188. If columnToSortBy Is Nothing Then
  189. columnToSortBy = Me.Columns(0).ColumnName
  190. End If
  191. If Not Me.Columns(columnToSortBy).Unique Then
  192. Throw New InvalidOperationException(String.Format("Column {0} must contain unique values.", columnToSortBy))
  193. End If
  194. ' Retrieve the specified number of rows from the database, starting
  195. ' with the row specified by the lowerPageBoundary parameter.
  196. command.CommandText = "Select Top " + rowsPerPage + " " + _
  197. CommaSeparatedListOfColumnNames + " From " + tableName + _
  198. " WHERE " + columnToSortBy + " NOT IN (SELECT TOP " + _
  199. lowerPageBoundary + " " + columnToSortBy + " From " + _
  200. tableName + " Order By " + columnToSortBy + _
  201. ") Order By " + columnToSortBy
  202. adapter.SelectCommand = command
  203. Dim table As DataTable = New DataTable()
  204. table.Locale = System.Globalization.CultureInfo.InvariantCulture
  205. adapter.Fill(table)
  206. Return table
  207. End Function
  208. End Class
  209. #End Region
  210. End Namespace