 | The AddOrders.adp sample project in the \Seua11\Chaptr30 folder of the accompanying CD-ROM demonstrates practical application for this chapter's example of the programming of ADO objects, methods, and properties. The primary purpose of the AddOrders project is to add a large number of records to the Orders and Order Details tables of a copy of the NorthwindCS SQL Server database. Working with test tables having a large number of rows lets you debug online transaction processing (OLTP) applications with real-world data. The code uses random record numbers to specify the CustomerID for each added order and the ProductID for order line items. |
The AddOrders project also lets you compare the performance difference between sending SQL statements to the server and using stored procedures to add, edit, and delete Orders and Order Details records. Code in the frmAddNorthwindOrders Class Module creates the required stored procedures. To give the AddOrders.adp project a test drive, do the following: Open NorthwindCS.adp, choose Tools, Database Tools, Transfer Database and create a copy of NorthwindCS as NorthwindSQL or any other name you prefer in your (local) SQL Server instance. Open AddOrders.adp, choose File, Connection, and specify the server, authentication method, and name of the database copy. When you add a connection to AddOrders.adp, you have the option of using the project's connection or specifying a connection to another server or database. Click Connect and click Yes when the message box asks if you want to use the current connection to the new database. If you specified SQL Server security, type your login ID and password in the two text boxes. You receive the message about the OrderID field's Identity attribute shown in Figure 30.25. Figure 30.25. Opening NorthwindCS or a copy of NorthwindCS displays the warning message shown here.  Note The Identity attribute must be removed from the Orders table to permit deleting added records and then adding new records with numbers that are consective with the orginal OrderID numbers (10248 11077).
Acknowledge the message, open the Orders table in Design view, and select the OrderID column. In the Columns property page, select Identity and change the value from Yes to No (see Figure 30.26). Close the table and save the changes. Figure 30.26. Set the Identity attribute of the OrderID field of the Orders table to No in the daVinci table designer.  Tip You also can change the design in SQL Server Enterprise Manager, but using Access's Table Design view is easier.
In the form, click the Clear Report Text button, click Connect to open the connection to the database, and click the Create Stored Procs button to add three stored procedures to the database. Adding the procedures enables the three Use Stored Procs check boxes. Change the Orders to Add, Items/Order, and Orders/Day values, if you want, and click Add New Orders. The number of Orders and Order Details records added and the time required for addition appears in the text box. Mark the Use Stored Procs check box under the Add New Orders button to compare the speed of order addition with a stored procedure. Depending on your hardware configuration, you might gain about a 10% performance improvement by using a stored procedure instead of sending INSERT statements to the database (see Figure 30.27). Figure 30.27. Adding, editing, or delecting records adds the number of records affected and timing data to the text box.  Repeat steps 6 and 7, but click the Edit Added Orders button to change the Quantity values of all Order Details records you've added. Click the Delete Added Orders button to restore the tables to their original number of records. A message box lets you choose between bulk and individual order deletion.
Note The code in the form's Class Module originated in a Visual Basic 6.0 program for testing SQL Server 7.0 and 2000 OLTP performance in a variety of server hardware configurations. Only a few code changes were necessary to the Visual Basic 6.0 code that was copied to the Access form.
|
