Share via


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:

  1. Create the table which holds the external data in the catalog sub-system database. Populate the data
  2. 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:

  1. 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!
    }

 

SetJoinExample.zip