          Help File for the Updgrams.vbg Projects
     (See the copyright notice and license at the end)
  Updategrams.doc is the illustrated version of this file

Purpose: The Updgrams.vbg project group includes Updgrams.vbp, which lets you compare the performance of conventional Transact-SQL (T-SQL) transactions for INSERT, UPDATE, and DELETE operations with their XML updategram counterparts, and UpdgEdit.vbp, which uses updategrams to emulate ADODB/ADOR disconnected Recordsets. The primary advantage of updategrams is the ability to perform updates to SQL Server 2000 tables with the firewall-friendly HTTP or HTTPS protocol. You can POST updategrams from a client application or script written in any language available for any OS. Another benefit of updategrams is that Web developers don't need to know T-SQL or database design principles to execute transacted updates with or without concurrency checking.

You also can use Updgrams.vbg or Updgrams.exe to add a very large number of orders and line items to the Northwind database for query performance analysis.

Prerequisites (Updgrams.vbg): SQL Server 2000 (any edition) or MSDE 2000, MDAC 2.6 (installed by SQL Server/MSDE 2000)  or MDAC 2.7 (installed by Visual Studio .NET or download from or by download from http://www.microsoft.com/data/), IIS 4.0+, and Visual Basic 6.0 SP5. SQLXML 2.0, which installs Sqlis2.dll and Sqlvdr2.dll, is required to create the virtual directory. Download SQLXML 2.0 as XML for SQL Server 2000 Web Release 2 from http://www.microsoft.com/sql/downloads/. The code uses version-dependent SQLXML 2.0 objects. Microsoft XML v3.0+ also is required. The application has been tested only under Windows 2000 Professional and Server SP1 with SQL Server 2000 Standard Edition SP1 and SP2, MSDE 2000, and IIS 5.0.

SQLXML Version Compatibility:  SQLXML 2.0 (a.k.a. XML for SQL Server 2.0) is REQUIRED to create the virtual directory for this project. Updategrams that update the Order Details table fail and you receive concurrency errors with virtual directories created with SQLXML 3.0. (This problem, which relates only to tables with spaces in their name, is under investigation.) SQLXML 2.0 and 3.0 install side-by-side, so installing SQLXML 3.0 doesn't overwrite version 2.0, and vice versa. Thus, you don't need to remove SQLXML 3.0 before installing SQLXML 2.0. Updgrams.vbg defaults to SQLXML 2.0 to create virtual directories automatically.

You must run Updgrams.vbp/.exe on the machine running IIS. This project has been tested only under Windows 2000 Professional, Server, and Advanced Server. SQL Server 2000's Northwind or Access 2002's NorthwindCS sample database is required, but the SQL Server instance can be on any network-accessible machine. Your login credentials on this machine must have permissions to administer IIS and the SQL Server instance.

Requirements (UpdgEdit.exe): The independent client application runs under Windows 98, Me, 2000 (any version), and XP any version. IE 5.5+, which installs MSXML 3.0+, ActiveX Data Objects 2.5+ Library, and a copy of Msdatgrd.ocx are required on the client machine. VB6 SP5+ is required to open UpdgEdit.vbp on the client. If you use a VeriSign trial certificate, you must also install a certificate on the client's instance of IE 5.5+.

Installation: Unzip Updgrams.zip to a subfolder (typically \Program Files\Updategrams) of the machine running your test Web server. To run the client on another machine, copy UpdgEdit.exe and LineItems.xml and LineItemsExt.xml to the client's \Program Files\Updategrams folder:

Login and Startup: Run Updgrams.exe or Updgrams.vbp to open the logon dialog. 

1. Type the NetBIOS name of the SQL Server running the Northwind[CS] database in the SQL Server text box and Northwind or NorthwindCS in the Initial Catalog text box. 

2. Accept the default SQL Server Security option, and type the logon ID and password for a system administrators account. When you create the virtual directory for the database, ISAPI uses this account (instead of the default Windows authentication) for the database connection.

Note: If you don't specify SQL Server security, anonymous can't obtain a connection to the database with UpdgEdit.exe on a client.

3. Initially, accept the default NetBIOS name of the machine on which you're running the application as the IIS 5+ Server Name. After you create virtual directories, names, and required XML template files, you can run Updgrams.exe/.vbp from any networked machine.

Note: If you have another Web site you want to use for this test, type its sequential number in the Web Site Number text box. The first new Web site you add is 3. (1 is the Default Web site and 2 is the Administration Web Site.)

4. Click Connect to connect to Northwind[CS], display the frmUPDG form, and open the OrderID Is an Identity Field message box. Using the default identity field causes significant performance hit when adding Orders records to the database. Adding and then deleting orders causes a discontinuity in the OrderID values, unless you remove and reinstate the identity attribute on the OrderID field with SQL Server Enterprise Manager. See the Changing or Resetting the Identity Attribute of the OrderID Field section for instructions on how to alter the identity settings.

5. Click Yes to proceed with adding orders and incrementing the @@IDENTITY value of the Orders table. Alternatively, click No to edit one of the existing Orders records with a NULL ShippedDate value (skip to the Order Editing section). Click Cancel to return to the login dialog if you want to remove the identity attribute from the OrderID column (skip to the Restoring the @@IDENTITY Value section.)

Specifying or Creating a Virtual Directory and Virtual Name (frmUPDG.GetVDirAndVName)

1. If you've previously set up one or more virtual directories and virtual names, a Virtual Directory message opens with the properties of the first virtual directory. If you haven't set up a virtual directory, skip to step 3. If this virtual directory is assigned to the Northwind[CS] database, click Yes to accept the choice. If not, click No to view the properties of the next virtual directory.

2. If you don't choose an existing virtual directory, a No Virtual Directory message opens to offer you the chance to create a new virtual directory or review your existing virtual directories again. Click Yes to create a new virtual directory and open a new message.

3. The virtual directory name and folder is the name of the database you specified in the login dialog (e.g., \Inetpub\wwwroot\Northwind[CS]). The default folder for XML query templates is \Inetpub\wwwroot\Northwind[CS]\Templates and the virtual name is Templates. If you want to change the virtual name and folder, click No to open an input box, type a name, and click OK. Clicking Cancel returns you to the login dialog.

4. Selecting or adding a virtual directory adds the vwLineItems view, creates template files in the \Inetpub\wwwroot\Northwind[CS]\Templates folder, and displays the templates in the text box.

The first two FOR XML AUTO templates (GetLastOrderID.xml and  GetLineItemIDs.xml) are used by code behind the frmUPDG form. UpdgEdit.frm uses the remaining six templates.

Bulk Operations with T-SQL Transactions and Updategrams

1. Accept the default Show Sample T-SQL and click Add New Orders (frmUPDG.cmdAddOrders_Click) to display the T-SQL statement for the INSERT transaction in the text box. 

With an identity field, you must include a field list in the INSERT statement, in addition to the value list. Thus, the T-SQL statement sent over the wire is more lengthy than that when you increment the last OrderID value by 1 for the INSERT operation. A typical T-SQL transaction statement for one Orders and eight Order Details records has 1,270 characters.

2. Select the Show Sample Updategram option and click Add Orders. The updategram documentformatted for readabilityto add an order and eight line items opens in the text box.

The unformatted updategram has 1,410 characters in the POSTed document.

4. Select Run T-SQL Transactions and click Add New Orders to add the default 100 orders and 800 line items to the Orders and Order Details tables. The OrderID value and OrderDate values appear in the Last Order No. and Last Date list boxes during order addition. When 100 orders are added, the time to add the records appears in the text box.

5. Select POST Updategrams and click Add New Orders. On completion, timing data for the POST operation appears in the text box. Without an identity field, T-SQL transactions outperform updategrams by a factor of two to three. With an identity field, insert times are much closer. There is no significant difference in insert times with a local or remote SQL Server on a fast network.

Note: Mark the Random Line Items check box to create more realistic orders having line items ranging between 1 and the maximum number specified in the Items/Order text box. If you mark the Add Ship Dates check box, 

4. Adding orders enables the Edit Added Orders and Delete Added Orders buttons. Repeat the preceding two steps, but click Edit Orders (frmUPDG.cmdEditOrders_Click) instead of Add Orders. Updates change the EmployeeID and Freight fields of the added Orders records to random values; Order Details records get random Quantity and re-calculated Discount values. There is no difference in the T-SQL statement and updategram document for identity or non-identity fields.

Note: Mark the Limit Edits and Deletions check box to edit or delete the last 100 orders or a different number entered in the Number of Orders text box.

5. There are three options for deleting the orders you've added. If you select T-SQL transactions and click Delete Orders, a message box offers the option of bulk deletion (delete all Order Details records with OrderID values > 11077 and then corresponding Orders records.)

There are 1,148 characters in a typical unformatted deletion updategram document. The requirement for deleting individual line items makes the updategram deletion process considerably slower than the T-SQL transaction.

Note: You can run multiple instances of Updgrams.exe to test for concurrency issues and see if you can find performance differences between MSDE 2000 and other editions of SQL Server 2000. (MSDE 2000 is limited to five concurrent batch operations.) Using updategrams without an identity column results in many failed transactions because of the time required to obtain the next OrderID value, during which another instance is adding records with the same OrderID.

Editing a Single Order (frmUpdgEdit)

1. Click the Edit an Order button to open the frmUpdgEdit form and display the Customers, Orders, and Order Details records for the last order you added by executing the GetCustomer.xml, GetOrder.xml, and GetLineItemsExt.xml template queries. All edits are performed by updategrams. Until you make a change to the text box or DataGrid values, the three Update... buttons are disabled.

Note: If you mark the Perform Concurrency Check for Updates check box, the updategram includes the original values in the form. If another user has made changes to any of the edited fields, the update fails, a message opens, and the current values appear in the form.

2. Click the Line Items Recordset button to display the DataGrid's ADODB.Recordset (persisted as XML) in IE 5.5+. The Recordset is created by appending the attribute-centric XML document returned by the GetLineItemsExt.xml template to the XDR schema for the Recordset (in LineItemsExt.xml). If you clear the Include Product Name and SKU Data in Grid check box, only the ID, Price, Quan. and Disc. columns appear. In this case, the template is GetLineItems.xml and the schema is in LineItems.xml.

3. Edit one or more fields in the customer and order regions, and change a ProductID value in the grid. Changing the ID value executes the GetProductData.xml template against the vwLineItems view to return the new ProductName and SKU (QuantityPerUnit) values. The edits enable the Update Customer, Update Order, and Update Both buttons. Update Both combines the customer and order update transactions into a single updategram with two <updg:sync>...</updg:sync> blocks.

4. Click Update Both to execute the changes, which enables the Customer Updategram and changes the caption of the Orders Updategram button to Both Transactions (and enables it).

Note: Code behind frmUpdgEdit verifies that values have changed. If you edit the customer or order data, then undo your changes, clicking any Update... button results in a No Change entry in the Time text box. No updategram is sent in this case.

5. Click the Both Transactions button to display the updategram in IE 5.5+.

Note: The UpdgEdit.exe/.vbp client doesn't display the three bottom buttons.

6. Mark the Perform Concurrency Checks for Updates check box and undo the edits you made in step 3. In this case, the updategram opens in Notepad, because IE throws an error when it encounters special Unicode characters in the XML document.

4. To find another customer, type the first letter or two of the CustomerID value in the Customer ID text box and click GetCust. and Orders to replace the DataGrid with a list box of matching Customers records. The list box appears if the GetCustomer.xml template returns more than one record.

5. Click one of the items in the list to return a list of the last 25 orders for the customer. (The 25-order limit is arbitrary and is specified in the GetOrderIDs.xml template.)

6. Click one of the orders to display. You can't edit orders that have a shipped date value.

Note: List boxes are filled by "walking the DOMDocument."

Changing or Resetting the Identity Attribute of the OrderID Field

To return the Orders table with the identity property set to its original condition (last OrderID = 11077 and next OrderID = 11078), do the following:

1, Launch SQL Server Enterprise Manager (EntMan), expand the nodes to display the Northwind[CS] database, and click the Tables node to display the user and system tables.

2. Right-click the Orders table and choose Design Table to open the da Vinci designer.

3. With the OrderID field selected, open the Identity list and select No.

4. Close the window (not EntMan), and click Yes twice to save the changes.

5. Reopen the designer, change the Identity value to Yes, close EntMan, and save the changes. (Don't change the Identity Seed or Identity Increment values).

Note: If you have Access 2000 or 2002, you can make the changes in the NorthwindCS.adp project's Table Design view.

Copyright (c) 2001 Roger Jennings (Roger_Jennings@compuserve.com) All Rights Reserved

BOILERPLATE: End User License Agreement (EULA) and Terms of Use

This program is provided "as is" and no warranties, express or implied, apply to the program code, executable file, or other program elements and components. The author is not responsible for any direct, indirect, or consequential damages that might occur from execution or other use of this code. The program has been exhaustively tested only with Windows 2000 Professional and Server, Jet 3.x and 4.0 databases, and SQL Server 2000 Developer, Standard, Enterprise, and MSDE 2000 editions.

You are licensed to use parts of this code for any purpose you want, except for publication of the source code or this help file (or parts thereof) in a book, magazine, newsletter, or on the Internet without permission of the copyright holder. You may make copies of the program's Microsoft Installer (.msi) file, if included, and provide this file, intact and without modification, to others for non-commercial use only.
