recordset.vb

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

'*************************************************************************************************
'*************************************************************************************************
'ADO.NET RecordSet Library
'Created by: João Martins 2006
'
'
'You are free to use and change this library as you please as long as you keep these comments
'and the application you're developing is for free
'
'*************************************************************************************************
'*************************************************************************************************




Public Class RecordSet
    Private conn As OleDb.OleDbConnection
    Private dadapter As OleDb.OleDbDataAdapter
    Private cmdbld As OleDb.OleDbCommandBuilder
    Private select_command As OleDb.OleDbCommand
    Private insert_command As OleDb.OleDbCommand
    Private delete_command As OleDb.OleDbCommand
    Private update_command As OleDb.OleDbCommand
    Private dset As DataSet
    Private dtable As DataTable
    Private drow As DataRow
    Private index As Long
    Private newRow As Boolean
    Private found As Boolean
    Private SQLQuery As String
    Private foundrecords_counter As Long
    Private operation_finished As Boolean = False

    '****************************
    'construtor e destrutor
    '****************************

    Sub New()
        Try
            conn = New OleDb.OleDbConnection
            dadapter = New OleDb.OleDbDataAdapter
            dset = New DataSet
            index = 0
            newRow = False
            foundrecords_counter = 0
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
        End Try
    End Sub

    Sub New(ByVal constr As String)
        Try
            conn = New OleDb.OleDbConnection(constr)
            dadapter = New OleDb.OleDbDataAdapter
            dset = New DataSet
            index = 0
            newRow = False
            foundrecords_counter = 0
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
        End Try
    End Sub

    Sub New(ByVal constr As String, ByVal SQLstr As String)
        Try
            conn = New OleDb.OleDbConnection(constr)
            SQLQuery = SQLstr
            operation_finished = False
            dadapter = New OleDb.OleDbDataAdapter(SQLQuery, conn)
            dadapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
            dadapter.MissingMappingAction = MissingMappingAction.Passthrough
            dset = New DataSet
            dtable = New DataTable
            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
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
        End Try
    End Sub

    Protected Overrides Sub Finalize()
        Try
            CloseRecordSet()
        Catch ex As Exception
        End Try
    End Sub

    '****************************
    'metodos
    '****************************

    Sub Dispose()
        Me.Finalize()
    End Sub

    Function Connection(ByVal constr As String) As Boolean
        Try
            conn.ConnectionString = constr
            conn.Open()
            conn.Close()
            Connection = True
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
            Connection = False
        End Try
    End Function

    Function OpenRecordSet(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
            OpenRecordSet = True
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
            OpenRecordSet = False
        End Try
    End Function

    Sub CloseRecordSet()
        Try
            dtable.Dispose()
            dset.Dispose()
            dadapter.Dispose()
            conn.Dispose()
        Catch ex As Exception
        End Try
    End Sub

    Sub MoveFirst()
        index = 0
    End Sub

    Sub MoveLast()
        Try
            index = dset.Tables(0).Rows.Count - 1
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
        End Try
    End Sub

    Sub MoveNext()
        index = index + 1
    End Sub

    Sub MovePrevious()
        index = index - 1
    End Sub

    Sub AddNew()
        Try
            drow = dset.Tables(0).NewRow()
            newRow = True
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
        End Try
    End Sub

    Sub Update()
        Try
            If newRow Then
                dset.Tables(0).Rows.Add(drow)
                newRow = False
            End If
            cmdbld = New OleDb.OleDbCommandBuilder(dadapter)
            dadapter.UpdateCommand = cmdbld.GetUpdateCommand
            dadapter.DeleteCommand = cmdbld.GetDeleteCommand
            dadapter.InsertCommand = cmdbld.GetInsertCommand
            dadapter.Update(dset)
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
        End Try
    End Sub

    Sub FindFirst(ByVal columnName As String, ByVal criteria As Object)
        Dim memory As Long
        Try
            found = False
            index = 0
            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

            If found Then
                foundrecords_counter = 0
                memory = index
                Do Until Me.NoMatch
                    foundrecords_counter = foundrecords_counter + 1
                    Me.FindNext(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 FindLast(ByVal columnName As String, ByVal criteria As Object)
        Dim memory As Long

        Try
            found = False
            index = dset.Tables(0).Rows.Count - 1
            Do Until found Or index <= -1