###AdoConverter
For better extensibility, the methods in that class shouldn't be calling each others the way ToLongParameter is calling ToIntegerParameter. Also instead of hard-coding the type
Private Type TypeMappings
BooleanMap As ADODB.DataTypeEnum
ByteMap As ADODB.DataTypeEnum
CurrencyMap As ADODB.DataTypeEnum
DateMap As ADODB.DataTypeEnum
DoubleMap As ADODB.DataTypeEnum
IntegerMap As ADODB.DataTypeEnum
LongMap As ADODB.DataTypeEnum
SingleMap As ADODB.DataTypeEnum
StringMap As ADODB.DataTypeEnum
End Type
Private mappings As TypeMappings
Option Explicit
Private Sub Class_Initialize()
mappings.BooleanMap = adBoolean
mappings.ByteMap = adInteger
mappings.CurrencyMap = adCurrency
mappings.DateMap = adDate
mappings.DoubleMap = adDouble
mappings.IntegerMap = adInteger
mappings.LongMap = adInteger
mappings.SingleMap = adSingle
mappings.StringMap = adVarChar
End Sub
The class can then expose a [Type]Mapping property for each [Type]Map member of mappings, and then the client code can control the type of ADODB parameter getting created.
Public Function ToLongParameter(ByVal value As Variant, ByVal direction As ADODB.ParameterDirectionEnum) As ADODB.Parameter
Dim longValue As Long
longValue = CLng(value)
Dim result As New ADODB.Parameter
With result
.type = mappings.LongMap ' mapped type is no longer hard-coded
.direction = direction
.value = longValue
End With
Set ToLongParameter = result
End Function
###SqlCommand
Passing in a Connection is a great idea: it enables wrapping these database operations in a transaction. However the interface of SqlCommand isn't consistent about it: there's no reason why SelectSingleValue shouldn't be taking a Connection parameter as well. Doing that will enable reusing an existing connection instead of creating a new one every time, on top of improving usage consistency.
Also each exposed method creates a Command object, and that code is duplicated every time. You could factor it into its own private factory method:
Private Function CreateCommand(connection As ADODB.connection, ByVal cmdType As ADODB.CommandTypeEnum, ByVal sql As String, parameterValues() As Variant) As ADODB.Command
Dim cmd As New ADODB.Command
cmd.ActiveConnection = connection
cmd.CommandType = cmdType
cmd.CommandText = sql
Dim i As Integer
Dim value As Variant
If IsArrayInitialized(parameterValues) Then
For i = LBound(parameterValues) To UBound(parameterValues)
value = parameterValues(i)
cmd.parameters.Append ToSqlInputParameter(value)
Next
End If
Set CreateCommand = cmd
End Function
This turns the Execute method into:
Public Function Execute(connection As ADODB.connection, ByVal sql As String, ParamArray parameterValues()) As ADODB.Recordset
Dim values() As Variant
values = parameterValues
Dim cmd As ADODB.Command
Set cmd = CreateCommand(connection, adCmdText, sql, values)
Set Execute = cmd.Execute
End Function
And then you could add an ExecuteStoredProc method just as easily, without duplicating all the command-creating code:
Public Function ExecuteStoredProc(connection As ADODB.connection, ByVal spName As String, ParamArray parameterValues()) As ADODB.Recordset
Dim values() As Variant
values = parameterValues
Dim cmd As ADODB.Command
Set cmd = CreateCommand(connection, adCmdStoredProc, spName, values)
Set ExecuteStoredProc = cmd.Execute
End Function
###Some Opportunities
This "wrapper" doesn't really abstract away the syntax for parameterized queries; if a value is needed twice, it needs to be specified twice; also the values must be specified in the same order they're replacing question marks.
You could implement something similar to this StringFormat code (taking a bit of a performance hit though), and enable named parameters, and a formatting syntax that would allow specifying Precision and Size for any parameter, or even a specific mapping for a given parameter (say Integer parameter 1 is mapped to a smallint and Integer parameter 2 maps to an int, both in the same query), and one could specify parameters' direction, enabling support for output parameters (then you'd need a way to return the parameter values) - and the order of parameters could be specified as well.
The flipside is that this would make a new syntax to learn, which somewhat defeats the purpose of making things simpler for inexperienced programmers.