Materializing any ADODB Query

Following-up on Creating ADODB Parameters on the fly and pushing the "wrapping" of ADODB a step further, I have written two more classes that allows me to expose methods that don't require a Connection object, without returning an ADODB.Recordset.

Taking this method as a reference:

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


A bit of context

I'm not comfortable with the idea of exposing a method that would return an ADODB.Recordset without taking in an ADODB.Connection, because this would mean opening a connection in a function that doesn't control when the connection needs to be closed.

To address this issue, I added two private fields to my SqlCommand:

Private connString As String
Private resultFactory As New SqlResult


I'm using a pre-determined connection string in Class_Initialize for the connString value:

Private Sub Class_Initialize()
connString = Application.ConnectionString
End Sub


I adopted the "Quick" prefix to refer to an "overload" method that owns its own connection, hence the connection-less "overload" for the Execute method above will be called QuickExecute:

Public Function QuickExecute(ByVal sql As String, ParamArray parametervalues()) As SqlResult

Dim parameters() As Variant
parameters = parametervalues

Dim connection As New ADODB.connection
connection.ConnectionString = connString

connection.Open

Dim rs As ADODB.Recordset
Set rs = Execute(connection, sql, parameters)

Set QuickExecute = resultFactory.Create(rs)

rs.Close
Set rs = Nothing

connection.Close
Set connection = Nothing

End Function


The method consumes the recordset a

A quick code inspection with MZ-Tools reveals the following:


Local variables names, fieldValues and row can be safely removed from the Create method.

That's all the tool is picking up though.

I like how it makes everything automagical, however if it were the only way to get the data I'd be worried about performance with some large recordsets. The List class makes it easier to find a value by field name, but the search for the field name happens every time, which means lots of time is spent wasted finding the same field index over and over again, for each record. Keeping the index for each name in a Dictionary would be more efficient than having to search for each column index for each row.

That said, SqlCommand has methods that take a ADODB.Connection and output a ADODB.Recordset, having the possibility to use these methods for larger recordsets and let the client code deal with the connection and the recordset, somewhat makes up for the performance hit of the wrapper SqlResult; you get the automagical parameters and the possibility to only iterate the data once.

StackExchange Code Review Q#46506, answer score: 7