How to Generate SQL Tables and Stored Procedures for Orders Mapped Storage

The Order Mapping tool (OrderMapping.exe) reads the OrderObjectMappings.xml file and generates three things:

• SQL table definitions

• SQL stored procedures

• An XSD schema for BizTalk adapters

This topic describes how to use the Order Mapping tool to generate SQL table definitions and stored procedures. For more information about generating an XSD schema, see Generating and Using the XSD Files Required by Different Commerce Server Systems.

The Order Mapping tool only applies to PurchaseOrders and the objects nested within a PurchaseOrder. The Order Mapping tool does not apply to Baskets.

The Order Mapping tool performs the following actions:

1. Reads the mapping file and performs internal consistency checks.

2. Validates the mapping file against the Web.config file.

3. Creates SQL statements to generate the database tables, columns, and relationships.

4. Creates SQL stored procedures to perform the following actions:

   • Insert data into the database.

   • Select data from the database.

   • Delete data from the database.

You can control which of these actions the Order Mapping tool performs by specifying command line arguments when you execute OrderMapping.exe.

To run the Order Mapping tool

1. Update the OrderObjectMappings.xml file to define the mapping between properties and database columns. For more information about updating the OrderObjectMappings.xml file, see Mapping Purchase Orders to the Database.

2. If you will modify the database schema manually (rather than having the Order Mapping tool generate a script to create the tables and delete any existing data), do so now.

3. Create a new directory and copy the following files into the new directory:

   • Copy the OrderMapping.exe file from the %COMMERCE_SERVER_ROOT%/Tools directory.

   • Copy the Web.config file from the virtual root of the Web site.

   • Copy the OrderObjectMappings.xml file from the virtual root of the Web site.

   • Copy the OrderPipelineMappings.xml file from the virtual root of the Web site.

   • If the assembly that contains your extensions to the Orders System is not in the Global Assembly Cache (GAC), copy the assembly that contains your extensions to the Orders System.

4. Run the Order Mapping tool by typing "%Commerce_Server_Root%"\Tools\OrderMapping.exe followed by whichever of the command-line arguments are appropriate for your situation. The following table lists and describes the command-line arguments.

   Argument	Description
   /w[ebconfig]filename	The name of the Web.config file to validate against. Do not specify the full path; this file must be in the local directory when you run the Order Mapping tool. The Order Mapping tool will process the mapping file that is specified in the MappingFiles element of this file. This argument is required.
   /o[utput]filename	The name of the output SQL file. The default value is OrdersStorage.sql.
   /i[gnore]	Overwrite the existing output file, if it exists.
   /n[osql]	Do not generate any SQL information.
   /s[qltables]	Generate SQL table definitions. If you do not specify this command line argument, the Order Mapping tool only generates SQL stored procedures. Important Note: If you specify the /s[qltables] argument, the Order Mapping tool generates DROP statements in the output SQL file, which will delete data in the database tables if the tables already exist. If you use this option and your database tables already exist, edit the output SQL file so that it does not delete data.

   The Order Mapping tool creates a file that is named OrdersStorage.sql.

   Note

   The Order Mapping tool accepts additional command-line arguments, which are used to generate the XSD schema. Only the command-line arguments that are related to generating the SQL tables and stored procedures are described in this topic.

5. If you are using SQL Server 2005, open the OrdersStorage.sql file from the SQL Management Studio and execute the query against the transactions database.

6. Reset IIS.

7. Delete the directory that you created in step 3.

See Also

Other Resources

How to Map a Weakly Typed Indexer Property to an Explicit Database Column

Orders Mapped Storage

Mapping Purchase Orders to the Database

Generating and Using the XSD Files Required by Different Commerce Server Systems

Orders Extensibility Samples