You could implement something similar to this StringFormat codethis 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.
replaced http://codereview.stackexchange.com/ with https://codereview.stackexchange.com/
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.
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.
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
###Some Opportunities
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
###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.
###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.
default