Click here to Skip to main content
15,867,568 members
Articles / Programming Languages / SQL

Implementation Example of Ranked Search in ASP.NET

Rate me:
Please Sign up or sign in to vote.
4.59/5 (10 votes)
16 Oct 2012LGPL37 min read 58K   2.2K   28   17
Description how to get started with your own search engine using full text search and SQL-Server 2012.

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.

Image 1

Name the database and click ok.

Image 2

Create a new table in the new database.

Image 3

Define some columns.

Image 4

And make one column the key column.

Image 5

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.

SQL
 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:

SQL
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:

Image 6

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:

SQL
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.

Image 7

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.

Image 8

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.

SQL
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.

Image 9

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:

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

Two specific columns:

SQL
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

Image 10

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

SQL
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.

Image 11

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:  

ASP.NET
<%@ 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. 

C#
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.

C#
/// <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:

XML
<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:

Image 12

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:

C#
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.  

C#
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.

ASP.NET
<%@ 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.

License

This article, along with any associated source code and files, is licensed under The GNU Lesser General Public License (LGPLv3)


Written By
Software Developer SharpedNET
Sweden Sweden
I have a development company called SharpredNET focusing on smaller project with high delivery precision on customer need and time. The solution always uses modern frameworks, is good looking and easy to use.

I am also a reseracher in Production System giving me specialization on implementation for manufacturing industry.

Comments and Discussions

 
QuestionAbout Auto complete Pin
JamesYang201212-Oct-12 17:30
JamesYang201212-Oct-12 17:30 
AnswerRe: About Auto complete Pin
Jon Larborn14-Oct-12 6:20
Jon Larborn14-Oct-12 6:20 
QuestionRe: About Auto complete Pin
JamesYang201215-Oct-12 5:55
JamesYang201215-Oct-12 5:55 
AnswerRe: About Auto complete Pin
Jon Larborn15-Oct-12 6:29
Jon Larborn15-Oct-12 6:29 
GeneralRe: About Auto complete Pin
JamesYang201216-Oct-12 9:33
JamesYang201216-Oct-12 9:33 
GeneralRe: About Auto complete Pin
Jon Larborn16-Oct-12 21:03
Jon Larborn16-Oct-12 21:03 
QuestionMVC4 Implementation Pin
Member 913526927-Aug-12 13:31
Member 913526927-Aug-12 13:31 
AnswerRe: MVC4 Implementation Pin
Jon Larborn27-Aug-12 21:54
Jon Larborn27-Aug-12 21:54 
GeneralMy vote of 4 Pin
Member 913526927-Aug-12 13:29
Member 913526927-Aug-12 13:29 
GeneralMy vote of 4 Pin
Carsten V2.021-Aug-12 6:07
Carsten V2.021-Aug-12 6:07 
GeneralMy vote of 4 Pin
Christian Amado21-Aug-12 5:38
professionalChristian Amado21-Aug-12 5:38 
GeneralRe: My vote of 4 Pin
Jon Larborn22-Aug-12 6:33
Jon Larborn22-Aug-12 6:33 
BugSQL Injection bug Pin
lambertje20-Aug-12 8:59
lambertje20-Aug-12 8:59 
GeneralRe: SQL Injection bug Pin
Jon Larborn21-Aug-12 1:06
Jon Larborn21-Aug-12 1:06 
SuggestionRe: SQL Injection bug Pin
Mario Majčica16-Oct-12 23:51
professionalMario Majčica16-Oct-12 23:51 
GeneralRe: SQL Injection bug Pin
Jon Larborn16-Oct-12 23:56
Jon Larborn16-Oct-12 23:56 
GeneralRe: SQL Injection bug Pin
Mario Majčica17-Oct-12 0:03
professionalMario Majčica17-Oct-12 0:03 
You are right, GC should do his work (with some overhead), still it makes it look cleaner. Wink | ;)
“The whole problem with the world is that fools and fanatics are always so certain of themselves, but wiser men so full of doubts.”

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.