What is ADO?

Learn how ActiveX Data Objects (ADO) fits with existing data access strategies in this introduction to ADO. Other topics covered include the origins of ADO and its role in the .NET future, the problems with OLE DB, the benefits of UDA, Microsoft’s strategy for accessing data in comparison to Oracle’s. (From the book ADO Programmer’s Reference, by David Sussman, published by Apress, ISBN: 1590593421.)

sussmanActiveX Data Objects (ADO) and Object Linking and Embedding Database (OLE DB), its underlying technology, currently play a big part in data access. Microsoft has unequivocally committed its future to it, and rightly so. The paperless office has yet to appear, but the amount of data stored on computer systems increases every day. This is illustrated by the rate at which the Web is expanding—and that’s just the public face of data. Much more data is hidden from general view in corporate applications or intranets.

This chapter describes the terms and technology behind ADO. If you need to start coding straight away, you could skip to the relevant chapters, but your understanding will be better if you have a good foundation.

So, to give you that good foundation, there are several important topics I will discuss in this chapter:

  • What I mean by “data”

  • What I mean by a “data store”

  • How ADO fits with existing data access strategies

  • Data access in the client/server world

ADO is central to Microsoft’s data access strategy, so it’s important to understand why it came about and what sort of a future it has. I’ll be looking at these issues too.

What is Data?

If you’ve got a few spare minutes some time, open Windows Explorer and have a look around your hard drive. Make a mental note of how many separate pieces of information you’ve got: databases, documents, spreadsheets, e-mail messages, HTML and Active Server Pages (ASP) documents, etc.

Quite a lot, eh? They are all pockets of data, but are stored in different forms. This might seem obvious, but traditionally data has been thought of as being stored only in a database; if you built a business application, the data had to be in a database. Although a large proportion of existing data may be contained in databases, why should the remaining data be excluded from our grasp? In fact, as computers become more powerful, the term “data” is starting to include multimedia items such as music and video, as well as objects and the more typical document-based data.

So, by “data” I mean any piece of information whatever its contents. Whether it’s your address book, your monthly expenses spreadsheet, or a pleading letter to the taxman, it’s all data.

What Are Data Stores?

Now that I have established what I consider data to be, the definition of a data store might be fairly obvious—it’s a place in which data is kept. However, there is much more to data stores than you might think. Instead of looking at your hard disk, let’s look at mine to see what I’ve got installed:

  • Databases: I consider these the traditional store of data—I’ve got both SQL Server and Access databases, which I use to store everything from accounts and invoicing to sample databases for books.

  • Spreadsheets: Financial data with year-end figures for my tax returns and bills.

  • Mail and News: I use Outlook and Outlook Express to handle my mail and Internet news.

  • Documents: This is the largest portion of data on my machine, containing all my personal letters and documents, and chapters for books (including this one).

  • Graphics: Screen captures and pictures for books.

  • Internet: HTML and ASP pages containing samples and applications.

  • Reference Material: Including MSDN and encyclopedias.

So that’s the actual data, but how is it stored? Well, the databases are self-contained, so they are their own data store. The reference material is, by and large, stored in its own format, so that could also be considered a data store. The mail and news hold data on their own as well, so they are data stores. Everything else is stored as files; therefore, the file system itself becomes a data store (OK, the data it stores is in myriad formats, but it’s all stored in the same way: folders and documents).

You could even include my CD-ROM drive and tape backup unit. The CD-ROM uses the standard documents and folders format, so this could be considered part of the file system data store, but the tape backup has its own format, so it could be considered a data store too.

Numerous other data stores exist, from mainframe file systems to databases and mail. As an enterprise becomes bigger it also must include user-account databases and other machines attached to the network, such as printers and scanners. Each of these things may not be data stores themselves, but as items of data, they’ll be contained in a data store somewhere. 

This is from ADO Programmer’s Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=About Universal Data Access}

Universal Data Access (UDA) is Microsoft’s strategy for dealing with all this data. It’s aimed at providing high-performance access to a variety of data stores. Cynics might suspect this to be an attempt to shoehorn another Microsoft technology into the scene, but let’s consider the modern business.

To have a successful business, you must be flexible and adapt to change. How do you know when to change? There’s no simple rule, but most companies make decisions by asking a few questions: How much can we sell? How much are we selling? How much are our competitors selling? What’s the profit margin? What does research show about what customers want? Statistical analysis can answer questions like these, but what is the source of the statistics? That’s right, data. But you’ve already seen that data is stored in many different ways, and there is no central way of accessing it all. UDA offers an easy-to-use methodology that allows access to multiple sources of data in a single way. Build in high performance and support for existing data access methods, and you’re on your way to something that could make a real difference. It’s important to remember that UDA is simply Microsoft’s strategy for accessing data, not a technology. UDA is physically implemented as a collection of four technologies: ADO, OLE DB, Remote Data Services (RDS), and Open Database Connectivity (ODBC). Collectively, these four technologies are known as the Microsoft Data Access Components (MDAC). This means that you don’t have to bundle all your data into a single data store. Here’s how it can work.

ado

When building an application, you can make sure it uses ADO for its data access, and ADO will talk to all the data sources required. This means that programming is easier, because you need learn only one programming syntax, as shown in the following illustration. Because ADO provides fast, transparent access to different types of data, there’s no reason to use any other method.

You can clearly see what Microsoft intends when you look at the three main design goals for the Data Access Components:

  • Meeting the key customer requirements, such as performance, reliability, and broad industry support

  • Giving access to the widest range of data sources through a common interface

  • Providing an easy migration path for existing data access technologies

So far they seem to be meeting these objectives admirably.

This method contrasts with Oracle, which is pushing Universal Server, in which all data will be stored under one central (proprietary) data store. The ultimate aim is the same: broader access to data. The Oracle approach involves a bigger initial investment in data conversion and translation as the data is imported to the store. But once that investment has been made, the data will be easy to access. Of course, ADO can access Oracle databases.

In terms of superiority, it’s difficult to choose between the two methods; the best method for you will depend upon your business needs and current computer systems. Bear in mind though, these points:

  • With UDA you can write code today to access most major data stores and develop your own OLE DB Provider to access those data stores that don’t already provide support for UDA technologies.

  • Data conversion is very costly and error prone. Additionally, it rarely results in a fully integrated solution—something much needed for the businesses of today to become the e-businesses of tomorrow.

  • New data types and data stores are emerging all the time. Instead of waiting on Oracle or other vendors to write data migration tools, you can write data access code to retrieve and manipulate the data in its native data store and in its native format.

This is from ADO Programmer’s Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Existing Technologies}

Before I explain why ADO came about, I’ll quickly describe some existing technologies and show you how they fit into the picture.

  • DB-Library (DBLib): This is the underlying technology for connecting to SQL Server. It is primarily designed for C, but is often used in Visual Basic. Because it is specific to SQL Server, it is extremely fast and functional. For this very reason, however, it doesn’t allow access to any other source of data. Other databases, such as Oracle and Sybase, have similar native communication libraries.

  • ODBC: Open Database Connectivity (ODBC) was the first step on the road to a universal data access strategy. ODBC was designed (by Microsoft and other database management system [DBMS] vendors) as a cross-platform, database-independent method for accessing data in any relational database through the use of an Application Program Interface (API), known as the ODBC API. Although ODBC was designed for multidatabase use, it is often used only on single relational databases. From a programmer’s point of view, ODBC, like DBLib, is also complex to use because it was a low-level library. ODBC is also restricted to data sources that support SQL, and is therefore not suitable for non-SQL based data stores, although ODBC Drivers exist that allow access to text files.

  • DAO: The Data Access Objects (DAO), introduced with Microsoft Access, provided a strictly hierarchical set of objects for manipulating data in Jet and other Indexed Sequential Access Method (ISAM) and SQL databases. These objects were first available with Visual Basic 3.0 and quickly became the most commonly used data access method for early Visual Basic programs. DAO also had the advantage of being able to sit on top of ODBC, which allowed it to communicate with many different databases.

  • RDO: Aimed as the successor to DAO for Visual Basic programmers, Remote Data Objects (RDO) is a thin layer that sits atop ODBC to allow better access to server databases, such as SQL Server. This brought the flexibility of ODBC with a much easier programming model than DBLib or the ODBC API, but like DAO it has a strictly hierarchical programming model.

    RDO also brought the world of remote database servers to the world of many programmers. RDO and ODBC share the same relationship as ADO and OLE DB: a thin layer on top of an underlying data access mechanism.

  • ODBCDirect: An extension to DAO, ODBCDirect combined portions of DAO and RDO. It allows programmers to use the DAO programming model and also allows access to ODBC data sources without having the Jet database engine loaded.

  • JDBC: Java Database Connectivity ( JDBC) was designed as another DBMS-neutral API especially for use in Java applications.

The problems with these technologies are very simple. DBLib and ODBC are low-level APIs and therefore, for many programmers, are complex to use. DAO and RDO offer the user another interface to ODBC, but this introduces another layer of code to go through, which can decrease performance. Moreover, all these technologies suffer from a very strict and hierarchical model, which adds extra overhead to programming and execution.

They are also more or less constrained to providing access to relational databases, although Microsoft Excel and simple text documents could also be used as data sources when using ODBC. ODBC drivers have also been produced for object-oriented and hierarchical databases to expose the data in a relational form with rows and columns.

What Is OLE DB?

OLE DB is designed to be the successor to ODBC. You might be asking, why do we need a successor? Well, there are three main trends at the moment. The first, fairly obviously, is the Internet. The second is the increasing amount of data being stored in a nonrelational form, such as Exchange Server and file systems. The third is Microsoft’s desire for a world in which all object usage is handled through their Component Object Model (COM). In fact, OLE DB encourages the use of componentization, allowing database functionality and data handling to be encapsulated into components.

Because of its distributed nature, the Internet brings a different aspect to standard data access. Applications are now being written on a truly global scale, and you can no longer guarantee that the data you access is stored on your local network. This means that you need to consider carefully the way you access data and the type of data you access. The new business opportunity of e-commerce has meant that selling becomes a whole new ball game—you can now have an application that shows pictures of your products, plays music, and even videos, all running over the Web. The Web is also more distributed (and often less reliable) than conventional networks, so your data access method must take this into account. You can’t, for example, assume that your client and server remain connected at all times during an application; in the stateless nature of the Web, this doesn’t make sense.

OLE DB is a technology designed to solve some of these problems, and over time it will gradually replace ODBC as the central data access method. OLE DB is the guts of the new data access strategy, but because it allows access to existing ODBC Data Sources, OLE DB provides an easy migration path. 

This is from ADO Programmer’s Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Why ADO?}

OLE DB is a COM-based set of object-oriented interfaces, so it is too complex for a large portion of the programming community to use, or it is not suitable because they use programming languages that don’t have access to custom COM interfaces. For example, accessing OLE DB directly requires C++ because of the OLE DB interface’s complexity. ADO is the higher-level model that most people will use, because it allows access from dual-interface COM components that can be accessed from Visual Basic and scripting languages. It equates fairly well to the DAO level, where you create an object and call its methods and properties. As a COM component, it can be used from any language that supports COM, such as Visual Basic, VBA, scripting languages, and Visual C++.

So now my diagram looks even more enticing:

Various languages all have the ability to use a central data access strategy. Some languages (like Java and Visual C++) can talk directly to OLE DB in addition to talking to the easier ADO—although we’ll be looking at only the ADO layer here.

ADO also improves speed and ease of development by providing an object model that allows data to be retrieved from a data source with as little as one line of code.

ADO addresses another pressing need that has been created by the increasing use of the Web as an application medium. Conventional applications are generally connected to their data store via a Local Area Network (LAN). They can open a connection to the data and keep that connection open throughout the life of the program. Consequently, the data store knows who is connecting. Many applications also process data on the client—perhaps a set of records that the user is browsing through or updating. Because the client is connected to the server—the data source—there’s no trouble updating the data.

On the Web, however, the underlying HTTP protocol is stateless. No permanent connection between client and server exists If you think about the way the Web works, you’ll realize why this is a problem:

  1. You request a Web page in your browser.

  2. The Web server receives the request, runs any server-side script, then sends the page back to you, and the connection is closed.

That’s it. As soon as this is over, the Web server forgets about you. Admittedly, with ASP you can store some sort of session state, but it’s not very sophisticated. How then, with this disconnected network, do you provide a system that allows data to be updated on the client and sent back to the server? This is where ADO comes in, with disconnected recordsets. This technology allows the recordset to be disassociated from the server and reassociated at a later date. Additionally, you can update, insert, and delete records on the disconnected recordsets locally, and update the central set of records (the server) at a later date. In fact, disconnected recordsets don’t have to be associated with a database at all—they can be manually created and used as a data store in much the same way as collections are. Manually created recordsets, however, cannot be used to update data stores.

The use of client-side data manipulation also allows you to sort data, find records, and generally manage recordsets without resorting to additional trips to the server. Although this idea primarily fits with the nature of Web applications, it can work just as well for the standard type of applications running on a LAN, and can reduce network traffic.

This is from ADO Programmer’s Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=ADO and ADO.NET}

Because Microsoft is pushing its .NET technology very heavily, you might wonder what role ADO has to play in the future. The answer is: a big role. A huge amount has been invested in existing technologies (i.e., those that aren’t .NET). This means that ADO is still important. So if you’re developing applications that use ADO, don’t worry that it will suddenly disappear; Microsoft is committed to supporting it for a very long time.

Data Providers and Data Consumers

OLE DB introduces two new terms that help to explain how OLE DB and ADO fit together:

  • A Data Consumer is something that uses (or consumes) data. Strictly speaking, ADO is actually a consumer, because it uses data provided by OLE DB.

  • A Data Provider is something that provides data. This isn’t the physical source of the data, but the mechanism that connects you to the physical data store. The provider may get the data directly from the data store, or it may go through another layer (such as ODBC) to get to the data store.

The initial set of OLE DB providers supplied with MDAC 2.8 consists of:

  • Directory Services, for stored resource data, such as Active Directory. With Windows 2000, the Directory Service allows access to user information and network devices.

  • Index Server, for Microsoft Index Server. This will be particularly useful as Web sites grow, because indexed data will be available.

  • Site Server Search, for Microsoft Site Server. Again for use with Web sites, especially large, complex sites, where Site Server is used to maintain them.

  • Oracle, for Oracle databases. Connecting to Oracle has never been particularly easy with Microsoft products, but a native driver will simplify access to existing Oracle data stores.

  • SQL Server, for Microsoft SQL Server, allows access to data stored in SQL Server.

  • Data Shape, for hierarchical recordsets. This allows creation of master/detail type recordsets, which allow drilling down into detailed data.

  • Persisted Recordset, for locally saved recordsets and recordset marshaling.

  • OLAP, for accessing Online Analytical Processing data stores.

  • Internet Publishing, for accessing Web resources that support Microsoft FrontPage Server Extensions or Distributed Authoring and Versioning (DAV).

  • Remoting Provider, for connecting to data providers on remote machines.

This is just the standard providers supplied by Microsoft; other vendors have created their own. For example, a company called ISG provides an OLE DB provider that allows connections to multiple data stores at the same time. Oracle provides an OLE DB provider, which it claims is better than Microsoft’s Oracle provider, and most other database suppliers have OLE DB providers for their databases.

OLE DB also provides a few other services, such as a query processor and a cursor engine, so these can be used at the client. There are two reasons for this. First, it frees the actual provider from providing the service, so the service can be smaller and faster. Second, it makes it available as a client service. This means that cursor handling can be provided locally, which is an important function of disconnected recordsets and Remote Data Services. Another advantage of the Cursor Service is that, as a client-based service, it can provide a more uniform set of features across all providers. Also, the persistence provider (MSPersist)—introduced in ADO 2.5—has the ability to persist a recordset to a stream.

Note that Jet and ODBC desktop drivers are not included in ADO versions 2.6 and above. For more details on this, please visit http://support.microsoft.com/ and search for Knowledgebase article number 271908. For the ODBC examples in this book, you must install ADO 2.5 before installing a later version.

This is from ADO Programmer’s Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Providers and Drivers}

It’s important to understand how OLE DB Providers relate to ODBC Drivers, especially because an OLE DB Provider exists for ODBC, which seems somehow confusing. Take a look at this diagram, which shows the difference between providers and drivers:

This distinctly shows the two layers. You can use OLE DB Providers to access data sources, including ODBC data sources. This allows OLE DB to access data for which there is an ODBC Driver, but no native OLE DB Provider (such as DAV or Directory Services). Note that the ODBC provider is deprecated, and although it will still work and is still supported, it may not be in the future. For example, no new changes will be made to the OLE DB Provider for ODBC, it is not supported from ADO.NET, and it will not be supported on 64-bit operating systems.

The essential distinction is that Providers are for OLE DB and Drivers are for ODBC.

Windows Distributed interNet Application Architecture (Windows DNA)

DNA is the strategy that Microsoft is defining as the ideal way to write distributed, n-tier, client/server applications. One of the interesting things about this strategy is that it’s really just a set of ideas and suggestions, rather than a complex, locked-in solution. It’s not a new idea, but some really good tools now make creating this type of application relatively easy.

The basic premise is to partition your application into at least three tiers, illustrated in this diagram:

The first tier is the user interface tier—this is what the user sees, and could be a Web page or another type of application written in any language. The second tier is where the business rules or processes lie; these determine where the data comes from, what rules apply to the data, and how it should be returned to the user interface. The third tier is the data layer—the actual data source.

The great thing about DNA is that it aims to be language-independent; it all hinges around COM (COM+ in Windows 2000 and later). COM allows easy object creation and re-use. Any component that supports COM will fit into this picture, so you can write your application in ASP and JavaScript, Visual Basic, Visual C++, Delphi, or any language that supports COM. Likewise, your business components can be in any language, so you can program in your preferred environment; there’s no need to learn a new language.

In the preceding diagram, you can see that Internet Information Server (IIS) and Microsoft Transaction Server (MTS) are mentioned in the Business Processes tier. IIS supplies the connection from Web applications to components, which could be MTS components or standalone components (that is, components not managed by MTS). The great advantage of MTS is that it makes your middle tier very easy to manage; you can just create a component and install it in MTS. Once you’ve done that, it becomes available to all applications that can call components. You can access the components on remote machines using DCOM, MTS, or HTTP (with RDS). You also get the added advantage of transaction processing and easy scalability without having to program it yourself. The user interface can then be built in any language that supports COM.

ADO Availability

The Microsoft Data Access Components (MDAC) are available as a separate download from the Microsoft Web site at http://www.microsoft.com/data. The file name is MDAC_TYPE.EXE.

If you’re unsure what version of ADO, or any of its components, you’ve got installed, download the Component Checker from http://www.microsoft.com/data/download.htm. This utility will give you a complete list of ADO DLLs and their version numbers. You can also use the Version property of the Connection object (see Chapter 3). 

This is from ADO Programmer’s Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=New Features}

ADO 2.5 and 2.6 have a host of new features that make programming easier and extend the goal of Universal Data Access. This section only summarizes them; I deal with them in more detail in later chapters.

ADO 2.5

The Record Object

The Record object is designed to deal with Document Source Providers, which are OLE DB Providers that don’t access databases, but provide data from semistructured data stores. Two examples of this are Microsoft’s Exchange Server 2000 and Internet Information Server 5.0; both are sources of large amounts of data, and the OLE DB Provider for Internet Publishing allows you to access the data storage structure and the stored objects themselves.

The primary purpose of the Record object is to map a node in a tree-like structure of a document source. It works in conjunction with the Recordset but is not directly applicable to relational data sources.

The Stream Object

The Stream object is a component that wraps the COM IStream interface, allowing easy access to streams of memory. This provides a way to transfer Recordsets directly to other components (such as the ASP 3.0 Request and Response objects) that support streams. The Stream is also used with Document Source Providers to allow access to file contents.

ADO 2.6

Command Streams

Command streams allow a Stream object to be used as the source of a command. A good example of this is a Stream containing an XML command to be executed against SQL Server 2000.

Results in Streams

Along with command streams, ADO 2.6 allows the results of a data query to be returned into a Stream object. This is particularly useful for obtaining XML data directly from SQL Server 2000.

Dialect Property

This identifies the syntax rules the provider uses when parsing strings or streams. Its main use is for XML-generated recordsets, where the dialect identifies what form of XML the recordset is stored in.

Single Row Resultsets

Because of the way ADO worked, singleton commands (commands that return only a single row) were always a performance hit, but ADO 2.6 has improved the performance of such commands.

Field Status Values

The Status property of the Field object is now filled with information to help with the dreaded “Errors Occurred” error.

SQL Variant Support for Cursor Service

Extended support for variant types has been added to the OLE DB Cursor Service.

ADOX Group and User Properties

The Properties collection has been added to the ActiveX Data Objects Extensions (ADOX) Group and User properties to allow access to provider-specific properties.

ADO MD UniqueName support

The UniqueName property can now be used to access ActiveX Data Objects Multidimensional ADO MD objects. This means that parent collections no longer need to be populated to retrieve schema objects.

ADO 2.7

ADO 2.7 didn’t introduce any new features; it simply provided support for 64-bit operating systems. 

This is from ADO Programmer’s Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=ADO 2.8}

ADO 2.8 is simply a security update done as part of the Microsoft Secure Computing initiative. In some cases this has meant a break in backward compatibility, but the benefits of the added security outweigh these breaking changes.

The first of these changes is that code able to access the disk (such as the Stream object or opening or saving a Recordset from a file) is limited to trusted sites. Additionally, the file source must be a physical file or URL (and not a printer or serial port, for example). This checking occurs only on Windows 2000, Windows Server 2003, and Windows XP.

The second change ensures that the ActiveCommand property of a Recordset cannot be read when running in Internet Explorer. This stops malicious script from hijacking the command.

The third main change affects the handling of integrated security. Depending upon settings, providers could reuse existing connections and credentials to access other servers. For sites not in the Trusted Sites zone, untrusted providers cause security dialogs to be shown to the user. If the user accepts that the data is coming from an untrusted source, or if the data is coming from a trusted source, connection is permitted depending upon certain conditions detailed in the following table:

IE Settings for authentication and login

Provider supports “Integrated Security”

Provider does not support “Integrated Security”

UID & PWD are specified

SSPI

(JOLT, MSDASQL, MSPersist)

(SQLOLEDB)

(no UID & PWD)

Automatic login with current user name and password Prompt for user name and password Automatic logon only in Intranet zone Anonymous logon

Allow connection Allow connection Allow connection Allow connection

Allow connection Fail connection Prompt with security warning Fail connection

Allow connection Fail connection Prompt with security warning Fail connection

The final security feature covers session pooling, which now uses access tokens instead of just the Security Identifier (SID) to determine if the requested connection matches an existing pooled connection.

Deprecated Components

The following ADO components are deprecated in version 2.8, which means that they are still supported, but may be removed in the future.

  • ODBC Provider (MSDASQL). You should use native OLE DB Providers instead of ODBC. Although deprecated, the OLE DB Provider for ODBC is still the default provider.

  • Remote Data Services (RDS). You should replace RDS use with the Simple Object Access Protocol (SOAP) toolkit, which is an open, XML-based standard.

  • Jet and Replication Objects ( JRO). Because the Jet OLD DB Provider was removed from MDAC 2.6, no new releases are planned, and they will not be available in future MDAC releases.

  • AppleTalk and Banyan Vines SQL Network Libraries. You should replace their use with TCP/IP.

  • 16-bit ODBC support. You should migrate to 32-bit data sources.

This is from ADO Programmer’s Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Examples 1-3}

Because this is primarily a reference book, I’ll give you a few documented samples of code so that you can see what’s possible and how some of the components fit together. You can use this as a sort of “table of contents” to the rest of the book, where the various aspects are covered in more detail. A later section of this chapter provide more detail about language specifics, so I use Visual Basic here because it’s easy to read and understand.

Example 1

The following section of Visual Basic code creates a connection to SQL Server, opens a recordset, and then adds some details from the recordset to a listbox:

‘ Define two object variables
‘ The object model is discussed in Chapter 2
Dim objConn As ADODB.Connection
Dim objRs As ADODB.Recordset
Set objConn = New ADODB.Connection
Set objRs As New ADODB.Recordset

‘ Open a connection to the pubs database using the
‘ SQL Server OLE DB Provider
‘ Connection strings are discussed in Chapter 2
‘ The Connection object is discussed in Chapter 3
objConn.Open “Provider=SQLOLE DB; Data Source=Tigger; ” &_
             “Initial Catalog=pubs; User Id=sa; Password=”

‘ Open a recordset on the ‘authors’ table
‘ The Recordset is discussed in Chapter 5
objRs.Open “authors”, objConn, adOpenForwardOnly, _ 
            adLockReadOnly, adCmdTable

‘ Loop while we haven’t reached the end of the recordset
‘ The EOF property is set to True when we reach the end
While Not objRs.EOF
   ‘ Add the names to the listbox, using the default Fields collection
   ‘ The Fields collection is discussed in Chapter 6
   List1.AddItem objRs.Fields(“au_fname”) & ” ” & _
                 objRs.Fields(“au_lname”)
   ‘ Move to the next record
   objRs.MoveNext
Wend

‘ Close the objects
objRs.Close
objConn.Close

‘ Release the memory associated with the objects
Set objRs = Nothing
Set objConn = Nothing 
 

Example 2

This example runs a SQL statement and doesn’t expect a set of records to be returned. It also uses an ODBC Data Source Name called pubs, which has been set up previously.

‘ Define the object variable
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection

‘ Open a connection to the pubs database using the
‘ OLE DB Provider for ODBC
objConn.Open “DSN=pubs; UID=sa; PWD=”

‘ Run a SQL UPDATE statement to update book prices by 10%
objConn.Execute “UPDATE titles SET price = price * 1.10” _
        , , adExecuteNoRecords + adCmdText

‘ Close the connection
objConn.Close

‘ Release the memory associated with the objects
Set objConn = Nothing

Example 3

This example runs a stored query in Access, passing in some parameters:

‘ Declare the object variables
‘ The Command object is discussed in Chapter 4
‘ The Parameter object is discussed in Chapter 4
Dim objConn     As ADODB.Connection
Dim objCmd      As ADODB.Command
Dim objRs        As ADODB.Recordset
Dim objParam    As ADODB.Parameter
Set objConn = New ADODB.Connection
Set objCmd = New ADODB.Command

‘ Open a connection to an Access pubs database,
‘ using the Access OLE DB provider
objConn.Open “Provider=Microsoft.Jet.OLE DB.4.0; ” & _
        “Data Source=C:temppubs.mdb” 

‘ Create a new Parameter, called RequiredState
Set objParam = objCmd.CreateParameter(“RequiredState”, _
               adChar, _ adParamInput, 2, “CA”)

‘ Add the Parameter to the Parameters collection of the Command object
objCmd.Parameters.Append objParam

‘ Set the active connection of the command to the open Connection object
Set objCmd.ActiveConnection = objConn

‘ Set the name of the stored query that is to be run
objCmd.CommandText = “qryAuthorsBooksByState”

‘ Set the type of command
objCmd.CommandType = adCmdStoredProc

‘ Run the stored query and set the recordset which it returns
Set objRs = objCmd.Execute

‘ Loop through the recordset, adding the items to a listbox
While Not objRs.EOF
     List1.AddItem objRs.Fields(“au_fname”) & ” ” & _ 
                      objRs.Fields(“au_lname”) & _ 
                      “: ” & objRs.Fields(“title”)
     objRs.MoveNext
Wend

‘ Close the recordset and connection
objRs.Close
objConn.Close

‘ Reclaim the memory from the objects
Set objRs = Nothing
Set objCmd = Nothing
Set objParam = Nothing
Set objConn = Nothing

 

This is from ADO Programmer’s Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Examples 4-6} 

Example 4

This example uses the Fields collection of the recordset to loop through all of the fields in a recordset, then prints the details in the debug window:

‘ Declare the object variables
Dim objRs As ADODB.Recordset
Dim objFld As ADODB.Field
Set pbjRs = New ADODB.Recordset

‘ Open a recordset on the ‘authors’ table using the
‘ OLE DB Driver for ODBC
‘ without using a connection
objRs.Open “authors”, “DSN=pubs”, _
         adOpenForwardOnly, adLockReadOnly, adCmdTable

‘ Loop through the Fields collection printing the
‘ name of each Field
‘ The Fields collection is discussed in Chapter 6
For Each objFld In objRs.Fields
     Debug.Print objFld.Name; vbTab;
Next
Debug.Print

‘ Loop through the records in the recordset
While Not objRs.EOF
     ‘ Loop through the fields, this time printing out
     ‘ the value of each field
     For Each objFld In objRs.Fields
          Debug.Print objFld.Value; vbTab;
     Next Debug.Print
     objRs.MoveNext
Wend

‘ Close the recordset and release the memory
objRs.Close
Set objRs = Nothing
Set objFld = Nothing

Example 5

This is an ADOX example; it uses a Catalog and its associated Tables collection to list the tables in a data store:

‘ Declare the object variables
Dim objCat    As ADOX.Catalog
Dim objTable As ADOX.Table
Dim strConn  As String
Set objCat = New ADOX.Catalog

‘ set the connection string
strConn=”Provider=Microsoft.Jet.OLE DB.4.0; Data Source= _
             C:temppubs.mdb”

‘ point the catalog at a data store
objCat.ActiveConnection = strConn

‘ loop through the tables collection
For Each objTable In objCat.Tables
     Debug.Print objTable.Name
Next

‘ clean up
Set objTable = Nothing
Set objCat = Nothing

Example 6

This example also shows ADOX, and uses a Catalog, its Tables collection, and the Indexes and Columns collections for a table to print a list of columns for the index:

Dim objCat       As ADOX.Catalog
Dim objTbl       As ADOX.Table
Dim objIdx       As ADOX.Index
Dim objCol       As ADOX.Column
Dim strConn     As String
Set objCat = New ADOX.Catalog

‘ set the connection string
strConn=”Provider=Microsoft.Jet.OLE DB.4.0; Data Source= _
             C:temppubs.mdb”

‘ point the catalog at a data store
objCat.ActiveConnection = strConn

‘ loop through the tables
For Each objTbl In objCat.Tables
     ‘ has the table got indexes
     If objTbl.Indexes.Count > 0 Then
          Debug.Print objTbl.Name
     ‘ loop through the indexes
     For Each objIdx In objTbl.Indexes
               Debug.Print vbTab; objIdx.Name; vbTab;
                          objIdx.PrimaryKey
               ‘loop through the columns in the index
               For Each objCol In objIdx.Columns
                    Debug.Print vbTab; vbTab; objCol.Name
               Next 
          Next
     End If
Next

‘ clean up
Set objCol = Nothing
Set objTbl = Nothing
Set objIdx = Nothing
Set objCat = Nothing

This is from ADO Programmer’s Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Language Differences}

I’ve tried to make this book relatively language-independent. Because ADO can be used by any programming language that supports COM, deciding which language to use for the samples in this book becomes sticky. To help the widest possible audience, I’ve stuck with a pseudo-code type style. For example, in the examples that show something being printed, I’ve just used Print. You can then substitute the command for your language of choice. The different methods for several common languages and environments are described here:

Language

Method

Notes

Visual Basic

Debug.Print message

Prints the text to the debug window

MsgBox message

Pops up a window with the text

ASP & VBScript

Response.Write message

Returns the text to the browser

ASP & JavaScript

Response.Write (message);

Returns the text to the browser

VBScript

document.write message

Inserts message into the HTML document

MsgBox message

Pops up a window with the text

JavaScript

document.write (message);

Inserts message into the HTML document

alert (message);

Pops up a window with the text

Although I’ve tried to make our samples as language-independent as possible, you’ll notice that most of the samples in the book use a Visual Basic/VBScript style—because I see this as the greatest market for ADO usage. However, the samples should be easy to translate into your favorite language.

If you want to use ADO in a variety of languages, have a look at the document entitled Implementing ADO with Various Development Languages at http://msdn.microsoft.com/library/techart/msdn_adorosest.htm. (Editor’s note: Link not currently available.)

A set of samples in a variety of languages is also available from the support page on the Apress Web site at http://support.apress.com/.

Creating Objects

Creating the ADO objects is one area where pseudo-code doesn’t really work too well. Significant differences exist, even between such apparently similar languages as Visual Basic and VBScript. Therefore, this section is devoted to the act of creating objects. I describe in some detail how it’s done in each of the five languages where I think ADO will have most impact.

This isn’t intended as a full explanation of all the objects and how they are used in each language; instead, it is a demonstration of the major differences between several of the most common languages.

Visual Basic

Before you can create an ADO object in Visual Basic, make sure you have a reference to the ActiveX Data Objects Database (ADODB) library set. From the Project menu, select References, and then choose Microsoft ActiveX Data Objects 2.8 Library. The ADO Extensions (ADOX) are in the library labeled Microsoft ADO Ext. 2.8 for DDL and Security. You can create objects three ways. The first is:

Dim objRs As New ADODB.Recordset

This creates the object reference immediately, but the object is not instantiated until the first property or method is called. This means that instead of being instantiated when declared, the object is instantiated later in the code—which can lead to debugging problems. A better solution is to use this method:

Dim objRs As ADODB.Recordset
Set objRs = New ADODB.Recordset

This creates a variable of the type Recordset, and then the Set line instantiates the object. The third method is the older style, using late binding, and is less used these days (and also doesn’t require a reference to the ADODB library to be set):

Dim objRs As Object
Set objRs = CreateObject(“ADODB.Recordset”)

After an object has been created, invoking the methods and properties is extremely simple. For example:

objRs.Cursorlocation = adUseClient
objRs.Open “authors”, objConn, adOpenStatic, _ 
                     adLockBatchOptimistic, adCmdTable

The ad constants are automatically available to you in Visual Basic once you have referenced the ADO library (as described previously).

Looping through a recordset is just a question of using the MoveNext method and checking the EOF property:

While Not objRs.EOF
     Debug.Print objRs(” field _ name“)
     objRs.MoveNext
Wend

ASP/VBScript

Creating objects in VBScript is different from Visual Basic because VBScript doesn’t have variable types (all variables are of Variant type) or support for adding references to type libraries (although it does use the Visual Basic syntax for assigning object variables using Set). Therefore, there’s no need to define the variables, although it’s better to define them for ease of code maintenance and reliability:

Dim objRs
Set objRs = Server.CreateObject(“ADODB.Recordset”)

This creates a Recordset object in ASP script code.

You can also use the <% Option Explicit %> command to ensure that variables are required to be defined.

Using the object follows the same procedure as for Visual Basic:

objRs.CursorLocation = adUseClient
objRs.Open “authors”, objConn, adOpenStatus, _
                      adLockBatchOptimistic, adCmdTable

The only difference here is that the constants are not automatically available; this is because scripting languages do not have access to the type library and its constants. You have two options. The first is to use the integer values that these constants represent. The problem with this is that your code becomes sprinkled with various numbers whose meaning is not obvious to the reader. For example, without ADO’s predefined constants, the previous statement would read:

objRs.CursorLocation = 3
objRs.Open “authors”, objConn, 1, 3, 2

This makes your code harder to read, and therefore harder to maintain.

The second option is to include the constants in your ASP script; this means that you can use the constant names instead of their values. You can include the ADO constants with the following line:

<!— #INCLUDE FILE=”adovbs.inc”—>

The include file, adovbs.inc, is installed in the default directory of Program FilesCommon FilesSystemADO. It can be moved to your local ASP directory or referenced from a central virtual directory.

A better way to use the constants is to create a direct reference to the type library, using some meta data:

<!— METADATA TYPE=”typelib”
FILE=”C:Program FilesCommon FilesSystemADO _
                           msado15.dll” —>

The advantage of this method is that you use the values from the ADO library itself rather than those from the include file. This means that you don’t have to worry that the location (or even the contents) of the include file might change. Note that the name of the DLL is always msado15.dll regardless of which ADO version you have.

Looping through recordsets in VBScript is exactly the same as in Visual Basic:

While Not objRs.EOF
     Response.Write objRs.Fields(” field _ name“).Value
     objRs.MoveNext
Wend

This is from ADO Programmer’s Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Creating Objects in JScript, Visual C++ and .NET}

JScript

JScript has a syntax different from VBScript’s, although much of the object usage is similar. The major thing to watch for is that JScript is case-sensitive. For example, to create a recordset in Jscript, you would use:

var objRs = Server.CreateObject(“ADODB.Recordset”);

Use of the methods and properties is also very similar:

objRs.CursorLocation = adUseClient;
objRs.Open (“authors”, objConn, adOpenStatic, _
             adLockBatchOptimistic, adCmdTable);

To loop through a recordset in Jscript, you would use:

while (!objRs.EOF)
{
     Response.Write (objRs(” field _ name“));
     objRs.MoveNext();
}

Visual C++

To use ADO within C++, you must import the ADO library:

#import “c:program filescommon filessystemado
         msado15.dll”  
         no_namespace
         rename( “EOF”, “adoEOF” )

You must make sure the file path points to the location of your version of the ADO DLL. The no_namespace keyword is added so that you don’t have to scope the ADO names. The EOF property must be renamed because of an unfortunate name collision with the EOF constant defined in the Standard C library.

Object creation follows this syntax:

_ConnectionPtr           pConnection;
pConnection.CreateInstance( __uuidof( Connection ) );
_RecordsetPtr            pRecordSet;
pRecordSet.CreateInstance( __uuidof( Recordset ) );

.NET

Although .NET comes with a new data access strategy (ADO.NET), You still can use ADO within .NET if required. For this you must create a .NET wrapper to use the ADO COM components. In Visual Studio .NET you can do this simply by creating a reference to the ADO component; otherwise, you can use the tlbimp.exe tool supplied with .NET to create the wrapper. See the .NET documentation for more details. Once the wrapper is created, ADO use is exactly the same as non-.NET languages.

Summary

So far you’ve explored the principles of data access and the range of problems created by the increasing variety and location of data and users. You’ve seen a bird’s eye view of the significance of ADO and its advantages, and briefly looked at the other data access technologies it builds on, replaces, or complements. In particular, you’ve considered:

  • Data and data stores

  • The existing technologies for accessing data, and Microsoft’s scheme to streamline data access, UDA

  • What OLE DB and ADO are, and how they tie in with each other

  • The distinction between data providers and data consumers, and Microsoft’s Distributed interNet Application Architecture framework for client/server solutions

  • The new features of ADO 2.5 and 2.6

  • The security changes introduced in ADO 2.8

  • How to use the basics of ADO in a variety of languages

The next chapter discusses the ADO and ADOX object models.

This is from ADO Programmer’s Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

 

[gp-comments width="770" linklove="off" ]