How to Perform a Search by Using the SQL WHERE Clause
For the latest version of Commerce Server 2007 Help, see the Microsoft Web site.
You can search catalogs using the SQL WHERE clause. This search takes advantage of the SQL language to search property values. For example, you can search for items with a specified price range or color. All properties are searched when using this search method.
You use the CatalogSearch object to create a SQL WHERE clause search. You can search across multiple catalogs, within a single catalog, or within categories across multiple catalogs. The properties of the CatalogSearch object are used to control the search, including the search domain, the type of search, language, sorting, and paging.
If you are searching multiple catalogs, the properties specified in the PropertiesToReturn property must exist in all searched catalogs. To determine if a property exists in all catalogs, use one of the GetSearchableProperties methods on the CatalogContext object.
You can combine this search with the free-text search. For information about the free-text search, see How to Perform a Free-Text Search.
To perform a search using the SQL WHERE clause
Use the GetCatalogSearch method on the CatalogContext object to get a CatalogSearch object.
Use the SqlWhereClause property on the CatalogSearch object to specify the search parameters.
Use the CatalogSearchOptions object to specify the search options.
Use one of the Search methods on the CatalogSearch object to perform the search.
Iterate through the returned dataset to access the individual catalog items.
To combine a free-text search with a SQL WHERE clause search
Use the GetCatalogSearch method on the CatalogContext object to get a CatalogSearch object.
Use the FreeTextSearchPhrase property on the CatalogSearch object to specify the search text.
Set the UseAdvancedFreeTextSearch property on the CatalogSearch object to true.
Use the SqlWhereClause property on the CatalogSearch object to specify the search parameters.
Use the CatalogSearchOptions object to specify the search options.
Use one of the Search methods on the CatalogSearch object to perform the search.
Iterate through the returned dataset to access the individual catalog items.
Example
This example describes how to search a catalog. The example uses both the free-text search and the SQL WHERE clause search. The example first creates the search options. It then gets a CatalogSearch object to perform the search. The example uses the CategoriesClause property to indicate that all categories in the specified catalogs that have "technical" as part of their descriptions should be searched.
The example uses the SQL WHERE clause to search for items in the catalog that have a price less than $10.00. It sets the UseAdvancedFreeTextSearch property to true to include a free-text search. The search string is "sql books". The search returns all items less than $10.00 that have the string "sql books" as part of a property value.
public static CatalogItemsDataSet SearchCatalog(CatalogContext context, string catalogName)
{
// Search the catalog.
// Create the search options.
CatalogSearchOptions searchOptions = new CatalogSearchOptions();
searchOptions.PropertiesToReturn = "CategoryName, DefinitionName, i_classtype, ProductId, VariantId, DisplayName";
searchOptions.SetPaging(1, 20);
searchOptions.SortProperty = "CategoryName";
CatalogSearch catalogSearch = context.GetCatalogSearch();
// Specify the catalogs to search.
// This is a comma-separated list of catalogs to search,
// for example, "Catalog1,catalog2".
catalogSearch.CatalogNames = catalogName;
// Specify the categories to search. All categories that match the
// expression are searched. This example searches all categories
// that have "technical" as part of their descriptions.
catalogSearch.CateoriesClause = @"Description like '%technical%'";
// Return all items with price less than $10.00.
catalogSearch.SqlWhereClause = "cy_list_price<10";
// Also use the free-text search.
catalogSearch.UseAdvancedFreeTextSearch = true;
// Return all rows that contain the phrase "sql books".
catalogSearch.AdvancedFreeTextSearchPhrase = "\"sql books\"";
catalogSearch.SearchOptions = searchOptions;
// Perform the search.
int totalRecords = 0;
CatalogItemsDataSet catalogItems = catalogSearch.Search(out totalRecords);
Console.WriteLine(totalRecords);
// Access each of the returned items.
foreach (CatalogItemsDataSet.CatalogItem catalogItem in catalogItems.CatalogItems)
{
Console.WriteLine(catalogItem.CategoryName);
Console.WriteLine(catalogItem.DisplayName);
}
return catalogItems;
}