Controlling SQL Text Indexing Services with SQL Managed Objects and VB.NET

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Introduction

Some applications create whole databases and tables dynamically. Entity Diagramming tools such as ERwin generate databases. Application builders or customizers—like ACT—modify databases, and applications that permit customized data storage sometimes generate new tables or even whole databases.

The challenge with dynamic generation is that your application is already running, and it is unlikely that you will be there to perform administrative tasks, such as enabling full text indexing.

Full text indexing uses an indexing service and permits you to index text fields in SQL Server. The benefit is that you can use functions like FREETEXT in SQL Server queries to query one or more fields for fragments of text. The net effect is that full text indexing makes your database capable of performing Google-like searches. That is, with full text indexing you can let the user enter any text data and SQL Server can search multiple fields for that fragment with expressing a WHERE predicate for every combination. Instead of WHERE CustomerName LIKE ‘somedata’ OR ADDRESS CONTAINS ‘somedata’, and so forth, you write one predicate using the FREETEXT function and SQL Server searches all of the indexed columns for that text.

Here is where full text searching is really useful. Many, many applications support search and find capabilities. Many of these applications typically have a label indicating the kind of data that will be used to build the filter and an input control of some sort. Based on the controls containing values a dynamic search is assembled with as many WHERE predicates as there are kinds of input data. All of these labels, controls, and dynamic assemblage of queries are time consuming.

The no-brainer is that Google is a metaphor that everyone seems to get; hence its popularity. So, why not have one input field every time where the user can input any data and let the indexing service and full text indexing find the matches for you? Full text indexing makes this possible. My article from August 2005, FREETEXT Searches with SQL Server and ADO.NET, demonstrates how to configure indexing services manually and perform full text searches. (This article builds on that concept in a different way.)

The aforementioned article walks you through FREETEXT searches and manually configuring indexing services. This article shows you how to enable full text indexing and FREETEXT searches dynamically.

With SQL Server 2005, the SQL Management Objects (SMO) capabilities were added to SQL Server and the .NET framework. The result is that many of the things that one could do manually using SQL Server tools can now be accomplished programmatically using SMO and VB.NET. In this article, you will learn how to support full text searches on SQL Server Express 2005—it’s already supported in non-Express versions—enable full text indexing with SMO, and write FREETEXT queries against indexed columns, all with .NET code.

Preparing the Demo

When you install Visual Studio 2005 or up, SQL Server Express 2005 is installed. Some of you may have access only to SQL Express and some more of you will be developing on your workstation with SQL Server Express. Unfortunately, the out-of-the-box install doesn’t support full text indexing for SQL Server Express. Therefore, if you are using SQL Server Express and you want to try the full text indexing demo, read the next paragraph. Otherwise, if you are using SQL 2005 developer edition or above, you can skip the next paragraph.

Downloading SQL Server Express 2005 with Advanced Services SP2

For SQL Express users, full text indexing is not supported. If you want to try the demo and be able to perform FREETEXT queries with SQL Server Express, download the SQL Server Express 2005 with Advanced services SP2 (Service Pack 2) update at http://msdn.microsoft.com/en-us/express/bb410792.aspx. This service pack contains support for full text indexing.

Reviewing Prerequisites for the Demo

Obviously, you will need access to SQL Server. SQL Express 2005 works if you install Service Pack 2 with Advanced Services. The example program was written in Visual studio 2008, but SMO was released with .NET 2.0 and Visual Studio 2005, so those versions should work too. (Make sure to write me at pkimmel@softconcepts.com if the demo doesn’t work in Visual Studio 2005.)

Configuring Full Text Indexing Dynamically and Writing FREETEXT Queries

The sample program assumes an existing database is present. The code configures full text indexing on an nvarchar column, populates the index, and performs a query using the FREETEXT function.

In the sample program, a database that contains company and stock ticker symbols along with historical quotes is used. You can use the Northwind database, or any database, to complete the example. Simply substitute details such as the connection string, database name, table names, and indexes in the code to reflect actual elements in the database you are using.

Adding References

SQL Management Objects (SMO) uses elements of the framework defined in the Microsoft.SqlServer namespace. To try the demo, create a VB.NET application—a console application will do—and add the following additional references:

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Smo.dll
• Microsoft.SqlServer.SmoEnum.dll
• Microsoft.SqlServer.SqlEnum.dll

Having added the SMO assemblies, you will need these Imports statements to support the code as demonstrated in Listing 1.

• Imports Microsoft.SqlServer.Management.Smo
• Imports Microsoft.SqlServer.Management.Common
• Imports System.Data
• Imports System.Data.SqlClient

If you miss an Imports staement, the compiler and Intellisense will help you fill in the blanks.

Configuring Full Text Searches with SQL Management Objects

The sample code is all in a single Main function in a Console application. This part describes the SMO code in the first half of the listing (see Listing 1) and the next section contains vanilla SQL code with a FREETEXT query. All of the code is shown in Listing 1.

In the Main function in Listing 1, the code from Dim server to the line before the connectionString variable is the SMO code. Everything else is the SQL code.

In Main, a Server object is created. In the example, I am using SQLExpress, as shown in the listing. Using the Server object, a database is requested via the Databases collection, passing in the name of the database. (If you are using Northwind, replace “StockHistory” with “Northwind”. From the Database object, request the Table desired by invoking accessing the Database.Tables collection. The code defines a catalog name and checks to see whether the catalog already exists. If the catalog doesn’t exist, the catalog is created by creating a new FullTextCatalog object passing in the DB object and the catalog name. This catalog is set as the default and the FullTextCatalog.Create method is called.

Next, the Table object is used to determine whether a FullTextIndex exists. If not, a new FullTextIndex is created passing in the Table object. A FullTextIndexColumn object is created passing in the FullTextIndex object and the column-name to index. The column is added to the FullTextIndex.IndexedColumns property. The implication is that you can index multiple columns. (In practice, index every column that you may want to search on.) The SMO code is wrapped up enabling change tracking, providing a unique key—use the primary key column, which is required—and associate the catalog name with the index. Invoke FullTextIndex.Create and StartPopulation.

Populating the index with an incremental population will permit you to start querying the full text indexes immediately, although on large databases some results may not be found until the index is substantially complete. If you elect to perform a full population, the database may be unavailable until indexing is complete.