c_sqlhelper.vb

这是一个订单管理系统

            ' Call the overload that takes an array of oledbParameters
            Return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters)
        Else            ' Otherwise we can just call the SP without params
            Return ExecuteDataset(transaction, CommandType.StoredProcedure, spName)
        End If
    End Function        ' ExecuteDataset

#End Region

#Region "ExecuteReader"
    ' this enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that
    ' we can set the appropriate CommandBehavior when calling ExecuteReader()
    Private Enum oledbconnectionOwnership
        ' Connection is owned and managed by SqlHelper
        Internal
        ' Connection is owned and managed by the caller
        [External]
    End Enum        ' oledbconnectionOwnership

    ' Create and prepare a oledbCommand, and call ExecuteReader with the appropriate CommandBehavior.
    ' If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
    ' If the caller provided the connection, we want to leave it to them to manage.
    ' Parameters:
    ' -connection - a valid oledbconnection, on which to execute this command 
    ' -transaction - a valid oledbTransaction, or ' null' 
    ' -commandType - the CommandType (stored procedure, text, etc.) 
    ' -commandText - the stored procedure name or T-SQL command 
    ' -commandParameters - an array of oledbParameters to be associated with the command or ' null' if no parameters are required 
    ' -connectionOwnership - indicates whether the connection parameter was provided by the caller, or created by SqlHelper 
    ' Returns: oledbDataReader containing the results of the command 
    Private Overloads Shared Function ExecuteReader(ByVal connection As OleDbConnection, _
      ByVal transaction As OleDbTransaction, _
      ByVal commandType As CommandType, _
      ByVal commandText As String, _
      ByVal commandParameters() As OleDbParameter, _
      ByVal connectionOwnership As oledbconnectionOwnership) As OleDbDataReader

        If (connection Is Nothing) Then Throw New ArgumentNullException("connection")

        Dim mustCloseConnection As Boolean = False
        ' Create a command and prepare it for execution
        Dim cmd As New OleDbCommand
        Try
            ' Create a reader
            Dim dataReader As oledbDataReader

            PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, mustCloseConnection)

            ' Call ExecuteReader with the appropriate CommandBehavior
            If connectionOwnership = oledbconnectionOwnership.External Then
                dataReader = cmd.ExecuteReader()
            Else
                dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            End If

            ' Detach the oledbParameters from the command object, so they can be used again
            Dim canClear As Boolean = True
            Dim commandParameter As OleDbParameter
            For Each commandParameter In cmd.Parameters
                If commandParameter.Direction <> ParameterDirection.Input Then
                    canClear = False
                End If
            Next

            If (canClear) Then cmd.Parameters.Clear()

            Return dataReader
        Catch
            If (mustCloseConnection) Then connection.Close()
            Throw
        End Try
    End Function        ' ExecuteReader

    ' Execute a oledbCommand (that returns a resultset and takes no parameters) against the database specified in 
    ' the connection string. 
    ' e.g.:  
    ' Dim dr As oledbDataReader = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders")
    ' Parameters:
    ' -connectionString - a valid connection string for a oledbconnection 
    ' -commandType - the CommandType (stored procedure, text, etc.) 
    ' -commandText - the stored procedure name or T-SQL command 
    ' Returns: A oledbDataReader containing the resultset generated by the command 
    Public Overloads Shared Function ExecuteReader(ByVal connectionString As String, _
      ByVal commandType As CommandType, _
      ByVal commandText As String) As oledbDataReader
        ' Pass through the call providing null for the set of oledbParameters
        Return ExecuteReader(connectionString, commandType, commandText, CType(Nothing, oledbParameter()))
    End Function        ' ExecuteReader

    ' Execute a oledbCommand (that returns a resultset) against the database specified in the connection string 
    ' using the provided parameters.
    ' e.g.:  
    ' Dim dr As oledbDataReader = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new oledbParameter("@prodid", 24))
    ' Parameters:
    ' -connectionString - a valid connection string for a oledbconnection 
    ' -commandType - the CommandType (stored procedure, text, etc.) 
    ' -commandText - the stored procedure name or T-SQL command 
    ' -commandParameters - an array of SqlParamters used to execute the command 
    ' Returns: A oledbDataReader containing the resultset generated by the command 
    Public Overloads Shared Function ExecuteReader(ByVal connectionString As String, _
      ByVal commandType As CommandType, _
      ByVal commandText As String, _
      ByVal ParamArray commandParameters() As oledbParameter) As oledbDataReader
        If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")

        ' Create & open a oledbconnection
        Dim connection As oledbconnection = Nothing
        Try
            connection = New oledbconnection(connectionString)
            connection.Open()
            ' Call the private overload that takes an internally owned connection in place of the connection string
            Return ExecuteReader(connection, CType(Nothing, oledbTransaction), commandType, commandText, commandParameters, oledbconnectionOwnership.Internal)
        Catch
            ' If we fail to return the SqlDatReader, we need to close the connection ourselves
            If Not connection Is Nothing Then connection.Dispose()
            Throw
        End Try
    End Function        ' ExecuteReader

    ' Execute a stored procedure via a oledbCommand (that returns a resultset) against the database specified in 
    ' the connection string using the provided parameter values.  This method will discover the parameters for the 
    ' stored procedure, and assign the values based on parameter order.
    ' This method provides no access to output parameters or the stored procedure' s return value parameter.
    ' e.g.:  
    ' Dim dr As oledbDataReader = ExecuteReader(connString, "GetOrders", 24, 36)
    ' Parameters:
    ' -connectionString - a valid connection string for a oledbconnection 
    ' -spName - the name of the stored procedure 
    ' -parameterValues - an array of objects to be assigned as the input values of the stored procedure 
    ' Returns: A oledbDataReader containing the resultset generated by the command 
    Public Overloads Shared Function ExecuteReader(ByVal connectionString As String, _
      ByVal spName As String, _
      ByVal ParamArray parameterValues() As Object) As oledbDataReader
        If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")
        If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")

        Dim commandParameters As oledbParameter()

        ' If we receive parameter values, we need to figure out where they go
        If Not (parameterValues Is Nothing) AndAlso parameterValues.Length > 0 Then
            ' Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName)

            ' Assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues)

            ' Call the overload that takes an array of oledbParameters
            Return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters)
            ' Otherwise we can just call the SP without params
        Else
            Return ExecuteReader(connectionString, CommandType.StoredProcedure, spName)
        End If
    End Function        ' ExecuteReader

    ' Execute a oledbCommand (that returns a resultset and takes no parameters) against the provided oledbconnection. 
    ' e.g.:  
    ' Dim dr As oledbDataReader = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders")
    ' Parameters:
    ' -connection - a valid oledbconnection 
    ' -commandType - the CommandType (stored procedure, text, etc.) 
    ' -commandText - the stored procedure name or T-SQL command 
    ' Returns: A oledbDataReader containing the resultset generated by the command 
    Public Overloads Shared Function ExecuteReader(ByVal connection As oledbconnection, _
      ByVal commandType As CommandType, _
      ByVal commandText As String) As oledbDataReader

        Return ExecuteReader(connection, commandType, commandText, CType(Nothing, oledbParameter()))

    End Function        ' ExecuteReader

    ' Execute a oledbCommand (that returns a resultset) against the specified oledbconnection 
    ' using the provided parameters.
    ' e.g.:  
    ' Dim dr As oledbDataReader = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new oledbParameter("@prodid", 24))
    ' Parameters:
    ' -connection - a valid oledbconnection 
    ' -commandType - the CommandType (stored procedure, text, etc.) 
    ' -commandText - the stored procedure name or T-SQL command 
    ' -commandParameters - an array of SqlParamters used to execute the command 
    ' Returns: A oledbDataReader containing the resultset generated by the command 
    Public Overloads Shared Function ExecuteReader(ByVal connection As oledbconnection, _
      ByVal commandType As CommandType, _
      ByVal commandText As String, _
      ByVal ParamArray commandParameters() As oledbParameter) As oledbDataReader
        ' Pass through the call to private overload using a null transaction value
        Return ExecuteReader(connection, CType(Nothing, oledbTransaction), commandType, commandText, commandParameters, oledbconnectionOwnership.External)

    End Function        ' ExecuteReader

    ' Execute a stored procedure via a oledbCommand (that returns a resultset) against the specified oledbconnection 
    ' using the provided parameter values.  This method will discover the parameters for the 
    ' stored procedure, and assign the values based on parameter order.
    ' This method provides no access to output parameters or the stored procedure' s return value parameter.
    ' e.g.:  
    ' Dim dr As oledbDataReader = ExecuteReader(conn, "GetOrders", 24, 36)
    ' Parameters:
    ' -connection - a valid oledbconnection 
    ' -spName - the name of the stored procedure 
    ' -parameterValues - an array of objects to be assigned as the input values of the stored procedure 
    ' Returns: A oledbDataReader containing the resultset generated by the command 
    Public Overloads Shared Function ExecuteReader(ByVal connection As oledbconnection, _
      ByVal spName As String, _
      ByVal ParamArray parameterValues() As Object) As oledbDataReader
        If (connection Is Nothing) Then Throw New ArgumentNullException("connection")
        If (spName Is Nothing OrElse spName.Length = 0) Then Throw New ArgumentNullException("spName")

        Dim commandParameters As oledbParameter()
        ' If we receive parameter values, we need to figure out where they go
        If Not (parameterValues Is Nothing) AndAlso parameterValues.Length > 0 Then
            commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName)

            AssignParameterValues(commandParameters, parameterValues)

            Return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters)
        Else            ' Otherwise we can just call the SP without params
            Return ExecuteReader(connection, CommandType.StoredProcedure, spName)
        End If

    End Function        ' ExecuteReader

    ' Execute a oledbCommand (that returns a resultset and takes no parameters) against the provided oledbTransaction.
    ' e.g.:  
    ' Dim dr As oledbDataReader = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders")
    ' Parameters:
    ' -transaction - a valid oledbTransaction  
    ' -commandType - the CommandType (stored procedure, text, etc.) 
    ' -commandText - the stored procedure name or T-SQL command 
    ' Returns: A oledbDataReader containing the resultset generated by the command 
    Public Overloads Shared Function ExecuteReader(ByVal transaction As oledbTransaction, _
      ByVal commandType As CommandType, _
      ByVal commandText As String) As oledbDataReader
        ' Pass through the call providing null for the set of oledbParameters