Browse DevX
Sign up for e-mail newsletters from DevX


Building Wiki Web Sites with ASP.NET and SQL Server : Page 5

You can easily build Wiki Web sites with ASP.NET and SQL Server and provide your teams with one of the most powerful ways of collaborating on the Web.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

The DotWiki Search Page
Since a Wiki Web page stores all the information in a database, you can imagine how easy it is to look for information. Using a Wiki is like having your own company-wide Web with a built-in search engine.

DotWiki provides a search page where users can enter one or more words that they want to search for in the Wiki database. Behind the scenes, the DotWiki passes the search request to the Search method of Business Services class.

You can configure the Search method via the Web.config file to perform the search in a variety of ways. In a "normal search" (default) the Search method builds a SQL SELECT statement using the LIKE operator in the WHERE clause. For example, if a user enters the word "modeling," the Search method will build a SQL SELECT like this:

SELECT name FROM topic WHERE name LIKE '%modeling%' OR content LIKE '%modeling%'

This very powerful SQL SELECT would find the word "modeling" in all topics present. However, there are two problems with this approach.

First, LIKE is a very expensive operator. In a query like the one in the last snippet, SQL Server must perform a full table scan on the TOPICS table and read through all the text in the name and content fields to try to find the word "modeling." If you have a small number of topics this might not be an issue. However, if you have a rather large number of topics, you'll have performance problems if your users perform many full table scans.

Second, even though SQL Server will perform a full scan on the table, it will only look for instances of the word "modeling," ignoring variations of the word that might be interesting for the users such as "modeled" or "models".

Full-Text Search
SQL Server provides a Full-Text Search service that is often a better approach for performing searches in text fields. SQL Server's Full-Text Search service is optimized for large blocks of text. Remember that the DotWiki database is basically a collection of large blocks of text (and remember that each topic can be as large as the users decide to make it).

When you use a full-text search, you ask SQL Server to create a separate index for the text fields. SQL Server optimizes these text indexes for text searches.

SQL Server's Full-Text Search service provides three main features out of the box. First, you can search on text fields without forcing SQL Server to perform a full table scan. Second, a full-text search allows SQL Server to find variations of a word in addition to exact matches. For example, a full-text search on the word "modeling" would find instances of the words "modeling," "model," and "modeled," Finally, the Full-Text Search service provides ranking capabilities so you can tell your users that you've found 100 instances of the word "modeling" and rank the results.

The Full-Text Search Service is not installed by default when you install SQL Server. You can verify whether you've installed the Full-Text Search service on your server via SQL Server's Enterprise Manager. Drill down to a database on your server. If you see a node called Full-Text Catalogs in your database, you have the feature installed (see Figure 8). If the service isn't on your server, you can add this feature at any time by running SQL Server's setup program and then select the Full-Text Search option. If you do this, you must reapply SQL Server service packs to your server; otherwise, the Full-Text Search service will be installed but disabled on your server.

Figure 8: Full-Text Catalogs in Enterprise Manager.
With Full-Text Search installed on your server, you can run the Full-Text Indexing wizard located in the Tools option of SQL Server's Enterprise Manager. This wizard will guide you through the required steps to configure full-text search in your database. This process includes:

  • Create a full-text catalog in the database.
  • Create a full-text index in the table.
  • Populate the full-text index for the first time.
  • Schedule incremental updates of the full-text index.
A comprehensive explanation of the SQL Server Full-Text Search service requires an article of its own. However, you can get started with the documentation that SQL Server Books Online provides on the subject.

Once you have set up your database for full-text search, you can use the FREETEXT and CONTAINS predicates in the WHERE clause of your SQL SELECT statements. The following code snippet uses the CONTAINS predicate to look for the word "modeling" and its variations (inflectional forms).


Additionally, you can use the FREETEXTTABLE and CONTAINSTABLE functions in your SQL SELECT statements. You use these functions in the FROM clause and they return a table with two columns: key and rank. Use the key column to join the returned table to your base table and the rank column to sort the records by relevance. The following code snippet uses the FREETEXTTABLE function. Notice the join with the TOPIC table (by using the key field) and the ORDER BY rank.

SELECT name, content, rank FROM topic INNER JOIN FREETEXTABLE( topic, *, 'modeling' ) AS ft ON topic.topicpk = ft.[key] ORDER BY rank

In the DotWiki code, the Search method of the Business Services class encapsulates the search process and the calls to SQL Server. This method will create the appropriate SQL SELECT statement to perform a typical search (using the LIKE operation) or a full-text search depending on the search method defined in the Web.config file.

Wiki Web sites are great collaboration tools and they are easy to implement with ASP.NET and SQL Server.

Think of all the Web sites in your organization where users would like to always have the latest version of their information. Using a Wiki, users don't need to depend on an administrator to update the content, they can augment their information and correct their own mistakes, and you can keep a record of changes so that users can trace how they arrived at the current conclusion.

We barely touched the surface of some of the things that you can do with .NET regular expressions and SQL Server's Full-Text Search service. I hope this article sparks your interest and gives you new alternatives to do text parsing and implement search features in your Web sites.

Complete source code for the DotWiki clone presented in this article is available at www.hectorcorrea.com.

Hector J. Correa is a software architect in the Kansas City area. Hector is a Microsoft Certified Solution Developer. He holds a B.S. in Computer Engineering from Colima's Institute of Technology. You can reach him at his Web site or via e-mail here.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date