Beginning Visual C++ 2005 (2006) [eng]-2

Connecting to Data Sources

Sorting Records

When you retrieve data from a database using the SELECT statement, you will often want the records sorted in a particular order. With the previous example, the tables shown are already ordered, but in practice this is not necessarily the case. You might want to see the output of the last example sorted in a different way, depending on the circumstances. At one time, it might be convenient to have the records ordered by Customer ID, and on another occasion perhaps ordered by Quantity within Product ID. The ORDER BY clause added to the SELECT statement will do this for you. For example, you could refine the last SELECT statement by adding an ORDER BY clause:

SELECT * FROM Products,Orders

WHERE Products.[Product ID] = Orders.[Product ID]

ORDER BY [Customer ID];

The result of this is the same records that you obtained with the last example but with the records arranged so that the Customer ID field is in ascending sequence. Because the kind of data stored in a given field is known, the records are ordered according to the data type applicable to the field. In this case the order is alphabetical.

If you wanted to sort on two fields, Customer ID and Product ID say, and you wanted the records arranged in descending sequence, you would write:

SELECT * FROM Products,Orders

WHERE Products.[Product ID] = Orders.[Product ID]

ORDER BY [Customer ID] DESC, Products.[Product ID] DESC;

You must use the qualified name, Products.[Product ID], in the ORDER BY clause to avoid ambiguity, as you do in the WHERE clause. The DESC at keyword the end of each field in the ORDER BY statement specifies descending sequence for the sort operation. There is a complementary keyword, ASC, for ascending sequence, although this is usually omitted because it is the default condition.

This is by no means all there is to SQL or even all there is to the SELECT statement, but it’s enough to get you through the database example that you will write.

Database Suppor t in MFC

You have a choice when you use MFC for database application development, because two principle approaches are supported:

OLE DB	Provides a way to access local and remote databases using COM, also referred to
	as ActiveX. OLE DB is used by ActiveX Data Objects (ADO), which provides an
	efficient way of accessing local and remote databases without the overhead implicit
	in the MFC.
ODBC	Open DataBase Connectivity, better known as ODBC, defines a standard function
	oriented interface for data access supported by a variety of database product
	vendors. I will be using ODBC to illustrate database application techniques in this
	chapter and the next.

929

Chapter 19

To use OLE DB and ADO you need a good, deep knowledge of COM (ActiveX), so I will concentrate on ODBC, for which you just need some insight into SQL. When you are familiar with COM, ADO is well worth investigating if you want to get deep into database applications, as it is much more efficient than using ODBC.

ODBC is a system-independent interface to a database environment that requires an ODBC driver to be provided for each database system from which you want to manipulate data. ODBC defines a set of function calls for database operations that are system-neutral, so using it is essentially function-call oriented. You can use a database with ODBC only if you have the DLL that contains the driver to work with that database application’s file format. The purpose of the driver is to interface the standard set of system-independent calls for database operations that will be used in your program to the specifics of a particular database implementation.

MFC Classes Supporting ODBC

MFC support for ODBC is implemented through five classes:

CDatabase	An object of this class represents a connection to your database. This
	connection must exist before you can carry out any operations on the
	database.
CRecordset	An object of a class derived from this class represents the result of an
	SQL SELECT operation — which is a set of records. The object makes one
	record from the set available at a time and provides functions for you to
	move backwards or forwards through the set.
CRecordView	An object of a class derived from this class is used to display current
	information from an associated recordset object. The view is essentially
	a dialog box, and the CRecordset object uses dialog data exchange
	(DDX) to access the data from the recordset.
CFieldExchange	This class provides for the exchange of data between the database and
	a recordset object. You would use this class directly only if you were
	implementing data exchange for custom data types.
CDBException	Objects of this class represent exceptions that occur within ODBC
	database operations.

You can best understand how database operations with MFC work by creating an example. I’ll explain how you can apply the ODBC approach to accessing a sample database called Northwind Traders. The Northwind Traders database has the merit that it is easy to work with, but also contains a considerable variety of tables that are populated by realistic numbers of records. This gives you a lot of scope for experimentation, as well as providing some feel for how well your code will work in practice. It’s easy to be lulled into a false sense of security by running your program against a test database where the numbers of tables and records within a table are trivial. It can be quite a surprise to find out how long transactions can take in a real-world context. One cautionary note about the Northwind database — you should not regard this as a good example of a database design, particularly with regard to security. It’s very useful as a vehicle for understanding the mechanics of accessing a database.

930

Connecting to Data Sources

To develop and run the examples in this chapter and the next you need to have the Northwind Traders database installed; therefore, you’ll need a database environment on your PC capable of supporting the Northwind database. There are versions available at the time of writing for SQL Server Express, SQL Server 2000, and Microsoft Access. You can locate these by visiting http://www.microsoft.com/ downloads and searching for “Northwind.” You’ll find documentation on how to install the various

versions available on the respective download page. The examples show the use of the Microsoft Access version of the Northwind Traders database, but the code is essentially the same whichever database system you are using.

Creating a Database Application

For the example, I’ll show how to use three related tables in the Northwind database.

In the first step, you’ll create a program to display records from the Products table in the database. You will then add code to allow you to examine all the orders for a given product using two other tables. Finally, you’ll access the Customers table to enable the customer details for an order to be displayed. Before you can start with the code, you need to identify the database to the operating system.

Registering an ODBC Database

Before you can use an ODBC database, it needs to be registered. You do this through the Control Panel that you access from the Windows XP Start menu. In the Control Panel, select the Data Sources (ODBC) icon. You should see the dialog box shown in Figure 19-6.

Figure 19-6

931

Chapter 19

You have the possibility of registering a database as a User DSN accessible only to you, as a System DSN accessible to all users on the machine, or as a File DSN that will be available generally, possibly over a network. I’ll describe how you register the database as a User DSN.

When you then click the Add button, you will see the Create New Data Source dialog box shown in Figure 19-7.

Figure 19-7

Here you must select from the list of ODBC drivers the one that you are going to use — Microsoft Access Driver (*.mdb) (or if you are using SQL Server, the driver for that). This should have been installed automatically with the typical setup when you installed Windows XP. If you don’t see the driver you want, you need to go back to Windows setup to install it. When you have selected the driver, click the Finish button. This takes you to yet another dialog box, as shown in Figure 19-8.

Figure 19-8

932

Connecting to Data Sources

Enter the name of the database file as the Data Source Name, which is typically Northwind. You’ll use this name to identify the database when you generate the application using an Application wizard. You should now click on the Select button to go to the final dialog box, which is the Select Database dialog, in which you can select the file in whichever directory it now sits. This last dialog to select the database is shown in Figure 19-9.

Figure 19-9

Finally, click three successive OK buttons, and you have registered the database. If this is not the same on your PC you will need to resort to Help for your operating system, or just experiment with the ODBC option on the Control Panel. The truth is in there.

When you have succeeded, you can now go ahead with your database application and, as ever, the starting point is the New > Project menu item in Visual C++ 2005, or you can just press Ctrl+Shift+N.

Generating an MFC ODBC Program

Create a new MFC project with the MFC Application template in the usual way and give it a suitable name, such as DBSample. After you click OK, select the Application Type set of options and choose the SDI interface for document support because that will be sufficient for your needs. The document is somewhat incidental to operations in a database application because most things are managed by recordset and record view objects. As you’ll see, the main use of the document is to store recordset objects, so you won’t need more than one document.

Select the Database Support set of options. You have a choice as to whether you include file support with the Database View option. File support refers to serializing the document, which is not normally necessary, because any database input and output that you require is taken care of using the recordset objects in your application. Choose the option without file support, as shown in Figure 19-10.

When you select either of the database options, the other checkboxes, radio buttons, and the Data Source button are activated. Select the ODBC radio button and then click the Data Source button to specify the database that your application is going to use. This displays the dialog box shown in Figure 19-11.

933

Chapter 19

Figure 19-10

Figure 19-11

If the Northwind database was registered as a user database, it appears on the Machine Data Source tab, as in Figure 19-11. When you have selected the database and clicked the OK button, a Login dialog box for the database is displayed. You then can enter the login name and password to open the database. When you click the OK button, you’ll see the dialog box shown in Figure 19-12 in which you have to select the database objects to which you need access.

934

Connecting to Data Sources

Figure 19-12

Expand the Tables node in the dialog and click on the Products table. You could select as many of the tables as you want by clicking on each of the tables with the Ctrl key held down, but here you’ll need only the Products table. Then click the OK button to close the dialog. You have now specified the operation for the recordset class that the Application wizard generates as:

SELECT * FROM Products;

The use of * for all fields is determined by the framework. It just uses the table name or names you chose here to form the SQL operation that is applied for the recordset.

The MFC Application wizard dialog box also shows a choice between Snapshot and Dynaset for the type of recordset your project uses. There is a significant difference between these options, so the next section looks at what they mean.

Snapshot versus Dynaset Recordsets

Your recordset object will provide you with the result of a SELECT operation on the database. In the

case of a snapshot recordset, the query is executed once, and the result is stored in memory. Your recordset object can then make available to you any of the records in the table that result from the query, so a snapshot is essentially static in nature. Any changes that might occur in the database due to other users updating the database are not reflected in the data you obtained with your snapshot recordset. If you need to see changes that may have been made, you’ll need to re-run the SELECT statement.

With the dynaset option, your recordset object automatically refreshes the current record from the database when you move from one record to another in the table generated by the query for the recordset. As a consequence, the record available in the recordset reflects the up-to-date status of the database when you accessed the record, not when you first opened the recordset. Be aware that the refresh occurs only when

935

Chapter 19

your recordset object accesses a record. If the data in the current record is modified by another user, this is not apparent in your recordset object unless you move to another record and then return to the original record. A dynaset recordset uses an index to the database tables involved to generate the contents of each record dynamically. Because you have no other users accessing the Northwind database, you can choose the Snapshot option for your example.

After Snapshot has been chosen, you can click the Generated Classes option to display the classes in your application. The dialog box is shown in Figure 19-13.

Figure 19-13

Here you can change the class names and the corresponding file names assigned by the wizard to something more suitable, if you want. In addition to the changes shown for the CDBSampleView and CProductView classes and the corresponding changes to the names of the .h and .cpp files for the class, you could also change the CDBSampleSet class name to CProductSet, and the associated .h and .cpp file names to be consistent with the class name. After that is done, click Finish and generate the project.

Understanding the Program Structure

The basic structure of the program is as you have seen before, with an Application Class CDBSampleApp, a Frame Window Class CMainFrame, a Document Class CDBSampleDoc, and a view class CProductView. A document template object is responsible for creating and relating the frame window, document, and view objects. This is done in a standard manner in the InitInstance() member of the application object. The document class is standard, except that the MFC Application wizard has added a data member, m_DBSampleSet, which is an object of the CProductSet class type. As a consequence, a recordset object is automatically created when the document object is created in the InitInstance() function member of the application object. The significant departures from a non-database program arise in the detail of the CRecordset class, and in the CRecordView class, so take a look at those.

936

Connecting to Data Sources

Understanding Recordsets

You can look at the definition of the CProductSet class that the Application wizard has generated piecemeal and see how each piece works. I’ll show the bits under discussion as shaded in the code fragments.

Recordset Creation

The first segment of the class definition that is of interest is:

class CProductSet : public CRecordset

{

public:

CProductSet(CDatabase* pDatabase = NULL); DECLARE_DYNAMIC(CProductSet)

//Plus more of the class definition...

//Overrides

// Wizard generated virtual function overrides

public:
virtual CString GetDefaultConnect();	// Default connection string
virtual CString GetDefaultSQL();	// default SQL for Recordset

virtual void DoFieldExchange(CFieldExchange* pFX);// RFX support

// Plus some more standard stuff

};

The class has CRecordset as a base class and provides the functionality for retrieving data from the database. The constructor for the class accepts a pointer to a CDatabase object that is set to NULL as a default. The parameter to the constructor allows a CProductSet object to be created for a CDatabase object that already exists, which allows an existing connection to a database to be reused. Opening a connection to a database is a lengthy business, so it’s advantageous to re-use a database connection when you can.

If no pointer is passed to the constructor, as is the case for the m_DBSampleSet member of the document class CDBSampleDoc, the framework automatically creates a CDatabase object for you and calls the GetDefaultConnect() function member of CProductSet to define the connection. The Application wizard provides the following implementation of this function:

CString CProductSet::GetDefaultConnect()

{

return _T( “DSN=Northwind;

DBQ=D:\\Beg Visual C++ 2005\\Model Access DB\\Northwind.mdb; DriverId=25;

FIL=MS Access; MaxBufferSize=2048; PageTimeout=5; UID=admin;”);

}

937

Chapter 19

The GetDefaultConnect() function is a pure virtual function in the base class, CRecordset, and so must always be implemented in a derived recordset class. The value returned from the function is a single string between double quotes but I have shown it spread over several lines to make the contents of the string more apparent. The implementation provided by Application wizard returns the text string shown to the framework. This identifies the database with its name and path plus values for the other parameters you can see and enables the framework to create a CDatabase object that establishes the database connection automatically. The meaning of the arguments in the connection string is as follows:

Argument	Description
DSN	The data source name.
DBQ	The database qualifier, which in this case is the path to the Access database
	file.
DriverID	The ID of the ODBC driver for the database.
FIL	The database file type.
MaxBufferSize	The maximum size of the buffer to be used for data transfer.
PageTimeout	The length of time in seconds to wait for a connection to the database. It is
	important to set this value to an adequate value to avoid connection failures
	when accessing a remote database.
UID	The user ID for accessing the database.

In practice, it’s usually necessary to supply a password as well as a user ID before access to a database is permitted, and it’s unwise to expose the password in the code in plain text form. For this reason the Application wizard has inserted the following line preceding the definition of the GetDefaultConnect() function:

#error Security Issue: The connection string may contain a password

Compilation fails with this directive in the code, so you must comment it out or delete it to compile the program successfully.

You can make the framework pop up a dialog box for the user to select the database name from the list of registered database sources by writing the return statement in the GetDefaultConnect() function as:

return _T(“ODBC;”);

You will also be prompted for a user ID and password when this is required for access to the database.

Querying the Database

The CProductSet class includes a data member for each field in the Products table. The Application wizard obtains the field names from the database and uses these to name the corresponding data members of the class. They appear in the block of code following the Field/Param Data comment in the CProductSet class definition:

938