Understanding Your Toolset

Today, ADO.NET is the mechanism applications of all kindsregardless of their architectureuse to communicate with SQL Server. Yes, because Visual Studio generates much of this code for you (if you choose that route), the low-level ADO.NET commands are often hidden from view like the gears in your car's transmission. ADO.NET is certainly one of your most frequently used data access development tools. I'm convinced that if you don't know ADO.NET and how it works, your ability to design and build successful, long-lasting, and well-performing applications will be very limited. By leveraging its features and avoiding its limitations, one can more easily build these successful applications. ADO.NET is not new. It was not invented overnight (as some of the early SQL Server data access interfaces appeared to be). Its sophistication and functionality lie in not just what it does, but what it no longer does. Since many of you are coming to this book from COM-based ADO (what I call ADO classic, or ADOc), its important to know how we (you and I) got to this point.

A Brief History of ADO and ADO.NET

In the early days of SQL Server (when Ashton-Tate sold about four copies of SQL Server 1.0 circa 1989), the only way to write a program to access the server was to write a C program that used DB-Library (DBLib)SQL Server's first data access interfaceit can still be used today, but with considerable difficulty. SQL Server 2005 won't expose anything but basic functionality when accessed with DBLib. That's because Microsoft has not really kept it updated. They want you to use their new object-based interfaces and steer clear of low-level API-based function calls.

DBLib API-based functions accept parameters that build TDS^[4] packets to open a connection to SQL Server akin to today's ADO connection. Using these API calls directly from an application was always a bit trickyif something went wrong, your customer's system could "blue-screen." If your application worked, it was very fast, and since there was virtually no client-side work being done by DBLib, much of the logic was written in TSQL server-sided procedures. In years past, many folks (and I) built applications using fairly simple paradigms where the data access interface was light, fast, and relatively simple. In those days, emphasis was on server-side intelligence leveraging the power of server-side stored procedures, rules, and triggers. Many of those applications are still in use todayand so is this "client/server" architecture, albeit no longer implemented with DBLib.

^[4] TDS: Tabular Data Streamthe proprietary protocol used to communiucate with SQL Server.

Jet and ODBC Are Born

As SQL Server matured, so did Microsoft's attempt to refine and broaden the data access interfaces. Since SQL Server had virtually no market share, it was important to create an API interface to access other popular data sources in the consumer and business markets. Remember, this was before Visual Basic existed, and Quick BASIC was not seen as a "real" language, so C languagebased API interfaces were about all we had to work withat least, if we wanted to use SQL Server.

Microsoft took three paths at this point in time. First, it started the process of finding a "consumer" or small-business DBMS system to compete with Dbase, Paradox, Btrieve, FoxPro, and the like. Next, Microsoft started development of a new DBMS built on their "Omega" data engine, which would eventually become what I dubbed the "Joint Engine Technology," or "JET," ISAM^[5] DBMS. Third, Microsoft created its first one-size-fits-all (OSFA) data access interface, dubbed Open DataBase Connectivity (ODBC), based on the SQL Access Group Call Level Interface (CLI), in 1992. It was destined to be the first of many "generic" data access interfaces they would create over the next decade. ODBC used "drivers" that translated low-level ODBC API calls to the proprietary interfaces exposed by the various DBMS engines. Eventually, hundreds of ODBC drivers were written and virtually every type of relational database could be accessed from this OSFA interface. While this "translation layer" approach slowed down the interface and sometimes ignored special features, it was widely accepted as a standard interface. The problem was that the DLLs used to hook up applications to ODBC were fragile. They had to be used in matched sets, and with so many drivers floating around (sometimes many for the same DBMS), applications sometimes crashed and brought the OS down with it. Remember, this was before COM came on the scene to "save the day" (and make matters worse).

^[5] ISAM: Index Sequential Access Method. This class of DBMS is characterized by use of primary-key indexes to access data in flat files.

On the first front, Microsoft eventually bought FoxPro and immediately started to incorporate its innovative technology in its new JET engine. JET found a home in Microsoft Access and behind the scenes in Exchange (the mail engine). About the same time, ODBC gained acceptance, so the JET team tried to assimilate that technology as well. Unfortunately, it seems the JET team did not fully understand (or care about) SQL Server or large DBMS technologythe implementation left a lot to be desired. Performance was, well, terrible, and there was little support for stored procedures, security, multiple resultsets, and many other features that made SQL Server more than just a data repository.

On the language front, a developer in the SQL Server team went home one weekend and wrote his own interface to DBLib for Quick Basic that he (and I) dubbed "VBSQL." At this point in time (around 1992), I wrote the first Hitchhiker's Guide^[6] about this technology as it expanded to incorporate the new Visual Basic. Microsoft missed their chance to get a head-start on ODBC connectivity when it failed to incorporate the ODBC API into Visual Basic 2.0. When Visual Basic 3.0 was launched (it was delayed to let the JET team get their engine ready), it incorporated a crude version (by today's standards) of the JET database engine and an even more crude interface to ODBC that could access SQL Server (just barely).

^[6] Hitchhiker's Guide to VBSQL (Beta V Corporation).

Data Access Objects (DAO) Is Born

In those days, Microsoft Access and Visual Basic 3 used Data Access Objects (DAO) to access JET. DAO was developed for the Microsoft Office team, so it followed the tiered "hierarchical object" approach used in Excel. This made it less efficient, but it was the only way to access JETthere was no published JET API interface. DAO also made a pretty sad interface to SQL Server, as it barely permitted execution of stored procedures and made some fatal mistakes when the stored procedure returned more than one rowset (as they often did). In those days, DAO basically treated SQL Server as a file server.

Unfortunately, ODBC fell by the wayside as far as Visual Basic was concerned, although it was (and is) a far more efficient way to access serious DBMS systems like SQL Server and its arch rival, Oracle. This made VBSQL far more popular and helped developers adopt SQL Server as a cheaper alternative to Oracle, DB2, and Informix. Meanwhile, back in the Visual Basic group at Microsoft, Dave Sterns, Drew Fetcher, and I devised an independent plan to use ODBC to bypass JET. The idea was to access SQL Server via a more developer-friendly Visual Basiccentric data access interface, so Microsoft's Visual Basic development team implemented what became known as Remote Data Objects (RDO). According to the initial specification, RDO was supposed to be a light "wrapper" interface to ODBC. Unfortunately, it was implemented using the clumsy DAO object model (to make writing the documentation easier and less intimidating for DAO developers), so it got pretty bloated. As kludgey as it was, RDO easily outperformed DAO when accessing SQL Server, and it exposed stored procedures like never beforeeven complex resultsets where handled correctly. It even had the ability to retry timed-out commandsa feature that was never brought forward past RDO. I can't help but think that RDO helped SQL Server become a winner with many DBMS decision-makers. It made writing front-ends for SQL Server far easier, faster, and more stable than using JET or DBLib.

OLE DB Is Spawned

But Microsoft was not done. Because of one somewhat deluded (IMHO) but vociferous product manager, Microsoft eventually launched its most generic and most ambitious OSFA interface to date: OLE DB. This "Object Linking and Embedding" information interface was not restricted to accessing just relational databases like ODBCit could access any source of information, including flat files, the Active Directory, 80-column cards, refrigerator magnets, or any other information source, including (incidentally) relational databases. As a result, Microsoft put yet another (highly complex) layer between the low-level TDS (and other native protocols) and the DBMS engines developers were trying to query.

Another (not so) brilliant move was to create OLE DB in C++ using complex interfaces that could not be accessed directly from Visual Basicthe most popular and widely used language in the world (at the time). It seems that Dumb and Dumber were at the tiller. This is when COM was king and COM-based ADO was the answer to the Visual Basic developer's dilemma and, so it appeared, all of the world's problems. From its inception, ADO was innovative but problematic. While it was a far better and richer interface to SQL Server (it followed many of the innovations designed into RDO), it was buggy and suffered from radical and sometimes violent fits of temper as it transitioned from version to version. It seemed that every time Microsoft "fixed" ADO, it broke a few scattered applications or every ADO application in the world. Developers became more and more hesitant to install updated ("new and improved") versions of ADO (included in what was called the "MDAC stack"), for fear that they would (again) break existing applications. This syndrome was eventually to be called "DLL Hell." It was clear to some of us that COM had been stretched well beyond its envelope of usability.

ADO.NET Is Created

In response to the drumbeat of dissent from angry and frustrated developers (and their customers), Microsoft started over (again). The .NET Framework was built to provide a radically new programming interface for Windows to be launched as "Longhorn" (what became Vista) sometime in this decade. This seemed a natural place to incorporate the newest data access interface. Worried that developers would not adopt this radically new programming model, Microsoft marketing (in its infinite wisdom) dubbed the new .NET language for Visual Basic developers "Visual Basic .NET" and hoped they would not notice that it was an entirely new language. They did the same thing with ADO, calling it "ADO.NET," although the new data access interface had little resemblance to its namesakeexcept the fonts.

ADO.NET got its start as the new .NET technology first took shape. Microsoft knew that they needed to replace the aging COM-based ADO and wanted to make it more universal, lighter, and faster. They also were enamored by the newest darling in the world of technology, XML, so they wanted ADO.NET objects to serialize to and be generated from XML. Originally, ADO.NET was called "XDO" (XML Data Objects) because of that emphasis. They stuck with XDO until very late in the development process, but the marketing team changed it (to my dismay) to ADO.NET. I wish they had kept XDO as the name, as it makes developers aware that it's an entirely new (and improved) data access interface and does not carry with it the baggage of COM-based ADO.

In its defense, ADO.NET takes an entirely new approach. Sure, it's an OSFA data access interface, but instead of trying to force developers to use generic interfaces, it's designed as a way to access the low-level protocols. For the first time since DBLib, Visual Studio developers can access TDS through a fairly thin layer of ADO.NET classes and .NET data access providers. Unlike COM-based ADO (ADOc), ADO.NET does not try to do much in "black boxes" of code over which developers had little controlat least, for the most part. It does not try to guess what kind of SQL is being executed; it does not try to build a Parameters collection for queries or construct UPDATE statements on the fly like ADOc (tries to do). This makes ADO.NET lighter, faster, but more code-intensive and, thus, harder to work withit means the developers have to write a lot of code or use wizards to build the code for them. That's where Visual Studio .NET comes in. Visual Studio includes a set of wizards that automatically generate code at design time that ADOc generated at runtimea radical departure. Unfortunately, Visual Studio's wizards still don't provide all the flexibility and features afforded by ADOc's black boxes. For example, in ADOc, one could set the Update Criteria property to determine what SQL was to be generated to manage update collisions. While ADOc offered at least four options, Visual Studio 2005 offers but twobut I'll get to that later (in Chapters 6, "Building Data Sources, DataSets, and TableAdapters," and 13, "Managing SQL Server CLR Executables").

ADO.NET still has not caught up with ADOc in a number of ways. It does not support any form of server-side cursors, nor does it support asynchronous connections, asynchronous rowset population, update concurrency management, value-set exceptions, and many other features. Yes, these features have been on the list to include in ADO.NET for over 6 years, and while they have tried, the ADO.NET development team has chosen to implement other new features instead. This simply means that those developers trying to convert from ADOc will have more issues to deal with, as there are no equivalent features in ADO.NET to implement.

What's a Client and What's a Server?

Before we go on, let's make a few terms clear. When an application opens a connection to SQL Server (or any DBMS data source), it's considered to be a "client." This "client" application can be a Windows forms application, a middle-tier XML Web Service, an ASP application, or an application on a hand-held PC connected to a milking machine. "Client/server" applications are considered to be those programs (like Windows forms applications) that open (sometimes persistent) connections and provide interactive user interfaces but are not ASP-based. That is, the application runs on an independent computer, separate from the system that hosts the DBMS engine (like SQL Server).

A "server" is the system that hosts SQL Server or other shared DBMS. Yes, a server can share files, and it's then dubbed a "file server." SQL Server originally got its name from the fact that it provides Structured Query Language (SQL) "services" to the network and runs as a Windows service on the host. Today, the term "SQL" has expanded well beyond its original meaning. Don't be confused by "SQL Server" Everywhereit's not a service-based DBMSit's file-based, as I'll discuss later.

In a nutshell, ADO.NET is Microsoft's newest data access interface. In Chapter 8, "Getting Started with ADO.NET," I'll talk about how ADO.NET works and how it's different than ADOc. This should help you transition from ADO classic, if that's on your agenda.