Lesson 3: Introduction to ADO

Microsoft now recommends the use of ADO as a standard interface to all kinds of external data. Because ADO is based on COM technology, you will not learn how to use the ADO API directly until you have learned more about COM. Chapter 10, COM Clients, shows you how to create and use ADO objects within your C++ application code.

As an introduction to using ADO technology, this lesson shows you how to use the ADO Data Control to display records from an OLE DB data provider in a dialog-based application.

After this lesson, you will be able to:

• Describe the advantages of using ADO objects.
• Describe the components and the structure of the ADO object model.
• Describe how to use the ADO Data Control and data-bound ActiveX controls to display records from an OLE DB data provider.

Estimated lesson time: 30 minutes

Advantages of Using ADO

ADO is designed as an easy-to use application interface to OLE DB. ADO is easy to use because it exposes Automation objects that abstract the OLE DB interfaces. This allows the programmer to focus on the tasks to accomplish rather than the complexity of OLE DB. Any development platform that supports COM and Automation, which includes scripting languages such as Microsoft Visual Basic Scripting Edition (VBScript) and Microsoft JScript, can use ADO objects. This means that ADO can be used in Web-based development, using technologies such as Active Server Pages (ASP); as well as in desktop application development environments such as Visual C++ and Visual Basic.

For more information about Automation technology, see Lesson 3 of Chapter 8.

ADO recordsets are unique because they can be disconnected from the data source. Disconnected recordsets can be passed to other applications; and updates can be made to the data in the recordset, without requiring any network trips or connections to the data source. This feature is particularly useful in Web-based applications.

The ADO object model has fewer objects and is easier to use when compared to other data access objects such as DAO or RDO.

ADO Object Model

The ADO object model is designed to present the most commonly used features of OLE DB. As illustrated in Figure 7.14, the ADO object model has three main components, the Connection object, the Command object, and the Recordset object:

• The Connection object makes a connection between your application and an external data source, such as SQL Server. The Connection object also provides a mechanism for initializing and establishing the connection, executing queries, and using transactions. It is the highest-level object in the ADO object model.
• The Command object builds queries, including user-specific parameters, to access records from a data source. Typically, these records are returned in a Recordset object. Command objects are created from a database table, or an SQL query. You can also create relationships between Command objects to retrieve a set of related data in the form of a hierarchy.
• The Recordset object accesses records returned from an SQL query. You can use Recordset objects to permit users to edit, add, or delete records in the data source.



Figure 7.14 The ADO object model

The ADO object model differs from the DAO and RDO object models in that many of the objects can be created independently of one another. ADO objects are stored in a hierarchical format, but the hierarchy is de-emphasized so that you create only the objects that you need for your solution. You can create a Recordset, Connection, or Command object directly, without having to create their parent objects. For example you can create a Recordset object without first explicitly creating a Connection object. ADO implicitly creates the required Connection object for you.

ADO Collections

In addition to the three main objects, ADO supports three collections that can provide additional functionality to your applications:

• Errors Collection Any operation involving ADO objects can generate one or more provider errors. As each error occurs, one or more error objects are placed in the Errors collection of the Connection object. Each Error object represents a specific provider error, not an ADO error.
• Parameters Collection A Command object has a Parameters collection made up of Parameter objects. The Parameters collection is used to pass specific data to the parameterized query or stored procedure encapsulated by the Command object. It is particularly useful if you need to retrieve the value of output parameters from a stored procedure.
• Fields Collection A Recordset object has a Fields collection made up of Field objects. Each Field object corresponds to a column in the recordset. You use the Fields collection to access specific fields in an existing Recordset object.

In addition to their inherent object properties, ADO objects support the Properties collection. This is a collection of Property objects, which contain provider- specific information about the object. For example, the Properties collection of the Connection object contains Property objects that specify the maximum number of columns or the maximum row size of the current provider.

Using the ADO Data Control

The ADO Data Control is a graphical ActiveX control, complete with record navigation buttons, which provides an easy-to-use interface to help you create database applications with a minimum of code. The ADO Data Control uses ADO to quickly create connections between data-bound controls and data providers. Data-bound controls are ActiveX user-interface controls that have two important characteristics:

• A DataSource property, which can be set to the ID of an ADO Data Control
• The ability to display data retrieved by the ADO Data Control to which they are bound

When you bind controls to an ADO Data Control, each field is automatically displayed and updated when navigating through records. This behavior is implemented by the controls themselves—you do not have to write any code.

Visual C++ includes several data-bound ActiveX controls such as the Microsoft DataGrid and Microsoft DataList Controls. You can also create your own data-bound controls or purchase controls from other vendors.

In the following exercises, you will create a simple dialog-based application, which uses the ADO Data Control and the DataGrid Control to display records from the authors table of the pubs database. You will also learn how to set the properties of these controls from within your application source code.

To create the ViewDB Application

1. Start a new MFC AppWizard (.exe) project to create an application named ViewDB.
2. In Step 1 of the AppWizard, select Dialog based, and then click Finish.
3. Click OK to confirm the selection.

A dialog-based application is the simplest form of application that can be created by the AppWizard; such an application is not a document/view application. The application main window is a modal dialog box, and the application terminates when this dialog box is closed.

The AppWizard creates a dialog template and a dialog class for the main window dialog box. For the ViewDB application it has created the IDD_VIEWDB_ DIALOG template and the CViewDBDlg class. The IDD_VIEWDB_DIALOG template resource should be currently open in the dialog editor.

The ADO Data Control and the DataGrid Control are installed as part of a standard installation of Visual C++. However, before you can place them in your application dialog box, you must use the Components and Controls Gallery to insert the controls into your project.

Inserting an ActiveX control into your project makes it available for you to use by:

• Placing an icon on your Controls toolbar so that you can use the mouse to place the control when editing a dialog template.
• Creating C++ classes for your project that wrap the Automation interfaces exposed by the control. You can use these classes to get and set the control properties, and to call the methods that the control provides.

To insert ActiveX controls into a project

1. From the Add to Project option on the Project menu, select Components and Controls.
2. In the Components and Controls Gallery, double-click the Registered ActiveX Controls folder to display all ActiveX controls registered on your system.
3. Click Microsoft ADO Data Control, version 6.0 (OLE DB) and then click Insert.
4. Click OK to insert the component. Click OK to confirm that you want to generate classes for the control.
5. Repeat the process to insert the Microsoft DataGrid Control, Version 6.0 (OLE DB) into the project.
6. Click Close to close the Components and Controls Gallery.

You will now see that icons for the ADO Data Control and the DataGrid Control have been added to your Controls toolbar.

Using Figure 7.15 as a guide, edit the IDD_VIEWDB_ DIALOG template by taking the following steps:

To edit the IDD_VIEWDB_DIALOG template

1. Remove the static text control that reads TODO: Place dialog controls here.
2. Set the dialog box caption to read View Database.
3. Place an ADO Data Control as shown in Figure 7.15. Edit the properties to remove the caption. Leave the control ID as IDC_ADODC1.
4. Place a DataGrid Control as shown in Figure 7.15. Change the caption to read Authors. Leave the control ID as IDC_DATAGRID1. On the Control page of the DataGrid Control Properties, clear the AllowUpdate checkbox to make the control read-only.
5. Add a static text control that displays the text Order by:. Next to this add a list box control that is tall enough to take two lines of text, as shown in Figure 7.15. Assign the list box the ID IDC_ORDERLIST.
6. Reorganize the OK and Cancel buttons as shown in Figure 7.15.



Figure 7.15 Editing the IDD_VIEWDB_DIALOG template

To connect the ADO Data Control to the data source

1. Edit the properties of the IDC_ADODC1 ADO Data Control. On the Control page, make sure that Use Connection String is selected. Click Build to create a connection string.
2. The Data Link Properties dialog box appears. On the Provider page, select Microsoft OLE DB Provider for SQL Server. Click Next to proceed to the Connection page.
3. Type (local) as the server name. Select Use Windows NT integrated security, or enter a SQL Server account name and password, as appropriate.
4. Select the pubs database on the server.
5. Click Test Connection. If the test is successful, click OK to save the Data Link properties.
6. Reopen the ADO Data Control Properties dialog box. Select the RecordSource page.
7. From the Command Type drop-down list select 1 - adCmdText, which is an ADO-defined constant that specifies that you will supply a textual definition of an SQL command. Notice the other options that indicate that you will specify a table name or a stored procedure name.
8. In the Command Text (SQL) box, type the following command:

 SELECT au_fname,au_lname,state FROM authors 

9. Close the ADO Data Control Properties dialog box.

To bind the DataGrid Control to the ADO Data Control

1. Edit the properties of the IDC_DATAGRID1 DataGrid Control. Select the All page, which displays a list of all available control properties. To see this page, you might have to scroll to the right by using the arrow key to the top right of the Properties dialog box.
2. Click in the Value column of the DataSource property (currently displaying the text <Not bound to a DataSource>). As you click here, a drop-down list control appears. From this list, select the ID of the ADO Data Control: IDC_ADODC1.

You can test your dialog box by pressing CTRL+T or by clicking the light-switch icon to the left of the Dialog toolbar. The dialog box should appear as shown in Figure 7.16, with the selected columns from the authors table displayed in the DataGrid Control. You can use the navigation buttons on the ADO Data Control to move through the recordset, or you can select the DataGrid Control and browse the records using the mouse and the cursor keys.



Figure 7.16 Testing the IDD_VIEWDB_DIALOG dialog box

3. Build and run the ViewDB application to verify that it behaves as expected.

Setting Control Properties from Your Source Code

If you look at the ViewDB project ClassView, you will see that it shows all the classes that were created for your project when you inserted the ADO Data Control and the DataGrid Control. Of particular interest are the CAdodc and the CDataGrid classes, which represent the controls themselves. If you expand these classes in ClassView, you will see that they provide member functions that allow you to get and set the control properties. The names of these functions are simply the names of the properties prefixed by Get or Set. The classes also provide member functions that wrap the methods exposed by the control.

In the following exercise, you will learn how to use these functions from within your application source code to set control properties and to call control methods. You will add two items to the Order By list box control, which will allow the user to specify whether they want to see the records ordered by author last name or by state. When the user changes the selection, the order of the records selected from the data source and the caption of the DataGrid Control will be updated.

To add member variables for the dialog box controls

1. Open ClassWizard. Click the Member Variables tab.
2. Select the CViewDBDialog class. Add the member variables shown in Table 7.4.

Table 7.4 CViewDBDialog Member Variables

Resource ID	Category	Variable type	Variable name
IDC_ADODC1	Control	CAdodc	m_adodc
IDC_DATAGRID1	Control	CDataGrid	m_datagrid
IDC_ORDERLIST	Value	CString	m_lbOrder
IDC_ORDERLIST	Control	CListBox	m_strOrder

3. Click OK to close ClassWizard and save your changes.

To initialize the Order By list box

Locate the CViewDBDlg::OnInitDialog() function. At the bottom of the function, just before the return statement, add the following code:

 m_lbOrder.AddString("By last name"); m_lbOrder.AddString("By state"); m_lbOrder.SetCurSel(0); OnSelchangeOrderlist(); 

This code adds the two items to the list box and sets the current selection to the first item in the list. You will now create the OnSelchangeOrderlist() function to handle the LBN_SELCHANGE control notification message that is fired when the user changes the selection in the list box.

To create the OnSelchangeOrderlist() function

1. Open ClassWizard. Click the Message Maps tab.
2. Select the CViewDBDlg class. Select the IDC_ORDERLIST object ID.
3. Select the LBN_SELCHANGE message. Click Add Function and specify the name OnSelchangeOrderlist.
4. Click Edit Code to edit the function. Replace the // TODO comment with the following code:

 if(m_lbOrder.GetCurSel() == 0) {           m_adodc.SetRecordSource("SELECT au_fname,au_lname,\           state FROM authors ORDER BY au_lname");           m_datagrid.SetCaption("Authors by name"); } else {           m_adodc.SetRecordSource("SELECT au_fname,au_lname,\           state FROM authors ORDER BY state");           m_datagrid.SetCaption("Authors by state"); } m_adodc.Refresh(); 

Note how member functions of the CAdodc and the CDataGrid classes are used to set properties and call methods of the ADO Data Control and the DataGrid Control. The code sets the RecordSource property of the ADO Data Control and the Caption property of the DataGrid Control. The code also calls the Refresh() method of the ADO Data Control.

Build and run the DBView application. Change the selection in the Order By list box. Check that the caption of the DataGrid Control and the order of the records it displays are updated as expected.

Lesson Summary

ADO is designed as an easy-to use application interface to OLE DB. ADO exposes Automation objects that abstract the OLE DB interfaces. This means that scripting languages such as VBScript and JScript as well as desktop application development environments such as Visual C++ and Visual Basic can use it. ADO supports disconnected recordsets that can be edited while disconnected from the data source and passed to other applications.

ADO presents a simple object model that organizes objects in a de-emphasized hierarchy. This means that you create only the objects that you need for your solution. If other objects are needed, they are created implicitly.

The three principal components of the ADO object model are:

• The Connection object makes a connection between your application and an external data source.
• The Command object builds queries, including user-specific parameters, to access records from a data source.
• The Recordset object accesses records returned from an SQL query.

In addition to the three main objects, ADO supports three collections that can provide additional functionality to your applications:

• The Errors collection is attached to the Connection object. Stores provider errors.
• The Parameters collection is attached to the Command object and stores parameters to be passed to a query or stored procedure.
• The Fields collection is attached to the Recordset object and provides access to columns in the recordset.

ADO objects support an additional Properties collection, which contains provider specific information about the object.

The ADO Data Control is a graphical ActiveX control that provides an easy-to-use interface to help you create database applications with a minimum of code. The ADO Data Control creates connections between data-bound controls and data providers. Data-bound controls are ActiveX user-interface controls that can connect to an ADO Data Control and automatically display the data that it retrieves.

The ADO Data Control and a set of data-bound controls are supplied with Visual C++. As with any ActiveX controls, you must insert them into your project before you can use them; use the Components and Controls Gallery to perform this action. Inserting a control enables you to use the dialog editor to place the ActiveX control just like any other control. Inserting a control will also create C++ classes for your project that wrap the Automation interfaces exposed by the control. You can use these classes to get and set the control properties, and to call the methods that the control provides.