基础类.vb

CSDN V3.0 使用VB。Net开发 可以使用该助手访问CSDN

Imports System.Data.OleDb
Imports System.Data

Public Class DbBase

    Private Conn As DbConnection
    Sub New()
        Conn = New DbConnection
    End Sub

    ' 保存的错误！
    Private _StrError As String
    Public ReadOnly Property StrError() As String
        Get
            Return _StrError
        End Get
    End Property

#Region "执行命令，返回影响的行数"
    '执行但行命令
    Function ExecuteNonQuery(ByVal OleDbCommandText As String, Optional ByVal OleDbCommandType As CommandType = CommandType.Text) As Integer
        Return ExecuteNonQuery(OleDbCommandText, Nothing, OleDbCommandType)
    End Function

    '执行但行命令
    Function ExecuteNonQuery(ByVal OleDbCommandText As String, ByVal OleDbParameters As ArrayList, Optional ByVal OleDbCommandType As CommandType = CommandType.Text) As Integer
        Dim SqlComm As OleDbCommand = CreateSqlComm(OleDbCommandText, OleDbParameters, OleDbCommandType)
        If SqlComm Is Nothing Then
            Return 0
        End If
        Try
            Return SqlComm.ExecuteNonQuery()
        Catch ex As Exception
            _StrError = ex.Message & vbCrLf & ex.StackTrace
        Finally
            Conn.ConnObj.Close()
        End Try
    End Function
#End Region

#Region "执行命令，返回影响的行数"
    '执行但行命令
    Function ExecuteBool(ByVal OleDbCommandText As String, ByVal OleDbParameters As ArrayList, Optional ByVal OleDbCommandType As CommandType = CommandType.Text) As Boolean
        If ExecuteNonQuery(OleDbCommandText, OleDbParameters, OleDbCommandType) >= 0 And Len(Me.StrError) = 0 Then
            Return True
        End If
    End Function

    '执行但行命令
    Function ExecuteBool(ByVal OleDbCommandText As String, Optional ByVal OleDbCommandType As CommandType = CommandType.Text) As Boolean
        Return ExecuteNonQuery(OleDbCommandText, Nothing, OleDbCommandType) >= 0 And Len(Me.StrError)
    End Function

#End Region

#Region "执行命令，返回单值"
    '执行但行命令
    Function ExecuteScalar(ByVal OleDbCommandText As String, ByVal OleDbParameters As ArrayList, Optional ByVal OleDbCommandType As CommandType = CommandType.Text) As Object
        Dim obj As Object = Nothing
        Dim SqlComm As OleDbCommand = CreateSqlComm(OleDbCommandText, OleDbParameters, OleDbCommandType)
        If SqlComm Is Nothing Then
            Return Nothing
        End If
        Try
            obj = SqlComm.ExecuteScalar
        Catch ex As Exception
            _StrError = ex.Message & vbCrLf & ex.StackTrace
        Finally
            Conn.ConnObj.Close()
        End Try
        Return obj
    End Function
    '执行但行命令
    Function ExecuteScalar(ByVal OleDbCommandText As String, Optional ByVal OleDbCommandType As CommandType = CommandType.Text) As Object
        Return ExecuteScalar(OleDbCommandText, Nothing, OleDbCommandType)
    End Function
#End Region

#Region "加载数据集合"
    Function LoadDateSet(ByVal OleDbCommandText As String, ByVal OleDbParameters As ArrayList, Optional ByVal OleDbCommandType As CommandType = CommandType.Text) As DataSet
        Dim SqlDa As New OleDbDataAdapter
        Dim ds As New DataSet
        SqlDa.SelectCommand = CreateSqlComm(OleDbCommandText, OleDbParameters, OleDbCommandType)
        If SqlDa.SelectCommand Is Nothing Then
            Return Nothing
        End If
        Try
            SqlDa.Fill(ds)
        Catch ex As Exception
            _StrError = ex.Message & vbCrLf & ex.StackTrace
            Console.WriteLine(_StrError)
        Finally
            Conn.ConnObj.Close()
            SqlDa.Dispose()

        End Try
        Return ds
    End Function
    Function LoadDateSet(ByVal OleDbCommandText As String, Optional ByVal OleDbCommandType As CommandType = CommandType.Text) As DataSet
        Return LoadDateSet(OleDbCommandText, Nothing, OleDbCommandType)
    End Function
#End Region

#Region "创建OleDbCommand对象"
    '创建命令对象
    Function CreateSqlComm(ByVal OleDbCommandText As String, ByVal OleDbParameters As ArrayList, Optional ByVal OleDbCommandType As CommandType = CommandType.Text) As OleDbCommand
        Dim comm As New OleDbCommand
        If Not Me.Conn.Connect Then
            Me._StrError = Conn.LastError
            Return Nothing
        End If
        With comm
            .Connection = Conn.ConnObj
            .CommandType = OleDbCommandType
            .CommandText = OleDbCommandText
            .CommandTimeout = 5
        End With

        AddParameters(comm, OleDbParameters)
        Return comm
    End Function
    Function CreateSqlComm(ByVal OleDbCommandText As String, Optional ByVal OleDbCommandType As CommandType = CommandType.Text) As OleDbCommand
        Return CreateSqlComm(OleDbCommandText, Nothing, OleDbCommandType)
    End Function
    Function CreateSqlComm()
        Return CreateSqlComm("", Nothing, CommandType.Text)
    End Function

#End Region

#Region "处理参数"

    '这里处理参数
    Sub AddParameters(ByVal SqlComm As OleDbCommand, ByVal OleDbParameters As ArrayList)
        If Not OleDbParameters Is Nothing AndAlso OleDbParameters.Count > 0 Then
            For Each Sqlp As OleDbParameter In OleDbParameters
                SqlComm.Parameters.Add(Sqlp)
            Next
        End If
    End Sub

#End Region

#Region "创建Datareader对象"
    '创建Datareader对象
    Function CreateOleDbDataReader(ByVal OleDbCommandText As String, ByVal OleDbParameters As ArrayList, Optional ByVal OleDbCommandType As CommandType = CommandType.Text) As OleDbDataReader
        Dim comm As OleDbCommand = Me.CreateSqlComm(OleDbCommandText, OleDbParameters, OleDbCommandType)
        If comm Is Nothing Then
            Return Nothing
        End If
        Return comm.ExecuteReader(CommandBehavior.CloseConnection)
    End Function
    Function CreateOleDbDataReader(ByVal OleDbCommandText As String, Optional ByVal OleDbCommandType As CommandType = CommandType.Text) As OleDbDataReader
        Return Me.CreateOleDbDataReader(OleDbCommandText, Nothing, OleDbCommandType)
    End Function
#End Region

End Class