给oracle命令的参数赋值 及 应用

  Public Function getDataTableFromCmd(ByVal CmdTxt As String, Optional ByVal ParamVals() As Object = Nothing) As DataTable

      

        Dim cmd As New ADODB.Command
        Dim rs As New ADODB.Recordset
        'Dim oReturnParam As SetReturnParam
        Dim oReturnReader As New QueryReturnReader
        Dim RecCount = 0
        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter()

        ' On Error GoTo ERRHANDLER

        SetCommandParameters(cmd, ParamVals)

        cmd.CommandText = CmdTxt
        cmd.ActiveConnection = conn
        cmd.CommandType = CommandType.Text

        rs = cmd.Execute()    '  执行查询命令

        If Not rs.EOF Then   '   如果有查询到记录

            'MessageBox.Show(" fillDataGridView OK ")

            RecCount = rs.RecordCount
            da.Fill(dt, rs)

            Return dt

        End If

 

        Return Nothing

 

    End Function

 

 

''' <summary>
    '''   给oracle命令的参数赋值
    ''' </summary>
    ''' <param name="cmd"></param>
    ''' <param name="inputVals"></param>

    Public Sub SetCommandParameters(ByRef cmd As ADODB.Command, ByVal inputVals() As Object)

 

        Dim ParamNmCnt = 0     '  输入value 的 数量，可以为 0
        If IsNothing(inputVals) = False Then
            ParamNmCnt = inputVals.Count
        End If

 

        If ParamNmCnt > 0 Then    ' 如果 输入value 的 数量 大于 0

            For i = 0 To inputVals.Count - 1   '  对各个value
                Dim ParamFldSize = 40
                Dim oParam As ADODB.Parameter = New ADODB.Parameter

                oParam = cmd.CreateParameter

                Select Case VarType(inputVals(i))
                    Case VariantType.Integer
                        oParam.Type = ADODB.DataTypeEnum.adInteger
                    Case VariantType.Long
                        oParam.Type = ADODB.DataTypeEnum.adLongVarChar
                    Case VariantType.Double
                        oParam.Type = ADODB.DataTypeEnum.adNumeric
                    Case VariantType.Date
                        oParam.Type = ADODB.DataTypeEnum.adDBDate
                    Case VariantType.String
                        ParamFldSize = IIf(inputVals(i) = STRING_EMPTY, 40, Len(inputVals(i)))
                        oParam.Type = ADODB.DataTypeEnum.adLongVarChar
                End Select
                oParam.Size = ParamFldSize
                oParam.Value = inputVals(i)

                cmd.Parameters.Append(oParam)

            Next

        End If

 

    End Sub

 

’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’

‘  下面是具体应用

 

       Dim cmd As New ADODB.Command
        Dim rs As ADODB.Recordset = Nothing
        cmd.CommandType = CommandType.Text

        cmd.ActiveConnection = pConn
        cmd.CommandText = "select *  from AT2S_INFO_NEW  where branch_code = ? and serial_no = ?"

        SetCommandParameters(cmd, {85, 1234})

        rs = cmd.Execute
       ***

       rs.Close()