Service Broker Ordering Service Sample

  • Article

The Adventure Works Storefront sample accepts an online order and returns a predefined ship date in the order status screen. This sample adds backend order processing capabilities to Adventure Works Storefront.

This sample illustrates the fundamental concepts of Service Broker. It lets you view the Transact-SQL scripts that are used to run the sample, and the results of executing that script in the Service Broker tables.

Note

This sample requires the Adventure Works Storefront sample. For more information about installing the Storefront sample, see Readme_Storefront. By default, the Adventure Works Storefront sample is installed at C:\Program Files\Microsoft SQL Server\100\Samples\Integrated Samples\Storefront.

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples. Sample applications and assemblies should not be connected to or used with your production SQL Server database or your report server without the permission of the system administrator.

Scenario

A developer wants to implement a backend order processing system for an e-commerce Web site.

Languages

Transact-SQL, XML, XSD, and Visual C#.

Prerequisites

Before running this sample, make sure the following software is installed:

  • SQL Server 2005, including the following components:
    • SQL Server Database Engine
    • SQL Server Management Studio
    • AdventureWorks sample database
    • Database Engine Samples
    • Adventure Works Storefront sample
    • .NET Framework SDK 2.0 or Microsoft Visual Studio 2005. You can obtain .NET Framework SDK free of charge. See Installing the .NET Framework SDK.

Building the Sample

The C# source code is built with the Storefront sample. To create the SQL Server portion of the sample, follow the steps below.

All Service Broker applications are composed of a collection of SQL Server objects that provide the service functionality. In this step, you will create the Service Broker environment, consisting of the XML schema collections, message types, contracts, queues, and services required to operate it.

Set up the Service Broker environment

  1. Run the Transact-SQL script to set up Service Broker. You can run installorderingservice.cmd, or you can run the individual scripts.

    1. Run installorderingservice.cmd. The default folder is C:\Program Files\Microsoft SQL Server\100\Samples\Integrated Samples\\Storefront.
    2. Alternatively, you can run the individual scripts in the following order:
    • OrderingServiceScripts\Setup.sql
    • OrderingServiceScripts\SetupOrderingServiceProgram.sql
    • OrderingServiceScripts\SetupPurchasingServiceProgram.sql
    • OrderingServiceScripts\SetupManufacturingServiceProgram.sql
    • OrderingServiceScripts\SetupShippingServiceProgram.sql

  2. Configure the Storefront.

    1. Edit the web.config file, located in the directory C:\Program Files\Microsoft SQL Server\100\Samples\Integrated Samples\\Storefront\CS\StoreCSVS. Find the ExtendedOrderProcessing attribute, and change the value to true. After making the change, this element should look like the element below:
      <add key="ExtendedOrderProcessing" value="true"/>

Running the Sample

In this exercise, you will visit the Adventure Works Web site and order an in-stock item. Because the item is in stock, the Ordering Service sends a Shipping Request message to the Shipping Service. The Shipping Service then responds to the Ordering Service with a Shipping Accept message. Follow the steps in this exercise to see this process in SQL Server Management Studio.

Order an in-stock item

  1. In Internet Explorer, open https://localhost/storeCSVS/ .

  2. In the left pane, expand Accessories, and then expand Helmets. Select Sports-100 Helmet, Red, and then click Add to Cart. On the Shopping Cart page, click Final Check Out. On the next page, click Submit.

  3. In SQL Server Management Studio, run AdministrationAndTroubleShooting.sql. The default folder is C:\Program Files\Microsoft SQL Server\100\Samples\Integrated Samples\\Storefront\OrderingServiceScripts. The result shows the processing flow, including Service Broker queue tables, ordering state, and audit logs.

  4. Run purge.sql to clean up contents in queues and tables before continuing on to the next exercise.

Order an out-of-stock buy item

  1. In Internet Explorer, open https://localhost/storeCSVS/ .

  2. In the left menu, expand Clothing.  Click the submenu item Tights. Pick Women’s Tights, M, and then click Add to Cart. In the next page, click Final Check Out. In the next page, click Submit.

  3. In SQL Server Management Studio, run AdministrationAndTroubleShooting.sql. The default folder is C:\Program Files\Microsoft SQL Server\100\Samples\Integrated Samples\\Storefront\OrderingServiceScripts. The result shows the processing flow, including Service Broker queue tables, ordering state, and audit logs.

  4. Run purge.sql to clean up contents in queues and tables before continuing on to the next exercise.

Order an out-of-stock make item ready for manufacturing

  1. In Internet Explorer, open https://localhost/storeCSVS/ .

  2. On the left menu, expand Components.  Click Mountain Frames, select HL Mountain Frame – Black, 38, and then click Add to Cart. On the next page, click Final Check Out. On the next page, click Submit.

  3. In SQL Server Management Studio, run AdministrationAndTroubleShooting.sql. The default folder is C:\Program Files\Microsoft SQL Server\100\Samples\Integrated Samples\\Storefront\OrderingServiceScripts. The result shows the processing flow, including Service Broker queue tables, ordering state, and audit logs.

  4. Run purge.sql to clean up contents in queues and tables before continuing on to the next exercise.

Remove the sample

  1. Run uninstallorderingservice.cmd. The default folder is C:\Program Files\Microsoft SQL Server\100\Samples\Integrated Samples\\Storefront.