vcg09.htm

Visual C++与数据库的连接经典实例

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Query Strategies</B></FONT></CENTER></H4>

<BR>

<P>After you've identified your datasources, you need to implement a <I>query </I><I>strategy</I>. The following sections discuss some of the issues you need to resolve before you commit to a particular strategy to obtain the recordset objects on which to base your decision-support applications.

<BR>

<BR>

<A NAME="E70E52"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Persistent Versus Impersistent Queries</B></FONT></CENTER></H5>

<BR>

<P>Decision-support applications that consist of a fixed feature set are likely candidates for the use of persistent QueryDef objects. You can store QueryDef objects in Access databases only, but if you're planning to roll up data from client-server or mainframe databases, an Access database is the most appropriate database type in which to store the rolled-up data, because you can attach foreign tables to an Access database if you want to.

<BR>

<P>You can pretest your SQL statements by using the MS Query application to create and store QueryDef objects for rollup or direct queries. MS Query's output windows give you a chance to preview the result of your query and to fine-tune the SQL statement that creates the QueryDef.

<BR>

<P>An alternative method of testing your SQL statements is to use Access. Like MS Query, Access can display the results of a given query for you in tabular format. Also, both MS Query and Access permit you to estimate how long a given query will take.

<BR>

<P>Alternatively, you can write the Access SQL statement for a query, then pass the value of the SQL statement as a char * variable to the szSqlStr argument of a call to the SQLPrepare() function in your code. The SQL statements of persistent QueryDef objects are stored after the Access database engine parses and optimizes them.

<BR>

<P>To optimize your application's performance, you need to test both persistent and impersistent versions of your queries. MS Query doesn't offer an exact method of timing different queries; however, manual timing techniques might suffice for large queries.

<BR>

<BR>

<A NAME="E70E53"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Designing Rollup Queries</B></FONT></CENTER></H5>

<BR>

<P><I>Rollup queries</I> are make-table queries that you execute from within a Visual C++ application. Rollup queries use the SQL aggregate SUM() function to total numeric values contained in tables of operational databases. Typically, a rollup query creates a new table with the following fields:

<BR>

<UL>

<LI>Period: One or more fields that identify the range of dates for which the operational data is summed. You can use separate fields for the year and subperiod (quarter, month, or week). You also can combine these two fields with a coding system, such as 1994Q4 (the fourth quarter of 1994), 199412 (December 1994), or 1994W52 (the last week of 1994). As a rule, you'll find that using separate fields for the year and subperiod makes subsequent record selection simpler.

<BR>

<BR>

<LI>Attribute: One or more optional fields that describe an object class or object. Attributes include categories of products, individual products, geographic regions, or persons (individual salespeople, for instance).

<BR>

<BR>

<LI>Value: One or more numeric fields that contain the result of the summation of the values of operational database records for the period. If you use more than one value field in the rollup table, the operational database table must contain each of the fields. For example, you can sum both the Quantity and [Unit Price]*Quantity values of a table containing invoice line items to obtain total units sold and total sales, and then divide total sales by total units sold to obtain average unit price.

<BR>

<BR>

</UL>

<P>The easiest method of developing rollup queries is to create a group of summary make-table SQL QueryDef objects in an Access database with Access. Then you write a simple Visual C++ application to execute the QueryDef objects that you created.

<BR>

<P>If necessary, your Visual C++ application can perform the data summarization. The program shown in Figure 9.3 does just that (this example is simple), summarizing data to create a bar chart of sales by month.

<BR>

<P>The DEC_SUPT.MDB sample database that is included on the CD and that provides the rolled-up data required by the sample decision-support forms in the section &quot;Displaying Detail Data with the WinWidgets HGrid Control&quot; contains several make-table QueryDef objects. An Access SQL statement of the qryMonthlySalesRollup QueryDef object is as follows:

<BR>

<PRE>

<FONT COLOR="#000080">SELECT Format(Orders.[Shipped Date],&quot;yyyy&quot;) AS Year,

    Format(Orders.[Shipped Date],&quot;mm&quot;) AS Month,

    SUM([Order Details].[Unit Price]*[Order Details].Quantity *

    (1-[Order Details].Discount)) AS Sales

    INTO tblSalesRollupMonth

    FROM Orders, [Order Details]

    WHERE Orders.[Order ID]=[Order Details].[Order ID]

    GROUP BY Format(Orders.[Shipped Date],&quot;yyyy&quot;),

    Format(Orders.[Shipped Date],&quot;mm&quot;)

    HAVING Format([Orders].[Shipped Date],&quot;yyyy&quot;))=&quot;1991&quot;;</FONT></PRE>

<P>The Access SQL statement differs from ANSI SQL syntax in the use of the Access SQL Format() function to return parts of dates (in the Year and Month fields) and in the GROUP BY and HAVING clauses. If this query were executed with the SQL pass-through option, you would replace the Format() function with the appropriate ODBC SQL scalar function, YEAR() or MONTH(). The GROUP BY aggregations you use must correspond exactly to the corresponding SELECT descriptors in your SQL statement.

<BR>

<P>The SUM() SQL aggregate function totals the net sale amount, taking into account the discount, if any, offered to the customer on a particular product. The INTO statement identifies the name of the table that is created by the query. The initial GROUP BY criterion that groups orders by the year in which the order was shipped is included in the GROUP BY clause because you might want to specify more than one year in the HAVING clause with an AND operator.

<BR>

<P>If the tblSalesRollupMonth table doesn't exist, the query creates the table. If the tblSalesRollupMonth table exists, the existing table is deleted before the new table is created.

<BR>

<P>Most of the other make-table QueryDef objects in DEC_SUPT.MDB are more complex than the qryMonthlySalesRollup query. You can examine the syntax of each QueryDef object by opening the QueryDef object in the MS Query application.

<BR>

<BR>

<A NAME="E70E54"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Implementing Ad Hoc Queries</B></FONT></CENTER></H5>

<BR>

<P>One of the incentives for purchasing database front-end application generators is that their users can generate their own ad hoc queries against large databases. The intensity of the desire to create ad hoc queries usually is inversely proportional to the individual's position in the corporate hierarchy. In the upper corporate echelons, executives want the click of a single button to deliver the summary information they need. At the operational level, managers and supervisors want the opportunity to choose from a multiplicity of record-selection options.

<BR>

<P>When an unhindered user executes a SELECT * query against large mainframe or client-server databases, it can bring even the highest performance RDBMS to its knees. Accidentally or intentionally returning all of the records in a monster table can cause severe network congestion, at least until the user's RAM and disk swapfile space are exhausted. The worst-case scenario is the accidental creation of a Cartesian product by the omission of a join condition when more than one table is involved in a query. This can create a very large result set. Some RDBMSs detect this condition and refuse to execute the query. Others, such as applications that use the Access database engine, attempt to return every combination of records in the tables.

<BR>

<P>Don't create decision-support applications that let users enter their own SQL SELECT statements against production databases. Use combo boxes or list boxes to restrict the fields to be displayed and to add required WHERE clause record-selection criteria.

<BR>

<P>This chapter concentrates on designing simple decision-support applications that use preprepared queries. Chapter 10, &quot;Creating Your Own Data Access Controls,&quot; describes how to design queries that give users more freedom to define the data they want to summarize or display in detail. Chapter 15, &quot;Designing Online Transaction-Processing Applications,&quot; shows you how to design a general-purpose query tool for generating user-defined queries that won't overtax your RDBMS or local area network.

<BR>

<BR>

<A NAME="E68E55"></A>

<H3 ALIGN=CENTER>

<CENTER>

<FONT SIZE=5 COLOR="#FF0000"><B>Designing the User Interface</B></FONT></CENTER></H3>

<BR>

<P>Microsoft Windows achieved its commercial success because Windows 3.x has a graphic interface that many users prefer to the DOS command-line prompt. Windows applications now dominate the PC software market because they use design elements that, at least in most cases, conform to the Common User Access (CUA) architecture developed by IBM in the 1980s. The CUA specification describes the design and operation of menus and other common control objects, such as check boxes, radio (now &quot;option&quot;) buttons, and message dialog boxes. Standards for the design of Windows applications appear in a Microsoft publication titled <I>The Windows </I><I>Interface: An Application Design Guide</I> (1991), which is available in most large bookstores. The sample applications in this book employ the principles embodied in <I>The Windows Interface.</I>

<BR>

<P>Windows 95's new set of user interface standards must be followed if you wish to have the Windows 95 logo on a Windows 95 product that will be resold. Because the logo can be a valuable selling point, this fact must be taken into consideration when creating your applications.

<BR>

<P>The primary objective of the CUA specification is to create uniformity in the overall appearance and basic operational characteristics of computer applications. CUA principles apply to character-based DOS applications executed on PCs and to mainframe sessions running on 3270 terminals. The user interface of Windows 3.x and Windows 95, Windows NT and OS/2 for PCs, X Window and Motif for UNIX systems, and System 7.x for Macintosh computers conforms in most respects to IBM's basic CUA specification. Thus, if you're accustomed to Microsoft Word for the Macintosh, you can quickly adapt to using Microsoft Word for Windows on the PC.

<BR>

<P>The following sections describe some of the basic requirements of the user interface for database decision-support applications designed for use at the upper-management level. Subsequent chapters in this book provide similar guidance for more flexible decision-support applications and data-entry (transaction-processing) applications.

<BR>

<BR>

<A NAME="E69E164"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Optimizing Application Usability</B></FONT></CENTER></H4>

<BR>

<P>The usability of mainstream Windows applications ultimately determines the products' success in the software market. Feature-list comparisons in product advertising and magazine reviews might influence the purchasing decisions of individual users, but the primary purchasers of Windows applications are large corporations. The objective of these corporate purchasers is to minimize the time and training expenses that are required for their personnel to learn to use the applications effectively. Thus, applications are rated by their usability, a wholly subjective attribute. An application that one user finds intuitive and easy to use might be totally incomprehensible to another.

<BR>

<P>Testing applications for usability is an art, not a science&#151;and it's a primitive art at best. Commercial firms that conduct usability tests on major software products charge $100,000 or more for testing relatively simple Windows applications. Microsoft has invested millions of dollars in usability testing of its Windows applications. It's quite unlikely that the applications you create will undergo commercial usability tests. Instead, your client might simply inform you that he or she doesn't understand how to use your application without reading the manual. When that happens, your application has failed the ultimate usability test.

<BR>

<P>A simple method of determining an application's usability is to talk to your customer support staff. They will often be able to quickly point out shortcomings in the user interface and in other areas of the application.

<BR>

<P>The following sections describe characteristics of applications that achieve high usability ratings and show you how to implement these characteristics in the forms that constitute a simple executive-level decision-support application.

<BR>

<BR>

<A NAME="E70E55"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Striving for Simplicity</B></FONT></CENTER></H5>

<BR>

<P>When you design decision-support applications, your watchword is <I>simplicity</I>. You achieve application simplicity by applying the following rules to your application design:

<BR>

<UL>

<LI>Remember: You're writing the application for the client, not for yourself! Do what the client wants and needs, and put your desires to the side.

<BR>

<BR>

<LI>Don't add features to an application that aren't needed to accomplish the client's fundamental objective. When in doubt, don't implement a feature that isn't in the minimum capabilities list. Wait for the client to request additional features. If you need special features to test the application, hide these features from other users.

<BR>

<BR>

<LI>Don't attempt to display more than one type of information on a single form. For example, don't combine graphs and tabular information on the same form. Instead, hide the graph window and show the window with the tabular data.

<BR>

<BR>

</UL>

<P>These three rules are especially important for executive-summary decision-support applications because top executives are unlikely to be PC power users. A simple, intuitive user interface and a limited feature list are the two primary characteristics of professional-quality executive-summary applications.

<BR>

<P>Figure 9.3 illustrates the first form of a hypothetical executive-summary decision-support application that displays sales information for a one-year period. A toolbar is the application's primary navigation device. This toolbar lets the user make the following choices:

<BR>

<UL>

<LI>Display total corporate sales by month using a line graph.

<BR>

<BR>

<LI>Display total corporate sales by month using a bar chart.

<BR>

<BR>

<LI>Display sales by product for the year to date or for a particular quarter or month in pie chart format.

<BR>

<BR>

<LI>Attach the current object or parts of the object displayed by the form to an e-mail message that requests additional data about a particular element of an object. The graph, chart, or selected cells of a grid control in the window is copied to the clipboard and then pasted into an e-mail message.

<BR>

<BR>

<LI>Create an e-mail message that requests information about, or that makes comments on, the object pasted into a message.

<BR>

<BR>

<LI>Save the current data, messages, and annotations in a file for future reference.

<BR>

<BR>

<LI>Exit the application when finished with the review of the current data.

<BR>

<BR>

</UL>

<P><B><A HREF="09vcg03.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/09vcg03.gif">Figure 9.3. The opening form of an executive-summary decision-support application.</A></B>

<BR>