dbopera.vb

需要在.net环境下运行需要安装框架

Imports System.Data.OleDb
Public Class DbOpera    '数据库操作类
    Private sqlStr As String
    Public Sub New(ByVal strComm As String)
        sqlStr = strComm
    End Sub
    Public Function opera() As Boolean '数据库的操作函数，主要负责插入，更新，删除记录
        Try
            Dim comm As OleDbCommand = New OleDbCommand(sqlStr, conn) '声明并初始化数据库操作命令
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            comm.ExecuteNonQuery()
            conn.Close()
            opera = True
        Catch e As System.Exception
            'MsgBox("DbOpera Error:" & sqlStr, MsgBoxStyle.Exclamation, "Exception")
            MsgBox(e.ToString(), MsgBoxStyle.Exclamation, "Exception")
            opera = False '根据返回值的真假来确定是否操作成功
        End Try
    End Function

    Public Function getDG() As System.Windows.Forms.DataGrid '根据数据库的内容来初始化DataGrid的内容
        Dim DG As System.Windows.Forms.DataGrid = New System.Windows.Forms.DataGrid()
        If (conn.State = System.Data.ConnectionState.Closed) Then
            conn.Open()
        End If
        Dim ada As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn)
        Dim ds As DataSet = New DataSet()
        ada.Fill(ds, "marvin")
        conn.Close()
        DG.DataSource = ds.DefaultViewManager.DataSet
        getDG = DG
    End Function

    Public Function checkValid() As Boolean '验证数据库中是否有符合条件的数据
        Dim count As Integer
        count = 0
        Try
            Dim comm As OleDbCommand = New OleDbCommand(sqlStr, conn)
            If (conn.State = System.Data.ConnectionState.Closed) Then
                conn.Open()
            End If
            Dim reader As OleDbDataReader
            reader = comm.ExecuteReader()
            While reader.Read()
                count = count + 1
            End While
            If (count > 0) Then
                checkValid = True
            Else
                checkValid = False
            End If
            conn.Close()
        Catch e As System.Exception
            MsgBox("check Valid:" & sqlStr, MsgBoxStyle.Exclamation, "Error")
            checkValid = False
        End Try
    End Function

    Public Function getSum() As Integer
        '取得符合条件的项的和
        Dim sum As Integer = 0
        Dim num As Integer
        Try
            Dim comm As OleDbCommand = New OleDbCommand(sqlStr, conn)
            If (conn.State = System.Data.ConnectionState.Closed) Then
                conn.Open()
            End If
            Dim reader As OleDbDataReader
            reader = comm.ExecuteReader()
            While reader.Read()
                num = reader.GetInt32(0)
                sum = sum + num
            End While
            conn.Close()
        Catch e As System.Exception
            MsgBox(e.ToString(), MsgBoxStyle.Exclamation, "Exception")
        End Try
        getSum = sum
    End Function
End Class