Teach Yourself Database Programming
with Visual C++ 6 in 21 days
Database servers have the capacity to process huge amounts of data at the server while requiring very little processing from client applications. This enables you to use database servers as backend data-processing engines for large client/server applications and for database-driven Web sites.
When you complete today's work, you will understand how to use relational database servers as backend data-processing engines.
Today you will
In a conventional C++ application, all the logic for the application is implemented in C++ code, and at runtime a single process executes the code. If the database for the application is merely a file on disk, the database is basically inert. The database has no life of its own because all the logic and processing are done inside the application's C++ code.
This is a classic single-tier system. The application consists of one process that does all the work. (As you know, a process in Win32 is one instance of a running program.)
In a multitier system, the application consists of more than one process. These processes run simultaneously and cooperate with each other to accomplish the work of the application. The processes can be running on different machines. Each process typically has certain tasks that it is optimized to perform. The different processes in the application are organized into tiers, based on the type of tasks they perform and the machine on which they are running.
A relational database server is a process that is optimized to handle large amounts of data and can do this without any help from the other processes in the application.
Keep in mind that in a multitier application, the database server is not inert. The database itself has logic and processing power. You can write code that is executed by the database. The trick is to build your database code so that the database fulfills the task of handling large amounts of data without burdening the other processes in the application.
To avoid burdening other processes in the application, the database server code must do all the heavy lifting when it comes to processing records from the database. The database code must reduce records from the database into information, information that has been processed and summarized into bite-size pieces that the other processes in the application can easily swallow. To accomplish this, you must learn how to process data at the server.
The programs you have thus far created for this book have processed records one at a time. Relational database servers are capable of processing many records at a time. In fact, they are built to do just that. Using a relational database to process one record at a time is like using a dump truck to haul one shovelful of dirt at a time. You can do it, but it's wasteful.
You need to know how to do two things to create multitier applications that take advantage of relational database servers. You need to learn how to use SQL statements that process more than one record at a time and how to call those SQL statements from within a C++ program in such a way that the database server (instead of your C++ program) does the heavy lifting of the data.
Yesterday you learned how to use the SQL INSERT, UPDATE, and DELETE statements. The SQL statements you worked with yesterday added, modified, and deleted single records.
In your work yesterday, the SQL INSERT, UPDATE, and DELETE statements were underutilized. A single INSERT, UPDATE, or DELETE statement needn't be limited to one record. The statement can affect literally millions of records in the database, and the database will execute these statements without any need for help or intervention from other processes.
As you will recall, the SQL INSERT statement enables you to add records to the database. The basic syntax to add a single record looks like this:
INSERT INTO which table( list of columns ) VALUES( list of values )
However, you can extend this syntax to add multiple records by using one INSERT statement. You can add multiple records to a table by replacing the VALUES clause with a SELECT statement that selects multiple records, like this:
INSERT INTO which table( list of columns ) SELECT * FROM which table
You can use this technique yourself in the VCDb.mdb database. Open your ADOMFC1 project, select the Data View, and open a Query window on the Orders table. You will see that three orders are recorded in the table. Let's say a customer named Bill Clinton wants to order all the 8-track tapes the company sells. (Remember that in the sample application from Day 1, "Choosing the Right Database Technology," you are writing a C++ program that salespeople use to record these orders.)
As you know, the products are listed in the Products table. Open a Query window on the Products table, and you will see that the company sells two 8-track tapes. You added a third 8-track yesterday when you learned about the SQL INSERT statement. You also removed it when you learned about the DELETE statement. To make this exercise more interesting, again add the third 8-track into the Products table by using the following INSERT statement:
INSERT INTO Products (partnumber, productname, price)
VALUES('8TRACK-003', 'Bell Bottoms and Bass Guitars', 29.95)
Mr. Clinton wants to order all the 8-tracks the company sells. That means that to record the order, you need to insert three records into the Orders table. You could issue three INSERT statements, one for each record. The first INSERT statement would look like Listing 6.1.
Listing 6.1. The INSERT Statement to Add a Single
Order Record
1: INSERT INTO Orders (ordernumber, orderdate,
2: customernumber, partnumber, price,
3: shippingandhandling, paymentmethod)
4: VALUES (4, { d '1998-11-16' }, 5, '8TRACK-001',
5: 19.95, 4, 'MC 1223 9873 2028 8374 9/99')
You would need to issue two more INSERT statements, one for each additional 8-track. Issuing these INSERT statements from your C++ program (using ADO) would mean three calls from your program into the database. If your C++ program and the database were running on different machines, it would require three network round trips between machines just to add one order.
For your application to be efficient, the database needs to do the bulk of the work with the data. There needs to be some way to add an order for all the 8-tracks by using just one call from your C++ program: You use a single INSERT statement to add multiple records. To do this, you must replace the VALUES clause of the INSERT statement with a SELECT statement.
First, you need to create a SELECT statement that produces output that can be inserted into the Orders table. The output from the SELECT must produce data that matches the fields in the Orders table. The SELECT statement in Listing 6.2 does this.
Listing 6.2. The SELECT Statement That Matches Order
Records
1: SELECT 4, { d '1998-11-16' }, 5, PartNumber, Price, 4,
2: 'MC 1223 9873 2028 8374 9/99'
3: FROM Products
4: WHERE (PartNumber LIKE '8TRACK%')
In the Data View, click the plus sign next to the Orders table so that you can see its fields and can compare them with the SELECT statement in Listing 6.2. The SELECT statement begins in line 1 by selecting 4, which will be the order number for this order. The 4 is hard-coded into the SELECT statement, so every record that the SELECT produces will begin with a numeric 4. The next value the SELECT produces is a date of November 16, 1998. This is followed by a 5, which is Mr. Clinton's customer number. Then it selects the PartNumber and Price fields (you can see in the FROM clause in line 3 that these fields come from the Products table). Last, the SELECT statement produces another numeric 4 (for the ShippingAndHandling field in Orders) and a credit card number (for the PaymentMethod field in Orders).
The WHERE clause in line 4 uses the LIKE keyword and the % wildcard character to find all product records that have a part number that begins with "8TRACK". Issue this SELECT statement against the Products table, and it will return three records.
You now have a SELECT statement that produces output that can be inserted into the Orders table. You can use this SELECT statement inside an INSERT statement to add an order for all the 8-tracks the company sells, as in Listing 6.3.
Listing 6.3. The INSERT Statement with SELECT for
Adding Multiple Records
1: INSERT INTO Orders (ordernumber, orderdate, customernumber,
partnumber,
2: price, shippingandhandling, paymentmethod)
3: SELECT 4, { d '1998-11-16' }, 5, PartNumber, Price, 4,
4: 'MC 1223 9873 2028 8374 9/99'
5: FROM Products
6: WHERE (PartNumber LIKE '8TRACK%')
Issue this statement from a Query window; then open the Orders table and see that it added three records.
Now you have a single SQL statement that adds multiple records. Your C++ program no longer needs to issue three INSERT statements to the database to add this order. Your C++ program can send this single INSERT statement to add all three records. The database is handling all the processing of the data, with only one call from the client application.
This is the essence of multitier database application development. The idea is to take advantage of the power of the relational database servers to reduce the number of round trips between the client and server processes.
The SQL UPDATE statements you wrote yesterday updated only one record. As you might have guessed, though, the UPDATE statement can update multiple records in a single call. The syntax for doing this is straightforward. As you recall, the syntax for UPDATE is
UPDATE which table SET which field = new value, which field = new value ... WHERE condition
Suppose you need to change the price of all the 8-tracks in the database. Despite sales to Mr. Travolta and Mr. Clinton, the company is not selling enough 8-track tapes. To spur demand, management has decided to reduce the price of each 8-track by $10.
As you know, there are three 8-track records in the Products table. You could issue three UPDATE statements, or you could issue a statement like the following:
UPDATE products SET price = (price - 10) WHERE (PartNumber LIKE '8TRACK%')
This statement will update every record where the PartNumber field starts with "8TRACK", replacing the Price field with $10 less than the current price listed in that record. Execute this statement to make sure it works the way you would expect.
Now suppose, even after the price decrease, that sales of the 8-tracks still aren't sufficient. Therefore, the company has decided to discontinue selling 8-tracks.
You must delete the three 8-track records from the Products table. Should you issue three DELETE statements? I think not. You should issue a single DELETE statement with the WHERE clause written so that it affects all the 8-track records, like this:
DELETE FROM Products WHERE (PartNumber LIKE '8TRACK%')
However, if you try to issue this command, you will receive an error from the database (provided the Access database is set up to enforce referential integrity, like the database on the CD-ROM). The DELETE statement is correct, but what you are trying to do will cause orphaned data in the Orders table. You can't delete these records from the Products table because their part numbers are included in orders recorded in the Orders table. If you delete these product records, you would not be able to obtain complete information on past orders that include these products. The orders would show a part number only. You would not be able to look up the name of the product because that information would no longer exist in the Products table. In relational database parlance, deleting these records would violate the referential integrity of the data.
A less-than-relational database would let you make the mistake of deleting these product records. Relational database servers such as SQL Server and Oracle prevent this type of mistake and help you preserve the referential integrity of your data. Microsoft Access, which does the best job of applying the relational model in desktop databases, also prevents this mistake. When using other database technologies, caveat developer (let the developer beware).
Rather than delete these records, a better approach might be to add a field to the Products table to indicate whether the product is currently for sale. You will learn more about relational database design in the next few days.
I mentioned earlier today that a database server is not inert. The database itself has logic and processing power. As you know, client programs can send text strings containing SQL statements to the database, and the database will interpret the statements and return any data that they produce. It is possible for relational database servers to save SQL statements in a compiled form.
Stored procedures are compiled SQL statements, which are stored inside a relational database. Each stored procedure is given a unique name so that client programs can call it.
Stored procedures provide two important benefits. They enable SQL code to run in compiled instead of interpreted form. The benefit of compiled SQL code is faster execution. In addition, stored procedures execute at the server and require no resources from a client program. The second benefit derives from stored procedures providing a layer of abstraction that can hide the details of the database design. The benefit of this abstraction is that client programs need not know the details of how the various tables and fields in database are constructed. The client code can be simpler, and the database design can be modified without breaking the client code.
A stored procedure may be a straight SQL statement that simply executes as it is written, or it may accept parameters from the calling program for more dynamic execution, as you will see later today.
Relational database servers (such as Oracle and SQL Server) are the only databases that provide true stored procedures. Microsoft Access provides something similar to stored procedures, called Queries. These Queries in Access are in some ways similar to stored procedures: They can be called by name from client programs, they can accept parameters, and they can abstract the details of the database.
However, Queries in Access are not compiled like stored procedures in relational database servers. Queries also do not execute at the server (because Access applications are file based, as described in Day 1). Another difference is that Visual Studio treats Access Queries as Views in the Data View window. This is unfortunate because, as you will see later, it prevents you from executing Queries that take parameters from inside Visual Studio. By contrast, SQL Server stored procedures appear in a folder titled Stored Procedures in the Data View window. If you execute a SQL Server stored procedure that takes parameters from inside Visual Studio, you will be prompted to enter the parameters, and the stored procedure will execute properly.
Despite their shortcomings, Queries in Access do provide a place to begin your exploration of stored procedures. In fact, two Queries are included in VCDb.mdb. The first Query is called CustomerWithMostRecentOrder and consists of a simple SELECT statement. The text of the SELECT statement is shown in Listing 6.4.
Listing 6.4. The CustomerWithMostRecentOrder SQL
Statement
1: SELECT 'Customers'.* 2: FROM Customers 3: WHERE custnumber IN 4: ( 5: SELECT 'Orders'.customernumber 6: FROM 'Orders' 7: WHERE orderdate = 8: ( 9: SELECT MAX(orderdate) 10: FROM Orders 11: ) 12: );
This is the same query you saw in Day 3, "Retrieving Data Through Structured Query Language (SQL)," that returns the customer who placed the most recent order. Because it's now stored as a Query in the Access database, you can run it without having to send all the SQL code to the database from a client program. A client program can simply call the Query. One way to call Queries in Access (and stored procedures in a relational database) is to use an ADO Command object. You will learn about ADO Command objects later today. You can execute this Query in Visual Studio by double-clicking it in the Data View.
The second Query is called CustomersWithOrdersSinceDate and consists of a SELECT statement that takes a date as a parameter. The text of the Query is shown in Listing 6.5.
Listing 6.5. The CustomersWithOrdersSinceDate SQL Statement
1: SELECT 'Customers'.* 2: FROM Customers 3: WHERE custnumber IN 4: ( 5: SELECT 'Orders'.customernumber 6: FROM 'Orders' 7: WHERE OrderDate > [param1] 8: );
The CustomersWithOrdersSinceDate query shown in Listing 6.5 selects the customers who have ordered after a certain date. What date? Well, the database lets the client application (or the human user) specify that date at runtime. Unfortunately, if you try to run this query from inside Visual Studio, you will receive an error message indicating that it was expecting one parameter. However, you will be able to execute this query from C++ code that you will write in the next section of today's work.
If this were a stored procedure in a relational database server, the SQL code would look like Listing 6.6.
Listing 6.6. The CustomersWithOrdersSinceDate Stored
Procedure
1: CREATE PROCEDURE CustomersWithOrdersSinceDate @param1 datetime AS 2: SELECT Customers.* 3: FROM Customers 4: WHERE custnumber IN 5: ( 6: SELECT Orders.customernumber 7: FROM Orders 8: WHERE OrderDate > @param1 9: )
Line 1 in Listing 6.6 uses the SQL CREATE PROCEDURE statement to cause the stored procedure to be compiled and saved in the database. Line 1 also specifies the parameter name (prefixed by an @) and type immediately after the stored procedure name. You execute this SQL code to create and store the stored procedure in the database. When the stored procedure is stored in the database, client applications can call CustomersWithOrdersSinceDate and pass it a date as a parameter to obtain a resultset of customers who have made purchases since that date. A client program can do this, using an ADO Command object.
Note that a client program that uses CustomersWithOrdersSinceDate doesn't try to obtain all the orders and all the customers and then process all that data to find the customer records. Rather, the client program makes a single request to the database and retrieves only the data that is relevant.
As you'll see in the next section, the programming models are identical, whether the client program is using Access or a relational database server. You can use an ADO Command object to call Access Queries as well as SQL Server stored procedures. The difference between Access and SQL Server is that, with Access, all the records are brought into the client process. This happens behind the scenes, so you don't deal with it in your code. It happens because Access applications are file based (as described in Day 1) and because the Jet database engine is a DLL that runs inside the client program's address space. With SQL Server, only the data that the client program requested is brought into the client process (because the server processes all the records and returns only the resultset).
Yesterday, you created ADO Recordsets and used the AddNew, Update, and Delete functions. These functions work well when you are dealing with single records or when the number of records in the resultset is very small.
However, there will likely be occasions when you need to perform an operation that affects thousands or millions of records. The following is a programming sequence you should not follow in your client program when you need to perform an operation on a large number of records:
Using a programming sequence like this to deal with a large number of records would be slow and could hog the network's bandwidth and consume the client computer's memory. The solution in cases where you have a large number of records to process is to write a stored procedure so that all those records can be processed at the server. You call the stored procedure by using an ADO Command object.
Open your ADOMFC1 project and add a menu for Commands with two choices, as shown in Figure 6.1.
Figure 6.1 : Menus for ADO Commands.
Use ClassWizard to create a handler function for the Most Recent Order menu choice. In the handler function, add the code shown in Listing 6.7.
Listing 6.7. The ADO Command Object to Call MostRecentOrder
){kind=link}
1: void CADOMFC1View::OnCommandMostrecentorder()
2: {
3: _CommandPtr pCommand;
4:
5: pCommand.CreateInstance(__uuidof( Command ));
6:
7: CADOMFC1Doc * pDoc;
8: pDoc = GetDocument();
9:
10: try
11: {
12: pCommand->ActiveConnection = pDoc->m_pConnection;
13:
14: pCommand->CommandType = adCmdStoredProc;
15:
16: pCommand->CommandText = _bstr_t("CustomerWithMostRecentOrder");
17:
18: _variant_t vNull;
19: vNull.vt = VT_ERROR;
20: vNull.scode = DISP_E_PARAMNOTFOUND;
21:
22: _RecordsetPtr pRS;
23:
24: pRS = pCommand->Execute( &vNull, &vNull, adCmdUnknown );
25:
26: if (!pRS->GetadoEOF())
27: {
28: CListCtrlEx& ctlList = (CListCtrlEx&) GetListCtrl();
29: ctlList.DeleteAllItems();
30: while(ctlList.DeleteColumn(0));
31:
32: ctlList.AddColumn(" Customer Number ",0);
33: ctlList.AddColumn(" First Name ",1);
34: ctlList.AddColumn(" Last Name ",2);
35:
36: int i = 0;
37: _variant_t vCustName;
38: _variant_t vFirstName;
39: _variant_t vLastName;
40: while (!pRS->GetadoEOF())
41: {
42: vCustName = pRS->GetCollect(L"CustNumber");
43: ctlList.AddItem(i,0,(_bstr_t) vCustName);
44: vFirstName = pRS->GetCollect(L"CustFirstName");
45: ctlList.AddItem(i,1,(_bstr_t) vFirstName);
46: vLastName = pRS->GetCollect(L"CustLastName");
47: ctlList.AddItem(i,2,(_bstr_t) vLastName);
48: i++;
49: pRS->MoveNext();
50: }
51: }
52:
53: pRS->Close();
54: }
55: catch( _com_error &e )
56: {
57: TRACE( "Error:%08lx.\n", e.Error());
58: TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
59: TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
60: TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
61: }
62: catch(...)
63: {
64: TRACE( "\n*** Unhandled Exception ***\n" );
65: }
66: }
Lines 3 and 5 of Listing 6.7 create an ADO Command object. Line 12 tells the Command object to use the existing database connection stored in the MFC Document. Lines 14 and 16 tell the Command object that you are going to call a stored procedure; also, lines 14 and 16 tell the Command object the name of the stored procedure. Line 22 creates a Recordset pointer, and line 24 calls the Command object's Execute function to execute the stored procedure and place any resulting data in a Recordset object (which is pointed to by the Recordset pointer). Lines 26-51 display the contents of the Recordset in the list control in the View. (The code in lines 40-50 uses a while loop, which is probably unnecessary because this stored procedure returns only one record.)
Note that the C++ code in Listing 6.7 does not retrieve a Recordset containing all orders, find the most recent order by looking at every record, and then finally retrieve the customer for that order. This code issues a single call to the database, enables the database to process the records, and retrieves only the customer information it's looking for.
This approach is elegant and harnesses the power of a relational database server. It could handle millions of records without hogging network bandwidth or consuming memory in client process space.
In this example, Microsoft Access appears to be processing the records at the server, just like a relational database server. However, with Access, all the records are brought into the client program address space to be evaluated by the Jet database engine (which resides in a DLL mapped into the client program address space). The programming model is identical to a relational database server, but the actual execution model doesn't utilize true client/server capabilities.
Use ClassWizard to create a handler function for the Ordered Since Date menu choice. In the handler function, add the code shown in Listing 6.8.
Listing 6.8. The ADO Command Object to Call OrderedSinceDate
1: void CADOMFC1View::OnCommandOrderedsincedate()
2: {
3: _CommandPtr pCommand;
4:
5: pCommand.CreateInstance(__uuidof( Command ));
6:
7: CADOMFC1Doc * pDoc;
8: pDoc = GetDocument();
9:
10: try
11: {
12: pCommand->ActiveConnection = pDoc->m_pConnection;
13:
14: pCommand->CommandType = adCmdStoredProc;
15:
16: pCommand->CommandText = _bstr_t("CustomersWithOrdersSinceDate");
17:
18: pCommand->Parameters->Append
19: (
20: pCommand->CreateParameter
21: (
22: _bstr_t("ParamDate"),
23: adDBTimeStamp,
24: adParamInput,
25: 0,
26: _variant_t(COleDateTime(1998, 10, 1, 0, 0, 0))
27: )
28: );
29:
30: _variant_t vNull;
31: vNull.vt = VT_ERROR;
32: vNull.scode = DISP_E_PARAMNOTFOUND;
33:
34: _RecordsetPtr pRS;
35:
36: pRS = pCommand->Execute( &vNull, &vNull, adCmdUnknown );
37:
38: if (!pRS->GetadoEOF())
39: {
40: CListCtrlEx& ctlList = (CListCtrlEx&) GetListCtrl();
41: ctlList.DeleteAllItems();
42: while(ctlList.DeleteColumn(0));
43:
44: ctlList.AddColumn(" Customer Number ",0);
45: ctlList.AddColumn(" First Name ",1);
46: ctlList.AddColumn(" Last Name ",2);
47:
48: int i = 0;
49: _variant_t vCustName;
50: _variant_t vFirstName;
51: _variant_t vLastName;
52: while (!pRS->GetadoEOF())
53: {
54: vCustName = pRS->GetCollect(L"CustNumber");
55: ctlList.AddItem(i,0,(_bstr_t) vCustName);
56: vFirstName = pRS->GetCollect(L"CustFirstName");
57: ctlList.AddItem(i,1,(_bstr_t) vFirstName);
58: vLastName = pRS->GetCollect(L"CustLastName");
59: ctlList.AddItem(i,2,(_bstr_t) vLastName);
60: i++;
61: pRS->MoveNext();
62: }
63: }
64:
65: pRS->Close();
66: }
67: catch( _com_error &e )
68: {
69: TRACE( "Error:%08lx.\n", e.Error());
70: TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
71: TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
72: TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
73: }
74: catch(...)
75: {
76: TRACE( "\n*** Unhandled Exception ***\n" );
77: }
78: }
The code in Listing 6.8 is nearly identical to Listing 6.7. One crucial difference is in line 16 where the name of the stored procedure is specified. Another important difference is in lines 18Ð28. The ADO Command object contains a Parameters collection, which stores the parameters that will be passed to the stored procedure when Execute is called. The pCommand->Parameters->Append call in line 18 appends a new parameter to the Parameters collection for this Command object. The argument passed to the Append function is the result of the pCommand->CreateParameter call in lines 20-27.
Line 22 names the parameter (so you can access it to change its value later if you want). Line 23 specifies the data type for this parameter, which is adDBTimeStamp. The available data types are declared in the DataTypeEnum in msado15.tlh, which is one of the files created when you use #import on the ADO type library. Line 24 specifies that this is an input parameter, meaning that this client program is giving this parameter to the database. The parameter directions (input, output, or both) are declared in the ParameterDirectionEnum in msado15.tlh. An output parameter would be one where the value of the parameter is changed by the stored procedure and then read by the client program after executing the stored procedure. Line 25 specifies the length of the parameter data. This is not used for adDBTimeStamp types but is used for numeric and string types. Line 26 is a _variant_t containing the data value for the parameter that will be passed to the stored procedure. In this case, a COleDateTime is used because it encapsulates the VARIANT date/time stuff and makes it easier to use. You need to pass data of the appropriate type to the _variant_t constructor in line 26, based on the data type you specify in line 23.
That's it. When you run the application and take this menu choice, you will see displayed in the list control the customers who have made purchases since the date specified. The parameter value needn't be hard-coded. You could, of course, expand this code to let the user enter a date, and then you could pass that date to the stored procedure.
| NOTE |
If you specify a COleDateTime of 11/1/1998 0:0:00, you will see that the stored procedure returns customers who made purchases on 11/1/1998. This might seem strange because the SQL code specifies OrderDates that are greater than the parameter value. However, you must realize that this a date/time data type. It will take the time into account, as well as the date. If you specified a COleDateTime of 11/1/1998 23:59:59 instead, you probably wouldn't see any customers who placed orders on 11/1/1998. |
Today you learned how to harness the power of relational database servers. You saw how the SQL INSERT, UPDATE, AND DELETE statements can be used to process many records at a time. You also learned about stored procedures and how to call them by using ADO Command objects.
You wrote code that illustrated methods for processing data at the server. Your ability to write code that processes data at the server will enable you to create applications that can handle huge amounts of data and work efficiently over a LAN, a WAN, or the Internet.
| What's the difference between client/server applications and multitier applications? | |
| Client/server applications typically consist of client machine(s) running a Windows application, connected over a LAN to a relational database server on a network server machine. This is a two-tier system (client and server). Multitier applications typically consist of more than two tiers. The client tier consists of machine(s) running some kind of a thin client program, such as a Web browser or a simple application. There is often a middle tier, consisting of machine(s) running a Web server, such as Internet Information Server (IIS) and/or an Object Request Broker (ORB) such as Microsoft Transaction Server (MTS). The server tier typically consists of a server running a relational database. Client tier programs typically communicate with programs on the middle tier, which then communicate with the database at the server tier. The clients do not communicate with the database directly, only through the middle tier programs. | |
| When should I use the ADO Recordset AddNew, Update, and Delete functions instead of stored procedures? | |
| For inserting records, the ADO Recordset AddNew function can be faster than calling a stored procedure to insert records. For summarizing, updating, and/or deleting records, it depends on the number of records you need to work with. If the number of records is small, you can get by with pulling the records into a Recordset at the client to process them. If, however, the number of records isn't small, you should consider using a stored procedure called from an ADO Command object. The only caveat for ADO Command objects seems to be that the process of changing parameter values in the Parameters collection can be CPU intensive at the client. Your mileage might vary, so write some test code and benchmark the performance for your own applications. | |
| Can I create Access Queries from within Visual Studio? | |
| No, the only way to create new Queries in an Access database (.mdb file) is to run Microsoft Access and create the new Queries through the Access UI. |
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.
© Copyright, Sams Publishing. All rights reserved.