Using SQL Server Full-Text Search

Microsoft SQL Server 7.0 and higher includes the Full-Text Search Service. You can use this service to perform queries that you could not efficiently perform with a standard SQL SELECT statement. For example, the Full-Text Search Service enables you to do the following:

• Search for particular combinations of words or phrases

• Search for a word with a particular prefix

• Search for inflectional variations of verbs and nouns

• Search for words that appear near each other

• Search for weighted terms

Imagine that you want to build a job site. In that case, you need to create a page that enables users to search through resumes or job descriptions that contain certain key phrases.

Or, imagine that you are creating an online store. In that case, you need to build a page that enables users to search through product descriptions. The Full-Text Search Service was designed for these types of queries.

Configuring the Full-Text Search Service

Before you can use the SQL Server Full-Text Search Service, you must complete the following three steps:

1. Install and start the Full-Text Search Service.

2. Define full-text indexing on a table.

3. Populate the full-text index.

The Full-Text Search Service is not installed by default when you install Microsoft SQL Server. You must choose the custom installation option to install the Search Service. You can check whether the Search Service is installed on your server by opening the SQL Server Service Manager and looking for a service named Search Service (see Figure 14.1). If you cannot find the Search Service, you need to rerun the Setup program from the SQL Server installation disk.

Figure 14.1. The SQL Server Service Manager.

After the Search Service is installed, you can start the service by opening the SQL Server Service Manager, selecting the Search Service, and clicking the Start/Continue button.

CAUTION

After you define full-text indexing on a table, you cannot change the columns in a table without removing its full-text index. Because populating a full-text index for a large table can take several hours, dropping and re-adding a full-text index to a table can be a very unpleasant experience.

The next step is to define full-text indexing on a table by completing these steps:

1. Launch the SQL Server Enterprise Manager.

2. Navigate to a particular database table.

3. Right-click the name of the table.

4. Choose Full-Text Index Table.

5. Select Define Full-Text Indexing on a Table.

Completing these steps launches the SQL Server Full-Text Indexing Wizard, which enables you to select several options:

• The unique index to use when indexing the table for full-text search. You cannot use the Search Service with tables that do not have a unique index. The Search Service needs a unique index to match the items in the full-text index with the rows in the table. Typically, the unique index is the primary key of the table.

• The character columns to use when indexing the table for a full-text search. The Full-Text Search Service can index only character columns such as VarChar, Text, and Char. Microsoft SQL Server 2000 also supports the indexing of Image columns.

• The catalog to use for the full-text index. A full-text index is stored in a catalog. If a catalog doesn't already exist, the wizard provides you with the option of creating one.

• The schedule to use for indexing the table for a full-text search. The wizard provides you with the option of creating a scheduled job for indexing the table. For the job to execute, the SQL Server Agent Service must be started. (You can start the Agent Service from the SQL Server Service Manager.)

• Unlike a normal table index, a full-text index is not updated automatically. If you do not populate the index, it will become outdated when new rows are added to the table or existing rows are modified.

• You can provide any population schedule you want. For example, you can repopulate the index every hour , once a day, or once a month.

• You are also provided with the option of performing either a full population or incremental population. A full population repopulates the index starting from scratch. An incremental population scans the table for changed or modified rows. An incremental population can be performed much faster than a full population. However, if you want to perform an incremental population efficiently, the table must contain a TimeStamp column.

The final step is to populate the full-text index. When a full-text index is first created, it's empty. To populate the index, you need to populate the catalog that contains the index.

NOTE

Microsoft SQL Server 2000 also supports a feature called Change Tracking. You can use Change Tracking to automatically update the full-text index when rows in a database table are modified. To learn more details, see the SQL Server Books Online.

You can view the number of items currently indexed in a catalog and total size of the catalog by launching the SQL Server Enterprise Manager, clicking the Catalogs folder for a particular database, and double-clicking the name of a catalog (see Figure 14.2).

Figure 14.2. Displaying the number of indexed items.

To populate the catalog, right-click the name of the catalog and select Start Full Population. Depending on the size of the table, populating the catalog can take several hours.

Performing Free Text Queries with Database Data

When most people perform a search at a Web site, they do not perform a Boolean query. Most people do not understand, or care about, Boolean operators such as AND , OR , and NOT . Instead, users typically enter a few keywords in a search box and hope for the best results.

The Full-Text Search Service includes support for free text queries. A free text query can consist of any set of words, any set of phrases, or even a sentence . The Search Service attempts to identify significant phrases in the query and return a matching set of records.

In the following sections, you learn how to create a search page by using the FREETEXT predicate and FREETEXTTABLE function.

Using the FREETEXT Predicate

The FREETEXT predicate accepts two parameters. The first parameter represents the column to search. You can supply either a single column name or the * character to search all columns in the table. (You can search only columns that have been enabled for full-text indexing.) The second parameter represents the search phrase.

The following sample statement searches the Title column in the Titles table for the phrase secret computer :

 SELECT Title FROM Titles WHERE FREETEXT( Title, 'secret computer' ) 

When you execute this query, the following results are returned:

 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean Cooking with Computers: Surreptitious Balance Sheets Computer Phobic AND Non-Phobic Individuals: Behavior Variations Secrets of Silicon Valley You Can Combat Computer Stress! Straight Talk About Computers The Psychology of Computer Cooking 

Notice that all the titles returned by the query contain some variation of the word secret or computer . For example, the first title contains the word Secrets .

The page in Listing 14.1 illustrates how you can use the FREETEXT predicate in an ASP.NET page (see Figure 14.3).

Listing 14.1 FreeText.aspx

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Button_Click( s As Object, e As EventArgs )   Dim conPubs As SqlConnection   Dim strSearch As String   Dim cmdSearch As SqlCommand   Dim dtrSearch As SqlDataReader   conPubs = New SqlConnection( "Server=Localhost;UID=sa;PWD=secret; Database=Pubs" )   strSearch = "SELECT Title FROM Titles WHERE FREETEXT( Title, @searchphrase )"   cmdSearch = New SqlCommand( strSearch, conPubs )   cmdSearch.Parameters.Add( "@searchphrase", txtSearchPhrase.Text )   conPUbs.Open   dtrSearch = cmdSearch.ExecuteReader()   While dtrSearch.Read     lblResults.Text &= "<li>" & dtrSearch( "Title" )   End While   conPubs.Close End Sub </Script> <html> <head><title>FreeText.aspx</title></head> <body> <form Runat="Server"> <h2>Search Titles:</h2> <asp:TextBox   ID="txtSearchPhrase"   Columns="50"   Runat="Server" /> <asp:Button   Text="Search!"   OnClick="Button_Click"   Runat="Server" /> <hr> <asp:Label   ID="lblResults"   EnableViewState="False"   Runat="Server" /> </form> </body> </html> 

The C# version of this code can be found on the CD-ROM.

Figure 14.3. Using the FREETEXT predicate.

NOTE

You'll need to set up full-text indexing on the Titles table before the page in Listing 14.3 will work. See the earlier section in this chapter, "Configuring the Full-Text Search Service."

The page in Listing 14.1 contains a form with a single TextBox control. If you enter a search phrase in the text box and submit the form, the Button_Click subroutine is executed.

The Button_Click subroutine uses a SqlCommand object to execute a query containing the FREETEXT predicate. The results of the query are displayed in a Label control.

Using the FREETEXTTABLE Function

You can use the FREETEXTTABLE function to perform the same type of free text query that the FREETEXT predicate performs . Like the FREETEXT predicate, the FREETEXTTABLE function searches for a set of words, a phrase, or a sentence. The FREETEXTTABLE function, however, returns a table of matching results.

The advantage of using the FREETEXTTABLE function is that it enables you to display a ranking next to each result. You can display how well each result matched the query.

The FREETEXTTABLE function returns a table with two columns: KEY and RANK. The KEY column represents the unique key of the record associated with the match. The RANK column represents how well the result matched the query. This column returns a value between 0 and 1000, where 1000 represents the best match and 0 represents the worst match.

The FREETEXTTABLE function accepts three parameters: the table to search, the columns to search, and the search phrase. For example, the following query searches the Titles table for all titles that match the search phrase A book about Silicon Valley :

 SELECT * FROM FREETEXTTABLE( Titles, Title, 'A book about Silicon Valley' ) 

Notice that FREETEXTTABLE is used in this query just like a table name; the function returns a table. This query returns the following results:

 KEY    RANK ------ ----------- MC2222 174 PC8888 174 (2 row(s) affected) 

This query returned two records. The values in the KEY column match the unique keys of two records in the Titles table. The two records matched the search phrase equally well, with the value 174.

Typically, you use the FREETEXTTABLE function in a query that joins the table returned by the function with the table being searched. For example, the following query joins the table returned by the function to the Titles table:

 SELECT RANK, Title FROM Titles, FREETEXTTABLE( Titles, Title, 'computer cooking' ) searchTable WHERE [KEY] = Titles.Title_ID ORDER BY RANK DESC 

The preceding statement joins the table returned by the FREETEXTTABLE function to the Titles table by using the common KEY and Title_ID columns. This query returns the following set of records:

 RANK        Title ----------- --------------------------------------------------------------- 224         The Psychology of Computer Cooking 2           Cooking with Computers: Surreptitious Balance Sheets 0           Computer Phobic AND Non-Phobic Individuals: Behavior Variations 0           Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 0           Straight Talk About Computers 0           You Can Combat Computer Stress! (6 row(s) affected) 

The page in Listing 14.2 demonstrates how you can perform a search by using the FREETEXTTABLE function in an ASP.NET page (see Figure 14.4).

Listing 14.2 FreeTextTable.aspx

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Button_Click( s As Object, e As EventArgs )   Dim conPubs As SqlConnection   Dim strSearch As String   Dim cmdSearch As SqlCommand   Dim dtrSearch As SqlDataReader   conPubs = New SqlConnection( "Server=Localhost;UID=sa;PWD=secret; Database=Pubs" )   strSearch = "SELECT RANK, Title FROM Titles," & _     "FREETEXTTABLE( Titles, Title, @searchphrase ) searchTable " & _     "WHERE [KEY] = Titles.Title_ID " & _     "ORDER BY RANK DESC "   cmdSearch = New SqlCommand( strSearch, conPubs )   cmdSearch.Parameters.Add( "@searchphrase", txtSearchphrase.Text )   conPubs.Open()   dtrSearch = cmdSearch.ExecuteReader()   lblResults.Text = "<table border=1 cellspacing=0 cellpadding=4>"   While dtrSearch.Read     lblResults.Text &= "<tr>"     lblResults.Text &= "<td align=right>&nbsp;"     lblResults.Text &= ShowRank( dtrSearch( "RANK" ) )     lblResults.Text &= "</td>"     lblResults.Text &= "<td>" & dtrSearch( "Title" ) & "</td>"     lblResults.Text &= "</tr>"   End While   lblResults.Text &= "</table>"   conPubs.Close() End Sub Function ShowRank( intRank As Integer ) As String   Dim intCounter As Integer   Dim strRank As String   strRank = "<font color=green>"   For intCounter = 1 to intRank / 10     strRank &= "*"   Next   strRank &= "</font>"   Return strRank End Function </Script> <html> <head><title>FreeTextTable.aspx</title></head> <body> <form Runat="Server"> <h2>Search Titles:</h2> <asp:TextBox   ID="txtSearchPhrase"   Columns="50"   Runat="Server" /> <asp:Button   Text="Search!"   OnClick="Button_Click"   Runat="Server" /> <hr> <asp:Label   ID="lblResults"   EnableViewState="False"   Runat="Server" /> </form> </body> </html> 

The C# version of this code can be found on the CD-ROM.

Figure 14.4. Using the FREETEXTTABLE function.

The page in Listing 14.2 displays a search form. When you submit a search phrase, the Button_Click subroutine is executed. This subroutine uses a FULLTEXTTABLE query to retrieve matching results from the Titles table. The results are displayed in a Label control.

The page also includes a function named ShowRank() that converts the value of RANK into a string of * characters . This string is displayed next to each search result.

Performing Boolean Queries with Database Data

The FREETEXT predicate and FREETEXTTABLE function were designed to enable users to execute free-form queries. If you attempt to use Boolean operators in a free text query, the operators are completely ignored.

To perform Boolean queries, you need to use either the CONTAINS predicate or CONTAINSTABLE function. Both the CONTAINS predicate and CONTAINSTABLE function correctly interpret Boolean operators as well as other advanced search options such as proximity operators and weighted search terms.

Using the CONTAINS Predicate

The CONTAINS predicate accepts two parameters: the search column and search phrase. You can list a single column name for the search column or use the * character to represent all columns. You can search only columns that have been enabled for full-text indexing.

The following sample query returns all records from the Titles table that contain the word Computer but not the word Cooking :

 SELECT Title FROM Titles WHERE CONTAINS( Title, 'Computer and not Cooking' ) 

This query returns the following results:

 Computer Phobic AND Non-Phobic Individuals: Behavior Variations You Can Combat Computer Stress! 

However, it does not return the following title from the Titles table:

 The Psychology of Computer Cooking 

The search phrase must contain a valid query. For example, the following query generates an error:

 SELECT Title FROM Titles WHERE CONTAINS( Title, 'Computers AND' ) 

Executing this query generates a syntax error. The problem results from the dangling Boolean AND operator. When you execute a CONTAINS query that generates an error with ASP.NET, an empty result set is returned.

The page in Listing 14.3 illustrates the proper way of executing a CONTAINS query in an ASP.NET page (see Figure 14.5).

Listing 14.3 Contains.aspx

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Button_Click( s As Object, e As EventArgs )   Dim conPubs As SqlConnection   Dim strSearch As String   Dim cmdSearch As SqlCommand   Dim dtrSearch As SqlDataReader   conPubs = New SqlConnection( "Server=Localhost;UID=sa;PWD=secret; Database=Pubs" )   strSearch = "SELECT Title FROM Titles WHERE CONTAINS( title, @searchphrase )"   cmdSearch = New SqlCommand( strSearch, conPubs  )   cmdSearch.Parameters.Add( "@searchphrase", txtSearchphrase.Text )   conPubs.Open()   dtrSearch = cmdSearch.ExecuteReader()   While dtrSearch.Read()     lblResults.Text &= "<li>" & dtrSearch( "Title" )   End While   conPubs.Close End Sub </Script> <html> <head><title>Contains.aspx</title></head> <body> <form Runat="Server"> <h2>Search Titles:</h2> <asp:TextBox   ID="txtSearchPhrase"   Columns="50"   Runat="Server" /> <asp:Button   Text="Search!"   OnClick="Button_Click"   Runat="Server" /> <hr> <asp:Label   ID="lblResults"   EnableViewState="False"   Runat="Server" /> </form> </body> </html> 

The C# version of this code can be found on the CD-ROM.

Figure 14.5. Using the CONTAINS predicate.

Using the CONTAINSTABLE Function

The CONTAINSTABLE function is similar to the CONTAINS predicate. However, the CONTAINSTABLE function returns a table. You use this function when you need to display a ranking next to each search result.

The CONTAINSTABLE function returns a table that contains two columns: KEY and RANK. The KEY column represents the unique key associated with each result. The RANK column represents how well each result matches the query. RANK contains a value between 0 and 1000, where higher numbers represent better matches.

The CONTAINSTABLE function accepts three parameters: the table to search, the columns to search, and the search phrase. You can supply a single column name for the column or the * character to search all columns enabled for full-text indexing.

The following query, for example, performs a search on the Title column of the Titles table for the search phrase Cooking AND NOT Computer :

 SELECT * FROM CONTAINSTABLE( Titles, Title, 'Cooking AND NOT Computer' ) 

This query returns the following results:

 KEY    RANK ------ ----------- BU1111 32 TC3218 16 (2 row(s) affected) 

The values of the KEY column represent values in the unique Title_ID column in the Titles table. The values of the RANK column represent how well each result matches the query.

Typically, you join the table returned by the CONTAINSTABLE function with the table being searched. For example, the following query joins the table returned by the CONTAINSTABLE function with the Titles table:

 SELECT RANK, Title FROM Titles, CONTAINSTABLE( Titles, Title, 'Cooking AND NOT Computer' ) searchTable WHERE [KEY] = Titles.Title_ID ORDER BY RANK DESC 

This query returns the following results:

 RANK        Title ----------- --------------------------------------------------------------- 32          Cooking with Computers: Surreptitious Balance Sheets 16          Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean (2 row(s) affected) 

The query returns both the RANK and Title for each search result.

Notice that the query Cooking AND NOT Computer does not exclude the result Cooking with Computers: Surreptitious Balance Sheets . The word Computer does not match the word Computers . In other words, the CONTAINS predicate and CONTAINSTABLE function do not automatically handle pluralization.

You can use prefix terms with the CONTAINSTABLE predicate. To use a prefix term, you must enclose the search term in quotation marks and use * as a wildcard character like this:

 SELECT RANK, Title FROM Titles, CONTAINSTABLE( Titles, Title, 'Cooking AND NOT "Computer*"' ) searchTable WHERE [KEY] = Titles.Title_ID ORDER BY RANK DESC 

This query excludes titles that contain either the word Computer or the word Computers .

You also can use the CONTAINSTABLE function to match inflectional variations of a word. For example, the following query matches both Cook and Cooking :

 SELECT RANK, Title FROM Titles, CONTAINSTABLE( Titles, Title, 'FormsOf( INFLECTIONAL, Cook )' ) searchTable WHERE [KEY] = Titles.Title_ID ORDER BY RANK DESC 

This example uses the FormsOf operator to find inflectional variations of the word Cook .

You can also use the CONTAINSTABLE function with a proximity operator. For example, you might want results in which the word Computer appears close to the word Cooking to be ranked higher than results in which the word Computer appears far from the word Cooking . The following query illustrates how you can use the NEAR proximity operator:

 SELECT RANK, Title FROM Titles, CONTAINSTABLE( Titles, Title, 'Cooking NEAR Computers' ) searchTable WHERE [KEY] = Titles.Title_ID ORDER BY RANK DESC 

The page in Listing 14.4 illustrates how you can use the CONTAINSTABLE function in an ASP.NET page (see Figure 14.6).

Listing 14.4 ContainsTable.aspx

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Button_Click( s As Object, e As EventArgs )   Dim conPubs As SqlConnection   Dim strSearch As String   Dim cmdSearch As SqlCommand   Dim dtrSearch As SqlDataReader   conPubs = New SqlConnection( "Server=Localhost;UID=sa;PWD=secret; Database=Pubs" )   strSearch = "SELECT RANK, Title FROM Titles," & _     "CONTAINSTABLE( Titles, Title, @searchphrase ) searchTable " & _     "WHERE [KEY] = Titles.Title_ID " & _     "ORDER BY RANK DESC "   cmdSearch = New SqlCommand( strSearch, conPubs )   cmdSearch.Parameters.Add( "@searchphrase", txtSearchphrase.Text )   conPubs.Open()   dtrSearch = cmdSearch.ExecuteReader()   lblResults.Text = "<table border=1 cellspacing=0 cellpadding=4>"   While dtrSearch.Read     lblResults.Text &= "<tr>"     lblResults.Text &= "<td align=right>&nbsp;"     lblResults.Text &= ShowRank( dtrSearch( "RANK" ) )     lblResults.Text &= "</td>"     lblResults.Text &= "<td>" & dtrSearch( "Title" ) & "</td>"     lblResults.Text &= "</tr>"   End While   lblResults.Text &= "</table>"   conPubs.Close End Sub Function ShowRank( intRank As Integer ) As String   Dim intCounter As Integer   Dim strRank As String   strRank = "<font color=green>"   For intCounter = 1 to intRank \ 10     strRank &= "*"   Next   strRank &= "</font>"   Return strRank End Function </Script> <html> <head><title>ContainsTable.aspx</title></head> <body> <form Runat="Server"> <h2>Search Titles:</h2> <asp:TextBox   ID="txtSearchPhrase"   Columns="50"   Runat="Server" /> <asp:Button   Text="Search!"   OnClick="Button_Click"   Runat="Server" /> <hr> <asp:Label   ID="lblResults"   EnableViewState="False"   Runat="Server" /> </form> </body> </html> 

The C# version of this code can be found on the CD-ROM.

Figure 14.6. Using the CONTAINSTABLE predicate.

Uploading and Indexing Binary Documents

Microsoft SQL Server 2000, but not earlier versions, supports indexing documents stored in an image column. You can use this feature to store and index Microsoft Word documents, PowerPoint presentations, HTML, XML, and text documents.

In the following sections, you learn how to create an ASP.NET application that enables you to upload documents, such as Microsoft Word documents, to a Microsoft SQL Server 2000 database and search through the documents. You could use this application, for example, to create a central document repository for a company intranet.

To create this application, you need to build three ASP.NET pages:

• DBUpload.aspx You use this page to upload files to a Microsoft SQL Server 2000 database table.

• DBUploadSearch.aspx You use this page to search through binary files stored in a database table.

• showDBUpload.aspx You use this file to retrieve and display binary files stored in the database table.

Configuring the Database

Before you do anything else, you need to create a database table named Uploads to contain your documents. You can create this table by executing the SQL CREATE TABLE statement included in Listing 14.5 within Microsoft Query Analyzer.

Listing 14.5 CreateUploadsTable.sql

 Create Table Uploads (   u_id INT NOT NULL IDENTITY Primary Key,   u_title Varchar( 200 ),   u_document Image,   u_documentType Varchar( 50 ),   u_entrydate DATETIME Default getDate() ) 

The C# version of this code can be found on the CD-ROM.

Next, you need to enable full-text indexing for this table by completing the following steps:

1. Launch Microsoft SQL Server Enterprise Manager.

2. Navigate to the Uploads table in your database.

3. Right-click the name of the table and select Full-Text Index Table and then Define Full-Text Indexing on a Table.

Completing these steps launches the Full-Text Indexing Wizard. After the wizard opens, enter the following options:

• Select the u_title and u_document columns for full-text indexing. You need to specify the column that contains the document type for the u_document column. Select the u_documentType column for the document type column.

• Select any full-text catalog for storing your full-text index. If one doesn't already exist, you can create it.

• Don't select a table population schedule. You'll configure the full-text index to be automatically updated.

After you complete the wizard, you must perform one last configuration step. So that the full-text index is updated automatically as soon as someone uploads a new file to the Uploads database table, you need to enable Change Tracking for the Uploads table. To enable Change Tracking, right-click the Uploads table, choose Full-Text Index Table, and then Change Tracking.

To update the full-text index immediately after changes to the Uploads table, right-click the Uploads table and choose Full-Text Index Table and then Update Index in Background.

Now that you've made these changes, whenever a new document is added to the Upload table's image column, the full-text index is automatically updated.

Creating the Database Upload Page

The first ASP.NET database page that you need to create is DBUpload.aspx . This page enables users to select a file from their hard drive to upload to the database server. The complete listing for the DBUpload.aspx page is included in Listing 14.6 (see Figure 14.7).

Listing 14.6 DBUpload.aspx

 <%@ Import Namespace="System.IO" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Button_Click( s As Object, e As EventArgs )   Dim strFileExtension As String   Dim strFileType As String   Dim intFileLen As Integer   Dim objStream As Stream   Dim conMyData As SqlConnection   Dim strInsert As String   Dim cmdInsert As SqlCommand   If Not IsNothing( txtFileContents.PostedFile ) Then     ' Determine File Type     strFileExtension = RIGHT( txtFileContents.PostedFile.FileName, 4 )     SELECT Case strFileExtension.ToLower       CASE ".doc"         strFileType = "doc"       CASE ".ppt"         strFileType = "ppt"       CASE ".htm"         strFileType = "htm"       CASE "html"         strFileType = "htm"       CASE ELSE         strFileType = "txt"     End SELECT     ' Grab the contents of uploaded file     intFileLen = txtFileContents.PostedFile.ContentLength     Dim arrFile( intFileLen ) As Byte     objStream = txtFileContents.PostedFile.InputStream     objStream.Read( arrFile, 0, intFileLen )     ' Add Uploaded file to database     conMyData = New SqlConnection( "Server=Localhost;UID=sa;PWD=secret; Database=myData" )     strInsert = "Insert Uploads ( u_title, u_documentType, u_document ) " & _       "Values ( @title, @fileType, @document )"     cmdInsert = New SqlCommand( strInsert, conMyData )     cmdInsert.Parameters.Add( "@title", txtfileTitle.Text )     cmdInsert.Parameters.Add( "@fileType", strFileType )     cmdInsert.Parameters.Add( "@document", arrFile )     conMyData.Open()     cmdInsert.ExecuteNonQuery()     conMyData.Close()   End If End Sub </Script> <html> <head><title>DBUpload.aspx</title></head> <body> <form   enctype="multipart/form-data"   Runat="Server"> <h2>Upload File:</h2> <b>File Title:</b> <br> <asp:TextBox   ID="txtFileTitle"   Runat="Server" /> <asp:RequiredFieldValidator   ControlToValidate="txtFileTitle"   Text="Required!"   Runat="Server" /> <p> <b>File:</b> <br> <input   id="txtFileContents"   type="file"   Runat="Server" /> <p> <asp:Button   Text="Upload File!"   OnClick="Button_Click"   Runat="Server" /> </form> </body> </html> 

The C# version of this code can be found on the CD-ROM.

Figure 14.7. Uploading a document to the database.

The DBUpload.aspx page in Listing 14.6 contains a form with a file upload button. For example, you can select a Microsoft Word document from your local hard drive, submit the form, and the file is added to the Uploads database table.

The uploaded file is added to the database table in the Button_Click subroutine. The uploaded file is converted into a byte array and then added to the Upload table's u_document column. Because Change Tracking is enabled on the Uploads table, any document you upload is immediately indexed.

Creating the Database Search Page

The DBUploadSearch.aspx page enables users to search through all the files that have been uploaded to the database server. This page enables users to perform free-form queries. The complete code for the DBUploadSearch.aspx file is contained in Listing 14.7 (see Figure 14.8).

Listing 14.7 DBUploadSearch.aspx

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Button_Click( s As Object, e As EventArgs )   Dim conMyData As SqlConnection   Dim strSearch As String   Dim cmdSearch As SqlCommand   Dim dtrSearch As SqlDataReader   conMyData = New SqlConnection( "Server=Localhost;UID=sa;PWD=secret; Database=myData" )   strSearch = "SELECT RANK, u_id, u_title FROM Uploads," & _     "FREETEXTTABLE( Uploads, *, @searchphrase ) searchTable " & _     "WHERE [KEY] = uploads.u_ID " & _     "ORDER BY RANK DESC "   cmdSearch = New SqlCommand( strSearch, conMyData )   cmdSearch.Parameters.Add( "@searchphrase", txtSearchphrase.Text )   conMyData.Open()   dtrSearch = cmdSearch.ExecuteReader()   lblResults.Text = "<table border=1 cellspacing=0 cellpadding=4>"   While dtrSearch.Read     lblResults.Text &= "<tr>"     lblResults.Text &= "<td align=right>"     lblResults.Text &= dtrSearch( "RANK" ) / 10     lblResults.Text &= "%</td>"     lblResults.Text &= "<td><a href="""     lblResults.Text &= String.Format( "ShowDBUpload.aspx?id={0}", dtrSearch( "u_id" ) )     lblResults.Text &= """>" & dtrSearch( "u_title" )     lblResults.Text &= "</a></td>"     lblResults.Text &= "</tr>"   End While   lblResults.Text &= "</table>"   conMyData.Close End Sub </Script> <html> <head><title>DBUploadSearch.aspx</title></head> <body> <form Runat="Server"> <h2>Search Uploads:</h2> <asp:TextBox   ID="txtSearchPhrase"   Columns="50"   Runat="Server" /> <asp:Button   Text="Search!"   OnClick="Button_Click"   Runat="Server" /> <hr> <asp:Label   ID="lblResults"   EnableViewState="False"   Runat="Server" /> </form> </body> </html> 

The C# version of this code can be found on the CD-ROM.

Figure 14.8. Searching uploaded documents.

The DBUploadSearch.aspx page contained in Listing 14.7 contains a search form. When you submit a search phrase, a query is constructed by using the FREETEXTTABLE function. This query is executed against all the free-text indexed columns in the Uploads database table. The results of the query are displayed in a Label control.

Each search result is displayed as a hypertext link. If you click a link, you go to the ShowDBUpload.aspx page. When you link to the ShowDBUpload.aspx page, a query string variable named ID representing the unique ID of the document that you selected is passed.

Creating the Show Database Upload Page

The ShowDBUpload.aspx page displays a particular document from the Uploads database table. The complete code for this page is contained in Listing 14.8.

Listing 14.8 ShowDBUpload.aspx

 <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load   Dim intItemID As Integer   Dim conMyData As SqlConnection   Dim strSelect As String   Dim cmdSelect As SqlCommand   Dim dtrSearch As SqlDataReader   intItemID = Request.Params( "id" )   conMyData = New SqlConnection( "Server=Localhost;UID=sa;PWD=secret; Database=myData" )   strSelect = "SELECT u_documentType, u_document From Uploads " & _     "WHERE u_id=@itemID"   cmdSelect = New SqlCommand( strSelect, conMyData )   cmdSelect.Parameters.Add( "@itemID", intItemID )   conMyData.Open()     dtrSearch = cmdSelect.ExecuteReader()     If dtrSearch.Read       ' Set Content Type       Response.ClearHeaders()       Select Case dtrSearch( "u_documentType" )         CASE "doc"           Response.ContentType = "application/msword"         CASE "ppt"           Response.ContentType = "application/ppt"         CASE "txt"           Response.ContentType = "text/plain"       End Select       Response.BinaryWrite( dtrSearch( "u_document" ) )     End IF     dtrSearch.Close    conMyData.Close End Sub </Script> 

The C# version of this code can be found on the CD-ROM.

The ShowDBUpload.aspx page contained in Listing 14.8 contains a single Page_Load subroutine. Within the subroutine, the value of the ID query string parameter is retrieved and assigned to a local variable named intItemID . Next, the database record matching the ID is retrieved from the Uploads table.

The content type of the page is set with the ContentType property of the Response object. For example, if the document being retrieved from the database is a Microsoft Word document, the ContentType property is set to the value application/msword .

The contents of the image column are output with the BinaryWrite method of the Response object. Because the image column contains binary data, not just text data, you need to use the BinaryWrite method rather than the normal Write method.

CAUTION

Netscape and Internet Explorer behave differently when you open a document with the ShowDBUpload.aspx page. For example, if you use this page to view a Microsoft Word document with Internet Explorer, the Word document opens automatically without any user interaction. If you open a Microsoft Word document with Netscape, on the other hand, you are asked whether you want to open the file or save the file to disk.

Opening PowerPoint presentations can be especially tricky. Netscape displays a dialog box that enables you to launch PowerPoint. Internet Explorer displays the raw contents of the PowerPoint presentation in a text file.