Creating AJAX Enabled Multi Keyword Database Search Engine
 
Published: 15 Jan 2008
Abstract
In this article an effort has been made to facilitate for developers, users, and practitioners an alternative option of developing and using a simple AJAX Enabled database search engine. The following pages provide a general understanding of the AJAX Enabled Multi Keyword database search engine, its usage, and the compatibility of its operation.
by Habiburahman Khaled
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 39965/ 79

Introduction

This article will show you how to develop a simple multi-column, multi-keyword, and multi option search engine; it can be used for any database driven application that requires search engine facility.

Figure 1

It is worth mentioning that at the time I was writing this article I found Kelly Gafford single column, multi-word database search sample code at planetsourcecode written in Classic ASP very useful which gave me the idea of how to go ahead.

Application features

This application is equipped with the following features:

·         Search for multiple keywords, separated either by comma, space, or +.

·         Search in multiple columns.

·         Different search options like All, Exact, or any.

Requirements

·         Microsoft SQL Server 2005/SQL Server 2005 Express

·         .NET Framework 2.0 or later

·         Microsoft AJAX Extensions

·         IIS

Database

The database used for the article is Northwind and it is hoped that users are acquainted and furnished with it. Those who do not have Northwind can get one from here.

Listing 1: Create the following View in your Database

CREATE VIEW [dbo].[CustomersView]
AS
SELECT CustomerID, CompanyName, ContactName, 
 ContactTitle, City, Country, Address, 
 ISNULL(CompanyName, '') + ' ' + ISNULL(ContactName, '') + 
 ISNULL(ContactTitle, '') + ' ' + 
 ISNULL(City, '') + ' ' + ISNULL(Country, ''AS FullDescription
FROM dbo.Customers
GO

The code in listing 1 is self descriptive; CustomerView has been created based on the Customers Table. A virtual column named FullDescription is added to the view which combines multiple columns into one.

There are two methods for searching multiple columns in the database table:

1.    To create a virtual column in view or stored procedure and use the + operator in SQL Server, CONCAT or CONCAT_WS in MySQL, and/or ||,CONCAT in ORACLE to combine the data into one.

2.    To use AND or OR logical operator for each single column that we are searching in.

In this article both of the above methods are used.

Listing 2: Part of Customer.aspx page

<form id="form1" runat="server">
    <div id="HeaderDiv" class="Header">
        <h1>Search for Customers in Northwind Database</h1>
    </div>
    <div id="ContentDiv" class="Content">
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <div id="SearchControls" class="SearchControls">
            <asp:TextBox ID="SearchTextBox" runat="server" />
            <asp:DropDownList ID="SearchCriteriaList" runat="server">
                <asp:ListItem Selected="True" Value="AND">All</asp:ListItem>
                <asp:ListItem Value="OR">Any One</asp:ListItem>
                <asp:ListItem Value="Exact">Exactly</asp:ListItem>
            </asp:DropDownList>
            <asp:Button ID="BtnSearch" runat="server" Text="Search" 
              OnClick="BtnSearch_Click" />
            </div>
            <div style="text-align:center"> 
            <asp:UpdateProgress ID="UpdateProgressControl" runat="server">
                <ProgressTemplate>
                    <img src="images/spinner.gif" alt="loading.." />
                </ProgressTemplate>
            </asp:UpdateProgress>
            </div>
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                <div>
                    <asp:Label ID="RecordCount" runat="server" 
                        EnableViewState="False" Font-Bold="True"
                        Font-Size="Medium" Visible="False"></asp:Label>
                </div>
                <asp:GridView ID="CustomersGridView" runat="server" 
                  AutoGenerateColumns="false">
                    <Columns>
             <asp:BoundField DataField="CompanyName" HeaderText="Company Name" />
                      …
                    </Columns>
                </asp:GridView>
            </ContentTemplate>
            <Triggers>
             <asp:AsyncPostBackTrigger ControlID="BtnSearch" EventName="Click" />
            </Triggers>
        </asp:UpdatePanel>
    </div>
    <div id="ErrorDiv" runat="server">
    </div>
    </form>

The code in listing 2 shows the aspx page which resides all the controls used for the search application. This is not the complete page, it just shows you the controls and AJAX coding.

A Complete application can be downloaded from the download section.

Listing 3: The doSearch method

private void doSearch(string InputKeyword, string SelectCriteria)
{
  //define objects
  string strSQL;
  SqlConnection conn;
  SqlCommand command;
  SqlDataReader reader;
  if (SelectCriteria == "Exact")
  {
    strSQL = "SELECT  CustomerID, CompanyName," +
      "ContactName, ContactTitle, City," +
      "Country, Address FROM CustomersView WHERE (FullDescription LIKE '%";
    strSQL = strSQL + InputKeyword + "%')";
  }
  else
  {
//Define an array list and then assign User Enterd 
//information separated by "+",","," "
    String[]SearchArrayList = InputKeyword.Split(new Char[]
    {
      '+', ' ', ','
    }
    , StringSplitOptions.RemoveEmptyEntries);
 
    strSQL = "SELECT  CustomerID, CompanyName, ContactName," +
      "ContactTitle, City, Country, Address FROM CustomersView WHERE";
    String strJoin = string.Join("%') " + SelectCriteria +
      " (FullDescription LIKE '%", SearchArrayList);
//if you want to add another column to the search criteria 
//in place of using Virual Column
/* String strJoin2 = string.Join("%') " + SelectCriteria + 
  " (Country LIKE '%", SearchArrayList);
strSQL = strSQL + " ((ProductName LIKE '%" + strJoin + "%')" + "OR" + 
  "(Country LIKE '%" + strJoin2 + "%'))";*/
    strSQL = strSQL + " (FullDescription LIKE '%" + strJoin + "%')";
  }
  //read the connection from web.config file
  string ConnStr =
    ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
  //Intialize connection
  conn = new SqlConnection(ConnStr);
  // intialize command object
  command = new SqlCommand(strSQL, conn);
  try
  {
    //open connection
    conn.Open();
    //Intialize reader
    reader = command.ExecuteReader();
    //assign reader to Gridview datasource
    CustomersGridView.DataSource = reader;
    //Bind the data to Gridview
    CustomersGridView.DataBind();
    //Display the number of record found
    RecordCount.Text = "Your search for: <span style=color:blue>" +
      InputKeyword.ToString() + "</span>" + " Found " +
      CustomersGridView.Rows.Count.ToString() + " Matches";
    // close reader
    reader.Close();
  }
  catch (SqlException exception)
  {
    ErrorDiv.InnerHtml = "Error!" + "<br/>" + exception.Message;
  }
  finally
  {
    //close connection
    conn.Close();
  }
}

The code in Listing 3 defines the doSearch method which is located in the code-behind file of the aspx page that takes two parameters, the first one specifies user entered information in the search box, and the second one specifies the search option it could be either AND(All), OR(Any), or Exact(Exactly).

Listing 4: If you would like to add more fields to the search criteria use the following code

String strJoin2 = string.Join("%') " + SelectCriteria + 
    " (Country LIKE '%", SearchArrayList); 
 strSQL = strSQL + " ((ProductName LIKE '%" + strJoin + "%')" + 
    "OR"+"(Country LIKE '%" + strJoin2 + "%'))";

Listing 5: Calling the doSearch method from a button click event

protected void BtnSearch_Click(object sender, EventArgs e)
{
  //make sure that user enter something for search
  if (SearchTextBox.Text.Length != 0)
  {
   /*Block current thread for 1 second in order to see the spinner in AJAX
   progress control */
    System.Threading.Thread.Sleep(1000);
    string strCriteria = SearchCriteriaList.SelectedItem.Value;
    doSearch(SearchTextBox.Text.Trim(), strCriteria);
    RecordCount.Visible = true;
  }
}

The code in Listing 5 is calling the doSearch method from a button click event which takes values of a textbox named SearchTextBox, and dropdown list named SearchCriteriaList.

The Sleep method of Thread object is called to block the current thread for one second so the user can see the spinner image in AJAX progress control.

What is happening in the background?

When you use the doSearch method you can see what is happening in the background. Below is a trace from Visual Studio 2005 in debug mode.

After entering maria+berlin in the search box and hitting the search button we have the following values for strJoin and strSQL.

Figure 2

First, it is splitting the values separated by comma, plus, or spaces and then it assigns each of those values into the LIKE operator of SQL.

Downloads

Conclusion

In this article you have learned how to create an AJAX enabled database search engine and you have see how effective it is to use the String.Split, and String.Join methods of the C# language. You have also learned how to search for any, all, or exact phrases in the database tables.



User Comments

Title: 123   
Name: 123
Date: 2012-12-19 3:28:40 AM
Comment:
123
Title: as   
Name: asd
Date: 2012-08-21 8:13:06 PM
Comment:
asd
Title: we   
Name: we
Date: 2012-08-21 8:12:40 PM
Comment:
we
Title: rwrwrwrwr   
Name: wrw
Date: 2012-08-21 8:12:23 PM
Comment:
erewrwer
Title: d   
Name: d
Date: 2012-04-30 8:45:14 PM
Comment:
d
Title: What about paging?   
Name: EJ
Date: 2010-07-20 11:23:33 AM
Comment:
I like to know if you can help with paging and sorting the results from the client side? I didn't see any page methods in your code.
Title: dale   
Name: dalesanchez24@gmail.com
Date: 2009-09-29 8:03:19 AM
Comment:
by reading this article it gives me a a tips and helps me familiarize some search engine that ive never heard even encountered here in the net, this is great, and thanks for sharing this.
Title: Developer   
Name: Ram
Date: 2009-02-11 6:12:41 AM
Comment:
very good and attractive article
Title: thank for keeping such a value information   
Name: madhu
Date: 2009-01-28 7:41:02 AM
Comment:
hai sir,

the information u provided is excellent.
thanks and keep on rock.
Title: Ajax   
Name: Abhishek
Date: 2009-01-02 8:14:48 AM
Comment:
good
Title: Problem with IIS 7   
Name: Sasa
Date: 2008-09-08 2:54:19 PM
Comment:
I have problem when I browse using IIS 7. When I press search button I get the spinner.gif but GridView is not populated. In Visual Studio 8 sp1 work just fine. I use Vista Ultimate and SQL Server 2008.
Title: Display option   
Name: M Tajuddin
Date: 2008-08-20 2:55:35 AM
Comment:
Hi Khalid,
I need your help again. I got the pagination working fine now with the page size of 10. now i want show in display (label) what number of record is displayed. As example if search return 30 records, for the first page i want to setup display like 'You are viewing 1-10' and when user click on the next page it will display 'You are viewing 11-20' and so on. Would you please advise how can i do that.

Regards,

Tajuddin
Title: Re:Paging Option   
Name: M Tajuddin
Date: 2008-08-16 9:24:25 AM
Comment:
Hi Khalid,
Thanks for correcting me. It was my fault, I did not think about this before. It is working fine now. I love it. Did you try repeater for this type of search function? I think that will be good as well.

Thanks again for your help.

Regards,

Tajuddin
Title: Re:Paging Option   
Name: H.Khalid
Date: 2008-08-16 12:16:01 AM
Comment:
Hi Tajuddin,
Change the doSearch method according to the sample I provided in my previous post. as you can see in the sample it uses DataSet Classes in place of DataReader because DataReader doesn't support Paging unless you do some extra coding.

thanks
Title: Re:Paging Option   
Name: M Tajuddin
Date: 2008-08-15 7:21:05 AM
Comment:
Hi Khalid,
Thanks for your prompt response. I have tried that but it does not return any records now. I have added -
protected void CustomersGridView_SelectedIndexChanged(object sender, EventArgs e)
{

}
protected void CustomersGridView_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{

}
protected void CustomersGridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
CustomersGridView.PageIndex = e.NewPageIndex;
CustomersGridView.DataBind();
System.Threading.Thread.Sleep(1000);
string strCriteria = SearchCriteriaList.SelectedItem.Value;
doSearch(SearchTextBox.Text.Trim(), strCriteria);
RecordCount.Visible = true;

}
in the cs file and added -
AllowPaging="true" onpageindexchanging="CustomersGridView_PageIndexChanging"
onselectedindexchanging="CustomersGridView_SelectedIndexChanging"
in the gridview tag but did not work. I could find any problem. Could you suggest me what i did wrong..

Thanks again for your help.

Regards,

Tajuddin
Title: Re:Paging Option   
Name: H.Khalid
Date: 2008-08-14 6:30:41 AM
Comment:
Hi Tajuddin,
I am glad you liked the article, for paging you can download a paging enabled sample application from the link bellow:

http://authors.aspalliance.com/KHabiburahman/SearchEngine%20.NET%203.zip

Let me know if you have any problem.
thanks
Title: Paging option   
Name: M Tajuddin
Date: 2008-08-13 10:58:59 PM
Comment:
Hi Khalid,
I have implemented this search function in my website and it works fine. Fantastic idea. I am having a bit problem when trying to enable pagination. For instance, I have more than 1000 records in my database. So when user search it returns large number of record. How can I set up pagination so that it will populatate muliple page with link like form view or details view. Could you please provide me some suggestions.

Thanks brother,

M Tajuddin
email: tajuddin335@gmail.com
Title: Creating AJAX Enabled Multi Keyword Database Search Engine   
Name: Ramesh
Date: 2008-05-09 9:19:43 AM
Comment:
Nice article...but here is a need of AJAX....?
Title: Re:Connect to remote database   
Name: H.Khalid
Date: 2008-05-03 12:23:15 AM
Comment:
Yes it does, but you will need to use OleDb classes inside of System.Data.OleDb namespace for data retrieval, and use Query instead of View.
Check out this link for connecting with Ms Access.
http://www.aspfree.com/c/a/Microsoft-Access/Connecting-to-a-Microsoft-Access-database-with-ASPNET/

thanks
Title: Re:Connect to remote database   
Name: sathish
Date: 2008-05-02 11:43:42 PM
Comment:
does the coding working in ms-access is possible or not....
please tell....
Title: Re:Connect to remote database   
Name: H.Khalid
Date: 2008-04-14 7:25:26 AM
Comment:
Well,Executing remote Stored Procedure, and Query requires some tricks. Check out this link for complete solution:
http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-calling-stored-procedure-on-linked-server/
Title: Re:Connect to remote database   
Name: Mohammed Tajuddin
Date: 2008-04-14 6:27:30 AM
Comment:
Hi Habib,
Thanks for your response. I am actually trying to do it in my database ms sql 2005 in remote computer. Can i do the same in remote database? I use database management tools to access to my database. Data type are text, int, string and char. please give me your tips...
Title: Re:Connect to remote database   
Name: H.Khalid
Date: 2008-04-12 4:46:18 AM
Comment:
Hi Tajuddin,
What Database System do you use?, the query I have provided should work fine.
If you are using SQL server right click on the Northwind Database then "New Query" then paste the code I have provided and click on the "Execute" Button in the toolbar.

thanks
Title: Connect to remote database   
Name: Mohammed Tajuddin
Date: 2008-04-09 7:12:06 AM
Comment:
Hi Habibur, I like your article and was looking for this sort of things. I am really struggling to sort out my problem. How can i create the view in my database? if i run the query to create view getting error 'can not add text and char', how can i fix this problem? is there any other way to create view? your response much appreciated ...
Title: Many thanks,   
Name: Wayne
Date: 2008-04-08 11:57:17 AM
Comment:
Hi again Habiburahman,

Many thanks for the added code - and for helping me no-end.

Kind regards,

W
Title: Re:AllowPaging   
Name: Habiburahman Khaled
Date: 2008-03-03 4:55:19 AM
Comment:
Thank you Wayne, I am glad that you liked the article.
For paging you will need to use DataSet classes since DataReader doesnt not support paging and filtering.
I have updated the sample in which paging is enabled you can download the new sample from the link bellow:
http://authors.aspalliance.com/KHabiburahman/SearchEngine%20.NET%203.zip

thanks
Title: AllowPaging   
Name: Wayne
Date: 2008-02-20 8:58:42 AM
Comment:
Nice article - helped me get my head around a few things.

Any chance you could add some code to allow paging - tried the simple AllowPaging="True" but obviously this didn't work. Been looking for a way to do this but as a newbie am getting stuck!
Title: SearchTextBox.Text.Length != 0)   
Name: protected void BtnSearch
Date: 2008-02-06 7:19:44 AM
Comment:
Threading.Thread.Sleep(1000);
string strCriteria = SearchCriteriaList.SelectedItem.Value;
doSearch(SearchTextBox.Text
Title: is calling the   
Name: is calling
Date: 2008-02-06 7:18:26 AM
Comment:
calling the doSearch method from a button click event which takes values of a textbox named SearchTextBox
Title: Great but   
Name: Radenko Zec
Date: 2008-01-22 1:50:50 PM
Comment:
This method is useful for small apps i think but for large web portals you must use Sql Server 2005 fulltextsearch .
Maybe it would be interesting to build implementation
fulltextsearch using Linq.
There isnt any article on using linq for fulltextsearch,and you cant use linq for fulltextsearch I think.
Maybe would be great to build some helper classes to do that.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-12-04 5:10:22 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search