recordset.vb

使用ADO.net 访问SQL server 数据集访问

                If criteria = Nothing Then
                    If IsDBNull(dset.Tables(0).Rows(index).Item(columnName)) Then
                        found = True
                    Else
                        index = index - 1
                    End If
                ElseIf IsDBNull(dset.Tables(0).Rows(index).Item(columnName)) Then
                    index = index - 1
                ElseIf dset.Tables(0).Rows(index).Item(columnName) = criteria Then
                    found = True
                Else
                    index = index - 1
                End If
            Loop

            If found Then
                foundrecords_counter = 0
                memory = index
                Do Until Me.NoMatch
                    foundrecords_counter = foundrecords_counter + 1
                    Me.FindPrevious(columnName, criteria)
                Loop
                index = memory
                found = True
            Else
                foundrecords_counter = 0
            End If

        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
        End Try
    End Sub

    Sub FindPrevious(ByVal columnName As String, ByVal criteria As Object)
        Try
            index = index - 1
            found = False
            Do Until found Or index <= -1
                If criteria = Nothing Then
                    If IsDBNull(dset.Tables(0).Rows(index).Item(columnName)) Then
                        found = True
                    Else
                        index = index - 1
                    End If
                ElseIf IsDBNull(dset.Tables(0).Rows(index).Item(columnName)) Then
                    index = index - 1
                ElseIf dset.Tables(0).Rows(index).Item(columnName) = criteria Then
                    found = True
                Else
                    index = index - 1
                End If
            Loop
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
        End Try
    End Sub

    Sub FindNext(ByVal columnName As String, ByVal criteria As Object)
        Try
            index = index + 1
            found = False
            Do Until found Or index >= dset.Tables(0).Rows.Count
                If criteria = Nothing Then
                    If IsDBNull(dset.Tables(0).Rows(index).Item(columnName)) Then
                        found = True
                    Else
                        index = index + 1
                    End If
                ElseIf IsDBNull(dset.Tables(0).Rows(index).Item(columnName)) Then
                    index = index + 1
                ElseIf dset.Tables(0).Rows(index).Item(columnName) = criteria Then
                    found = True
                Else
                    index = index + 1
                End If
            Loop
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
        End Try
    End Sub

    Function Requery() As Boolean
        Try
            operation_finished = False
            dadapter = New OleDb.OleDbDataAdapter(SQLQuery, conn)
            dadapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
            dadapter.MissingMappingAction = MissingMappingAction.Passthrough
            dtable = dset.Tables.Add("Table")
            dadapter.Fill(dset, "Table")
            operation_finished = True
            If dset.Tables(0).Rows.Count() = 0 Then
                index = -1
            Else
                index = 0
            End If
            newRow = False
            foundrecords_counter = 0
            Requery = True
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
            Requery = False
        End Try
    End Function

    Function Requery(ByVal SQLstr As String) As Boolean
        Try
            SQLQuery = SQLstr
            operation_finished = False
            dadapter = New OleDb.OleDbDataAdapter(SQLQuery, conn)
            dadapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
            dadapter.MissingMappingAction = MissingMappingAction.Passthrough
            dtable = dset.Tables.Add("Table")
            dadapter.Fill(dset, "Table")
            operation_finished = True
            If dset.Tables(0).Rows.Count() = 0 Then
                index = -1
            Else
                index = 0
            End If
            newRow = False
            foundrecords_counter = 0
            Requery = True
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
            Requery = False
        End Try
    End Function

    '****************************
    'propriedades
    '****************************

    ReadOnly Property NoMatch() As Boolean
        Get
            If found Then
                NoMatch = False
            Else
                NoMatch = True
            End If
        End Get
    End Property

    Property AbsolutePosition()
        Get
            AbsolutePosition = index
        End Get
        Set(ByVal Value)
            index = Value
        End Set
    End Property

    ReadOnly Property BOF() As Boolean
        Get
            If index < 0 Then
                BOF = True
            Else
                BOF = False
            End If
        End Get
    End Property

    ReadOnly Property EOF() As Boolean
        Get
            If index > dset.Tables(0).Rows.Count - 1 Then
                EOF = True
            Else
                EOF = False
            End If
        End Get
    End Property

    Property FieldValue(ByVal columnName As String)
        Get
            Try
                FieldValue = dset.Tables(0).Rows(index).Item(columnName)
            Catch ex As Exception
                FieldValue = Nothing
            End Try
        End Get
        Set(ByVal Value)
            Try
                If newRow Then
                    drow.Item(columnName) = Value
                Else
                    dset.Tables(0).Rows(index).Item(columnName) = Value
                End If
            Catch ex As Exception
                MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
            End Try
        End Set
    End Property

    Property FieldValue(ByVal columnIndex As Integer)
        Get
            Try
                FieldValue = dset.Tables(0).Rows(index).Item(columnIndex)
            Catch ex As Exception
                FieldValue = Nothing
            End Try
        End Get
        Set(ByVal Value)
            Try
                If newRow Then
                    drow.Item(columnIndex) = Value
                Else
                    dset.Tables(0).Rows(index).Item(columnIndex) = Value
                End If
            Catch ex As Exception
                MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
            End Try
        End Set
    End Property

    ReadOnly Property RecordsFound() As Long
        Get
            RecordsFound = foundrecords_counter
        End Get
    End Property

    ReadOnly Property RecordsCount() As Long
        Get
            RecordsCount = dset.Tables(0).Rows.Count()
        End Get
    End Property

    ReadOnly Property IsLoadingFinnished() As Boolean
        Get
            IsLoadingFinnished = Me.operation_finished
        End Get
    End Property

    ReadOnly Property Fields() As DataColumnCollection
        Get
            Fields = dset.Tables(0).Columns
        End Get
    End Property

End Class