Changing Column Matching for OrderTemplate and Basket Persistence
During Basket and OrderTemplate persistence, the Orders system performs column matching against column names in the OrderTemplatesAndBaskets table. When a column of the table matches in a case-insensitive fashion the name of a strongly-typed or weakly-typed property of the Basket or OrderTemplate instance, the value of the property is placed in the column during save and the column value placed in the property during load. This allows you to perform queries against the column values; the binary data in OrderTemplatesAndBaskets.marshaled_data, which is the full binary serialization of the Basket or OrderTemplate instance, is not understandable by SQL Server.
When you wish to modify the OrderTemplatesAndBaskets table to add a column to participate in this matching, you must also modify following stored procedures that interact with this table:
SPI_OrderTemplatesAndBaskets SPS_Search_OrderTemplatesAndBasketsBySoldToIdOrderGroupId, SPS_Search_OrderTemplatesAndBasketsByOrderGroupId, SPS_Search_OrderTemplatesAndBasketsByStatusCodeSoldToId and SPS_Search_OrderTemplatesAndBasketsByStatusCodeSoldToIdName
These stored procedures do not need to be modified:
· SPD_OrderTemplatesAndBaskets
· SPD_BulkDeleteBaskets
· SPM_Ord_MetaData_OrderTemplatesAndBaskets
The following sections detail how to make changes to each of these stored procedures.
Modifying SPI_OrderTemplatesAndBaskets This stored procedure will either update (if the Basket or OrderTemplate row is pre-exisitng) or insert a row into OrderTemplatesAndBaskets. If you add a column to the OrderTemplatesAndBaskets table, perform the following steps to update SPI_OrderTemplatesAndBaskets:
· Add a parameter to SPI_OrderTemplatesAndBaskets that follows the pattern, “@@param_<ColumName>” in the same order that the new column is specified in the OrderTemplatesAndBaskets declaration. Note that the parameter @@param_marshaled_data must appear last in the parameter list.
· Modify the two UPDATE and one INSERT statements in the stored procedure to set the column value to the value of the parameter.
Modifying SPS_OrderTemplatesAndBasketsByStatusCodeSoldToIdName This stored procedure searches for a specific Basket or OrderTemplate based on the SoldToID and order name (name can be null). Update this stored procedure by adding the new column name to the two SELECT statements that perform the search query. Add the new column in the same order as its declaration in the OrderTemplatesAndBaskets table definition. Note that the marshaled_data column must be the first column in each of these SELECT statements.
Modifying SPS_OrderTemplatesAndBasketsByStatusCodeSoldToId
This stored procedure searches for a specific Basket or OrderTemplate based on the SoldToID. Update this stored procedure by adding the new column name to the SELECT statement that performs the search query. Add the new column in the same order as its declaration in the OrderTemplatesAndBaskets table definition. Note that the marshaled_data column must be the first column in each of these SELECT statements.
Modifying SPS_OrderTemplatesAndBasketsByOrderGroupId
This stored procedure searches for a specific Basket or OrderTemplate based on the OrderGroupId. Update this stored procedure by adding the new column name to the SELECT statement that performs the search query. Add the new column in the same order as its declaration in the OrderTemplatesAndBaskets table definition. Note that the marshaled_data column must be the first column in each of these SELECT statements.
Modifying SPS_OrderTemplatesAndBasketsBySoldToIdOrderGroupId
This stored procedure searches for a specific Basket or OrderTemplate based on the SoldToId and OrderGroupId. Update this stored procedure by adding the new column name to the SELECT statement that performs the search query. Add the new column in the same order as its declaration in the OrderTemplatesAndBaskets table definition. Note that the marshaled_data column must be the first column in each of these SELECT statements.
