The Shopping Cart sample uses the conversation group identifier to maintain state for a simple shopping cart application. The application uses the ServiceBrokerInterface sample.

By default, the Shopping Cart sample application is installed at C:\Program Files\Microsoft SQL Server\100\Samples\Engine\\ServiceBroker\ShoppingCart. If the sample folder is not present, see Installing Samples.

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.

Building and Installing the Sample

  1. In Visual Studio 2005, open ShoppingCartCS.sln.

  2. Build the solution by pressing F6, or by selecting Build Solution from the Build menu.

  3. Open a command prompt, locate the Scripts folder, and then run the following command:

    install all

    The install script installs the SQL Server objects for the application. This includes the common language runtime (CLR) stored procedure that implements the ShoppingCartService service.

Running the Sample

  1. Run the ShoppingCartClient.exe application from the sample directory. For simplicity, the client processes one order each time the client runs.

  2. Click Create Order to create an order. Add items to the shopping cart by selecting the item from the Item list, and then clicking the Add Item button.

  3. Click the Service Broker Trace tab at any time to see a summary of the Service Broker messages sent and received.

  4. The ShoppingList and StateTable tables in the ssb_ShoppingCart database maintain state for the application. The CLR stored procedure updates the tables based on messages from the client. You can use Management Studio or sqlcmd to inspect these tables.

Uninstalling the Sample

  1. Open a command prompt, locate the Scripts folder, and then run the following command:

    uninstall all
  2. The uninstall script removes the SQL Server objects for the application.


This sample requires Visual Studio 2005. Because the sample uses features of the CLR that were not available in earlier versions, versions of Visual Studio earlier than 2005 cannot build the sample.


Service Broker applications most often store state in the database that hosts the service. When messages arrive, the application loads state from the database, and then processes the messages.

The Service class of the Service Broker Interface sample provides a convenient way to maintain state. This sample demonstrates how to use the Service class to maintain state.

To maintain state by using the Service class, you implement two methods in your application and a stored procedure in SQL Server, as listed in the following table:

Item Signature Description

Stored Procedure

CREATE PROCEDURE name (@cgid uniqueidentifier)

Returns the state for the application. The result sets that are returned by this procedure must contain the conversation group identifier.

public method

public override bool LoadState(System.Data.Sql.SqlReader reader)

Reads the result sets that are provided by the stored procedure to restore state. This stored procedure must save the conversation group identifier in the class, because the Service Broker Interface does not provide the identifier to the SaveState method.

public method

public override void SaveState()

Saves state to the database.

This topic describes the general outline for restoring state. See ShoppingCartService.cs for the full example.

The stored procedure must accept a conversation group identifier and return two result sets. The first result set consists of the conversation group identifier itself. The second result set contains the application-specific state. There is no fixed name for this stored procedure. Instead, you supply the name of the stored procedure by setting the AppLoaderProcName property. The Service Broker Interface simply calls the stored procedure provided.

The LoadState method restores the state for the application. The exact process the application follows depends on the state that the application requires. The method takes an instance of System.Data.Sql.SqlReader as a parameter. The SqlReader contains the result sets returned the stored procedure. Typically, implementations of the LoadState method update the State property of the Service class, save the conversation group identifier in a private field, and then save the application-specific state in appropriate data structures. For example, the ShoppingCartService stores information about items, quantities, and prices in a private instance of the ShoppingCart class.

After calling LoadState, the Service Broker Infrastructure dispatches the message. Notice that, if you use the attribute-based message dispatch framework, you can dispatch messages based on the State property in addition to the message type name. In the methods that handle messages, you can read or update the data structures that are created in LoadState.

When the message handling method returns, the Service Broker Infrastructure calls the SaveState method. The SaveState method uses the Connection property of the Service class to create a SqlCommand object, and then uses that object to update the state stored in the database. For example, the ShoppingCartService deletes the saved contents of the shopping cart and then inserts the current contents of the shopping cart into the database.

Change History

Release History

5 December 2005

Changed content:
  • Changed instructions for generating a key file, including the name and location of the key file.