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.