This is a follow up on the recent thread about this ability to work with various data adapters and the use of the parameters. On that thread, we came up with a solution by the use of the hashtable to hold parameters until we know which adapter to use. The hashtable, however, causes problems as we add more parameters as there is no way to knowing for sure the order of how this is stored. I even tried to add an integer in front of each key as I thought it was being indexed by the key field but this doesn't work. So, I dropped the hashtable approach.

Remembering the ToString you told me which led to the solution, I decided to go back to the array approach, by the use of an object, this time so I can hold any type of values, and to use ToString to convert the key so that would work.

It does, here is the approach:

Imports System.Data
Imports System.Data.Common
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data.Odbc

Public Class Data

    Public oDataSet As DataSet
    Public oDataView As DataView
    Public cSQL As String
    Public cConnectionString As String
    Public nCount As Integer
    Public nSQLMode As Integer = Framework.App.nSQLMode
    Private oConnection As IDbConnection = Nothing
    Private oDataAdapter As IDbDataAdapter = Nothing
    Private oCommand As IDbCommand = Nothing
    Private oDataParameter As IDbDataParameter = Nothing
    Private oParameters(25, 2) As Object
    Private oParameters2 As New Collection
    Private nParameter As Integer = 0

    ' SQL exec
    ' expC1 SQL command
    ' expO1 Connection
    Public Function SQLExec(ByVal tcSQL As String, ByVal tcConnection As String) As Boolean
        InitializeDataObject(nSQLMode, tcConnection, tcSQL)
.
.
.

    Public Function InitializeDataObject(ByVal tnConnType As Integer, ByVal tcConnection As String, _
     ByVal tcSql As String) As IDbConnection
        Dim lnCounter As Integer
        cConnectionString = tcConnection
        cSQL = tcSql
        Select Case tnConnType
            Case 1
                oConnection = New OleDbConnection(tcConnection)
                oDataAdapter = New OleDbDataAdapter
                oCommand = New OleDbCommand
                oDataParameter = New OleDbParameter
                For lnCounter = 1 To nParameter
                    oParameters2.Add(New OleDbParameter(oParameters(lnCounter, 1).ToString, oParameters(lnCounter, 2)))
                Next
            Case 2
                oConnection = New SqlConnection(tcConnection)
                oDataAdapter = New SqlDataAdapter
                oCommand = New SqlCommand
                oDataParameter = New SqlParameter
                For lnCounter = 1 To nParameter
                    oParameters2.Add(New SqlParameter(oParameters(lnCounter, 1).ToString, oParameters(lnCounter, 2)))
                Next
            Case 3
                oConnection = New OdbcConnection(tcConnection)
                oDataAdapter = New OdbcDataAdapter
                oCommand = New OdbcCommand
                oDataParameter = New OdbcParameter
                For lnCounter = 1 To nParameter
                    oParameters2.Add(New OdbcParameter(oParameters(lnCounter, 1).ToString, oParameters(lnCounter, 2)))
                Next
        End Select
        Return oConnection
    End Function

    ' Add a parameter
    ' expC1 Field
    ' expO1 Value
    Public Function ParameterAdd(ByVal tcField As String, ByVal toValue As Object) As Boolean
        nParameter = nParameter + 1
        oParameters(nParameter, 1) = "@" + tcField
        oParameters(nParameter, 2) = toValue
    End Function

    ' Clear the parameter
    ' This is needed is you are doing multiple loDataProvider SQL calls in a row such as a SQLExec()
    ' followed by a SQLUpdate() and you have defined a parameter for the SQLExec() that you don't need
    ' for the SQLUpdate().
    Public Function ParameterClear() As Boolean
        Dim lnCounter As Integer
        For lnCounter = 1 To nParameter
            oParameters(lnCounter, 1) = ""
            oParameters(lnCounter, 2) = ""
        Next
        nParameter = 0
        oParameters2.Clear()
    End Function

End Class

So, in order to isolate the addition of parameters from the interface, I created a ParameterAdd() method in the class. So, if I enhance that, I won't have to worry about the interface. So, as you can see, I add each parameter in an array object. Then, when I know the data adapter, I read from that array into the collection. That collection can then be used in SQLExec(), SQLUpdate(), SQLInsert() and SQLDelete() methods to be added as parameters into the command object.