Wrapping DAO, for better or worse

[Sean Kirkpatrick]

I have a very ugly problem and I need some sincere guidance.

My legacy VB6 application depends heavily on DAO, especially as it
relates to custom properties on database objects. These custom
properties are, as I understand it, not avabilable with SQL Server,
which we need to migrate to in the not too distant future.

My boss, the owner of the company, requires that we provide for a
transition plan that minimizes (he really wants none) code changes to
the existing app (which is in use by over 8000 clients). It is also
required that we provide 100% backward compatibility with all of the
existing installed base, so we will never be able to abandon support for
Jet DBs. (Yes, never is a long time, but he still supports his DOS
product of 18 years ago!)

His desired solution is that we create a set of VB.Net interface classes
that presents a DAO like interface to the existing codebase. Then, we
implement two worker classes in VB.Net, one that uses DAO via Interop,
and then a few months from now, implement an ADO.Net worker class for
SQL Server that mimics the custom properties of Jet (this part is rather
straight forward, as we can do this using special tables.)

As I am exploring the topic, it seems that I'm going to have to make my
own collection classes (for the DAO recordset object, for example) and
pass those back to the app, essentially caching the DAO data. Yuck. I
can see memory usage and performance going right to hell, not to mention
the unintended consequences of failing to correctly implement some
aspect of DAO.

If I had my druthers I'd start over with a clean slate and rearchitect
this app to give it some reasonable object oriented structure and take
advantage of all that .Net has to offer - that just ain't gonna happen.

Are there any _sensible_ approaches to solving this problem?

Sean

[Michael C#]

Uggh haven't messed with DAO in a while. If you want to store custom
properties for your database objects, how about storing them in a table in
your database? You can wrap the SqlClient functionality in classes that
present the customer properties and should hide the changes from the
programmers/users pretty well. As for 100% backward compatibility, ugghh...
I would probably just provide an upgrade tool to get users of the old
product into the new century... After all, MSDE is free...

"Sean Kirkpatrick" <na**********@c ommunity.nospam > wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

I have a very ugly problem and I need some sincere guidance.

My legacy VB6 application depends heavily on DAO, especially as it relates
to custom properties on database objects. These custom properties are, as
I understand it, not avabilable with SQL Server, which we need to migrate
to in the not too distant future.

My boss, the owner of the company, requires that we provide for a
transition plan that minimizes (he really wants none) code changes to the
existing app (which is in use by over 8000 clients). It is also required
that we provide 100% backward compatibility with all of the existing
installed base, so we will never be able to abandon support for Jet DBs.
(Yes, never is a long time, but he still supports his DOS product of 18
years ago!)

His desired solution is that we create a set of VB.Net interface classes
that presents a DAO like interface to the existing codebase. Then, we
implement two worker classes in VB.Net, one that uses DAO via Interop, and
then a few months from now, implement an ADO.Net worker class for SQL
Server that mimics the custom properties of Jet (this part is rather
straight forward, as we can do this using special tables.)

As I am exploring the topic, it seems that I'm going to have to make my
own collection classes (for the DAO recordset object, for example) and
pass those back to the app, essentially caching the DAO data. Yuck. I can
see memory usage and performance going right to hell, not to mention the
unintended consequences of failing to correctly implement some aspect of
DAO.

If I had my druthers I'd start over with a clean slate and rearchitect
this app to give it some reasonable object oriented structure and take
advantage of all that .Net has to offer - that just ain't gonna happen.

Are there any _sensible_ approaches to solving this problem?

Sean

[Jay B. Harlow [MVP - Outlook]]

Sean,
| Yuck. I
| can see memory usage and performance going right to hell, not to mention
| the unintended consequences of failing to correctly implement some
| aspect of DAO.
You would be implementing simply Proxy classes, in my experience simply
Proxy classes do not have adverse effects on memory usage or performance.
Seeing as you want to work with either DAO or ADO.NET, I would recommend an
Adapter pattern. Where you have a base class that defines the interface for
the Adapter, then DAO & ADO.NET concrete classes that define the
implementation of the interface...

| If I had my druthers I'd start over with a clean slate and rearchitect
| this app to give it some reasonable object oriented structure and take
| advantage of all that .Net has to offer - that just ain't gonna happen.
You can still do this & support both DAO & SQL Server. You could define a
Domain Model & then define 2 specific Data Mappers: one Data Mapper does
SqlClient and one does DAO. (I define a base classes for my Data Mappers to
minimize coupling).

http://www.martinfowler.com/eaaCatalog/domainModel.html

http://www.martinfowler.com/eaaCatalog/dataMapper.html

If you define the base class for your Data Mappers correctly, you should be
able to introduce support for Oracle, AS/400 or any other datasource (such
as an Xml File).

In addition to/instead of the Domain Model & Data Mapper patterns you could
also use the Table Module & Table Data Gateway patterns.

http://www.martinfowler.com/eaaCatalog/tableModule.html

http://www.martinfowler.com/eaaCatal...taGateway.html
| My legacy VB6 application depends heavily on DAO, especially as it
| relates to custom properties on database objects. These custom
| properties are, as I understand it, not avabilable with SQL Server,
| which we need to migrate to in the not too distant future.
SQL Server 2000 fully support custom properties on database objects. Lookup
"Extended Properties" in the BOL.

http://www.microsoft.com/sql/techinf...properties.asp

http://msdn.microsoft.com/library/de...l/sql02a10.asp

Hope this helps
Jay
"Sean Kirkpatrick" <na**********@c ommunity.nospam > wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..
|I have a very ugly problem and I need some sincere guidance.
|
| My legacy VB6 application depends heavily on DAO, especially as it
| relates to custom properties on database objects. These custom
| properties are, as I understand it, not avabilable with SQL Server,
| which we need to migrate to in the not too distant future.
|
| My boss, the owner of the company, requires that we provide for a
| transition plan that minimizes (he really wants none) code changes to
| the existing app (which is in use by over 8000 clients). It is also
| required that we provide 100% backward compatibility with all of the
| existing installed base, so we will never be able to abandon support for
| Jet DBs. (Yes, never is a long time, but he still supports his DOS
| product of 18 years ago!)
|
| His desired solution is that we create a set of VB.Net interface classes
| that presents a DAO like interface to the existing codebase. Then, we
| implement two worker classes in VB.Net, one that uses DAO via Interop,
| and then a few months from now, implement an ADO.Net worker class for
| SQL Server that mimics the custom properties of Jet (this part is rather
| straight forward, as we can do this using special tables.)
|
| As I am exploring the topic, it seems that I'm going to have to make my
| own collection classes (for the DAO recordset object, for example) and
| pass those back to the app, essentially caching the DAO data. Yuck. I
| can see memory usage and performance going right to hell, not to mention
| the unintended consequences of failing to correctly implement some
| aspect of DAO.
|
| If I had my druthers I'd start over with a clean slate and rearchitect
| this app to give it some reasonable object oriented structure and take
| advantage of all that .Net has to offer - that just ain't gonna happen.
|
| Are there any _sensible_ approaches to solving this problem?
|
| Sean

[Paul Clement]

On Tue, 19 Jul 2005 10:49:36 -0700, Sean Kirkpatrick <na**********@c ommunity.nospam > wrote:

¤ I have a very ugly problem and I need some sincere guidance.
¤
¤ My legacy VB6 application depends heavily on DAO, especially as it
¤ relates to custom properties on database objects. These custom
¤ properties are, as I understand it, not avabilable with SQL Server,
¤ which we need to migrate to in the not too distant future.
¤
¤ My boss, the owner of the company, requires that we provide for a
¤ transition plan that minimizes (he really wants none) code changes to
¤ the existing app (which is in use by over 8000 clients). It is also
¤ required that we provide 100% backward compatibility with all of the
¤ existing installed base, so we will never be able to abandon support for
¤ Jet DBs. (Yes, never is a long time, but he still supports his DOS
¤ product of 18 years ago!)
¤
¤ His desired solution is that we create a set of VB.Net interface classes
¤ that presents a DAO like interface to the existing codebase. Then, we
¤ implement two worker classes in VB.Net, one that uses DAO via Interop,
¤ and then a few months from now, implement an ADO.Net worker class for
¤ SQL Server that mimics the custom properties of Jet (this part is rather
¤ straight forward, as we can do this using special tables.)
¤
¤ As I am exploring the topic, it seems that I'm going to have to make my
¤ own collection classes (for the DAO recordset object, for example) and
¤ pass those back to the app, essentially caching the DAO data. Yuck. I
¤ can see memory usage and performance going right to hell, not to mention
¤ the unintended consequences of failing to correctly implement some
¤ aspect of DAO.
¤
¤ If I had my druthers I'd start over with a clean slate and rearchitect
¤ this app to give it some reasonable object oriented structure and take
¤ advantage of all that .Net has to offer - that just ain't gonna happen.
¤
¤ Are there any _sensible_ approaches to solving this problem?

I see several problems with attempting to impose a DAO interface, the primary of which would be that
the architecture is sufficiently different to cause a major headache when integrating with ADO.NET
database objects or SQL Server.

While you may be able to work with an underlying DAO Recordset, it doesn't translate directly to
ADO.NET. That will be an interop stumbling block. With respect to developing a custom interface that
mimics DAO, once again, the way in which DAO was designed to function is different than ADO.NET. Not
even Microsoft would develop a native DAO interface assembly for .NET.

You also have to consider that DAO was designed to operate through a persistently connected
environment, typically with a file based database, while ADO.NET's connections are generally
available only for the duration of the database update or query. This could be very different than
your current architecture if you're using an Access database.

Personally, since you're not only migrating to another development product but also from a file
based to server database, I would abandon the notion of trying to mimic the DAO interface in .NET.
Paul
~~~~
Microsoft MVP (Visual Basic)

[Bonj]

Why don't you skip the bit involving creating a DAO wrapper, and just go
straight to the bit that involves "implement an ADO.Net worker class for
SQL Server that mimics the custom properties of Jet (this part is rather
straight forward, as we can do this using special tables.)" .. ?

This sounds like how I'd do it...

My legacy VB6 application depends heavily on DAO, especially as it
relates to custom properties on database objects. These custom
properties are, as I understand it, not avabilable with SQL Server,
which we need to migrate to in the not too distant future.

My boss, the owner of the company, requires that we provide for a
transition plan that minimizes (he really wants none) code changes to
the existing app (which is in use by over 8000 clients). It is also
required that we provide 100% backward compatibility with all of the
existing installed base, so we will never be able to abandon support for
Jet DBs. (Yes, never is a long time, but he still supports his DOS
product of 18 years ago!)

His desired solution is that we create a set of VB.Net interface classes
that presents a DAO like interface to the existing codebase. Then, we
implement two worker classes in VB.Net, one that uses DAO via Interop,
and then a few months from now, implement an ADO.Net worker class for
SQL Server that mimics the custom properties of Jet (this part is rather
straight forward, as we can do this using special tables.)

As I am exploring the topic, it seems that I'm going to have to make my
own collection classes (for the DAO recordset object, for example) and
pass those back to the app, essentially caching the DAO data. Yuck. I
can see memory usage and performance going right to hell, not to mention
the unintended consequences of failing to correctly implement some
aspect of DAO.

If I had my druthers I'd start over with a clean slate and rearchitect
this app to give it some reasonable object oriented structure and take
advantage of all that .Net has to offer - that just ain't gonna happen.

Are there any _sensible_ approaches to solving this problem?

Sean

[Sean Kirkpatrick]

Paul Clement wrote:

Personally, since you're not only migrating to another development product but also from a file
based to server database, I would abandon the notion of trying to mimic the DAO interface in .NET.

You're preaching to the choir, but this ignores the much more
fundamental problem: I do not have a choice to abandon DAO - the owner
of the company has mandated that this be done. I am directed to write
some sort of interface that looks like DAO such that under the covers, I
can substitute a different class that interacts with any old arbitrary
DB engine. This means that I am going to have to implement my own
Recordsets, Fields, Properties, and etc. so that the existing code works
without (much) change, and provide translations from the native DB
notion of these entities.

It is not pretty.

Silly? Yep. Other choices? Nope.

Sean

[Sean Kirkpatrick]

That was my initial approach, but it doesn't solve the backwards
compatibility problem: i.e., I must continue to support those 8000+
clients out there who are using Jet DBs (some of whom have written their
own custom addins to my product that likewise depend on Jet custom
properties.) Also, my boss does not want to migrate right now to to
MSDE/SQL Express, so my alternatives are severely constrained.

Sean

Bonj wrote:

Why don't you skip the bit involving creating a DAO wrapper, and just go
straight to the bit that involves "implement an ADO.Net worker class for
SQL Server that mimics the custom properties of Jet (this part is rather
straight forward, as we can do this using special tables.)" .. ?

This sounds like how I'd do it...

[Jay B. Harlow [MVP - Outlook]]

Sean,
You can use DAO via COM interop, you don't need to write any wrappers per
se. The COM interop writes all the wrappers you need...

When you are ready to implement the SQL side of things, you could (could)
simple define your own classes that implement the interfaces from DAO.

I however would take a different approach. I would decouple the current app
from DAO, encapsulating all the DAO code in my own data objects. Then when
you are ready for SQL, you would create a new set of data objects that
talked SQL Client instead of DAO.

Of course depending on how tightly the current app is coupled to DAO
creating data objects might not be a viable option.

Hope this helps
Jay

"Sean Kirkpatrick" <na**********@c ommunity.nospam > wrote in message
news:uv******** ******@TK2MSFTN GP12.phx.gbl...
| Paul Clement wrote:
|
|
| > Personally, since you're not only migrating to another development
product but also from a file
| > based to server database, I would abandon the notion of trying to mimic
the DAO interface in .NET.
| >
|
| You're preaching to the choir, but this ignores the much more
| fundamental problem: I do not have a choice to abandon DAO - the owner
| of the company has mandated that this be done. I am directed to write
| some sort of interface that looks like DAO such that under the covers, I
| can substitute a different class that interacts with any old arbitrary
| DB engine. This means that I am going to have to implement my own
| Recordsets, Fields, Properties, and etc. so that the existing code works
| without (much) change, and provide translations from the native DB
| notion of these entities.
|
| It is not pretty.
|
| Silly? Yep. Other choices? Nope.
|
| Sean

[Paul Clement]

On Wed, 20 Jul 2005 10:39:07 -0700, Sean Kirkpatrick <na**********@c ommunity.nospam > wrote:

¤ Paul Clement wrote:
¤
¤
¤ > Personally, since you're not only migrating to another development product but also from a file
¤ > based to server database, I would abandon the notion of trying to mimic the DAO interface in .NET.
¤ >
¤
¤ You're preaching to the choir, but this ignores the much more
¤ fundamental problem: I do not have a choice to abandon DAO - the owner
¤ of the company has mandated that this be done. I am directed to write
¤ some sort of interface that looks like DAO such that under the covers, I
¤ can substitute a different class that interacts with any old arbitrary
¤ DB engine. This means that I am going to have to implement my own
¤ Recordsets, Fields, Properties, and etc. so that the existing code works
¤ without (much) change, and provide translations from the native DB
¤ notion of these entities.
¤
¤ It is not pretty.
¤
¤ Silly? Yep. Other choices? Nope.
¤
¤ Sean

Not that management would understand (or even care about) the ramifications of attempting to do what
they are requesting, but I just wanted you to understand what you're in for.

It's one thing to abstract the data access method from your application so that is can be easily
changed once technology changes, but it's quite another to attempt to retrofit an old architecture
into a new one.
Paul
~~~~
Microsoft MVP (Visual Basic)

[Sean Kirkpatrick]

Hi Jay.

Any and all of this discussion helps, if only to clarify my thinking.

First of all, thanks for the reference to Fowler's book - I just went
out and picked up a copy of it and am about to dig in.

I think we're on the same page here: what I'm calling wrappers you're
calling data objects.

Let me run this past you.

It seems like I should write my own abstract class heirarchy beginning
with a CDatabase object that has an OpenRecordset call that returns a
CRecordset object. Then, I'll write write a subclass that implements
these in terms of DAO calls, and later a SQL Server subclass. Then, with
proper redeclaration of variables in my app, the essential call
structure remains the same.

This means that I have to provide my own Recordset class collection that
maps DAO records sets to my own, as well as to ADO.Net recordsets. I
don't have to (and am not yet planning on) implementing the ADO.Net map.

Another related question: It is highly likely that I will pass off the
ADO.Net mapper off to another fellow in the shop here. I want to make
sure that he implements the same interface that I define in its
entireity. This suggests that I should define a suite of Interface
classes that he must implement. However, I've discovered that there's a
few wrinkles with this in that I really want to make typesafe collection
classes for Fields, Properties, and the like.

I think an alternative is to define a set of abstract base classes with
MustOverride methods.

Are there other things that I need to consider?

Thanks for your help and insight. This has not been an easy or fun
project for me to work on - if you saw the extant code base you'd know why.

Sean
Jay B. Harlow [MVP - Outlook] wrote:

Sean,
You can use DAO via COM interop, you don't need to write any wrappers per
se. The COM interop writes all the wrappers you need...

When you are ready to implement the SQL side of things, you could (could)
simple define your own classes that implement the interfaces from DAO.

I however would take a different approach. I would decouple the current app
from DAO, encapsulating all the DAO code in my own data objects. Then when
you are ready for SQL, you would create a new set of data objects that
talked SQL Client instead of DAO.

Of course depending on how tightly the current app is coupled to DAO
creating data objects might not be a viable option.

Hope this helps
Jay