I had another question in the newsgroups about converting existing VB6 applications to VB.NET. We had already discussed the fact that ADO and ADO.NET are about as similar as bicycles and motorcycles. The thread progressed to the question of creating a non-targeted application that would work against a variety of back-end databases like SQL Server, MySQL and FoxPro.
Here's my answer...
Okay, okay... let's add some clarity here.
First let's define some terms. COM means you build code that depends on external COM components that might be replaced (often arbitrarily) AFTER your application has been deployed--even before it runs for the first time. IMHO COM is a big problem in many environments.
OLE DB is a one-size-fits-all (OSFA) set of providers that can access anything from a relational database to a tuna salad (unless it has dill pickles). The spec is as complicated as a pork-filled bill sent out of the House. OLE DB is based on COM (see above). In the past, we used OLE DB to access ODBC (the Kagera bridge) to get to any DBMS that supports ODBC or OLE DB providers to access the DBMS “directly”. Today, you can/should use the SNAC OLE DB provider to access SQL Server--but it's still COM. Each layer in the data access interface (the “stack”) slows down the operations as well as opens the application for issues caused when one or more layers are “improved”. This is what we call “DLL hell” (or “DLL that bad, hot place” in parts of Minnesota).
A telephone is an example of an OSFA provider. However, just because you can dial (or push buttons), it does not mean you can order pizza from that guy in Peru that does not speak English. In a similar way, just because you can connect to MySQL, Paradox, Oracle or FarkleStar databases from an OLE DB or other OSFA provider does not mean your code or application can interface with these other back-ends. Each has its own unique characteristics, features, weaknesses and strengths. One might support stored procedures, another not. One might support BLOBs or enough capacity to store images in the database, another might not. Some are designed for multi-user architectures, others struggle to support a single user. The differences in administration, performance, capacity, compatibility and everything else make writing an OSFA application of your own a living, breathing nightmare. Yes, it's been done but is the application competitive? It often takes complex multi-tiered architectures that dereference the actual data access interfaces and implementations. Possible? Sure--got a PhD in computer science or a dozen year experience? Got someone who'll pay for this beast?
.NET (and ADO.NET) approaches this problem differently. Instead of depending (just) on OSFA providers it has implemented “managed” interfaces built into the .NET Framework or added on by third-party providers (as those supplied by DirectConnect or Oracle). These interfaces built to eliminate DLL hell issues, talk to the DBMS using its native tongue—like TDS in the case of SQL Server. This makes a (far) faster interface that’s only dependent on the ability of the server to understand its native language—which is not usually a problem.
Accessing COM-based ADO via OLEDB (or the OleDb namespace in .NET) is problematic. Doing so from a .NET application is an order of magnitude more problematic. Yes, the application code attempting to reach the ADO COM classes has to traverse the .NET to COM interop layer which does not map the entire functionality you use to access ADO COM objects in VB6. In my book “ADO and ADO.NET Examples and Best Practices” documented a half-dozen cases where the COM interop failed to return the same results or was even accepted in .NET code--the only reason some of these applications worked was "side-effect" or unintended functionality exposed by the COM objects created in VB6. In other words, it only worked by accident.
hth
Bill
Leave a comment