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:Estimated lesson time: 30 minutes
- 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.
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.
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:
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.
In addition to the three main objects, ADO supports three collections that can provide additional functionality to your applications:
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.
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:
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.
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:
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:
Figure 7.15 Editing the IDD_VIEWDB_DIALOG template
SELECT au_fname,au_lname,state FROM authors |
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
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.
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 |
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.
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.
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:
In addition to the three main objects, ADO supports three collections that can provide additional functionality to your applications:
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.