Microsoft Data Access Technologies

for RuBoard

If you're a developer who's been working on the Microsoft platform for some time, you're probably painfully aware of the succession of data access technologies that have come down the pike. Although my intent is not to bring up any painful memories regarding conversions or compatibility, I do think it's important to revisit this past to see clearly where we're heading with ADO.NET and why it's important that we do so.

The Long Road to ADO.NET

In many respects, the data access libraries that have been issued from Redmond have followed the architectural trends in software development of the last decade . As the 1990s began , Microsoft released its version of SQL Server (a similar version based on the same code base was released by Sybase), and in doing so, it officially jumped into the world of client/server computing. As a result, Microsoft needed a way for Visual Basic and C developers to make calls to SQL Server and return result sets and other interesting things you can do with a relational database. To fill this gap, a library of code (actually a DLL) referred to as DB-Library was released. Later, the functionality of DB-Library was incorporated into a Visual Basic Custom Control (VBX) called VBSQL that allowed easier access for Visual Basic users.

Figure 1.1. A timeline of important events in Microsoft's data access story. Note how the data access technologies have tracked with changes in the software development paradigms .

On a parallel track, Microsoft had developed a relational database product, called Access, for desktop and workgroup use. VB developers were clamoring for desktop data access, so in order for VB developers to programmatically manipulate Access databases, an API had to be developed. Data Access Objects (DAO) was released with VB 3.0 in 1993 and proved to be very popular. However, it was designed first and foremost as an API to use with the Access database engine (Jet), so its programming model included objects designed for manipulating Jet tables and queries directly through the TableDef and QueryDef objects, respectively. Results were returned using a Recordset object that could be populated in different ways depending on its initialization.

Data for the Masses

Although DB-Library allowed access to SQL Server and DAO was useful for Jet, the promise of client/server computing relied on interoperability ”the ability to develop code that could be easily ported to run against a variety of relational database systems. This meant not having to use a different library and rewrite all your data access code. To address this issue, in the early 1990s, Microsoft was working to create the Open Database Connectivity (ODBC) standard. This standard incorporated Structured Query Language (SQL) and is based on, and is actually a superset of, the Call-Level Interface (CLI) specifications from X/Open and ISO/IEC. In addition, ODBC adheres to Microsoft's Windows Open Services Architecture (WOSA) model, which dictates that the API exposed to developers in a client application be held constant while the functionality that differs must be abstracted into vendor-specific drivers.

Note

Although sometimes confused as to its name (it's referred to as both Windows Open Systems Architecture and Windows Open Standards Architecture in Microsoft documents), Microsoft has used the WOSA design pattern successfully in a whole host of scenarios including messaging (MAPI), telephony (TAPI), networking (Windows Sockets library), and universal data access (OLE DB).

ODBC obviously became the de facto standard for relational database access and has now gone through several major releases, each adding additional functionality, such as a cursor library supporting scrollable cursors and connection pooling. In addition, ODBC drivers can now be found for scores of data sources. The success of ODBC meant that both Access and DAO gained the ability to connect to ODBC data sources and so, for VB developers, DAO could be used as the common data access API for both Jet and other data sources. Of course, ODBC also exposed its API through DLL functions, so C developers, as well as intrepid VB developers, could use its functionality directly.

Although DAO could be used to access an ODBC data source, doing so often incurred extra overhead that decreased performance. Also, its object model was more complex than necessary for ODBC applications. To address these issues, Microsoft released Remote Data Objects (RDO) with VB 5.0 in 1996. RDO is mostly a lightweight object wrapper around the ODBC APIs. This resulted in increased performance while simplifying the object model for developers. In addition, as the name implies, RDO introduced the concept of disconnected or disassociated result sets through the use of its Client Batch Cursor Library. This allowed an rdoResultset object to be populated and then disassociated from a connection object so that the client application was free to make changes and then send them to the server at a later time.

Go Connectionless

Even though RDO supported disassociated result sets, up to this point the data access technologies were really built around the paradigm that the application would run in a continuously connected LAN environment and further, that the data access model was two- tier . In other words, the code that implemented the user interface (UI) would be making calls directly to the database and processing the results. This was central to client/server computing architectures using products such as PowerBuilder.

Two developments in the industry upset this apple cart: the adoption of multi-tiered architectures to create distributed applications and the use of the Internet for database-driven applications, both of which had gained popularity by 1995. Together, these events required that a new data access model be developed that was better designed to handle these new architectures.

In response, in 1996, Microsoft introduced its Universal Data Access (UDA) strategy that at its core consists of a series of COM interfaces dubbed OLE DB. These interfaces allow developers to build data providers that flexibly represent data that is stored in various formats, and use service components to manipulate data through cursor and sorting engines. Although the familiar WOSA concept is utilized, the major difference between OLE DB and ODBC is found in the flexibility of using OLE DB to access more than simply tabular data from relational databases.

For example, Microsoft created OLE DB providers for non-tabular data sources such as Microsoft Index Server, Active Directory Services Interface (ADSI), and Microsoft OLAP Services. In addition, OLE DB can be used to connect to ODBC data sources through a special OLE DB provider called MSDASQL. As with the DLL interface of ODBC, the COM-based OLE DB model is great for C/C++ programmers, but because it requires manipulation of interface pointers, VB programmers can't access it directly. This is where ADO ”originally from the term ActiveX Data Objects, which has since been dropped ”comes in. ADO was developed as an object interface to OLE DB accessible by automation clients , so ASP and VB developers could use OLE DB through ADO to communicate with data providers.

Because scripting clients could not otherwise use OLE DB, the first release of ADO coincided with the introduction of Visual InterDev (VID) in 1996 for building Web-based applications using Active Server Pages (ASP) on IIS 3.0. ADO consists of a very simple and non-hierarchical object model that provided increased performance and made it easy to build data-driven Web sites with ASP. ADO, through a special OLE DB provider called the Data Shape Provider, also provided rudimentary support for hierarchical data.

Middleware to the Rescue

Although ADO allowed simple ASP Web sites to access data, it didn't initially support building multi-tiered distributed applications. At the same time, Microsoft released the first component-oriented middleware product with the unfortunate name of Microsoft Transaction Server (MTS). Simply put, MTS provided an environment complete with interception, activation, and threading support in which COM components could be hosted. It also allowed the components to participate in distributed transactions using resource managers such as ODBC and OLE DB and using a two-phase commit protocol. Utilizing MTS meant that components accessing data could reside in isolated processes and even remote machines. This architecture furthered the need for a good way to move ADO Recordset objects between tiers of the application.

The need to move data between physical tiers was addressed with the introduction of the disconnected Recordset in ADO 2.0 in 1998. Originally, Microsoft introduced the technology as a part of Active Data Services (ADS), renamed to Remote Data Services (RDS) for use in Internet Explorer (MSIE). The idea was that a Recordset could be created on the Web server and downloaded to MSIE, where it was cached and manipulated on the client. The success of this technique was limited because of the obvious problem of moving away from browser independence (the Internet's key strength). In addition, the client had to have the correct versions of the various DLLs. It turned out that the major usefulness for disconnected record sets as implemented in ADO 2.0 was to move data between components residing in MTS (and later COM+) and ASP pages that manipulated the data for display on the page. This enabled data access code to be truly separate from presentation code. In addition, it provided a standard, albeit proprietary, way to move data without resorting to manually parsing the data for insertion in custom data structures or simply using the GetRows method of the Recordset object to stream the data to a Variant array.

As OLE DB and ADO went on to supplant ODBC and RDO as Microsoft's flagship data access technologies, new versions with features such as asynchronous programming, events, a more sophisticated cursor library, advanced searching, and custom control of updates were introduced. Typically, the new releases would ship with new products, such as when ADO 2.0 shipped with VB 6.0 and ADO 2.1 shipped with SQL Server 7.0 and Office 2000.

Note

For more information on ADO and how to use it in various scenarios, see Chapter 14 of my book Pure Visual Basic, published by Sams.

At this point, it became apparent that the collection of data access technologies and their various versions had become interdependent and taken on a life of their own. Microsoft responded by creating a separate group and distribution point for the Jet, ODBC, ADO, and OLE DB software (although Jet software and the desktop ODBC drivers were removed starting with MDAC 2.6). All the data access software is now bundled in the Microsoft Data Access Components (MDAC) SDK and can be downloaded in installation packages from www.microsoft.com/data. As of this writing, the current release is version 2.7 and was installed with Windows XP.

Note

As discussed shortly, ADO.NET actually requires MDAC 2.6 or later to be installed because use of both the SQL Server .NET Data Provider and the OLE DB .NET Data Provider require it.

The Web Grows Up

As the Internet protocols TCP/IP, HTTP, and HTML became ubiquitous, the breadth of applications that were targeted for the Web increased. In addition to basic "brochure ware" Web sites, organizations began using the Web for selling products, as typified by high-profile e-commerce sites such as Amazon.com. As bandwidth increased, other organizations soon recognized that business transactions could be conducted over the Web as well. Unfortunately, there were few common data exchange formats and none that spanned more than a single industry. The need for efficiently exchanging data over the Web led to the development of the eXtensible Markup Language (XML) 1.0 specification by the World Wide Web Consortium (W3C) and its release as a recommendation in February 1998. Quickly, vendors ”with Microsoft leading the charge ”began adopting XML and incorporating it into their products.

Unlike HTML, another markup language derived from SGML, the key feature of XML is, of course, the fact that it's a self-describing , structured document that's strictly parsed and can be validated against a schema to ensure that it's both syntactically and semantically correct.

Note

For a good overview of XML and its related specifications, see the book Sams Teach Yourself XML in 21 Days, Second Edition by Devan Shepherd.

Although ADO incorporated some basic support for XML in ADO 2.5 with the ability to save a Recordset as XML to a file or COM object that implemented the IStream interface, ADO was obviously not designed from the ground up to work with XML. For example, when saving an ADO Recordset to XML, it always used the same schema. Developers would have to spend development time (and CPU cycles) coding transformations using eXtensible Stylesheet Language Transformation (XSLT). Clearly, as XML and its related specifications (XSD, XSLT, XPath, among others) became the primary way to describe and exchange data over the Web, Microsoft's data access technologies would have to evolve .

The XML Embrace

It cannot be said strongly enough that Microsoft has gotten the XML religion and has been the clear leader in adopting XML in its products, as well as working with the World Wide Web Consortium (W3C), an industry organization whose over 500 member organizations work to create standard protocols and specifications for use on the Internet, to further define XML-based standards.

First, Microsoft has been active in integrating XML into its products. For example, the XML support added to SQL Server 2000 allows XML documents to be created using a FOR XML statement. In addition, support for querying XML data using XML templates via http is available. XML has found its way into products as wide ranging as Office and BizTalk Server 2000, and even Bill Gates noted at the Microsoft PDC in 2001 that XML is the core technology that Microsoft has rallied around to build the next generation of all its products.

Another primary example, of course, is the Simple Object Access Protocol (SOAP) specification. This specification was originally jointly developed by Microsoft, DevelopMentor, and UserLand Software and subsequently led to collaboration with IBM, Hewlett-Packard, and others. Version 1.1 was submitted to the W3C (www.w3c.org) in May 2000. Visual Studio .NET (VS .NET) and the .NET Framework are testaments to Microsoft's commitment to XML because they are steeped in XML and SOAP 1.1 from their very foundations. Everything from configuration files, to data access, to XML Web Services, to classes used to manipulate XML documents, to the serialization format of objects within the Common Language Runtime (CLR) relies on XML, SOAP 1.1, and an XML specification for describing XML Web Services called Web Service Description Language (WSDL), pronounced "wiz-dull." In addition, Microsoft has recently worked with IBM to develop the next set of SOAP-based standards for adding enterprise features such as reliable messaging, routing, security, and inspection to the protocol under the umbrella term Global XML Web Service Architecture.

Finally, Microsoft is embracing XML in its .NET "My Services" initiative designed to unify disparate silos of information and make them securely accessible via Web protocols. Basically, to developers, the interaction with .NET My Services takes the form of sending and receiving XML documents to do things such as send an alert notification to a user that his or her flight is going to be delayed.

Certainly, in all these ways, Microsoft has been ahead of the curve, so it's no surprise that from the ground up, ADO.NET is built around XML.

The final twist in this road is the idea of using the Internet as the platform for application development. This is the thrust of Microsoft's .NET strategy, which was first announced in June 2000. The core of this strategy is to build products and services for a "programmable Web" where information and services are exchanged not just by human interaction through browsers, but also programmatically through software. Because XML's strength is its self-describing nature, it became the obvious technology substrate upon which to build the programmable Web. For developers, the end result is exposing functionality over the Web programmatically through the use of the XML grammar SOAP. Visual Studio .NET and the .NET Framework fully support this new paradigm by providing tools to create and consume XML Web Services. Of course, all these new XML Web Services will need to interoperate with backend data stores. This is where ADO.NET comes in.

So, this is the landscape from which ADO.NET arose. There was a clear need for a data access technology that deeply integrated XML standards and provided the scalability to satisfy an increasing demand for Web-based applications of all kinds, including XML-based data exchange and XML Web Services.

for RuBoard