ch23.htm

VC使用所有细节的逻列

<A HREF="Xfigs01.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs01.gif"><b>Fig. 23.1</b></A>

<P><I>Start AppWizard in the usual way.</I></P>

<P>In step 1 of AppWizard, choose an SDI application. Click Next to move to step 2 of AppWizard. As shown in Figure 23.2, select the Database view without file support option. Click Data Source to connect a data source to your application.</P>

<A HREF="Xfigs02.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs02.gif"><b>Fig. 23.2</b></A>

<P><I>This application needs database support but will not have a document.</I></P>

<P>Select the ODBC option, and select Local Server from the drop down box next to it, as shown in Figure 23.3. Leave the Recordset type as Snapshot, and click OK to specify the exact data source.</P>

<A HREF="Xfigs03.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs03.gif"><b>Fig. 23.3</b></A>

<P><I>Your data source is a local ODBC database.</I></P>

<P>The SQL Server login dialog appears. Click the Options button to show the enlarged dialog of Figure 23.4. Choose <font color="#008000">pubs</font> from the Database: drop-down box, and enter your login ID and password at the top of the dialog. Click 
OK.</P>

<A HREF="Xfigs04.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs04.gif"><b>Fig. 23.4</b></A>

<P><I>Connect to the sample </I><I><font color="#008000">pubs</font></I><I> database.</I></P>

<P>The Select Database Tables dialog, shown in Figure 23.5, appears. Click on <font color="#008000">dbo.authors</font>, <font color="#008000">dbo.titleauthor</font> and <font color="#008000">dbo.titles</font>. Click OK.</P>

<A HREF="Xfigs05.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs05.gif"><b>Fig. 23.5</b></A>

<P><I>Choose the </I><I><font color="#008000">authors</font></I><I>, </I><I><font color="#008000">titles</font></I><I>, and </I><I><font color="#008000">authortitle</font></I><I> tables.</I></P>

<P>You are back to Step 2 of AppWizard. Click Next to move to Step 3. Choose no support for compound documents or ActiveX controls, and click Next to move to Step 4. Click Next to accept the Step 4 defaults, then Next again to accept the Step 5 defaults. 
On Step 6, click Finish. The New Project Information summary, shown in Figure 23.6, appears. Click OK to create the project.</P>

<A HREF="Xfigs06.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs06.gif"><b>Fig. 23.6</b></A>

<P><I>Confirm your choices are all correct before clicking OK.</I></P>

<P>You have now completed a shell of an application that displays daabase values in a record view, much like the one discussed in <A HREF="index22.htm" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/index22.htm" target="text">Chapter 22</A>, &quot;Database Access.&quot; Nothing you have done so far has been 
specific to the Enterprise Edition. That is about to change.</P>

<P><B> Making a Data Connection</B></P>

<P>The database tables you specified are connected to your record set, but they are not available for use with the SQL features of the Enterprise Edition. You need to make a data connection to connect the database to your application. Follow these steps 
to make the connection:</P>

<ol> 

<li><P> Choose <U>P</U>roject, <U>A</U>dd to Project, <U>N</U>ew</P>

<li><P> Click the Projects tab. </P>

<li><P> As shown in Figure 23.7, select a Database Project, name it PubDB, and select the Add to current workspace radio button. Click OK.</P>

</ol>

<A HREF="Xfigs07.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs07.gif"><b>Fig. 23.7</b></A>

<P><I>Create a subproject within this project.</I></P>

<ol start=4>

<li><P>  The Select Data Source dialog appears. Choose LocalServer, as shown in Figure 23.8, and click OK.</P>

</ol>

<A HREF="Xfigs08.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs08.gif"><b>Fig. 23.8</b></A>

<P><I>Connect to the local server.</I></P>

<ol start=5>

<li><P> The SQL Server Login dialog appears. As before, specify your Login-ID and password, and make sure the <font color="#008000">pubs</font> database is selected. Click OK to complete the data connection.</P>

</ol>

<P>In the Workspace pane on the left of the screen, a new tab has appeared. Figure 23.9 shows the new DataView. Expand the Tables section, and expand Authors to show the columns within the table. Double-click the Authors table and you can actually see 
your data, on the right in Figure 23.9.</P>

<A HREF="Xfigs09.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs09.gif"><b>Fig. 23.9</b></A>

<P><I>The DataView shows you the database structure, and can display your data </I><I>in the working area.</I></P>

<P>Also featured in Figure 23.9 is the Query toolbar, with the following buttons:</P>

<ul>

<li> <B>Show Diagram Pane</B>&#151;toggles the Query designer diagram pane, discussed in the next section</P>

<li> <B>Show Grid Pane</B>&#151;toggles the Query designer grid pane, discussed in the next section</P>

<li> <B>Show SQL Pane</B>&#151;toggles the Query designer SQL pane, discussed in the next section</P>

<li> <B>Show Results Pane</B>&#151;toggles the Query designer results pane, discussed in the next section</P>

<li> <B>Create Select Query</B>&#151;builds a <font color="#008000">SELECT</font> query in the four panes of Query designer</P>

<li> <B>Create Insert Query</B>&#151;builds an <font color="#008000">INSERT</font> query in the four panes of Query designer</P>

<li> <B>Create Update Query</B>&#151;builds an <font color="#008000">UPDATE</font> query in the four panes of Query designer</P>

<li> <B>Create Delete Query</B>&#151;builds a <font color="#008000">DELETE</font> query in the four panes of Query designer</P>

<li> <B>Verify SQL Syntax</B>&#151;checks the syntax of the SQL you have written</P>

<li> <B>Run</B>&#151;executes your SQL</P>

<li> <B>Remove Filter</B>&#151;shows all the records instead of just those that meet the filter specifications</P>

<li> <B>Sort Ascending</B>&#151;displays records from low value of a selected column to high</P>

<li> <B>Sort Descending</B>&#151;displays records from high value of a selected column to low</P>

<li> <B>Properties</B>&#151;displays information about a column or table</P>

</ul>

<P><B>Working with Query Designer</B></P>

<P>When you double-click a table name, such as <font color="#008000">authors</font>, in the DataView to display all the columns and all the records, you are actually executing a simple SQL query, as follows:</P>

<pre><font color="#008000">SELECT authors.* FROM authors</font></pre>

<P>The results of this query appear in the results pane, which by default is the only one of the four Query Designer panes to be displayed. This query was built for you by Query Designer, and means &quot;show all the columns and records of the <font 
color="#008000">authors</font> table.&quot; Figure 23.10 shows the four panes of Query Designer as they appear when you first make the data connection. To see all four panes, use the toolbar buttons to toggle them on. You can adjust the vertical size of 
each pane, but not the horizontal.</P>

<A HREF="Xfigs10.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs10.gif"><b>Fig. 23.10</b></A>

<P><I>The DataView shows you the database structure, and can display your data </I><I>in the working area.</I></P>

<P>To change your query, deselect <font color="#008000">* (All Columns)</font> in the diagram pane (at the top of Figure 23.10) and then select <font color="#008000">au_lname</font>, <font color="#008000">au_fname</font>, and <font 
color="#008000">phone</font>. The values in the results pane go gray to remind you that these are not the results of the query you are now building. As you make these selections in the diagram pane, the other panes update automatically, as shown in Figure 
23.11.</P>

<A HREF="Xfigs11.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs11.gif"><b>Fig. 23.11</b></A>

<P><I>You can build simple queries even if you know no SQL.</I></P>

<P>Highlight phone in the diagram pane and click the Sort Ascending button on the Query toolbar. This will sort the results by phone number. Click the Run button on the Query toolbar to execute the SQL that has been built for you. Figure 23.12 shows what 
you should see, including the new values in the Results pane.</P>

<A HREF="Xfigs12.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs12.gif"><b>Fig. 23.12</b></A>

<P><I>Running your SQL queries is a matter of a single click.</I></P>

<blockquote><p><img src="note.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/note.gif">

<P>After you have been looking at your Results pane for a while, a message like the one in Figure 23.13 will appear. If you don't need the results any more, click No. If you are still looking at them, click Yes. If you click neither (for example if 
another application has focus and you ignore the alert sound that acompanies the message) the results pane will be cleared after another minute. You can get the results back by running the query again.</P>

<p><img src="bottom.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/bottom.gif"></blockquote>

<A HREF="Xfigs13.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs13.gif"><b>Fig. 23.13</b></A>

<P><I>Results take up space and are cleared as soon as possible.</I></P>

<P><B>Stored Procedures</B></P>

<P>The capability to create simple SQL queries quickly, even if your SQL skills are not strong, is an amazing aspect of the Enterprise Edition. But using stored procedures is where the real payoff of this software displays itself.</P>

<P>Collapse the tables section in the DataView, and expand the Stored Procedures section. This shows all the stored procedures that are kept in the database and are available for you to use. Double-click <font color="#008000">reptq2</font> to display the 
procedure, and you should see something like Figure 23.14.</P>

<A HREF="Xfigs14.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs14.gif"><b>Fig. 23.14</b></A>

<P><I>Using the Developer Studio editor to work with SQL means no learning </I><I>curve for you.</I></P>

<P>One thing you probably noticed immediately was the syntax coloring in the editor window. The colors used are:</P>

<ul>

<li> Blue: for keywords such as <font color="#008000">PRINT</font> and <font color="#008000">SELECT</font></pre>

<li> Green: for both styles of comment</P>

<li> Black: for other kinds of text</P>

</ul>

<P>To run a stored procedure, choose <U>T</U>ools, <U>R</U>un, or right-click the stored procedure name in DataView and choose <U>R</U>un, or right-click in the editor and choose <U>R</U>un. The results appear in the Results pane of the Output 
window&#151;don't confuse this with the Results pane of Query Designer. Figure 23.15 shows the Output window stretched very large to show some of the results of <font color="#008000">reptq2</font>.</P>

<A HREF="Xfigs15.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs15.gif"><b>Fig. 23.15</b></A>

<P><I>You can see the results of any stored procedure from within Developer </I><I>Studio.</I></P>

<P>Some stored procedures take parameters. For example, double-click reptq3; its code looks like this:</P>

<pre><font color="#008000">CREATE PROCEDURE reptq3 @lolimit money, @hilimit money,</font></pre>

<pre><font color="#008000">@type char(12)</font></pre>

<pre><font color="#008000">AS</font></pre>

<pre><font color="#008000">select pub_id, type, title_id, price</font></pre>

<pre><font color="#008000">from titles</font></pre>

<pre><font color="#008000">where price &gt;@lolimit AND price &lt;@hilimit AND type = @type </font></pre>

<pre><font color="#008000">      OR type LIKE '%cook%'</font></pre>