VB.NET学习笔记：ADO.NET操作ACCESS数据库——读写ACCESS数据库的OleDbHelper帮助类

本文代码由微软官方原版SqlHelper类改编而来，如何获取微软官方原版SqlHelper类请阅读《ADO.NET操作ACCESS数据库——微软官方原版SqlHelper类》

Imports System.Data
Imports System.Data.OleDb

Public NotInheritable Class OleDbHelper
   #Region "私有构造函数和方法"
    ''' <summary>
    ''' 由于此类只提供静态方法，因此将默认构造函数设为私有以防止使用“new OleDbHelper（）”创建的实例'。
    ''' </summary>
    Private Sub New()
    End Sub ' New

    ''' <summary>
    '''此方法用于将OleDbparameters参数数组（参数值）分配给OleDbcommand命令。
    '''这个方法将给任何一个参数分配DBNull.Value
    '''将阻止使用默认值
    ''' </summary>
    ''' <param name="command">要分配OleDbparameters参数的OleDbcommand命令</param>
    ''' <param name="commandParameters">OleDbparameters参数数组</param>
    Private Shared Sub AttachParameters(ByVal command As OleDbCommand, ByVal commandParameters() As OleDbParameter)
        If (command Is Nothing) Then Throw New ArgumentNullException("command")
        If (Not commandParameters Is Nothing) Then
            Dim p As OleDbParameter
            For Each p In commandParameters
                If (Not p Is Nothing) Then
                    '检查未分配值的派生输出值
                    If (p.Direction = ParameterDirection.InputOutput OrElse p.Direction = ParameterDirection.Input) AndAlso p.Value Is Nothing Then
                        p.Value = DBNull.Value
                    End If
                    command.Parameters.Add(p)
                End If
            Next p
        End If
    End Sub ' AttachParameters

    ''' <summary>
    ''' 此方法打开（如果需要）并分配连接、事务、命令类型和参数到提供的命令。
    ''' </summary>
    ''' <param name="command">要准备的OleDbCommand命令</param>
    ''' <param name="connection">一个有效的数据库连接，用于执行此命令</param>
    ''' <param name="transaction">一个有效的事务或者是null值</param>
    ''' <param name="commandType">命令类型 (存储过程,命令文本等)</param>
    ''' <param name="commandText">存储过程名或SQL命令文本</param>
    ''' <param name="commandParameters">与该命令关联的OLEDBParameters数组，如果不需要参数，则为“null”</param>
    ''' <param name="mustCloseConnection">如果连接是打开的,则为true,其它情况下为false</param>
    Private Shared Sub PrepareCommand(ByVal command As OleDbCommand,
                                      ByVal connection As OleDbConnection,
                                      ByVal transaction As OleDbTransaction,
                                      ByVal commandType As CommandType,
                                      ByVal commandText As String,
                                      ByVal commandParameters() As OleDbParameter, ByRef mustCloseConnection As Boolean)

        If (command Is Nothing) Then Throw New ArgumentNullException("command")
        If (commandText Is Nothing OrElse commandText.Length = 0) Then Throw New ArgumentNullException("commandText")

        '如果提供的连接未打开，我们将打开它
        If connection.State <> ConnectionState.Open Then
            If connection.State = ConnectionState.Broken Then
                connection.Close()
                connection.Open()
            Else
                connection.Open()
            End If

            mustCloseConnection = True
        Else
            mustCloseConnection = False
        End If

        '将连接与命令关联
        command.Connection = connection

        '设置命令文本（存储过程名称或SQL语句）
        command.CommandText = commandText

        '分配事务
        If Not (transaction Is Nothing) Then
            If transaction.Connection Is Nothing Then Throw New ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction")
            command.Transaction = transaction
        End If

        ' 设置命令类型.
        command.CommandType = commandType

        ' 分配命令参数
        If Not (commandParameters Is Nothing) Then
            AttachParameters(command, commandParameters)
        End If
        Return
    End Sub ' PrepareCommand
#End Region

#Region "ExecuteNonQuery"

    ''' <summary>
    ''' 对指定连接字符串的数据库执行OLEDBCommand命令（不返回任何结果集，不接受任何参数）。
    ''' 示例：
    ''' dim result as integer=executeNonQuery（connstring，commandType.storedProcedure，“publishOrders”）。
    ''' </summary>
    ''' <param name="connectionString">一个有效的数据库连接字符串</param>
    ''' <param name="commandType">命令类型 (存储过程,命令文本等)</param>
    ''' <param name="commandText">存储过程名称或SQL语句</param>
    ''' <returns>返回命令影响的行数</returns>
    Public Overloads Shared Function ExecuteNonQuery(ByVal connectionString As String,
                                                   ByVal commandType As CommandType,
                                                   ByVal commandText As String) As Integer
        'OledBParameters参数数组为Nothing
        Return ExecuteNonQuery(connectionString, commandType, commandText, CType(Nothing, OleDbParameter()))
    End Function ' ExecuteNonQuery

    ''' <summary>
    ''' 使用提供的参数对指定连接字符串的数据库执行OLEDBCommand命令（不返回任何结果集）
    ''' 示例：
    ''' Dim result As Integer = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24))
    ''' </summary>
    ''' <param name="connectionString">一个有效的数据库连接字符串</param>
    ''' <param name="commandType">命令类型 (存储过程,命令文本等)</param>
    ''' <param name="commandText">存储过程名称或SQL语句</param>
    ''' <param name="commandParameters">用于执行命令的OleDbParameter参数数组</param>
    ''' <returns>返回命令影响的行数</returns>
    Public Overloads Shared Function ExecuteNonQuery(ByVal connectionString As String,
                                                     ByVal commandType As CommandType,
                                                     ByVal commandText As String,
                                                     ByVal ParamArray commandParameters() As OleDbParameter) As Integer
        If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")
        '创建并打开一个OLEDB连接，完成后将其丢弃
        Dim connection As OleDbConnection
        Try
            connection = New OleDbConnection(connectionString)
            connection.Open()

            '调用将连接替换为连接字符串的重载
            Return ExecuteNonQuery(connection, commandType, commandText, commandParameters)
        Finally
            If Not connection Is Nothing Then connection.Dispose()
        End Try
    End Function ' ExecuteNonQuery

    ''' <summary>
    ''' 对提供的数据库连接对象执行OleDbCommand命令（不返回结果集，不接受参数）。
    ''' 示例：
    ''' Dim result As Integer = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders")
    ''' </summary>
    ''' <param name="connection">一个有效的数据库连接对象</param>
    ''' <param name="commandType">命令类型(存储过程,命令文本等)</param>
    ''' <param name="commandText">存储过程名称或SQL语句</param>
    ''' <returns>返回影响的行数</returns>
    Public Overloads Shared Function ExecuteNonQuery(ByVal connection As OleDbConnection,
                                                     ByVal commandType As CommandType,
                                                     ByVal commandText As String) As Integer
        'OledBParameters参数数组为Nothing
        Return ExecuteNonQuery(connection, commandType, commandText, CType(Nothing, OleDbParameter()))

    End Function ' ExecuteNonQuery

    ''' <summary>
    ''' 使用提供的参数对指定的数据库连接对象执行OLEDBCommand（不返回任何结果集）
    ''' 示例：
    ''' Dim result As Integer = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24))
    ''' </summary>
    ''' <param name="connection">一个有效的数据库连接对象</param>
    ''' <param name="commandType">命令类型(存储过程,命令文本等)</param>
    ''' <param name="commandText">存储过程名称或SQL语句</param>
    ''' <param name="commandParameters">用于执行命令的OleDbParameter参数数组</param>
    ''' <returns>返回影响的行数</returns>
    Public Overloads