Download Example Source File.zip - 7.9 MB

Introduction

When you first try to implement a search function to a web page you realize that there it is quite hard to make it work as you want. It is ovious how coddled you are with googles search algorithms that "always" return what you want. However, you can make this work close to the same by yourself using already developed tools and queries. In this article we explain how you can do this using free versions of SQL Server 2012 and C# and ASP.NET. However, as long as you stay in the .NET platform the article will be easy to convert to any other language.

Background

To understand more about what full text are, how it could be used and the underlying technics I provide a few links to other articles.

MSDNs latest guide
http://msdn.microsoft.com/en-us/library/ms142571.aspx

A good Code Project article by GanesanSenthilvel explaining many things.
http://www.codeproject.com/Articles/315101/SQL-Full-Text-Search-Programming

Needed Software

SQL Server 2012 with Advanced tools (Full text engine) Download ENU\x64 SQLEXPRADV_x64_ENU.exe
http://www.microsoft.com/en-us/download/details.aspx?id=29062&WT.mc_id=aff-n-in-loc--pd

Be sure to install the full text module by clicking that box. Also install Management Studio as this will be used for configurations.

This video takes you through a first simple installation of SQL-Express.

http://www.youtube.com/watch?v=GeuJEID9rSA

Create a Table and Enable Full Text Search

Open SQL Server Management Studio and connect to your database you installed. Create a new new database instance.

Name the database and click ok.

Create a new table in the new database.

Define some columns.

And make one column the key column.

Close and save the design of the table, name the table "Names".

You can also run this script to generate the table in this example.

 CREATE TABLE [dbo].[Names](
 [MyKey] [nvarchar](50) NOT NULL,
 [Name] [nvarchar](50) NULL,
 [Email] [nvarchar](50) NULL,
 [Description] [text] NULL,
 CONSTRAINT [PK_Names] PRIMARY KEY CLUSTERED 
(
 [MyKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Now we are ready to insert some testing rows. Either you can enter rows by right click on the table and click edit to easy change the table. Or you can use the script to generate rows.

The script:

INSERT [dbo].[Names] ([MyKey], [Name], [Email], [Description]) VALUES (N'1', N'Jon', <a href="mailto:N'jon@mail.se'">N'jon@mail.se', N'This is a user')
GO
INSERT [dbo].[Names] ([MyKey], [Name], [Email], [Description]) VALUES (N'2', N'Kim', <a href="mailto:N'kim@mail.se'">N'kim@mail.se', N'My game is yellow.')
GO
INSERT [dbo].[Names] ([MyKey], [Name], [Email], [Description]) VALUES (N'3', N'Hans', <a href="mailto:N'hans@email.se'">N'hans@email.se'</a>, N'Game on')
GO
INSERT [dbo].[Names] ([MyKey], [Name], [Email], [Description]) VALUES (N'4', N'Tom', <a href="mailto:N'clou@post.com'">N'clou@post.com'</a>, N'Soccer')
GO
INSERT [dbo].[Names] ([MyKey], [Name], [Email], [Description]) VALUES (N'5', N'Sven', <a href="mailto:N's@post.com'">N's@post.com'</a>, N'Handball is the best sport')
GO
INSERT [dbo].[Names] ([MyKey], [Name], [Email], [Description]) VALUES (N'6', N'Jonas', <a href="mailto:N'jonas@mails.se'">N'jonas@mails.se'</a>, N'Users')
GO

Edit rows in Manegment Studio:

After you created some testing rows its time to enable full text search. For this to work two things are needed, a global full-text catalog and a table definition for full-text. The Express edition do right now come with a bug that do not allow this to work by fully using the wizard. The full text catalog needs to be created by script. The script to create a catalog is simply:

CREATE FULLTEXT CATALOG MyCatalog; 
GO

After that is run successfully you can define your full-text search table. Right click on the table, click on "Full-Text Index" and then define Full-Text index.

In the wizard that opens click next until the columns for the table comes up. Mark the columns you want to search in and click next until the end. There will probably be one exception during the progress. However, you can ignore that and continue anyway. Hopefully you get a full index configured successfully in the end of the wizard.

Your full-text index is ready and configurated.

Test the full-text table

The test table with six rows are now created and configured and ready for some test searches.

There are four different built-in functions in SQL-Server to preform queries with using full-text searches. Contains, ContainsTable, FreeText and FreeTextTable. Contains and FreeText return a bool value to be used in a WHERE clause, and ContainsTable and FreeTextTable returns a table with the KEY and RANK, the search hit points for that row and search word.

Contains

Open a new query window in sql management studio and run the following script.

SELECT * 
    FROM Search.dbo.Names
    WHERE Contains(*,'mail')

This will return all rows in the table Names that somewhere in any column (in the full-text index) contains the word mail. Contains is not case-sensitive, which means it will have the same result for Mail as mail.

For the test table created earlier this query returns two rows. Contains is only searching for full words matching the search word, but dots and '@' do count as word separator. For details check http://msdn.microsoft.com/en-us/library/ms187787

Contains functions support boolean operators (AND, OR, NOT) and special characters as *. This means that searches as "jon*" will search for "jonte", "jonas" as well as "jon".

Search string is defined inside two ' '. Words with an asterisk ending should have string characters around the word, e.g. "Word*".

Example of a search string '"First words" OR Second AND "Third*"'

To search only in one column:

SELECT * 
    FROM Search.dbo.Names
    Where Contains(Name,'Jon')

Two specific columns:

SELECT * 
    FROM Search.dbo.Names
    Where Contains((Name, Email),'Jon')
    go

ContainsTable

ContainsTable works in another why. It returns a table with the key to the row in the table searched together with a search score which Ranks the hits. This enables us to buld own well preforming ranked searches a la google. Or probable more like BING.

SELECT *
  FROM ContainsTable(dbo.Names,*,'mail' )

Gives us

If you now something of SQL, you have already understand how to create us a ranked search query together with the table.

SELECT *
    FROM CONTAINSTABLE(dbo.Names, *, '"han*"') AS r INNER JOIN
        Names ON r.[KEY] = Names.MyKey
        order by RANK DESC

Witch returns two rows with Hans in top as the most relevant search hit.

FreeText and FreeTextTable

Freetext uses the language specified on each column (defined when the free-text index was created, this can be changed) to find similar words as the actual search word. This means that in the English language the word fox will find both results containing foxes and fox, which can be very useful in a search engine.

A big difference from contains is that the boolean operators do not used at all and will be removed from the search query.

FROM FREETEXTTABLE(dbo.Names, *, '"gameing"') AS r INNER JOIN
    Names ON r.[KEY] = Names.MyKey
    order by RANK DESC

This give us two rows back for FreeTextTable however using the same search string, "gameing" in the function CONTAIN give us zero rows as result. The FreeTextTable function know that game is a conjunction of gaming. FreeText work in a similar why as contains.

Implementation in a Asp.NET page

It is time for implementation into small web page. This exampel uses Visual Studio 2012 but it should work in a similar why in any other IDE supporting Asp.NET. The page will only have one text box, a search button and a table to display results. I start by creating a empty Asp.NET project and add a Web Forms page with background code.

To protect the db from SQL Injections there are some characters that is not allowed. Those will be deleted on serverside later. However, to help the user to not insert those caracters there is a ajax extender added to the textbox. To use ajax use the NuGet addon to add ajax framework to your solution, http://stephenwalther.com/archive/2011/05/23/install-the-ajax-control-toolkit-from-nuget.aspx. The extender is given the valid characters, other characters will not be possible to type in to the text box.

The code for search.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="search.aspx.cs" Inherits="WebTest.search" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>Search Site</title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:TextBox ID="SearchBox" runat="server"></asp:TextBox>
                <asp:FilteredTextBoxExtender ID="SearchBox_FilteredTextBoxExtender" 
                            runat="server" Enabled="True" TargetControlID="SearchBox" 
                            ValidChars="*&| ().@åäöÅÄÖ" 
                            FilterType="Custom, Numbers, LowercaseLetters, UppercaseLetters">
                </asp:FilteredTextBoxExtender>
                <asp:Button ID="SearchButton" runat="server" Text="Search" 
                            OnClick="SearchButton_Click" />
                <asp:Table ID="ResultTable" runat="server">
                </asp:Table >
                <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
                </asp:ToolkitScriptManager>
            </div>
        </form>
    </body>
</html>

The background code will have one event function "SearchButton_Click" where the db call is added to the as table. The event code use a search method called "SearchDB" that cleans the search string to make it work. Update the cleaning to support more advaced searches.

protected void SearchButton_Click(object sender, EventArgs e)
{
    DataTable dt = SearchDB(SearchBox.Text);
    if (dt == null)
        return;
    var row = new TableRow();
    //Add the column headers
    for (int j = 0; j < dt.Columns.Count; j++)
    {
        var headerCell = new TableHeaderCell();
        headerCell.Text = dt.Columns[j].ColumnName;
        row.Cells.Add(headerCell);
    }

    ResultTable.Rows.Add(row);

    //Add the row values
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        row = new TableRow();
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            var cell = new TableCell();
            cell.Text = dt.Rows[i][j].ToString();
            row.Cells.Add(cell);
        }
        // Add the TableRow to the Table
        ResultTable.Rows.Add(row);
    }
}

And the search function. Notice the Regex function cleaning any remaining invalid characters. More information how to protect your site from SQL Injections read: http://msdn.microsoft.com/en-us/library/ff648339.aspx.

        /// <summary>
        /// Searches the database in all columns and returns the result as a datatable.
        /// </summary>
        /// <param name="searchString">Search String</param>
        /// <returns>DataTable with the results all columns ordered in rank</returns>
        public static DataTable SearchDB(string searchString)
        {
            if (searchString == null)
                return null;
            //Clean Up search string to avoid SQL Injection
            var reg = new Regex(@"[^\w(@)\|&]");
            searchString = reg.Replace(searchString, "");
            searchString = searchString.Trim();
            if (searchString == "")
                return null;

            //The search string
            var dt = new DataTable();
            using (
                var connection = new SqlConnection(
                               ConfigurationManager.ConnectionStrings[
                                           "DBConnection"].ConnectionString))
            {
                var userDataset = new DataSet();
                var myDataAdapter = new SqlDataAdapter(
                    "SELECT TOP(20) * FROM FREETEXTTABLE(dbo.Names, *, @param) AS r INNER JOIN Names ON r.[KEY] = Names.MyKey order by RANK DESC",
                    connection);
                myDataAdapter.SelectCommand.Parameters.Add("@param", SqlDbType.VarChar, 255);
                myDataAdapter.SelectCommand.Parameters["@param"].Value = searchString;
                myDataAdapter.Fill(dt);
            }
            return dt;
        }

The search function uses a connection string defined in web.config as:

<configuration>
<connectionStrings>
  <add name="DBConnection" 
      connectionString="Data Source=localhost;Initial Catalog=Search;Integrated Security=True"
      providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>

This webpage give us the following result:

AutoComplete Search

It is mostly nice to use a presearch to get a instant search while typing the search string. This can be implementing by using a ajax component and a WebService.

Start by create a WebService. The AutoCompleteExtender in Ajax need a Array of strings. The method created should therefor compile the result strings. In this exampel the string is compiled as NAME "Email@Email", the description is skipped as this could be very long.

Create a new WebService by right-click on the solution and choose add, New Item and choose WebService. In order to be used by AutoCompleteExtender it is importent that WebService method is declared exactly as:

 public string[] Search(string prefixText, int count)

This is the complete code for the exampel, the method uses the same method as the previous database search. However it only uses two of the columns for information.

using System.Data;
using System.Web.Services;

namespace WebTest
{
    /// <summary>
    /// Summary description for WebService1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    [System.Web.Script.Services.ScriptService]
    public class SearchService : System.Web.Services.WebService
    {
        public SearchService()
        {

        }
        [System.Web.Services.WebMethod]
        [System.Web.Script.Services.ScriptMethod]
        public string[] Search(string prefixText, int count)
        {
            var searchString = prefixText;
            DataTable dt = search.SearchDB(searchString);
            if (dt == null)
                return null;
            var countormax = dt.Rows.Count > count ? count : dt.Rows.Count;
            var result = new string[countormax];
            for (int i = 0; i < countormax; i++)
            {
                result[i] = dt.Rows[i][3].ToString() + " \"" + 
                            dt.Rows[i][4].ToString() + '"';
            }
            return result;
        }
    }
}

The last thing to do is to add a AutoCompleteExtender to the search box. The web service page and its WebService method is defined to the extenders ServicePath and ServiceMethod properties.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="search.aspx.cs" Inherits="WebTest.search" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>Search Site</title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:TextBox ID="SearchBox" runat="server"></asp:TextBox>
                <asp:FilteredTextBoxExtender ID="SearchBox_FilteredTextBoxExtender" 
                                             runat="server" Enabled="True" 
TargetControlID="SearchBox" ValidChars="*&| ().@åäöÅÄÖ" FilterType="Custom, Numbers, LowercaseLetters, UppercaseLetters">
                </asp:FilteredTextBoxExtender>
                <asp:AutoCompleteExtender ID="SearchBox_AutoCompleteExtender" runat="server" 
                                          DelimiterCharacters="" Enabled="True" ServicePath="~/SearchService.asmx" MinimumPrefixLength="1"  
                                          TargetControlID="SearchBox" CompletionSetCount="5" ServiceMethod="Search" EnableCaching="False">
                </asp:AutoCompleteExtender>
                <asp:Button ID="SearchButton" runat="server" Text="Search" 
                            OnClick="SearchButton_Click" />
                <asp:Table ID="ResultTable" runat="server">
                </asp:Table >
                <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server"></asp:ToolkitScriptManager>
            </div>
        </form>
    </body>
</html>

Wrapping up

The article have showed you how to create a database table in SQL-Server 2012 with full text indexing. Then it continued to show how to implement powerful ranked full-text search in an Asp.NET page, including AutoComplete function.

Implementation Example of Ranked Search in ASP.NET