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.
