Local Database search in ASP.NET

Introduction

In this tutorial we will perform the search in our local database. In general we need to provide some search functionality within our application. We can provide the simple search facility using this article. For Search we will use the feature of sql server i.e. Full Text Index Search. How To Create Full Text Index In Sql Server? To create the full text index search see my previous article.

We will use the Full text search and display the record in datalist control with highlighting the query words in displayed result as well as paging for datalist control. The paging used from this article taken from www.c-sharpcorner.com only.

After reading How To Create Full Text Index In Sql Server? You know now creating full text index now we can move to further step i.e. designing user interface. So lets start step by step.

Step 1

Download the attached code and run the script present in QueryScript folder in your database. That's our database is ready with index. Insert some rows.

Step 2

Start new website and name it as local_search. Add new page to the application and design it with Textbox for accepting user query and button to perform the postback and datalist to display the result.

Step 3

You can copy and paste this datalist source also. Here we are creating two datalist control one for displaying results of query and second one for performing paging with page no.

DataList For Results:

<asp:DataList ID="dListItems" runat="server" onitemdatabound="dListItems_ItemDataBound"
BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px"
CellPadding="3" GridLines="Vertical">
<AlternatingItemStyle BackColor="#DCDCDC" />
<FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
<HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
<ItemStyle BackColor="#EEEEEE" ForeColor="Black" />
<ItemTemplate>
<table>
<tr>
<td>
<asp:Label ID="lbltitle" runat="server" Text='<%#Eval("Title") %>' Font-Bold="true" ForeColor="Blue" Font-Underline="true"></asp:Label>
</td>
</tr>
<tr>
<td>

<asp:Label ID="lbldesc" runat="server" Text='<%#Eval("Desc") %>'></asp:Label>

</td>
</tr>
<tr>
<td>
<asp:HyperLink ID="hplnavigate" runat="server" Font-Bold="false"
ForeColor="#993300" NavigateUrl='<%#Eval("Link") %>'
Text="Click Here To Read More...">

</asp:HyperLink>
</td>
</tr>
<tr>
<td>
---------------------------------------------------------------------------------------------
</td>
</tr>
</table>
</ItemTemplate>
<SelectedItemStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
</asp:DataList>

Data List For Page No.

<asp:DataList ID="dlPaging" runat="server" OnItemCommand="dlPaging_ItemCommand"
OnItemDataBound="dlPaging_ItemDataBound" RepeatDirection="Horizontal">
<ItemTemplate>
<asp:LinkButton ID="lnkbtnPaging" runat="server"
CommandArgument='<%# Eval("PageIndex") %>' CommandName="Paging"
Font-Bold="false" Font-Size="Small" Text='<%# Eval("PageText") %>'></asp:LinkButton>

</ItemTemplate>
</asp:DataList>

Step 4

Still now we have given two datalist controls only now we again need to provide the first, next, last, previous facility also so create four linkbuttons. You can use this code for creating them.

<asp:LinkButton ID="lbtnFirst" runat="server" CausesValidation="false"
Font-Size="Small" OnClick="lbtnFirst_Click">First</asp:LinkButton>

<asp:LinkButton ID="lbtnPrevious" runat="server" CausesValidation="false"
Font-Size="Small" OnClick="lbtnPrevious_Click">Previous</asp:LinkButton>

<asp:LinkButton ID="lbtnNext" runat="server" CausesValidation="false"
Font-Size="Small" OnClick="lbtnNext_Click">Next</asp:LinkButton>
<asp:LinkButton ID="lbtnLast" runat="server" CausesValidation="false"
Font-Size="Small" OnClick="lbtnLast_Click">Last</asp:LinkButton>

Step 5

Now our user interface is ready but the controls which we created still yet are referring to some events in code behind. You can find out all events in .cs file. It's very much code is so I'm leaving it for you. Out of that on important event is for datalist control i.e. onitemdatabound which we will discuss here because in this event we had highlighted the user query.

string _que = txtquery.Text.ToLower();
string[] _splquery = _que.Split(' ');
Label lbldesc = (Label)e.Item.FindControl("lbldesc");
string[] _spldesc = lbldesc.Text.Split(' ');
lbldesc.Text = "";
foreach (var item in _spldesc)
{
if (_splquery.Contains(item.ToLower()))
{
lbldesc.Text += "<span style='font-family: BatangChe;font-size: medium;color: Red;font-weight: bold;'>" + item + "</span> ";
}
else
{
lbldesc.Text += item + " ";
}
}

This code is written in that event which we are talking to highlight the query of user which he enters in textbox. For effective display it's developer responsibility to highlight the user input in result.

In the above code you can see we are splitting the description lable text and giving some style to user query which it match with desc retrived from database.

Conclusion:

In very easy way we are able to provide the local database functionality in our asp.net web site.

Local Database search in ASP.NET

Reactions

Comments