How to Create Catalog Queries in Commerce Server

  • Article

This sample shows how to select a category and drill down into subcategories and products. It also illustrates how to retrieve child categories for a category and child products for a category; how to retrieve and display a product with variants (color); how to perform a simple search of the product catalog; and how to query to retrieve simple discounts that apply to a product.

Note This sample code is provided to illustrate a concept and should not be used in applications or Web sites, as it may not illustrate the safest coding practices. Microsoft assumes no liability for incidental or consequential damages should the sample code be used for purposes other than as intended.

Prerequisites

  • Your have installed the Commerce Server 2009 R2 SDK Samples in your development environment.

Unpackage and Configure the SolutionStorefrontWithSampleData.pup

You must first unpackage and configure the Commerce Server site prepackaged with Commerce Server 2009 R2 (the PUP file "SolutionStorefrontWithSampleData.pup").

The samples depend upon the data within this package.

To create the Commerce Server site

  1. In Windows Explorer, navigate to the <dir>\Pup Packages.

  2. Double-click the SolutionStorefrontWithSampleData.pup file.

  3. In the Unpack screen, select Quick Unpack, and then click Next.

  4. In the Quick Unpack screen, ensure that the SQL Server computer name is correct, enter the SQL Server login name and password or select Windows Authentication as the authentication method, and then click Next.

  5. In the Profiling System screen, accept the defaults, and then click Next.

  6. In the second Profiling System screen, accept the defaults, and then click OK.

  7. In the Commerce Server Site Packager dialog box, click OK.

  8. In the Unpacking is complete! screen, click Finish.

The Commerce Server site is unpacked to the Solution Storefront in IIS.

Building the Solution

You will have to build the CatalogAPIUsage.sln before proceeding.

To build the solution within Visual Studio

  1. In Windows Explorer, go to <dir>\Extensibility Kits\Samples\.

  2. Unzip the FoundationSamples.zip to a new directory.

  3. Browse to the FoundationSamples folder.

  4. Double-click the Catalog folder.

  5. Double-click the CatalogAPIUsage.sln file. This opens the solution in Visual Studio.

  6. Under Solution Explorer, right-click Solution <solution name>, and then click Build Solution.

Creating a Web Application

Use the following steps to add the sample application to your Commerce Server site in Internet Information Services (IIS). When you do so, you will choose an IIS application pool to assign to the Catalog Web application. To make sure that the application works correctly, the application pool you select must comply with the following:

  • The application pool must have all the necessary security privileges to access your Commerce Server site and its associated databases.

To add the application to the Commerce Server site in IIS 7.0

  1. In Internet Information Services (IIS) Manager, in the navigation tree, click Sites, right-click Default Web Site, and then click Add Application.

  2. In the Alias box, type a name for your sample.

  3. Click the Select button next to the Application pool box, select the application pool to use, and then click OK.

    Note

    The application pool must have all the necessary security privileges to access your Commerce Server site and its associated databases.

  4. In the PhysicalPath box, type the full path of the application. This is the path to which you browsed to the FoundationSamples\Catalog folder.

    For example, when you extract the FoundationSamples.zip to the same folder, the application path is as follows: %commerce_server_root%Extensibility Kits\Samples\FoundationSamples\Catalog.

  5. Click OK.

  6. In Internet Explorer, go to the following URL to access the Web application:

    https://localhost/<your_sample_vdir>/Default.aspx

    where <your_sample_vdir> is the value used in step 4.

Examples

The following code snippet illustrates how to build a query to retrieve child categories for a catalog. It assumes usage of wrappers that are included in the sample.

// Add Related Query Operation for child categories
            {
                CommerceQueryRelatedItem<Category> queryChildCategories = new CommerceQueryRelatedItem<Category>(Category.RelationshipName.ChildCategories);
                queryChildCategories.Model.Properties.Add(Category.PropertyName.Id);
                queryChildCategories.Model.Properties.Add(Category.PropertyName.DisplayName);

                queryChildCategories.SearchCriteria.SortProperties = new List<CommerceSortProperty>();
                queryChildCategories.SearchCriteria.SortProperties.Add(new CommerceSortProperty(
                    Category.ModelNameDefinition,
                    Category.PropertyName.DisplayName,
                    Microsoft.Commerce.SortDirection.Descending));
                queryChildCategories.SearchCriteria.NumberOfItemsToReturn = 10;
                queryChildCategories.SearchCriteria.FirstItemIndex = 0;
                queryChildCategories.SearchCriteria.ReturnTotalItemCount = true;

                queryBuilder.RelatedOperations.Add(queryChildCategories);
            }

The following code snippet illustrates how to build a query to retrieve a specific product and its variants. It assumes usage of wrappers that are included in the sample.

{
            // Query for a Product
            var queryBuilder = new CommerceQuery<Product>();
            string productId = "AW425-11";

            queryBuilder.SearchCriteria.Model.Id = productId;
            queryBuilder.SearchCriteria.Model.CatalogId = "Adventure Works Catalog";

            queryBuilder.Model.Properties.Add(Product.PropertyName.Id);
            queryBuilder.Model.Properties.Add(Product.PropertyName.DisplayName);
            queryBuilder.Model.Properties.Add(Product.PropertyName.Description);
            queryBuilder.Model.Properties.Add(Product.PropertyName.ListPrice);
            queryBuilder.Model.Properties.Add(Product.PropertyName.DefinitionName);
            queryBuilder.Model.Properties.Add(Product.PropertyName.ImageFileName);

            // Add Related Query Operation for Variants
            {
                CommerceQueryRelatedItem<Variant> queryVariants = new CommerceQueryRelatedItem<Variant>(Product.RelationshipName.Variants);
                queryBuilder.RelatedOperations.Add(queryVariants);
            }

The following code snippet illustrates how to build a query to search the product catalog. It assumes usage of wrappers that are included in the sample.

{
            if (!string.IsNullOrEmpty(this.txtSearchKey.Text))
            {
                var query = new CommerceQuery<CatalogEntity, CommerceCatalogFullTextSearchBuilder>();
                query.SearchCriteria.Catalogs.Add(CommerceFoundationServiceAgent.Catalog);
                query.SearchCriteria.FirstItemIndex = 1;
                query.SearchCriteria.FullTextSearchType = CommerceFullTextSearchType.FreeText;
                query.SearchCriteria.NumberOfItemsToReturn = 100;
                query.SearchCriteria.Phrase = this.txtSearchKey.Text;
                query.SearchCriteria.ReturnTotalItemCount = true;
                query.SearchCriteria.WhereClause = null;
                query.SearchCriteria.SortProperties = new List<CommerceSortProperty>() { new CommerceSortProperty(Product.ModelNameDefinition, Product.PropertyName.Id, Microsoft.Commerce.SortDirection.Descending) };
                query.SearchCriteria.TypesToSearch = CommerceCatalogEntityTypes.Product;

                CommerceResponse response = CommerceFoundationServiceAgent.Execute(query);
                CommerceQueryOperationResponse queryResponse = response.OperationResponses[0] as CommerceQueryOperationResponse;

                int totalResultCount = queryResponse.CommerceEntities.Count;

                if (totalResultCount != 0)
                {
                    this.RenderHTMLTable();
                    foreach (CommerceEntity entity in queryResponse.CommerceEntities)
                    {
                        this.RenderHTMLRow(entity);
                    }

                    this.Controls.Add(this.productTable);
                }
                else
                {
                    Response.Output.WriteLine("No search results were returned.");
                }

The following code snippet illustrates how to build a query to retrieve the simple discounts (discounts with a single clause expression) that apply to a product. The code snippet assumes usage of wrappers that are included in the sample.

            var queryBuilder = new CommerceQuery<Product>();
            queryBuilder.SearchCriteria.Model.CatalogId = "Adventure Works Catalog";
            queryBuilder.SearchCriteria.Model.Id = "AW042-03";

            var discountsQuery = new CommerceQueryRelatedItem<DiscountDefinition, CommerceModelSearch<DiscountFilter>>(Product.RelationshipName.DiscountDefinitions);
            discountsQuery.SearchCriteria.Model.FilterOnAward = true;
            discountsQuery.SearchCriteria.Model.FilterOnCondition = true;
            discountsQuery.SearchCriteria.Model.IncludeDiscountsWithEligibilityRequirements = false;
            discountsQuery.SearchCriteria.Model.IncludeDiscountsWithPromoCodes = false;
            queryBuilder.RelatedOperations.Add(discountsQuery);

See Also

Other Resources

How to Display Discounts for Specific Products