DAY 4
Retrieving SQL Data Through a C++ API

Today you will learn how to retrieve data from relational databases by using an API for C++ programs. Using a database API in your C++ programs enables you to combine the strengths of C++ and SQL. When you complete today's work, you will know how to build applications in C++ that have the data- handling power and elegance of SQL.

Today you will:

• Retrieve data, using relational database APIs for C++.
• Work with Microsoft Universal Data Access.
• Understand ActiveX Data Objects (ADO).

Relational Database APIs

On Day 1, "Choosing the Right Database Technology," you learned that relational databases have their own language interpreters and type systems. On Day 3, "Retrieving Data Through Structured Query Language (SQL)," you learned that relational databases use a language called SQL. You also learned that SQL is fundamentally different from C++ (SQL is interpreted, deals only with relational data, has no procedural constructs, and so on).

C++ compilers don't know SQL, and SQL interpreters don't know C++. Therefore, you have to use a relational database API to act as a translator for them.

Relational database APIs provide an interface between C++ and SQL. They offer a way for C++ programs to communicate with relational databases. Database APIs provide a bridge between C++ and SQL by translating between the type system of the database and the type system of C++. They provide a way to pass SQL code to the database to be run by the database interpreter and retrieve the resultset into C++ program variables.

Some database APIs are database specific and are built to work only with a particular database from a particular vendor. Other database APIs try to provide an open interface to multiple databases. ODBC is a database API that tries to provide an API to all relational databases.

OLE DB is supplanting the venerable ODBC. OLE DB is newer, more modern, and more feature-rich. OLE DB encapsulates ODBC functionality for relational databases and also provides access to nonrelational data sources, such as data from spreadsheets, VSAM data (from mainframes), email systems, directory services, and so on.

Microsoft Universal Data Access

OLE DB is the foundation of Microsoft Universal Data Access. With Microsoft Universal Data Access, you can access data through one API, regardless of where the data resides. Your application speaks to a common set of interfaces that generalize the concept of data. Microsoft examined what all types of data have in common and produced an API that can represent data from a variety of sources.

The Microsoft Universal Data Access strategy is based on OLE DB, a low-level C/C++ API designed to enable an individual data store to easily expose its native functionality without necessarily having to make its data look like relational data. The vendor for that particular data store needs simply to provide an OLE DB driver for it. OLE DB drivers are actually called OLE DB providers. As a C++ programmer, you can use the OLE DB API to gain access to any data source for which there is an OLE DB provider.

ActiveX Data Objects (ADO)

ActiveX Data Objects (ADO) is a high-level database API that sits on top of OLE DB. Compared to OLE DB, ADO programming is much simpler. ADO is suitable for scripting languages such as VBScript and JavaScript and for programming languages such as Visual Basic (VB) and Java. You can also call ADO from C++ programs.

ADO provides a dual interface. This dual interface makes it possible to use ADO from scripting languages, such as VBScript and JavaScript, as well as from C++. ADO actually provides two APIs (hence the term dual). One API is provided through OLE Automation for languages that don't use pointers, such as scripting languages. The other API is provided through a vtable interface for C++ programming. You will learn more about COM on Day 10, "Database Client Technologies and the Secrets of ADO," and Day 11, "Multitier Architectures." Now you will jump right into learning ADO.

The programming model in ADO typically consists of a sequence of actions. ADO provides a set of classes that simplifies the process of building this sequence in C++ code.

• Connect to a data source.
• Specify a query of the data source.
• Execute the query.
• Retrieve the data from the query in an object that you can easily access in C++ code.
• If appropriate, update the data source to reflect the edits made to the data.
• Provide a general means to detect errors.

NEW TERM: A recordset is a resultset coupled with a cursor. ADO returns the data from a query in a recordset object. A recordset object encapsulates the data that was retrieved from the database (which is the resultset) as well as functions for moving or navigating through records one at a time (which is the cursor).

Typically, you will employ all the preceding steps in the ADO programming model. However, ADO is flexible enough that you can use just part of the model if you need to. For example, you could create a new recordset object and populate it from your code, without making a connection to a data source or executing any queries. You could even pass that recordset object to other routines or applications.

The first step in accessing a data source with ADO is opening (or connecting to) the data source. With ADO, you can open a data source through its OLE DB provider or through its ODBC driver. You installed the Jet OLE DB Provider on Day 2, "Tools for Database Development in Visual C++ Developer Studio." You will use the Jet OLE DB Provider for the examples in this book.

With ADO, you can connect to ODBC data sources by using the OLE DB provider called MSDASQL. In the Visual C++ setup program, it's called the Microsoft OLEDB ODBC Provider. You can use it with ADO for those data sources that have no OLE DB provider but do have an ODBC driver.

In ADO, you make a connection to a data source by using the ADO Connection object. The ADO Connection object has data members for the OLE DB provider name, data source name, username, password, and so on. The idea is to set the Connection object's data members and call the Open member function to establish and the Close member function to terminate the connection. You use the Connection object to handle transactions, which are often crucial in database applications. You will learn about transactions in Day 6, "Harnessing the Power of Relational Database Servers." You can also use the ADO Connection object's Execute function to send SQL queries to and receive resultsets from the data source.

An alternative to sending queries with the Connection's Execute function is to create an ADO Command object and use its Execute function. The Command object enables more complex queries and commands to be run against the data source. For instance, you could use a Command object to call a stored procedure with parameters in SQL Server. You will learn more about stored procedures on Day 6. A Command can create its own connection to the database or use a reference to an existing Connection object for greater efficiency.

Techniques for Using ADO in C++ Applications

There are a couple of ways to use ADO in your C++ code. You can use the ADO header files and import library from the OLE DB SDK. You include the ADO header files (adoid.h and adoint.h) in your source and add the ADO import library adoid.lib to your linker input. This enables you to create instances of the ADO objects and access their member functions. Using this method, the code to connect to a data source and create a Command object could look something like Listing 4.1. The code in Listing 4.1 doesn't check return values for the sake of code brevity. This is a code snippet only and will not compile as shown.

LISTING 4.1.  USING ADO VIA THE OLE DB SDK

 1:  ADOConnection* piConnection;  2:  ADOCommand* piCommand;  3:  4:  CoCreateInstance(CLSID_CADOConnection, NULL,CLSCTX_INPROC_SERVER,      ÂIID_IADOConnection, (LPVOID *)&piConnection);  5:  6: CoCreateInstance(CLSID_CADOCommand, NULL,CLSCTX_INPROC_SERVER,      ÂIID_IADOCommand, (LPVOID*)&piCommand);  7:  8:  piConnection->Open(L"MyDSN", L"sa", L"bodacious");  9: 10: piCommand->putref_ActiveConnection(piConnection);

Lines 1 and 2 declare pointers to two ADO COM interfaces. Lines 4 and 6 call CoCreateInstance to create instances of the ADO interfaces and assign the pointers to them. (You will learn more about CoCreateInstance and COM interfaces on Day 9, "Understanding COM.") Line 8 uses the Open function of the ADO Connection object to open a connection with an ODBC data source called MyDSN. It uses a username of sa (system administrator) and a password of bodacious. Line 10 calls the ADO Command object's putref_ActiveConnection function to tell it to use the connection that was opened in line 8. Later in your program you will need to call the Release function on piConnection and piCommand to free those objects.

The other way to use ADO in a C++ application is to use the Visual C++ #import directive. Using the #import directive with the ADO library enables you to write less verbose code to accomplish the same tasks with ADO. For instance, with #import, the preceding code can be abbreviated to the code in Listing 4.2. This code doesn't check return values for the sake of code brevity. This is a code snippet only and will not compile as shown.

LISTING 4.2.  USING ADO VIA #IMPORT

 1:  _ConnectionPtr pConnection;  2:  _CommandPtr pCommand;  3:  4:  pConnection.CreateInstance(__uuidof( Connection ));  5:  pCommand.CreateInstance(__uuidof( Command ));  6:  7: pConnection->Open(L"MyDSN", L"sa", L"bodacious");  8:  9:  pCommand->ActiveConnection = pConnection;

In Listing 4.2, lines 1 and 2 define instances of two ADO smart pointer classes. Lines 4 and 5 call the CreateInstance function of those smart pointer classes to create instances of the ADO classes. Line 7 uses the Open function of the ADO Connection object to open a connection with an ODBC data source called MyDSN. It uses a username of sa and a password of bodacious. Line 9 sets the ADO Command object's ActiveConnection data member so that it uses the connection opened in line 7.

Later in your program, you should not call the Release function on pConnection and pCommand to free those objects. pConnection and pCommand are smart pointers, so when they go out of scope, Release will be automatically called. Also, using the #import directive means that you don't need to include the ADO header files (adoid.h and adoint.h) in your source, nor do you need to link with the ADO import library adoid.lib. Article Q169496 in the Microsoft Knowledge Base (KB) provides additional information on using the #import directive with ADO. You can obtain KB articles from the MSDN subscription CDs. Also, you can send email to the KB email server at mshelp@microsoft.com.

As you can see from the code listings, using the #import directive enables you to write code that is less verbose than OLE DB SDK code. Article Q174565 in the Microsoft Knowledge Base compares the processes of using ADO via the OLE DB SDK, via the #import directive, and via the OLE DB SDK, using the MFC OLE classes. That Knowledge Base article recommends using ADO via the #import directive. Based on my personal experience in writing ADO applications, I have found that the #import directive hides some of the complexity of using ADO. Therefore, the ADO examples in this book use ADO via the #import directive.

Article Q174565 in the Microsoft Knowledge Base compares the process of using ADO via the OLE DB SDK, via the #import directive, and via the OLE DB SDK using the MFC OLE classes. That Knowledge Base article also recommends using ADO via the #import directive.

If you are interested in exploring the process of using ADO via the OLE DB SDK, there is a sample application, ADOSDK, on the CD-ROM. It's an MFC application that gives you some idea of what code you need to write in order to use ADO via the OLE DB SDK.

---

NOTE: ADO is a COM DLL. To call ADO functions from your C++ programs, the ADO DLL must be registered, which means that the location of the ADO DLL must be recorded in your machine's registry. When you install the Visual C++ data access components, ADO is automatically installed and registered for you. If you ever need to register ADO manually, at the command line you can run the following:

RegSvr32 msado15.dll

You run RegSvr32 from the directory containing msado15.dll; this directory typically is

\program files\common files\system\ado

---

Building C++ Applications That Use ADO

The best way to learn ADO is to build an application with it. Your application needn't be an MFC application in order to use ADO--MFC is not required for ADO. However, the ADO examples in this book use MFC because MFC provides an application framework that you don't have to build from scratch. Using MFC for the ADO examples enables you to concentrate on learning ADO, not on building an application framework. It's also interesting to see how the ADO objects can map to the objects in the MFC document/view architecture.

The first step is to create a new MFC AppWizard (exe) project in Visual C++: Name it ADOMFC1.

FIGURE 4.1. A new AppWizard exe.

Specify that the application should be a multiple document application, as in Figure 4.2.

FIGURE 4.2. Choose Multiple Documents in Step 1.

Specify that AppWizard include no database or OLE support in the application. You will add that code yourself. Specify whatever you like on the AppWizard options for toolbars, status bars, and so on. In AppWizard's last step (step 6 of 6, shown in Figure 4.3), make sure that the View class derives from CListView instead of CView.

Let AppWizard generate the project and the source code. Run the application to make sure it builds successfully with no errors or warnings.

FIGURE 4.3. Derive the View class from CListView.

As mentioned earlier, the ADO library is a COM DLL. This means applications that use it must initialize the OLE/COM libraries before making any ADO calls. In your MFC application, the call to initialize the OLE/COM libraries is best done in the InitInstance function of the application class.

LISTING 4.3.  INITIALIZING THE OLE/COM LIBRARIES

 1:  BOOL CADOMFC1App::InitInstance()  2:  {  3:      // Add this function to initialize the OLE/COM libraries  4: AfxOleInit();

Add the code shown in line 4 to initialize the OLE/COM libraries every time the application is loaded. Listing 4.4 shows some additions to StdAfx.h for ADO.

LISTING 4.4.  CHANGES TO STDAFX.H

 1:  #include <comdef.h>  2:  3:  #import "C:\program files\common files\system\ado\msado15.dll" \  4:           no_namespace \  5: rename( "EOF", "adoEOF" )

The code in Listing 4.4 can be added to the end of the StdAfx.h file. The most important thing is to not place the code inside the brackets in StdAfx.h that mark the autogenerated code. Line 1 includes a header file that enables your application to use some special COM support classes in Visual C++. These classes make it easier to work with OLE Automation data types, which are the data types ADO uses. Lines 3, 4, and 5 use the #import directive to import the ADO library class declarations into your application.

As mentioned earlier, ADO is a COM DLL and provides dual interfaces. The declarations of the ADO classes are stored as a resource in the ADO DLL (msado15.dll) inside what is called a Type Library. The Type Library describes the automation interface as well as the COM vtable interface for use with C++. When you use the #import directive, at runtime Visual C++ reads the Type Library from the DLL and creates a set of C++ header files from it. These header files have .tli and .tlh extensions and are stored in the build directory. The ADO classes that you call from your C++ code are declared in these files.

Line 4 in Listing 4.4 specifies that no namespace is to be used for the ADO objects. In some applications, it might be necessary to use a namespace because of a naming collision between objects in the application and objects in ADO. You can specify a namespace by changing line 4 to look like the following:

rename_namespace("AdoNS")

Specifying a namespace for ADO enables you to scope the ADO objects using the namespace, like this:

AdoNS::ADO_Object_Name

Line 5 renames EOF (end of file) in ADO to adoEOF so that it won't conflict with other libraries that define their own EOF. Microsoft Knowledge Base article Q169496 provides further information on this topic, if you need it.

Run your application to make sure it builds successfully with no errors or warnings. After the build, you should see the TLI and TLH files in the build directory. They are the header files that the compiler created from the Type Library in msado15.dll. Feel free to have a look at them. They declare the ADO classes you can call from your code.

As mentioned earlier, the typical ADO programming sequence starts with making a connection to the database. A single ADO Connection object is normally shared and reused by multiple instances of other ADO objects.

This is very similar to the way an MFC Document class is used in an MFC application. Therefore, it makes sense to place an ADO Connection object in the Document class of an MFC application. When a document is opened in OnNewDocument, you can call the ADO Connection object's Open function to connect to the data source. In OnCloseDocument, you can call the Connection object's Close function to close the connection to the data source.

In an MFC application, the Document object is easy to access from the other objects (particularly the MFC View objects). By placing the ADO Connection object within that Document object, you create a connection to the data source, which you can share and re-use and which automatically opens and closes as the document(s) opens and closes.

Declare a pointer to an ADO Connection object in your Document class's header file as a public member of the Document class. You also need to add a data member that you will use to indicate whether the connection is open.

LISTING 4.5.  CHANGES TO THE DOCUMENT HEADER FILE

 1:  class CADOMFC1Doc : public CDocument  2:  {  3:  // Attributes  4:  public:  5:      BOOL m_IsConnectionOpen;  6: _ConnectionPtr m_pConnection;

After making the additions shown in lines 5 and 6, your application should still build with no errors or warnings. In your Document class constructor, initialize the m_IsConnectionOpen member to FALSE, like this:

m_IsConnectionOpen = FALSE;

Open the connection to the data source in the Document class OnNewDocument function.

LISTING 4.6.  ONNEWDOCUMENT

 1:  BOOL CADOMFC1Doc::OnNewDocument()  2:  {  3:    if (!CDocument::OnNewDocument())  4:      return FALSE;  5:  6:    HRESULT hr;  7:    hr = m_pConnection.CreateInstance( __uuidof( Connection ) );  8:    if (SUCCEEDED(hr))  9:    { 10:      hr = m_pConnection->Open( 11:      _bstr_t(L"Provider=Microsoft.Jet.OLEDB.3.51;                  ÂData Source=c:\\tysdbvc\\vcdb.mdb;"), 12:      _bstr_t(L""), 13: _bstr_t(L""), 14:      adModeUnknown); 15:      if (SUCCEEDED(hr)) 16:      { 17:        m_IsConnectionOpen = TRUE; 18:      } 19:    } 20: 21:    return TRUE; 22: }

Line 6 defines an HRESULT variable, hr. ADO functions (and COM functions, in general) return HRESULTs. Macros help you decode the meaning of an HRESULT. The SUCCEEDED macro on line 8 is a good example. Line 7 calls the _ConnectionPtr class CreateInstance function to create an instance of an ADO Connection object. If that succeeds, lines 10-14 call the Open function to actually make a connection to a data source. Line 11 is location specific, so you need to verify that it matches the location of the file on your machine. If it succeeds, line 17 sets m_IsConnectionOpen to TRUE.

Lines 11-13 create (temporary) instances of the _bstr_t class, which are passed as parameters to the Open function. As you know, _bstr_t(L"") calls the _bstr_t class constructor to create a temporary instance of _bstr_t. The L in front of the quote marks makes the string that is passed to the _bstr_t constructor a wide-character string.

The _bstr_t class is one of the COM support classes included with Visual C++. It is declared in comdef.h, which you included in StdAfx.h (see Listing 4.4, line 1). _bstr_t encapsulates the BSTR data type, which COM and ADO use to pass strings in function calls. You will learn more about COM, _bstr_t, and BSTR later. For now, know that _bstr_t makes it easier to use BSTR from C++ (using BSTR in C++ without the _bstr_t class is somewhat involved). You can pass instances of _bstr_t as arguments to ADO functions that require BSTR.

Line 11 is where you tell ADO which OLE DB provider to use and how to find the database file. The Provider= portion of the string specifies that you want to use the Jet OLE DB Provider. The Data Source= portion of the string specifies the location of the file.

Line 12 specifies the username for logging on to the database. The sample database vcdb.mdb has no users defined, so it can be an empty string. Line 13 specifies the password for logging on to the database. Again, it can be empty because none are defined in vcdb.mdb.

Now that you have the code to open a connection to the database when the document opens, you need to add the code to close the connection when the document closes in the Document class OnCloseDocument function. You can run ClassWizard to do the work of adding the function to the class (see Figure 4.4).

FIGURE 4.4. Using ClassWizard to override OnCloseDocument.

Specify the Document class, highlight the OnCloseDocument function, and press the Add Function button. Then highlight the OnCloseDocument function in the Member Functions list box and press the Edit Code button.

LISTING 4.7.  ONCLOSEDOCUMENT

 1:  void CADOMFC1Doc::OnCloseDocument()  2:  {  3:    if (m_IsConnectionOpen)  4:    {  5: m_IsConnectionOpen = FALSE;  6:      m_pConnection->Close();  7:    }  8:  9:    CDocument::OnCloseDocument(); 10:  }

Add the code so that your OnCloseDocument function looks like Listing 4.7. Line 3 looks at m_IsConnectionOpen to see whether the connection is open. If it is, line 5 sets the flag to FALSE and line 6 closes the connection. After you add this code, your application will build without any errors or warnings.

Before running your application, however, you add some error handling code. One of the peculiarities of using ADO with #import is that, rather than return errors from functions, ADO throws exceptions . This means that you must add exception-handling code where you use ADO, or your application will die an ugly death every time ADO encounters an error condition at runtime and throws an exception.

Exception Handling for ADO

The exception-handling code for ADO is easy to add to your code. Add try...catch blocks where your code calls ADO functions, as in Listing 4.8.

LISTING 4.8.  ONNEWDOCUMENT WITH EXCEPTION HANDLING

 1:  BOOL CADOMFC1Doc::OnNewDocument()  2:  {  3:    if (!CDocument::OnNewDocument())  4:      return FALSE;  5:  6:    HRESULT hr;  7:  8:    try  9:    { 10:      hr = m_pConnection.CreateInstance( __uuidof( Connection ) ); 11:      if (SUCCEEDED(hr)) 12:      { 13:        hr = m_pConnection->Open( 14: _bstr_t(L"Provider=Microsoft.Jet.OLEDB.3.51;                    ÂData Source=c:\\tysdbvc\\vcdb.mdb;"), 15:        _bstr_t(L""), 16:        _bstr_t(L""), 17:        adModeUnknown); 18:        if (SUCCEEDED(hr)) 19: 20:          m_IsConnectionOpen = TRUE; 21:        } 22:      } 23:    } 24:    catch( _com_error &e ) 25:    { 26:      // Get info from _com_error 27:      _bstr_t bstrSource(e.Source()); 28:      _bstr_t bstrDescription(e.Description()); 29: TRACE( "Exception thrown for classes generated by #import" ); 30:      TRACE( "\tCode = %08lx\n", e.Error()); 31:      TRACE( "\tCode meaning = %s\n", e.ErrorMessage()); 32:      TRACE( "\tSource = %s\n", (LPCTSTR) bstrSource); 33:      TRACE( "\tDescription = %s\n", (LPCTSTR) bstrDescription); 34:    } 35:    catch(...) 36:    { 37:      TRACE( "*** Unhandled Exception ***" ); 38:    } 39: 40:    return TRUE; 41: }

Add the try and the open brace in lines 8 and 9. Add the close brace and the catch blocks in lines 23-38. The catch in line 24 catches _com_error exceptions, which is the type that ADO throws. _com_error is another of the COM support classes declared in comdef.h.

If an error occurs at runtime--for example, if the mdb database file doesn't exist at the location specified in the Open function, the Jet OLE DB Provider will create the _com_error object and fill its members with information describing the error. The ADO Open function will throw the _com_error object. Lines 27-33 catch the _com_error object, retrieve that error information, and display it in TRACE output to the debug window in Visual C++. Without catching this exception and displaying the error information, tracking down the error would be nearly impossible. With this code to catch the exception, you can track down errors quite easily. Add a similar try...catch block to the OnCloseDocument function as well.

Make sure your application builds with no errors or warnings; then run the application in debug mode to see whether you can successfully connect to the database. You can do this by setting three breakpoints. Set one breakpoint on the CreateInstance call in OnNewDocument. Set another on a line inside the catch block in OnNewDocument. Set the third in OnCloseDocument on the if statement that tests whether m_IsConnectionOpen is TRUE. When you reach a break point, single step to see what the code does. You should create an instance of an ADO connection and open the connection when the application loads. When you close the application, the ADO connection will close as well. If there are errors, you should see some indications of what caused the errors in your TRACE statements in the debug window in Visual Studio.

Displaying Records in a List Control

When your application can connect to the database, the next step is to display records in a list control in your application. One easy way to create a list control is to use the ClistCtrlEx class that is included with the DAOVIEW example in Visual C++. To use the ClistCtrlEx class, copy three files--CtrlExt.cpp, CtrlExt.h, and CtrlExt.Inl--from the DAOVIEW example into the source code directory for your application. Add CtrlExt.cpp to the list of source code files and add CtrlExt.h and CtrlExt.Inl to the list of header files in your project. Make the additions to StdAfx.h shown in Listing 4.9.

LISTING 4.9.  MORE CHANGES TO STDAFX.H

 1:  #include <comdef.h>  2:  3:  #import "C:\program files\common files\system\ado\msado15.dll" \  4:           no_namespace \  5:           rename( "EOF", "adoEOF" )  6:  #include <afxcmn.h>  // if not already included  7:  #include <afxcview.h>  8: #include "ctrlext.h"

Lines 1-5 show the code you added previously. Add the include files shown in lines 6-8. Line 6 (afxcmn.h) might already be included, depending on the options you chose in AppWizard. These include files are required for the ClistCtrlEx class. After adding this code, your application should build without any errors or warnings.

Use ClassWizard to override the OnCreate function in your application's View class.

FIGURE 4.5. Using ClassWizard to override OnCreate.

Specify the View class, highlight the WM_CREATE message, and press the Add Function button. Then highlight the OnCreate function in the Member Functions list box and press the Edit Code button.

LISTING 4.10.  OVERRIDING THE VIEW'S ONCREATE

 1:  int CADOMFC1View::OnCreate(LPCREATESTRUCT lpCreateStruct)  2:  {  3:    lpCreateStruct->style |= LVS_REPORT;  4:    if (CListView::OnCreate(lpCreateStruct) == -1)  5:      return -1;  6:  7:    return 0;  8: }

Listing 4.10 shows how the OnCreate function should look. The only code you need to add is line 3. All the other code is already there. Line 3 gives the list control a LVS_REPORT style, which means that the list control will display its columns.

Add some code to the OnInitialUpdate function of the View class. The OnInitialUpdate function already exists in your code; AppWizard put it there when you specified that the View class should derive from CListView. Add code like that shown in Listing 4.11.

Listing 4.11.  ONINITIALUPDATE

 1:  void CADOMFC1View::OnInitialUpdate()  2:  {  3:  CListView::OnInitialUpdate();  4:  5:  CListCtrlEx& ctlList = (CListCtrlEx&) GetListCtrl();  6:  7:  ctlList.AddColumn("  First Test Column  ",0);  8:  ctlList.AddColumn("  Second Test Column  ",1);  9:  ctlList.AddColumn("  Third Test Column  ",3); 10: 11:  ctlList.AddItem(0,0,"First Test Row"); 12:  ctlList.AddItem(1,0,"Second Test Row"); 13: }

Lines 7-12 use the AddColumn and AddItem functions from the ClistCtrlEx class declared in CtrlExt.h. The AddColumn and AddItem functions in the ClistCtrlEx class make it very easy to add columns and rows to a list control.

Your application should build successfully without any errors or warnings. When you run the application, it will look like Figure 4.6.

FIGURE 4.6. The application with a list control.

Querying Records from the Database

You will now add code to execute a query and display the results in the list control every time you right-click the View.

Use ClassWizard to override the WM_RBUTTONDOWN message. In the OnRButtonDown function, add the code shown in Listing 4.12.

LISTING 4.12.  ONRBUTTONDOWN

 1:  void CADOMFC1View::OnRButtonDown(UINT nFlags, CPoint point)  2:  {  3:    _RecordsetPtr pRecordSet;  4:    CADOMFC1Doc * pDoc;  5:    pDoc = GetDocument();  6:  7:    _bstr_t bstrQuery("SELECT * FROM Customers");  8:    _variant_t vRecsAffected(0L);  9: 10:    try 11:    { 12:      pRecordSet = pDoc->m_pConnection->Execute(bstrQuery, Â&vRecsAffected, 13: adOptionUnspecified); 14:      if (!pRecordSet->GetadoEOF()) 15:      { 16:        CListCtrlEx& ctlList = (CListCtrlEx&) GetListCtrl(); 17:        ctlList.DeleteAllItems(); 18:        while(ctlList.DeleteColumn(0)); 19: 20: ctlList.AddColumn("  First Name  ",0); 21:        ctlList.AddColumn("  Last Name   ",1); 22: 23:        int i = 0; 24:        _variant_t vFirstName; 25:        _variant_t vLastName; 26:        while (!pRecordSet->GetadoEOF()) 27:        { 28:          vFirstName = pRecordSet->GetCollect(L"CustFirstName"); 29:          ctlList.AddItem(i,0,(_bstr_t) vFirstName); 30:          vLastName = pRecordSet->GetCollect(L"CustLastName"); 31: ctlList.AddItem(i,1,(_bstr_t) vLastName); 32: i++; 33:          pRecordSet->MoveNext(); 34:        } 35:      } 36: 37:      pRecordSet->Close(); 38:    } 39:    catch( _com_error &e ) 40:    { 41:      // Get info from _com_error 42: _bstr_t bstrSource(e.Source()); 43:      _bstr_t bstrDescription(e.Description()); 44:      TRACE( "Exception thrown for classes generated by #import" ); 45:      TRACE( "\tCode = %08lx\n", e.Error()); 46:      TRACE( "\tCode meaning = %s\n", e.ErrorMessage()); 47:      TRACE( "\tSource = %s\n", (LPCTSTR) bstrSource); 48:      TRACE( "\tDescription = %s\n", (LPCTSTR) bstrDescription); 49:    } 50:    catch(...) 51:    { 52:      TRACE( "*** Unhandled Exception ***" ); 53:    } 54: 55:    CListView::OnRButtonDown(nFlags, point); 56: }

Line 3 defines a smart pointer to a Recordset class. Lines 4 and 5 define and initialize a pointer to the MFC Document. Line 6 constructs a _bstr_t that contains the SQL query string to select records from the Customers table. Line 7 constructs a _variant_t that is passed as a parameter to the Connection's Execute function in line 12. This variable is used to show how many records were affected. The Jet OLE DB Provider apparently does not use this variable. Other OLE DB providers might use it. In any case, you are required to pass the address of the variable as a parameter to the Execute function.

The _variant_t class is another of the COM helper classes declared in comdef.h. The _variant_t class encapsulates the OLE Automation VARIANT data type. Using the _variant_t class is much easier than trying to use the VARIANT data type directly in C++. See the explanation of lines 28-31 for examples of using the _variant_t class.

Line 12 (and line 13) calls the Connection object's Execute function through the MFC Document object. The Execute function returns a Recordset object that contains the resultset from the query and a cursor pointing to the first record. Line 14 tests for an EOF file condition of the cursor in the Recordset. An EOF condition immediately after the Recordset object is created indicates that the query returned no records. If the query did return some records, the cursor would be positioned at the first record in the Recordset.

If the query returned records, lines 17-21 delete the existing rows and columns from the list control and add a last name column and a first name column.

Line 26 starts a while loop that tests for the EOF condition in the cursor of the Recordset. Line 33 calls the MoveNext function in the Recordset to scroll the cursor forward one record at a time. The loop executes until the cursor moves beyond the last record in the Recordset.

Lines 24 and 25 create two instances of _variant_t, for the customer first name and last name. Lines 28 and 30 call the GetCollect function from the Recordset class. The field name from the table in the database is passed as a parameter to the GetCollect function. The GetCollect function returns a VARIANT containing the contents of the field in the current record.

The current record is the record on which the cursor is currently positioned.

Lines 28 and 30 store the VARIANT data from the field in the instances of _variant_t that were created in lines 24 and 25. Lines 29 and 31 cast these _variant_ts as _bstr_ts and pass them to the list control's AddItem function to display their contents in the window.

The _variant_t class is very handy for dealing with data from fields in a database. You can simply cast the contents of a _variant_t, whether it is numeric or string or date/time data, to C++ and COM data types. See the Visual C++ documentation under "_variant_t Extractors" for further information.

There you have it. Your application is now capable of displaying data from the database in a list control. Run the application and right-click the View window. The list control will display the contents of first and last name fields in the Customers table.

Summary

To access a database that has a different type system from C++ and has its own language interpreter, it's necessary to use a database API. Several database APIs are available to C++ developers.

The future of all data access in Microsoft Windows is OLE DB. The easiest way to use OLE DB is to use ADO. ADO provides an object model that encapsulates the process of communicating with databases from within C++ programs as well as from other programming languages.

Q&A

Q What is the best database API to use for my application?

A The database API you choose depends on the particular database you have chosen for your application. If you are using a popular relational database, the most modern and robust APIs are OLE DB and ADO.

Q I've heard that ODBC and OLE DB are slow. Shouldn't I use my database's native API?

A The speed of the drivers for ODBC or OLE DB depends largely on the quality of the vendor's implementation. For at least a couple of the popular databases, namely SQL Server and Access, ODBC and OLE DB are highly optimized.

Q Where do I find an OLE DB provider for my database?

A You should check with your database vendor. If the vendor doesn't offer an OLE DB provider, it might know of a third party that does. You can also check the Microsoft Web site for a list of available OLE DB providers.

Q Using these _variant_ts and _bstr_ts looks a little weird. Isn't there an API that lets me use C++ data types?

A Databases typically don't use C++ data types. It's the job of the database API to translate between these data types. The _variant_t and _bstr_t classes provide great functionality for translating between database types and C++ types, so don't be afraid of them.

Workshop

The Workshop quiz questions test your understanding of today's material. (The answers appear in Appendix F, "Answers.") The exercises encourage you to apply the information you learned today to real-life situations.

Quiz

1. What does a database API do?

2. What database APIs work with nonrelational data sources?

3. What does an ADO Connection object do?

4. What does the current record mean?

Exercises

1. Change the code in Listing 4.12 so that the customers are sorted by last name.

2. Change the code in Listing 4.12 to display the customer number as well as the customer first and last name.