📄 ch23.htm
字号:
<HTML>
<HEAD>
<TITLE>Special Edition Using Visual C++ 5 - Chapter 23</TITLE>
<LINK REL="Next" HREF="ch24.htm" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/ch24.htm">
<LINK REL="Previous" HREF="ch22.htm" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/ch22.htm"></HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H2>Chapter 23</H2>
<H2>SQL and the Enterprise Edition</H2>
<hr>
<P>The Enterprise Edition of Visual C++ was developed for those of you who are integrating SQL databases and C++ programs, especially if you use stored procedures. It is sold as a separate edition of the product: you can buy a copy of the Enterprise
Edition instead of the Professional Edition. If you already own a Professional or Subscription Edition, you can upgrade to the Enterprise Edition for a reduced price.</P>
<ul>
<li> <B>SQL</B></P>
<P> SQL, Structured Query Language, allows you to retrieve, update, or add records to or from a relational database.</P>
<li> <B>SQL from C++</B></P>
<P> Calling a SQL stored procedure from C++ is not a difficult task</P>
<li> <B>The DataView</B></P>
<P> Look at and change the structure and contents of your database, build queries on-the-fly, edit and debug stored procedures, all in one powerful new view.</P>
<li> <B>New Database Tools</B></P>
<P> Query Designer, Database Designer, and Database Diagrams are all designed to simplify the job of dealing with database and with SQL.</P>
<li> <B>Microsoft Transaction Server</B></P>
<P> If your database work involves transactions, you need to learn about MTS and what it can do for you.</P>
<li> <B>Visual Source Safe</B></P>
<P> This revision control system is important to any developer, even if your applications don't involve databases at all. It comes with the Enterprise Edition.</P>
</ul>
<H3>Understanding SQL</H3>
<P>Structured Query Language, SQL, is a way to access databases, interactively or in a program, that is designed to read as though it was English. Most SQL statements are queries, requests for information from one or more databases, but it is also
possible to use SQL to add, delete, and change information. As mentioned in <A HREF="index22.htm" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/index22.htm" target="text">Chapter 22</A>, “Database Access,” SQL is an enormous topic. This section reviews the most important SQL commands, so that even if you
haven’t used it before you can understand these examples and see how powerful these tools can be.</P>
<blockquote><p><img src="note.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/note.gif">
<P>The Enterprise Edition comes with a Developer Edition of Microsoft SQL Server 6.5. The SQL Server Books Online included with the product contains an excellent SQL Reference.</P>
<p><img src="bottom.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/bottom.gif"></blockquote>
<P>SQL is used to access a relational database, which contains several <I>tables</I>. A table is made up of rows, and a row is made up of columns. Table 23.1 lists some names used in database research or in some other kinds of databases for tables, rows,
and columns.</P>
<P><I>Table 23.1—Database Terminology</I></P>
<TABLE BORDER>
<TR>
<TD>
<P><B>SQL</B></P>
<TD>
<P><B>Also Known As</B></P>
<TR>
<TD>
<P>Table</P>
<TD>
<P>Entity</P>
<TR>
<TD>
<P>Row</P>
<TD>
<P>Record, Tuple</P>
<TR>
<TD>
<P>Column</P>
<TD>
<P>Field, Attribute</P></TABLE>
<P>Here's a sample SQL statement:</P>
<pre><font color="#008000">SELECT au_fname, au_lname FROM authors</font></pre>
<P>It produces a list of author first and last names from a table called <font color="#008000">authors</font>. (This table is included in the sample <font color="#008000">pubs</font> database that comes with SQL Server that you'll be using in this
chapter.) Here's a far more complicated SQL statement:</P>
<pre><font color="#008000">SELECT item, SUM(amount) total, AVG(amount) average FROM ledger</font></pre>
<pre><font color="#008000"> WHERE action = 'PAID'</font></pre>
<pre><font color="#008000"> GROUP BY item</font></pre>
<pre><font color="#008000">having AVG(amount) > (SELECT avg(amount) FROM ledger</font></pre>
<pre><font color="#008000"> WHERE action = 'PAID')</font></pre>
<P>A SQL statement is put together from keywords, table names, and column names. The keywords include:</P>
<ul>
<li> <font color="#008000">SELECT</font>: returns specific column of the database. Secondary keywords including <font color="#008000">FROM</font>, <font color="#008000">WHERE</font> , <font color="#008000">LIKE</font>, <font color="#008000">NULL</font>,
and <font color="#008000">ORDER BY</font> restrict the search to certain records within each table.</P>
<li> <font color="#008000">DELETE</font>: removes records. The secondary keyword <font color="#008000">WHERE</font> specifies which records to delete.</P>
<li> <font color="#008000">UPDATE</font>: changes the value of columns (specified with <font color="#008000">SET</font>,) in records specified with <font color="#008000">WHERE</font>. Can be combined with a <font color="#008000">SELECT</font>
statement.</P>
<li> <font color="#008000">INSERT</font>: inserts a new record into the database</P>
<li> <font color="#008000">COMMIT</font>: saves any changes you have made to the database</P>
<li> <font color="#008000">ROLLBACK</font>: undoes all your changes back to the most recent <font color="#008000">COMMIT</font></pre>
<li> <font color="#008000">EXEC</font>: calls a stored procedure.</P>
</ul>
<P>Like C++, SQL supports two kinds of comments:</P>
<pre><font color="#008000">/* This comment has begin and end symbols */</font></pre>
<pre><font color="#008000">-- This is a from-here-to-end-of-line comment</font></pre>
<H3>Working with SQL databases from C++</H3>
<P>As you saw in <A HREF="index22.htm" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/index22.htm" target="text">Chapter 22</A>, "Database Access," an ODBC program using <font color="#008000">CDatabase</font> and <font color="#008000">CRecordset</font> can already access a SQL Server database, or any
database that supports SQL queries. What's more, with the <font color="#008000">ExecuteSQL</font> function of <font color="#008000">CDatabase</font>, you can execute any line of SQL at all from within your program. Most of the time, the line of SQL that
you execute is a stored procedure, a collection of SQL statements stored with the database and designed to be executed on-the-fly.
<BR></P>
<P>There are lots of reasons not to hard-code your SQL into your C++ program. The three most compelling are:</P>
<ul>
<li> Reuse</P>
<li> Skill separation</P>
<li> Maintainability</P>
</ul>
<P>Many programmers accessing a SQL database from a C++ application are building on the work of other developers who have been building the database and its stored procedures for years. Copying those procedures into your code would be foolish indeed.
Calling them from within your code lets you build slick user interfaces, simplify Internet access, or take advantage of the speed of C++, while retaining all the power of the stored procedures that have already been written.</P>
<P>Highly skilled professionals are always in demand, and sometimes the demand exceeds the supply. Many companies find it hard to recruit solid C++ programmers and just as hard to recruit experienced database administrators who can learn the structure of
a database and write in SQL. Imagine how hard it would be to find a single individual who can do both. Almost as difficult would be to expect two developers to work on the parts of the program that called SQL from C++. A much better approach is to have the
C++ programmer call well-documented SQL stored procedures, and the SQL developer build those stored procedures and keep the database running smoothly.</P>
<P>Separating the C++ and SQL parts of your application has another benefit: changes to one may not affect the other. For example, a minor C++ that doesn't involve the SQL will compile and link more quickly since the C++ part of the application is a
little bit smaller without the SQL statements in it. And changes to the SQL stored procedure, if they don't involve the parameters to the function or the values it returns, will not take effect without compiling and linking the C++ program.</P>
<P>There is a downside, however. It can be very difficult to track down problems when you are not sure whether they are in the C++ or the SQL part of your program. When one developer is doing both parts, learning two different tools and switching between
them makes the job harder than it would be in a single tool. And the tools available for working with SQL have not had many of the features that Visual C++ has offered C++ programmers.</P>
<P>Now with the Enterprise Edition of Visual C++, you can have the best of both worlds. You can separate your C++ and SQL for reuse and maintenance, but use the editor, syntax coloring, and even the debugger from Visual C++ to work on your SQL stored
procedures.</P>
<H3>Exploring the Publishing Application</H3>
<P>One of the sample databases that comes with SQL Server is called <font color="#008000">pubs</font>. It tracks the sales of books and the royalties paid to their authors. In this chapter you will write a new stored procedure and display the records
returned by it in a simple record view dialog box. SQL Server should be up and running before you start to build the application.</P>
<P><B> Building the Application Shell</B></P>
<P>Bring up Developer Studio and choose <U>F</U>ile, <U>N</U>ew, then click the Projects tab. Select MFC AppWizard (exe), and name the project Publishing, as shown in Figure 23.1. Click OK to start the AppWizard process.</P>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -