Design Question

[Sandoval Gonzalez]

I am writing a class that will encapsulate all the interaction with my db.
I need to have methods that will return recordsets. My question is, which
is better:


A) Put the connection object within the class so that-

In the app:

Set rst = gdb.rsSomeRecordset()
...
do something with it
...

rst.Close
Set rst = Nothing


In the class:
Private mcnn As ADODB.Connection

Public Function rsSomeRecordset()
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
msSQL = "select ... from ... where ...;"
rst.Open msSQL, mcnn, adOpenStatic, adLockReadOnly
set rst.ActiveConnection = Nothing

Set rsSomeRecordset = rst
End Function



B) Have the connection object external from the class, and pass it in as
a parameter:

In the app:

Set rst = gdb.rsSomeRecordset(mcnn)
...
do something with it
...

rst.Close
Set rst = Nothing


In the class:

Public Function rsSomeRecordset(cnn As ADODB.Connection)
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
msSQL = "select ... from ... where ...;"
rst.Open msSQL, cnn, adOpenStatic, adLockReadOnly
set rst.ActiveConnection = Nothing

Set rsSomeRecordset = rst
End Function


Is there any difference?

tia,
Sandoval

[Arthur Wood]

It is ALMOST always preferable to have the class be completely self-contained,
and NOT to require any external "connection" to be passed in. That way,
you can easily change ANYTHING about the data access, since that class does
not need to know anything from trhe outside, and similarly, the outside user
of the class does not have to know anything in order to use the class. The
second approach would require that a cunsumer of your class would need to
know about 1) how to connect to A database, and 2) which database to connect
to, and 3) what technology to use to connect to that database. That makes
the consunmer of the class into to a PRIMARY cointroiller of the access to
the data. But the consumer of the data should be JUST THAT, a consumer of
some data, supplied from a "BLACK BOX" - How the data gets into that black
box is of very little concern to the consumer--all that the consumer should
need to know is that the BLACK BOX will supply some data.

Using the first approach, you can easily change the KIND of DATABASE (Access
or SQL Server or Oracle or MySQL or...) and the Names of that DataBAse, as
long as the Requested data can in fact be supplied from THAT Database, without
having to change ANYTHING about the user of your class.

This is a much more flexible solution, in general...

Arthur Wood

Arthur Wood


"Sandoval Gonzalez" <xxxyyy@zzzzz.com> wrote:
>
>I am writing a class that will encapsulate all the interaction with my db.
> I need to have methods that will return recordsets. My question is, which
>is better:
>
>
>A) Put the connection object within the class so that-
>
>In the app:
>
> Set rst = gdb.rsSomeRecordset()
> ...
> do something with it
> ...
>
> rst.Close
> Set rst = Nothing
>
>
>In the class:
>Private mcnn As ADODB.Connection
>
>Public Function rsSomeRecordset()
> Dim rst As ADODB.Recordset
>
> Set rst = New ADODB.Recordset
> msSQL = "select ... from ... where ...;"
> rst.Open msSQL, mcnn, adOpenStatic, adLockReadOnly
> set rst.ActiveConnection = Nothing
>
> Set rsSomeRecordset = rst
>End Function
>
>
>
>B) Have the connection object external from the class, and pass it in as
>a parameter:
>
>In the app:
>
> Set rst = gdb.rsSomeRecordset(mcnn)
> ...
> do something with it
> ...
>
> rst.Close
> Set rst = Nothing
>
>
>In the class:
>
>Public Function rsSomeRecordset(cnn As ADODB.Connection)
> Dim rst As ADODB.Recordset
>
> Set rst = New ADODB.Recordset
> msSQL = "select ... from ... where ...;"
> rst.Open msSQL, cnn, adOpenStatic, adLockReadOnly
> set rst.ActiveConnection = Nothing
>
> Set rsSomeRecordset = rst
>End Function
>
>
>Is there any difference?
>
>tia,
>Sandoval

[Willy Van den Driessche]

I agree with Arthur but have some additional comments.

You should only consider passing in the connection if your application needs
it. P.e. if you app accesses data with the same structure from multiple
databases during one run.
I you need that, then you could just sep up your application to do both
things. Have a property Connection on the class that is auto-created if not
assigned by the user.

A general remark : don't fall into the trap of analysis paralysis. Don't
try to design your class for the unforeseeable future. If you need
something in the future, you'll add it then. Don't torture yourself into
foreseeing it all. Nobody can and the designs that do it are overly complex
and only allow variations along several dimensions.

For your specific problem, I would use a singleton (roughly a encapsulated
global variable) for the connection. Chances are high this connection will
be shared between several database classes, so it's best to keep it around.
The initialization of this singleton should be either explicit (by setting
it at startup) or implicit (by reading the required info from some
configuration file or so). Anyway, you database class uses the singleton
and the code that uses your database class never needs to care about it.
--
Van den Driessche Willy
For a work in progress :
http://users.skynet.be/wvdd2/index.html

[Sandoval Gonzalez]

Makes sense. I was just wondering - if I have the connection internal to
the class and I pass disconnected recordsets back to the app, don't the recordsets
have a pointer back to the connection in the class? And will this cause
any memory leaks? In this particular case, I am keeping the class instantiated
for the duration of the app, but if I wanted to destroy it with one of those
a recordsets still open, would having a pointer to its connection cause an
error?

thanks,
Sandoval


"Arthur Wood" <wooda@saic-trsc.com> wrote:
>
>It is ALMOST always preferable to have the class be completely self-contained,
>and NOT to require any external "connection" to be passed in. That way,
>you can easily change ANYTHING about the data access, since that class does
>not need to know anything from trhe outside, and similarly, the outside
user
>of the class does not have to know anything in order to use the class.
The
>second approach would require that a cunsumer of your class would need to
>know about 1) how to connect to A database, and 2) which database to connect
>to, and 3) what technology to use to connect to that database. That makes
>the consunmer of the class into to a PRIMARY cointroiller of the access
to
>the data. But the consumer of the data should be JUST THAT, a consumer
of
>some data, supplied from a "BLACK BOX" - How the data gets into that black
>box is of very little concern to the consumer--all that the consumer should
>need to know is that the BLACK BOX will supply some data.
>
>Using the first approach, you can easily change the KIND of DATABASE (Access
>or SQL Server or Oracle or MySQL or...) and the Names of that DataBAse,
as
>long as the Requested data can in fact be supplied from THAT Database, without
>having to change ANYTHING about the user of your class.
>
>This is a much more flexible solution, in general...
>
>Arthur Wood
>
>Arthur Wood
>
>
>"Sandoval Gonzalez" <xxxyyy@zzzzz.com> wrote:
>>
>>I am writing a class that will encapsulate all the interaction with my
db.
>> I need to have methods that will return recordsets. My question is, which
>>is better:
>>
>>
>>A) Put the connection object within the class so that-
>>
>>In the app:
>>
>> Set rst = gdb.rsSomeRecordset()
>> ...
>> do something with it
>> ...
>>
>> rst.Close
>> Set rst = Nothing
>>
>>
>>In the class:
>>Private mcnn As ADODB.Connection
>>
>>Public Function rsSomeRecordset()
>> Dim rst As ADODB.Recordset
>>
>> Set rst = New ADODB.Recordset
>> msSQL = "select ... from ... where ...;"
>> rst.Open msSQL, mcnn, adOpenStatic, adLockReadOnly
>> set rst.ActiveConnection = Nothing
>>
>> Set rsSomeRecordset = rst
>>End Function
>>
>>
>>
>>B) Have the connection object external from the class, and pass it in as
>>a parameter:
>>
>>In the app:
>>
>> Set rst = gdb.rsSomeRecordset(mcnn)
>> ...
>> do something with it
>> ...
>>
>> rst.Close
>> Set rst = Nothing
>>
>>
>>In the class:
>>
>>Public Function rsSomeRecordset(cnn As ADODB.Connection)
>> Dim rst As ADODB.Recordset
>>
>> Set rst = New ADODB.Recordset
>> msSQL = "select ... from ... where ...;"
>> rst.Open msSQL, cnn, adOpenStatic, adLockReadOnly
>> set rst.ActiveConnection = Nothing
>>
>> Set rsSomeRecordset = rst
>>End Function
>>
>>
>>Is there any difference?
>>
>>tia,
>>Sandoval
>

[Sandoval Gonzalez]

"Willy Van den Driessche" <Willy.Van.denDriessche@skynet.be> wrote:
>I agree with Arthur but have some additional comments.
>
>You should only consider passing in the connection if your application needs
>it. P.e. if you app accesses data with the same structure from multiple
>databases during one run.
>I you need that, then you could just sep up your application to do both
>things. Have a property Connection on the class that is auto-created if
not
>assigned by the user.
>
>A general remark : don't fall into the trap of analysis paralysis. Don't
>try to design your class for the unforeseeable future. If you need
>something in the future, you'll add it then. Don't torture yourself into
>foreseeing it all. Nobody can and the designs that do it are overly complex
>and only allow variations along several dimensions.
>
>For your specific problem, I would use a singleton (roughly a encapsulated
>global variable) for the connection. Chances are high this connection will
>be shared between several database classes, so it's best to keep it around.
>The initialization of this singleton should be either explicit (by setting
>it at startup) or implicit (by reading the required info from some
>configuration file or so). Anyway, you database class uses the singleton
>and the code that uses your database class never needs to care about it.
>--
>Van den Driessche Willy
>For a work in progress :
>http://users.skynet.be/wvdd2/index.html
>
>

Thanks Willy. So by singleton, you mean that the connection object is declared
at the module level?

[Willy Van den Driessche]

For my application it would mean a module-level variable.
I'll shortly explain what I mean. Modules-level variables are stored in
thread-local-storage (TLS). This means no more or less than that there is a
value per thread. If your application is single-threaded (like mine for
exactly this reason) then this is enough to provide a singleton in VB.
Regardless of this, I would always encapsulate the "global variable" in a
public module function so that I can do additional things in this function
(like logging when it's accessed).
For multi-threaded applications, VB has to resort to using the ROT (Running
Object Table). I've never used this technique but I'm very convinced that
it works because it is described in Matthew Curland's book (www.powervb.com)

I hope this helps.
--
Van den Driessche Willy
For a work in progress :
http://users.skynet.be/wvdd2/index.html