Google-like Auto Suggest in ASP.NET for A Small Table

• Download source code - 1.17 MB

Introduction

In most websites, the search button searches data using "like" in database, as follows: 

select * from book where bookName like '%all keywords come from user interface%'

However, such an approach misleads the users of that system. In this study a Google-like search is performed on a table with a small amount of rows !  This is not an academic essay that includes some text processing algorithms. This is only a practice, trying to get better sort and results according to given keywords, on small tables. Existence of indexes on used columns will provide better performance at database level.

Using the code

Firstly, a table must be created in any version of MS SQL Server database.   

------------------ step 1 -----------
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[book](
	[bookNo] [int] IDENTITY(1,1) NOT NULL,
	[bookName] [varchar](500) NULL,
 CONSTRAINT [PK_book] PRIMARY KEY CLUSTERED 
(
	[bookNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
------------------ /step 1 -----------

After creating table, some rows can be inserted to make the study more understandable.

 ------------------ step 2 -----------
insert into book(bookName) values('mathematics question bank')
insert into book(bookName) values('mathematics questions')
insert into book(bookName) values('physics question bank')
insert into book(bookName) values('mathematics question with solutions')
insert into book(bookName) values('chemistry question bank')
insert into book(bookName) values('chemistry questions wit detailed solutions')
insert into book(bookName) values('physics questions')
insert into book(bookName) values('detaied solutions of mathematics questions')
insert into book(bookName) values('example of mathematics questions')
insert into book(bookName) values('detailed mathematics')
------------------ /step 2 -----------

Step 3

After these steps, the web.config must configured according to your database connection info.

<add name="connStr" 
  connectionString="SERVER =.\SQLEXPRESS2008; database=testDB; user id=sa; pwd=yourPwd" />

Step 4

To be able to use AJAX abilities a ScriptManager must be put on the master page. A textbox, a button, and a DataGridView are enough for the visual design of this study, as below.

For autocomplete ability of the textbox on the page AJAX AutoCompleteExtender is used as below. This usage is from the AJAX Control Toolkit sample website:

 ------------------ step 4 -----------
<asp:autocompleteextender
ID="AutocompleteextenderBook"
runat="server"
TargetControlID="TBbookName"
BehaviorID="AutoCompleteExBook"
OnClientItemSelected="aceBook_itemSelected"
ServicePath="AutoComplete.asmx"               
ServiceMethod="GetBookList"
MinimumPrefixLength="3" 
CompletionInterval="1"
UseContextKey="True"
EnableCaching="true"                
CompletionListCssClass="autocomplete_completionListElement" 
CompletionListItemCssClass="autocomplete_listItem"                 
CompletionListHighlightedItemCssClass="autocomplete_highlightedListItem" 
  DelimiterCharacters="">
       <Animations>
        <OnShow>
            <Sequence>
                <%-- Make the completion list transparent and then show it --%>
                <OpacityAction Opacity="0" />
                <HideAction Visible="true" />
        
                <%--Cache the original size of the completion list the first time
                    the animation is played and then set it to zero --%>   
        
                <ScriptAction Script="
                    // Cache the size and setup the initial size
                    var behavior = $find('AutoCompleteExBook');
                    if (!behavior._height) {
                        var target = behavior.get_completionList();
                        behavior._height = target.offsetHeight - 2;
                        target.style.height = '0px';
                    }" />
        
                <%-- Expand from 0px to the appropriate size while fading in --%>                              
        
        
                <Parallel Duration=".4">
                    <FadeIn />
                    <Length PropertyKey="height" StartValue="0" 
                      EndValueScript="$find('AutoCompleteExBook')._height" />
                </Parallel>
            </Sequence>
        </OnShow>
        <OnHide>
            <%-- Collapse down to 0px and fade out --%>      
    
            <Parallel Duration=".4">
                <FadeOut />
                <Length PropertyKey="height" 
                  StartValueScript="$find('AutoCompleteExBook')._height" EndValue="0" />
            </Parallel>
        </OnHide>
    </Animations>
</asp:autocompleteextender> 
------------------ /step 4 -----------

• ServicePath="AutoComplete.asmx"
• ServiceMethod="GetBookList"
• OnClientItemSelected="aceBook_itemSelected"

These are important properties of autocompleteextender. AutoComplete.asmx is the web service that includes GetBookList which is used when onchange event of the textbox is triggered. When any line of the bookList is selected aceBook_itemSelected that is JavaScript method is fired.

After adding a web service called AutoComplete.asmx the line:

// [System.Web.Script.Services.ScriptService]

must be uncommented. GetBookList in AutoComplete.asmx is as follows:

//------------------ step 5 -----------

[WebMethod]
public string[] GetBookList(string prefixText, int count)
{

    // this query could be run as stored procedure

    string sql = " select  bookNo, bookName" +
         " from book " +
         " where " +         
         " bookName like @par1 " +
         " order by bookName";

    //-------------
    SQLandParameters sqlObj = new SQLandParameters();
    sqlObj.SQL = sql;
    sqlObj.parameters.Add("@par1", prefixText + "%");    
    //-------------

    DataTable dt = Provider.execMySQL(sqlObj);
    //-----------------------------------
    List<string> custList = new List<string>();
    string custItem = string.Empty;
    
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        
        custItem = AutoCompleteExtender.CreateAutoCompleteItem(                
                            dt.Rows[i]["bookName"].ToString(),
                            dt.Rows[i]["bookNo"].ToString());
        custList.Add(custItem);
    }
    //-----------------------------------
    return custList.ToArray();
    
}

//------------------ /step 5 ----------- 

Provider is another class in this study. It has a method to run the given SQL as follows. The parameter sqlObj used in execMySQL includes both the SQL expression and the parameters used in the SQL expression. SQL injection can be prevented by parametrized work.

------------------ step 6 -----------

public static DataTable execMySQL(SQLandParameters sqlObj)
{
    DataTable dt = new DataTable();

    string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ToString();
        
    SqlConnection conn = new SqlConnection(connStr);

    SqlDataAdapter da = new SqlDataAdapter(sqlObj.SQL, conn);


    foreach (KeyValuePair<string, string> item in sqlObj.parameters)
    {            
        da.SelectCommand.Parameters.AddWithValue(item.Key.ToString(), item.Value.ToString());
    }

    da.SelectCommand.CommandType = CommandType.Text;


    conn.Open();
    da.Fill(dt);
    conn.Close();

    return dt;
}

------------------ /step 6 -----------

The aceBook_itemSelected JavaScript method is as below.

//------------------ step 7 -----------

 
 <script type="text/javascript">
     function aceBook_itemSelected(sender, e) {
 
         if (e.get_value() == null)
             return false;
 
         var iSearchbtn = $get('<%= btnSearch.ClientID %>');
         iSearchbtn.focus();
 
         __doPostBack(iSearchbtn, 'OnClick');
 
         var iSearcBox = $get('<%= TBbookName.ClientID %>');
         
         iSearcBox.focus();
     }
 </script>
 
//------------------ /step 7 -----------

After adding some styles for autocomplete extender the figure below is seen.

After all the steps above done, GoogleLikeSearchLib can be added to the project. It generates the SQL to run according to the keywords. The main idea is to generate the most appropriate SQL expression to run.

public static string GenerateSQL(string keyWords,string tableName,string columnNameTxt,string columnNameVal)

This is the method that generates SQL according to the keywords entered from the user interface and related table in the database. So the code under btnSearch is as follows:

//------------------ step 8 -----------
protected void btnSearch_Click(object sender, EventArgs e)
{
   SQLandParameters SQLobj = GoogleLikeSearchLib.GenerateSQL(TBbookName.Text.Trim(), 
                        "book", "bookName", "bookNo");



   dgv1.DataSource = Provider.execMySQL(SQLobj);
   dgv1.DataBind();
}
//------------------ /step 8 -----------

When btnSearch is clicked the SQL expression is generated as below:

select distinct t1.bookNo, t1.bookName, max(t1.myRank) myRank  from  
( 
	select b.*, 2 myRank from book b where bookName like '%mathematics%' 
	and bookName like '%questions%' 
union 
	select b.*, 1 myRank from book b where bookName like '%mathematics%' 
union 
	select b.*, 1 myRank from book b where bookName like '%questions%' 
) t1  
group by t1.bookName, t1.bookNo order by max(t1.myRank) desc

And when this SQL expression is run in MS SQL editor the following resultset set is gained:

The result can be shown on page as follows: