Commerce Server 2007 SetJoin API
The official documentation for the commendable SetJoin API in commerce Server 2007 seems more like a class reference without any code samples or introductory material into the way SetJoin should be used. So here's the "low-down" as to how a SetJoin API needs to be developed.
A fully tested and working version of the C# code project file is attached with this post!
Problem: While developing a commerce web site, you come across a situation in which there is some data that needs to be pulled in, say some special code for a product, which is not included in the product in the catalog system. This code might or might not be present for each of the products and is stored in a table which is updated by a different sub-system by (say) a nightly process. How do you retrieve the code for the product if it exists from the external table without hitting the DB twice.
Solution: Use the SetJoin APIs to perform a join of the ProductCatalog with the external table when specifying the search.
Caveat: The SetJoin APIs *CANNOT* be used with the SpecificationSearch API (also called as Guided Search) of the catalog system but only with the CatalogSearch APIs. The SpecificationSearch can be mimicked by using the CategoriesClause in the CatalogSearch API resulting in a minimal impact on the search performance.
Steps to Perform a SetJoin action:
I. Database Actions:
- Create the table which holds the external data in the catalog sub-system database. Populate the data
- Assign Select permission for the Catalog Reader and the CS runtime account so that you don't get an access denied error
II. Catalog Web Service Configuration:
The Catalog Web Service needs to have a config entry so that the join is allowed by the web service. The following snippet needs to be added in the web.config (courtesy Max Akbar's Blog). The complete web.config entry for the Commerce Server / catalogWebServices section would be something like:
<catalogWebService siteName="BuyOnline" authorizationPolicyPath="CatalogAuthorizationStore.xml" debugLevel="Production" maxChunkSize="1024" maxUploadFileSize="204800" timeOutHours="24" enableInventorySystem="true" disableAuthorization="false" maxSearchResults="500"> <cache enable="false" schemaTimeout="5" itemInformationCacheTimeout="5" itemHierarchyCacheTimeout="5" itemRelationshipsCacheTimeout="5" itemAssociationsCacheTimeout="5" catalogCollectionCacheTimeout="5"/> <!--Add JOIN TABLE entry here--> <JoinTable> <JoinTable>tblANDealerStore</JoinTable> </JoinTable> </catalogWebService>III. The C# code for the SetJoin API
private static void TestSetJoin() { //Accessing the site agent of a hypotheticla site BuyOnline CatalogSiteAgent catalogSiteAgent = new CatalogSiteAgent(); catalogSiteAgent.SiteName = "BuyOnline";//Here its assumed that the context under which the method runs // is already added in the AZMAN for the catalog web service catalogSiteAgent.AuthorizationMode = AuthorizationMode.ThreadContext; catalogSiteAgent.AuthorizationPolicyPath = @"C:\Inetpub\wwwroot\CatalogWebService\CatalogAuthorizationStore.xml"; //Inventory subsytem being ignored catalogSiteAgent.IgnoreInventorySystem = true; //configure the caching parameters CacheConfiguration cacheConfiguration = new CacheConfiguration(); cacheConfiguration.CacheEnabled = false; //Get the catalog context for the current site CatalogContext catalogContext = CatalogContext.Create(catalogSiteAgent, cacheConfiguration); //Get the product catalog for the site ProductCatalog vehicleCatalog = (ProductCatalog)catalogContext.GetCatalog("VehicleBaseCatalog"); //Prepare the join table information JoinTableInformation jti = new JoinTableInformation(); jti.JoinType = CatalogJoinType.InnerJoin; //the column name of the source aka ProductCatalog table in the database jti.SourceJoinKey = "HyperionId"; //the column name on which the join has to be performed at the external table jti.TargetJoinKey = "HYPERION_ID"; //the external table name which needs to match with the web.config entry of the // Catalog Web Service jti.TargetTableName = "tblANDealerStore"; //prepare the catalog search CatalogSearch genericSearch = catalogContext.GetCatalogSearch(); //prepare the options for the search genericSearch.SearchOptions = new CatalogSearchOptions(); //specify the properties genericSearch.SearchOptions.PropertiesToReturn = "Vin,ProductId,HyperionId"; //specify which class types you want to retrieve genericSearch.SearchOptions.ClassTypes = CatalogClassTypes.ProductClass; //set the Join Table Information prepare above genericSearch.JoinTableInformation = jti; //set the categories to minimize the impact of the CatalogSearch being used // We cannot use SpecificationSearch for the SetJoin genericSearch.CategoriesClause = "CategoryName = 'Chevrolet'"; //Add the SqlWhere clause for the items you want to select. //to select all items specify "1=1" genericSearch.SqlWhereClause = "Model='Tahoe'"; //perform the search CatalogItemsDataSet genericSearhResult = genericSearch.Search(); Console.WriteLine(string.Format("Results found = {0}", genericSearhResult.CatalogItems.Count)); //for the result returned, display the columns retrieved and check if the // join is as per your liking for (int i = 0; i < genericSearhResult.CatalogItems.Columns.Count; i++) { Console.Write(string.Format("Column Name:{0}", genericSearhResult.CatalogItems.Columns[i].ColumnName )); if (genericSearhResult.CatalogItems.Rows[0][i] != null && genericSearhResult.CatalogItems.Rows[0][i] != DBNull.Value) { Console.WriteLine(string.Format(" | Row[0] Value:{0}", genericSearhResult.CatalogItems.Rows[0][i].ToString())); } else { Console.WriteLine(" | Row[0] Value:<NULL>"); } } } //Thats all folks! }