熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> .NET編程 >> 正文

在 ADO 和 ADO.NET 中管理離線數據

2013-11-13 10:11:59  來源: .NET編程 

  您可以使用無數種方法在 VB 和 VBNET 中處理數據這兩個平台都支持諸如數組和集合之類的結構開發人員通常將自定義類包裝到這些結構中就如您在 Carl Ganz 月的專欄中看到的那樣數據獨立(這可能是創建自定義數據抽象層的最大優勢)允許用戶在獨立於數據庫的情況下操作數據這不僅降低了服務器的負擔還消除了維護到該數據庫的開放連接的需要或是對忘記更新 RDBMS 的擔心本月Carl 將分析如何同時使用 ADO 和 ADONET 來創建此類數據對象
  
  在 VBOLE DB 游標服務允許您創建通常作為虛構的Recordset 的內容這些只是 ADO Recordset 對象的內存中實例Field 對象將添加到這些實例中以形成一個數據結構您需要顯式創建這些 Recordset 客戶端如清單 中所示默認情況下它們是在服務器端創建的您在服務器上不能有離線 Recordset
  
  Set oRS = New ADODBRecordset
  oRSCursorLocation = adUseClient
  oRSCursorType = adStatic
  oRSLockType = adLockBatchOptimistic
  Add a few fields
  With oRSFields
  Append LastName adVarChar adFldIsNullable
  Append FirstName adVarChar adFldIsNullable
  Append HireDate adDate
  End With
  oRSOpen
  
  一旦創建 Recordset您就可以用數據填充它了請注意Field 集合的 Append 方法只能用於關閉的 Recordset如果您在打開 Recordset 的 Fields 集合上或是已設置 ActiveConnection 屬性的 Recordset 上調用 Append就會導致運行時錯誤清單 闡釋了如何將數據添加到 Recordset 對象
  
  清單 中的最後兩行代碼闡釋了如何創建索引以使排序更加高效(通常您應通過一個提供初始排序的 SQL 命令來加載此類數據但是如果數據在網格中您可能希望允許用戶通過(比如說)單擊列標題來排序它
  
  清單 用數據填充 Recordset 對象
  
  Populate with some data
  oRSAddNew Array(LastName FirstName HireDate)_
   Array(Washington George //)
  oRSAddNew Array(LastName FirstName HireDate)_
   Array(Adams John //)
  oRSAddNew Array(LastName FirstName HireDate)_
   Array(Jefferson Thomas //)
  oRSUpdate
  Create an inmemory index
  oRSFields(LastName)Properties(Optimize)Value _
  = True
  Sort on first name
  oRSSort = FirstName
  
  創建 Recordset 的另一種方法涉及到離線 Recordset這只是一個 Recordset 對象它隨後將連接到由於將 ActiveConnection 屬性設置為 Nothing 而離線的服務器(請參見清單
  
  清單 創建離線 Recordset
  
  Dim objConnection As ADODBConnection
  Dim szSQL As String
  Set objConnection = New ADODBConnection
  Open a connection
  objConnectionConnectionString = whatever
  objConnectionOpen
  Instantiate a Recordset object
  Set oRS = New ADODBRecordset
  oRSCursorLocation = adUseClient
  oRSCursorType = adOpenStatic
  oRSLockType = adLockBatchOptimistic
  and set the ActiveConnection property
  Set oRSActiveConnection = objConnection
  szSQL = SELECT LastName FirstName & _
  FROM Employees & ORDER BY LastName FirstName
  Open the Recordset and return the employee data
  oRSOpen szSQL
  Set oRSActiveConnection = Nothing
  objConnectionClose
  Set DataGridDataSource = oRS
  
  使用 ADONET
  ADONET 為離線數據管理提供了更強大的功能DataTable 和 DataSet 對象本身就定義為離線因此您不必執行任何操作來使它們離線從概念上來說ADONET DataTable 對象和 ADO Recordset 對象的編程創建過程非常類似如清單 中所示在本示例中我們要創建一個 DataTable 對象並向其添加列和行對象正如您將看到的那樣ADONET 的方法使用了一個比基於 COM 的 ADO 所使用的更加層次化的對象模型
  
  清單 創建 ADONET DataTable 對象
  
  Dim oDataRow As DataRow
  Dim oDataColumn As DataColumn
  Dim aPrimaryKey() As DataColumn
  oDS = New DataSet
  Create a new DataTable oect
  oEmployeeDT = New DataTable
  Add an event handler for column data changes
  AddHandler oEmployeeDTColumnChanged _
  New DataColumnChangeEventHandler(AddressOf _
  ColumnChanged)
  Create primary key (PK) col and add it to the columns
  collection Set init value to increment as needed
  oDataColumn = New DataColumn
  oDataColumnColumnName = ID
  oDataColumnDataType = _
  SystemTypeGetType(SystemInt)
  oDataColumnAutoIncrement = True
  oDataColumnAutoIncrementSeed =
  oEmployeeDTColumnsAdd (oDataColumn)
  PK property receives array of DataColumn objects in
  case you have multicol index I prefer an ID column
  aPrimaryKey() = oDataColumn
  oEmployeeDTPrimaryKey = aPrimaryKey
  Create the individual data columns
  a€|
  oDataColumn = New DataColumn
  oDataColumnColumnName = Salary
  oDataColumnDataType = _
  SystemTypeGetType(SystemDecimal)
  oEmployeeDTColumnsAdd (oDataColumn)
  Here we use DataColumns Expression property to show
  what a % tax on the salary will amount to
  oDataColumn = New DataColumn
  oDataColumnColumnName = IncomeTax
  oDataColumnDataType = _
  SystemTypeGetType(SystemDecimal)
  oDataColumnExpression = Salary *
  oEmployeeDTColumnsAdd (oDataColumn)
  oDataColumn = New DataColumn
  oDataColumnColumnName = HireDate
  oDataColumnDataType = _
  SystemTypeGetType(SystemDateTime)
  oEmployeeDTColumnsAdd (oDataColumn)
  This col receives the value calcd by event handler
  oDataColumn = New DataColumn
  oDataColumnColumnName = DaysSinceHire
  oDataColumnDataType = _
  SystemTypeGetType(SystemInt)
  oEmployeeDTColumnsAdd (oDataColumn)
  Once the columns are added add sample data
  oDataRow = oEmployeeDTNewRow()
  oDataRow(LastName) = Washington
  oDataRow(FirstName) = George
  oDataRow(Salary) =
  oDataRow(IncomeTax) = True
  oDataRow(HireDate) = //
  oEmployeeDTRowsAdd (oDataRow)
  a€|
  Commit new data to the DataTable object and set
  RowState values of each row to Unchanged
  oEmployeeDTAcceptChanges()
  Lets show the user
  DataGridDataSource = oEmployeeDT
  
  在實例化 DataTable 對象之後您就可以使用這個事件模型將事件處理程序DataColumnChangeEventHandler設置為自定義例程通過傳遞 ColumnChanged 例程的地址只要一個列中的數據更改您就可以執行這個處理程序在本例中我們要使用它來確定職員被雇傭以來的天數(請參見清單
  
  清單 ColumnChanged 事件處理程序
  
  Sub ColumnChanged(ByVal sender As Object _
  ByVal e As DataColumnChangeEventArgs)
  If eColumnColumnName = HireDate Then
  eRow(DaysSinceHire) = _
  DateDiff(DateIntervalDay _
  eRow(HireDate) DateToday)
  End If
  End Sub
  
  由於用戶在網格中編輯數據添加和刪除行因此每個行對象的 RowState 屬性會更改以指示所作修改的類型當用戶將離線數據提交到 RDBMS 時需要計算每一行來確定用戶執行了何種類型的更改要完成這一操作您可以通過 GetChanges 方法檢索包含所有新近添加修改和刪除的行的 DataTable 對象然後循環訪問這個新 DataTable 中的每個行對象並計算 RowState 屬性如清單 中所示
  
  清單 標識更改的行並保存更改
  
  Dim oDataRow As DataRow
  Dim oChangeDT As DataTable
  Get DataTable with only changed rows
  oChangeDT = oEmployeeDTGetChanges()
  Save button s/b disabled until a change is made
  If oChangeDT Is Nothing Then
  Exit Sub
  End If
  Loop thru changed rows make db updates
  For Each oDataRow In oChangeDTRows
  Select Case oDataRowRowState
  Case DataRowStateAdded
  Insert code here
  Case DataRowStateModified
  Update code here
  Case DataRowStateDeleted
  Delete code here From:http://tw.wingwit.com/Article/program/net/201311/12866.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.