Item Finder Sample

Article
12/03/2008

The ItemFinder sample demonstrates techniques for implementing search capabilities in database applications. Using the AdventureWorks sample database and the Full-Text Search capabilities of Microsoft SQL Server 2005, ItemFinder allows users to search through resumes and product descriptions stored as xml, as well as documents stored as binary large objects (BLOBs). This sample application demonstrates:

Advanced techniques, such as paging and caching.
New features in Full-Text Search, such as enhanced multilingual capabilities and the implementation of xml data type.

Default Location: C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Full Text Search\ItemFinder

Languages

Transact-SQL

Features

Prerequisites

Before running this sample, make sure the following software is installed:

SQL Server 2005
AdventureWorks database is included with SQL Server 2005, and is also available at the Microsoft SQL Server Developer Center.

Building the Sample

If you have not already created a strong name key file, generate the key file using the following instructions.

To generate a strong name key file

Open a Microsoft Visual Studio 2005 command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.

-- or --

Open a Microsoft .NET Framework command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.
Use the change directory command (CD) to change the current directory of the command prompt window to the folder where the samples are installed.

Note

To determine the folder where samples are located, click the Start button, point to All Programs, point to Microsoft SQL Server, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <system_drive>:\Program Files\Microsoft SQL Server\100\Samples.
At the command prompt, run the following command to generate the key file:

sn -k SampleKey.snk

Important

For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.

To build the sample, you must do the following:

Step 1: Load and execute scripts

In SQL Server Management Studio, load and execute the Scripts\install.sql script to set up full-text indexes and create the following stored procedures:
- usp_SearchResumes
- usp_SearchProducts
- usp_SearchDocuments
- usp_AddDocument

Step 2: Build the application

Build the application by using Microsoft Visual Studio 2005 or type the following in a .NET Framework command prompt window:

msbuild /nologo /verbosity:quiet /property:Configuration=Debug CS\ItemFinder.sln

Running the Sample

To run the sample, you need to:

Step 1: Run the sample

Run bin\Release\ItemFinder.exe (or bin\Debug\ItemFinder.exe).

Comments

The ItemFinder sample application provides a user interface that allows you to search resumes, product descriptions, and documents. You also will be able to use the sample to add documents to the Document table.

Sample data is available in a number of languages and formats. Through a set of drop-down lists, you can select a library (Resumes, Products or Documents), language (any language supported by Full-Text Search), maximum number of results, and search type (CONTAINS or FREETEXT).

Use ItemFinder for the following tasks:

Choose a search condition

Enter the search condition in the text box, and click Go to execute the query.

If you choose the search type CONTAINS, ItemFinder uses the contains_search_condition syntax. For example, you can search for "rear reflector" using the proximity operator NEAR by typing in the text box: "rear" NEAR "reflector". Whatever the contains_search_condition you use, make sure the search text you type is enclosed in double quotation marks ("search text"). For more information, see CONTAINS (Transact-SQL).

Similarly, if you choose the search type FREETEXT, ItemFinder uses the freetext_search_condition syntax. For example, using the "rear reflector" search condition above, type in the text box: rear reflector. Unlike CONTAINS, however, you do not enclose the search text in double quotation marks. For more information, see FREETEXT (Transact-SQL).

Browse large search results

If the search results cannot fit a single page of the ListView element, then only the first page of results is displayed. A simple, efficient in-memory cache stores the key and rank values that matched the search criteria and any data that has already been retrieved.

To scroll back and forth through the pages of results, use the Last and Next buttons.

To clear the cache, click Clear Results, perform a new search, or close the application.

Change the host name of the database server

The host name of the database server to be used is stored in the app.config file.

To change the host name:

Click Edit, and then click Database Settings...
Edit the app.config file directly.

Add supported document types to the Document table

To add document types, click File, and then click Add New File....

In addition, you can use the Add New File... menu for the following tasks:

Specify a name for a file.
Place a file in the file system.
Specify the file type.
Store a short description of the file.

Get help on the sample

To obtain information about the sample, click the Help menu and select About.

Change History

Release	History
5 December 2005	Changed content: Changed instructions for generating a key file, including the name and location of the key file.