Post by Simon WoodsI've had a quick trawl over Google etc looking for the above
(hopefully open source) but have had no success.
Does anyone here know of an OR mapping component I could
use in a vb6 project?
If not, what are some of the strategies (patterns?) used (by vb6'ers)
to decouple the DB/Data layer from the business tier?
OR-mappers are only one way, to decouple from the
DB/Data-layer - and their usage (also with regards to performance)
depends on the ability of a given language, to provide:
- something like a "reflection-API", to be able to map
Object-interface-definitions (Properties) to appropriate
DB-Table-Fields (possible in VB6 per Eduardo Morcillos
olelib.tlb or alternatively per tlbinf32.dll)
- ability, to "model" Object-Hierarchies, including (enumerable)
Parent-Child-relations (possible in VB6 over Collection-Classes
and their IEnumVariant-interfaces).
- but finally, you need to ensure especially the last requirement with
a good performance, even if the total Class-instance-Count in such
an Object-Hierarchy reaches hundreds of thousands (or even
Millions) of such ObjectInstances - and that is where VB6
falls short and why you will have difficulties, to find such ORMs
for the VB-Classic-language.
ORMs are discussable nonetheless, even when offered for
languages, which have a lesser "ClassInstance-footprint".
Their memory-resource-usage is usually much higher, compared
with the more direct usage of the usual "first-level-abstraction-
layers" - and in case of VB5/6 that is ADO (or the somewhat older
DAO per ODBC).
You can achieve a nice (and performant) DB-Backend-decoupling
also without ORMs.
So your question boils down to:
What degree of abstraction from your DB-Layer do you really
want to achieve?
Do you want to abstract only from certain DB-Backends (then
you could use ADO, which already tries to achieve that goal
to a certain, often satisfying degree) - or do you want to
abstract also from that primary, objectbased abstraction-layer
ADO already is (copying over all the ADO-Rs-content then in
an additional step into "plain Class-Structures of your language")?
If you say, the latter option would be nice to have too, but you
fear the performance-degradation-effects ... - then you could
try to implement such a "secondary abstraction-layer" in a "thin
way", wrapping only the ADO-resultset-containers (the Recordsets)
in "an ADO-Rs like behaving class" (without the need to copy over
from a "concrete, internally hosted Rs", only an internal *mapping*
to the current internal RsContainers-Properties/Methods would
need to be performed).
Same thing then regarding small wrappers around the usual
"Connection"- and "Command"-Objects (although these two
could remain "hidden" already within your DL-class).
If you give some thoughts into these (3 Classes) interfaces:
IMyConnection, IMyCommand, IMyRecordset - your first
implementation of them then able, to work with ADO and compiled
finally into a small ActiveX-Dll-Component, then all what your real
Applications will have to reference, are only these (your own)
"thin" interfaces.
Easy then, to reimplement the functionality of these 3
interfaces, to cover other "first layer DB-access Objects" ...
for example it would not take all that long, to map
from IMyConnection, IMyCommand or IMyRecordset
to "cConnection, cCommand and cRecordset" as the
"first layer DB-access-Objects", provided by my SQLite-
wrapper - or to map to the appropriate "primary
DB-access-Objects" of the .NET-world (ADO.NET).
But as said - in case the DB-Backend-abstraction which ADO
already offers is (or "always will be") enough for you, then
you should not resist, to use these (in the meantime) very
stable and reliable ADO-interfaces also at the clientside,
within your "business-objects" directly (at least the Recordset).
ADO-Rs are only nice array-like containers, which in such
layered approaches could(or should) be used disconnected,
able to be retrieved (populated) at the serverside directly
(and very fast) from the DB-Backend and then transported
to the clientside in a "serialized way" from an AppServer-instance
(as COM+ for example) - but also a direct clientside fill from
a dedicated SQL-Server-instance is possible (followed by
Set Rs.ActiveConnection = Nothing) - not to mention local
Rs-FillUp directly from a JET-engine - or even from *.csv-
Files - or directly populated within your own loops from
"normal VB-Arrays" - in either case you will end up with
a nice Data-Container - which can perform local Sorts,
Filter- and Find-operations - and stores its internal Data-
Content in a relative efficient way.
There are "purist-recommendations" of course, which state,
that you should not have any Project-references to ADO in
your business-layer- and GUI-classes - and that passing around
(and using) e.g. Recordsets is a tabu in such implementations.
That is IMO a contraproductive recommendation, since
you will have to host your retrieved "DB-Table-" or "Query-
content" somewhere finally (also at the clientside) - and
a (disconnected) ADO-Rs is just an ideal transport- and
access-container in these cases - with no "copy-over efforts"
(involving the usual performance-degradations) which come
with such "pure class-representation-requirements".
So, I'd define a DataLayer-component, which does make
use of the interfaces ADODB.Connection, ADODB.Command
ADODB.Recordset (or as already said, if you want to abstract
also from ADO, it should reference IMyConnection, IMyCommand
and IMyRecordset).
And in your Business-Layer-Objects you will need a
reference to the DataLayer-Component then of course -
but also a reference to (usually only) ADODB.Recordset
(or alternatively to only IMyRecordset).
A BL-method, as for example
cCustomers.Fill
would then internally (at Class-level) need a declaration:
Private RsCustomers as ADODB.Recordset
or alternatively
Private RsCustomers as IMyRecordset
'the method-implementation then...
Public (or Friend) Sub Fill()
Set RsCustomers = DL.GetCustomers() 'just set the internal Rs
End Sub
The DataLayer-Class' .GetCustomers-method then defined:
Public Function GetCustomers() As ADODB.Recordset
or alternatively
Public Function GetCustomers() As IMyRecordset
'doing internally...
Dim SQL as String
'map the two only needed Fields already to generic Names here
SQL = "Select ID As ItemID, CustName As ItemText From Customers"
Set GetCustomers = OpenRecordset(SQL)
End Function
the used OpenRecordset-Method being the main-workhorse,
defined as a Private Function within your DL-Class:
Private Function OpenRecordset(SQL$) As ADODB.Recordset
Set OpenRecordset = oADOCnn.Execute(SQL)
End Function
or alternatively
Private Function OpenRecordset(SQL$) As IMyRecordset
Set OpenRecordset = oMyCnn.OpenRecordset(SQL)
End Function
In either case you should end up with *no* explicit
"copy-over-loops" from the retrieved Rs-Containers-
content -> into your own "single-Record-class-representations" -
whatever interface these Rs-Containers will finally have
(or are mapped-to internally in case of the more abstracting
IMyRecordset).
What I want to say finally is only - do allow such generic
DataContainers (as Recordsets already are) in your Business-
Classes and also in your GUI - you will end up with much
better performance - and nonetheless achieve a decoupling
from your DB-Backends (already doable with plain ADO) -
and in case you want to decouple also from ADO, the outlined
additional "thin decoupling" with IMyRecordset, etc. will also
solve *that* problem.
If you have to fill up a List in your GUI from cCustomers
(or any other BL-Class) - you should also make use of
that BL-Class-internal, generic Rs-Datacontainer.
'in your GUI-code you can use a Public generic function then
Public Sub PopulateList(LB as ListBox,
BLDataContainer As ADODB.Recordset)
or alternatively
Public Sub PopulateList(LB as ListBox,
BLDataContainer As IMyRecordset)
'full implementation of that generic ListBox-Function for ADO:
Public Sub PopulateList(LB as ListBox,
BLDataContainer As ADODB.Recordset)
Dim FldItemText as ADODB.Field, FldItemID as ADODB.Field
LB.Clear
With BLDataContainer
.MoveFirst
Set FldItemText = .Fields("ItemText")
Set FldItemID = .Fields("ItemID")
Do Until .EOF
LB.AddItem FldItemText.Value
LB.ItemData(LB.NewIndex) = FldItemID.Value
.MoveNext
Loop
End With
End Sub
usage in your GUI (your Forms) then, to visualize all Customers in
oCustomers:
PopulateList lstCustomers, oCustomers.GetDataContainer
or the same thing, to fill up e.g. Order-Details for a given
(single) Customer into another ListBox-Control (from a
Click into the Customers-ListBox)...
Private Sub lstCustomers_Click()
Dim oCustomer as cCustomer
With lstCustomers
'create a single Customer-instance from oCustomers
Set oCustomer = oCustomers.GetCustomer(.ItemData(.ListIndex))
'now populate the Order-Details listbox for this specific Customer
'using the same direct Recordset-Container-passing-mechanism
PopulateList lstCustomerOrders, oCustomer.GetOrdersContainer
End With
'in case you want to keep the currently selected Customer-instance alive
Set CurrentCustomer = oCustomer
End Sub
All these relative few lines of code are only possible, in case you
use and pass around a generic usable container, which offers a
flexible and powerful interface - referenced and used within *all*
your layer-Classes: DL - BL - and also the GUI.
And the best thing for these transport and interacting-purposes is
without any doubt a Recordset, used either directly (per ADO) - or
already "hidden" behind a thin IMyRecordset-interface.
Just my 0.02$...
Olaf