What is MDAC\WDAC?
MDAC – Microsoft Data Access Components. We call it WDAC also i.e. Windows Data Access Components.
This is a set of various components that enable your application to access any kind of data and make use of it. This data can be from Sql Server, Oracle or any other RDBMS. Not only RDBMS but the source can be any non RDBMS also like xml files, documents etc.
MDAC is not a single application or component, there are multiple libraries involved to make the set. Major components which we are going to discuss in this article are:
MDAC architecture may be viewed as three layers:
- A programming interface layer: Consisting of ADO and ADO.NET
- A database access layer: Developed by database vendors such as Oracle and Microsoft (OLE DB, .NET managed providers and ODBC drivers)
- The database itself.
These component layers are all made available to applications through the MDAC API.
The latest version of MDAC (2.8) consists of several interacting components, all of which are Windows specific except for ODBC (which is available on several platforms).
Let’s discuss these components.
The ODBC interface is an industry standard and a component of Microsoft Windows Open Services Architecture (WOSA). The ODBC interface makes it possible for applications to access data from a variety of database management systems (DBMS). ODBC permits maximum interoperability as application can access data in diverse DBMS through a single interface. Furthermore, that application will be independent of any DBMS from which it accesses data. Users of the application can add software components called drivers, which create an interface between an application and a specific DBMS.
Where to Use
An ODBC driver uses the Open Database Connectivity (ODBC) interface by Microsoft that allows applications to access data in database management systems (DBMS) using SQL as a standard for accessing the data.
As mentioned ODBC is an open standard to access Sql based data. So this is typically used in RDBMS only. If we want to access data from other sources which are not RDMS then we need to think of some other solution.
This is the place where OLEDB comes into picture.
As we know that ODBC was specifically meant for SQL databases. Due to this limitation of ODBC, OLEDB came into picture. OLEDB providers are able to fetch data from other data sources also.
OLE DB is an open specification designed to build on the success of ODBC by providing an open standard for accessing all kinds of data.
Where to Use
Whereas ODBC was created to access relational databases, OLE DB is designed for relational and non-relational information sources, including mainframe ISAM/VSAM and hierarchical databases; e-mail and file system stores; text, graphical, and geographical data; custom business objects; and more.
If a database supports ODBC and that database is on a server that don’t support OLE then ODBC is your best choice.
Non-SQL environment: ODBC is designed to work with SQL. If you have non-SQL environment then OLE-DB is better choice.
However there is no issues in OLEDB at all if you are using it with native code i.e. VB6, C etc. In these cases it will work fine.
But if you are planning to use it somehow with managed code then there will be some underlying plumbing which will convert these managed calls to unmanaged code. This plumbing is going to impact your system. But this is something which we can’t consider limitation of OLEDB.
ADO is the strategic application programming interface (API) to data and information. It provides consistent, high-performance access to data and supports a variety of development needs, including the creation of front-end database clients and middle-tier business objects that use applications, tools, languages, or Internet browsers.
ADO is designed to be the one data interface needed for single and multi-tier client/server and Web-based data-driven solution development. The primary benefits of ADO are ease of use, high speed, low memory overhead, and a small disk footprint.
ADO provides an easy-to-use interface to OLE DB, which provides the underlying access to data. ADO is implemented for minimal network traffic in key scenarios, and a minimal number of layers between the front end and data source-all to provide a lightweight, high-performance interface.
Where to Use
ADO is easy to use because it uses a familiar metaphor-the COM automation interface, available from all leading Rapid Application Development (RAD) tools, database tools, and languages on the market today. It’s a nice wrapper for OLDDB.
ADO Performance Advantages: As with OLE DB, ADO is designed for high performance. To achieve this, it reduces the amount of solution code developers must write by “flattening” the coding model.
The programmer can create a recordset in code and be ready to retrieve results by setting two properties, then execute a single method to run the query and populate the recordset with results. The ADO approach dramatically decreases the amount and complexity of code that needs to be written by the programmer.
ADO is based on COM Technology and it used OLEDB data provider for accessing data. It has a limited number of data types which are defined by the COM standard.
ADO works with connected data architecture. That means, when you access the data from data source, such as viewing or updating data, ADO recordset is keeping connection with the data source.
ADO can’t be integrated with XML because ADO have limited access of XML.
In ADO, You can create only Client side cursor.
Using a single connection instance, ADO cannot handle multiple transactions.
ADO.NET is a data access technology from the Microsoft .NET Framework that provides communication between relational and non-relational systems through a common set of components. ADO.NET is a set of computer software components that programmers can use to access data and data services from a database.
ADO.NET provides consistent access to data sources such as SQL Server and XML, and to data sources exposed through OLE DB and ODBC. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, handle, and update the data that they contain.
Where to Use
In every managed code where we want to access data, we should use Ado.Net. It hides underlying implementations and provide with a cleaner model to access and manipulate data.
It is specifically for .net managed environment.
Below are few differences between ADO and ADO.Net
From above picture we can see that for every data access, ODBC and OLEDB are the core components which serve their specific purpose. Both components are equally important and required. However ADO.net have many benefits over ADO but it doesn’t mean that ADO is completely eliminated. ADO is still com based and should be used by com based application.
SNAC is also part of data access component which we’ve covered here