Pro vs. Con: Encapsulate ADO

[Dave Sell]

This is a continuation from another thread that got off-topic so I'd like to fire it up in here:

Originally posted by shunt

It is pointless trying to encapsulate ADO. Ask yourself: Why do I want to encapsulate ADO?
Actually tell me why you want to encapsulate ADO and I will convince you not to. ADO is the perfect adapter for database access... If anyone could make it any better, M$ would have payed them out ages ago. USE ADO, AS IS!

I agree, ADO follows its design pattern perfectly, but I disagree that the design pattern is complete.

As an adapter, ADO hides complexities from the consumer in a good albeit general way. Since it cannot hide all the complexities of your database - the specific design of your database (table names, field names - blah blah blah), some complexity leaks back into the client application, making it unnecessarily messy. (Ever seen what 100 different configurable SELECT statements looks like?

This is why I would want to encapsulate the ADO object - to contain all that mess. I guess what my goal is - is to write applications that have access to databases, but do not ever have to write SQL statements of any kind. Instead I would want a binary object to act as another Adapter interface between my application and ADO.

[TalonSoftware]

While ADO is quite good.... Encapsulating it is perfectly acceptable - provided you are doing it for the right reasons.

Example. I found myself copying & pasting the same code from project to project, code that all it did was to open a DB connection and close it. That was my first level of encapsulation. Now, following the design used in the Data Access Application Block (see MSDN) for .NET, I ceated a class that connects to a db, runs an SP or SQL statement passed to it, and returns the results if any. Now I include this into any project I'm working on, and I instantly have a component that does my data access for me, comeplete with error handling. All I do now is create the object, create a collection of parameters for the SP, then invoke the RunSP command, passing in the connecitonstring, SP name, collection of paramters, and a flag indicating if there will be a recordset returned or not. And that's it. Access the DB now has just become a black box. It works great, and I use it every chance I get.

I even went so far as to extend the RunSP into RunSPExtend, where an additional paramter of a multi-dimentional array can be passed in. Then the SP will get run for each row found in the array. Fast and easy as well as consistent. I knwo all my connections and commands are going to be handled the same way everytime.

[TalonSoftware]

I jsut took a different light to that post. What we've done here, and I try to do it every chance I get, for each user interface, there's a data object ot go with it. It's responsible for marshialing the data between the source (usualy the DB) and the UI. The UI never sees the light of SQL, it's all in the data access (DA) component. The encapsulated object in my last post then sits between the DA and the DB.

Just thought I'd clarify that.

[Dave Sell]

That sounds exactly like what I am talking about making. Sounds like you love it, I'm not surprized

[shunt]

For me, I have written a single data service component. In this component is a system that can return the data for any object using a set of dynamic classes and making use of reflection. All I have to do is program my actual business objects. I never have to worry about loading or saving another object.... EVER. I have a config file that sets up the database to connect to, and the data service connects and handles transactions as needed.

my code has become:

dim c as OOCriteria = new OOCriteria([object type to load], [property], [value])
dim obj as bo = server.load(OOCriteria)

msgbox obj.name

--------------------------------

My days of worrying about how to write to a db are over!

I only need to write a reader/writer when the db is designed weird and need some specific rules to write data to the right places. And even this I have got as an adapter for every business object.

The point is:
There is no point in encapsulating a connection class with another class with a similar name that does the same thing, just with less parameters. Inevitably, you are going to need those extras and are going to have to break something to be able to use them.

Rather write the data access components for your application to return the data and data types for THAT specific application!

I had the fortune of having to swap my application between Oracle, SQL Server, Access and Sybase. I found (what I beleive to be) the best way to implement an adapter for saving and loading objects.

For that matter, I even use the same infrastructure for exporting/importing data! And this is also great functionality!

Dont you guys want to post a class or two to explain your position?
Instead of us arguing this point, lets work on it together.... I think its a point that everyone always battles with, and its actually something we should have down ages ago! Admittedly, there are still some aspects which are dodgy about my approach. Mainly being the amount of memory that is being consumed (but I think thats just because of the way I implemented it. I still think the design is right)!

Unfortunately I dont have time to put it together right now, but I will post back again asap with some classes and explanation. Then you can give feedback on my approach and maybe make this into something constructive.

In the mean time.... Does anyone have some classes they would like to put forward?