ASP.NET 3.5 Search Function Basic Development Concepts

With this approach, having search on your site is more user-friendly and flexible because it saves time in the process of searching for related content. In ASP.NET, tables of data are conveniently placed in an MS SQL server database, and the ASP.NET web application will just retrieve information from the database based on the user query.

The objective of this important tutorial is to provide basic concepts for developing a search function in ASP.NET 3.5. This will be very helpful for beginners needing to add a search form to their website.

Before proceeding, keep in mind that this tutorial is targeted to ASP.NET 3.5 users using MS SQL server database 2008 and using Visual Web Developer Express 2008 edition.

Illustrated in this tutorial are real world application case studies. The case example below will show the guitarist database discussed in an earlier ASP Free tutorial.  

First situation: suppose you are asked to develop an ASP.NET web application that accepts a user search query in the web form. The search query should be any string containing a guitarist's name, and when entered, ASP.NET will retrieve database records of rows containing the matched query (guitarist's name). The user can either enter the first name, last name or the complete full name to make the query valid.

Second situation: suppose that the web form not only accepts a guitarist's name, but will allow the user to search the entire guitarist database column, which includes the famous performance, associated acts, and playing style.

Create the Project and the Sample Database

You will need to launch Visual Web Developer Express and follow the steps below.

Step 1. Click “Create Website.”

Step 2. Under Visual Studio Installed Templates, click “ASP.NET Web site.”

Step 3. Under location and path select “File System” and enter the path where you would like to save your ASP.NET project in your Windows file system. Do not forget to include the project name in the path (example: aspdotnetsearch), so your path will look like this:

E:aspdotnetprojectsaspdotnetsearch

The above path means that the project is saved in Drive E of the Windows hard drive, in the folder named “aspdotnetprojects,” and then inside that folder, Visual Web Developer Express will create the project folder “aspdotnetsearch,” which is the name of the project and where all of the ASP.NET files will be saved.

Step 4. Under Language, select “Visual Basic.”

Finally, click OK.

Before you can add any search form to your ASP.NET web page, you need to create a MS SQL server database which contains the data to be searched. For simplicity, you are going to use the databases discussed in this tutorial, and follow the steps outlined on that page to create the database, tables and associated data.

Make sure that the database name is “guitarist.mdf” and place it under App_Data. In the Visual Web Developer Solution Explorer, you will then see the database under App_Data:

Add the Search Form and the Button

To add a search web form and a button, you will need to add an ASP.NET text box web control and button web controls. To add a search web form to be placed on the Default.aspx, go to View à Toolbox à Standard à TextBox. Click and drag the TextBox web control next to:

<form id="form1" runat="server">

<div>

Just before <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> , you will need this text to describe the purpose of the web form:

“Search the guitarist database by entering either the first name, last name or full name of the guitarist:” 

The next step is to add a submit button. Go to View à Toolbox à Standard à Button. Click and drag the Button web control from the toolbox and place it next to:

<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

Do not forget to insert <br /><br />  between the button and the text box web control. To make the button more meaningful, change the Text property from “Button” to “Submit Query.”

Add and Configure SqlDataSource to the Web Form

The next step is to add a SqlDataSource web control to the web form. Go to View à Toolbox à Data à Sqldatasource. Click and drag Sqldatasource from the Toolbox next to: <asp:Button ID="Button1" runat="server" Text="Submit Query" />  

Now click the “Design” tab in Visual Web Developer and right click on the SqlDataSource web control. Click “Configure Data Source.” Under “Which data connection should your application use to connect to the database?” select guitarist.mdf and then click next.

Check “Yes, save this connection as: ConnectionString” and then click next. Under “Specify columns from a table or view” select “topguitarist” and under Columns, check “*”.

First, let’s implement the first situation of the search form requirement stated in the first section of this tutorial, which is to allow users to just search in the guitarist player name column only, by entering any string. ASP.NET will then search the playername database and return rows that matched the user's query.

To implement this in the SqlDataSource, click “WHERE.”  Under “Column,” choose “playername” since the search query is based on playername. Under “Operator,” choose “LIKE,” since you will need to match whether users enter only the first name, last name or even the full name of the guitarist.

Under “Source,” select “Control” since you need to dynamically send the search query to the SQL Server based on what the user enters in the text box. Finally, in the Control ID, select TextBox1 (this is the ID of the Textbox used in the ASP.NET web page Default.aspx).

When everything is set, click “Add” and then click OK. Click Next and Finish.

Add and Configure the GridView Web Control

You need a web control that will display the results retrieved from the MS SQL Server database provided by SqlDataSource. To do this, go back to the Source view in Visual Web Developer, and then click and drag the “GridView” web control (from: View à Toolbox à Data à GridView) next to </asp:SqlDataSource> 

Go to the Design view, right click on the GridView web control, click “Show Smart Tag” and under “Choose Data Source,” select SqlDataSource1.

Since you do not need to display the guitarist ID to the web browser, you need to remove that by going to Show Smart Tag à Edit Columns, and under “Selected fields” select guitaristid and click the red x button to delete it.

To display a text to the web browser in case there are no associated records for that specified user query, click the “GridView” web control in the Design, and then to your right, you can see the properties. Find the EmptyDataText property under “Appearance” and then type this text:

“Sorry but no associated database records related to your query”.

Test the Application in the Web Browser

Now that the basic functionality is completed, go to File à View in Browser. Since the grid view is still empty (no search result is given because no query has been made) it displays the default empty data text property value: “Sorry but no associated database records related to your query.”

Try entering “Jimi.” It should return rows matching Jimi Hendrix because this record exists in the database.

Extending the Search Capability to All Columns in the Database

The above example only returns results based on the playername column in the database table. This means that if users query about their famous performances or associated acts, they will get an empty result since this is not configured in the SqlDataSource.

To extend the search capability to all columns, you will need to reconfigure the SqlDataSource. Right click on it in the Design View and then Show Smart Tag à Configure Data Source.

The only thing that needs to be altered is the WHERE statement. Proceed until you see the WHERE button in the “Configure the SELECT statement” and click on it. At this time, only the playername is configured with the LIKE operator. You need to add all columns. This means you need to repeat the procedure under the "Add and Configure SqlDataSource to the Web Form" section earlier in this article for these three columns: associatedacts, famousperformance and playerstyle

Use the same configuration settings for all three remaining columns:

Operator: LIKE

Source: Control

Control ID: TextBox1

After adding all the remaining three columns, the “Add WHERE Clause” should look like this:

You can see the different WHERE clause statement being added in the above screen shot. After that, click next and Finish.

When you examine the SelectCommand in the source code of Visual Web Developer, the automatically generated markup code you get from executing the steps above is as follows:

SelectCommand="SELECT * FROM [topguitarist] WHERE (([playername] LIKE '%' + @playername + '%') AND ([associatedacts] LIKE '%' + @associatedacts + '%') AND ([famousperformance] LIKE '%' + @famousperformance + '%') AND ([playerstyle] LIKE '%' + @playerstyle + '%'))"> 

However, when this SQL query is used, it will not provide any results at all in the browser since it uses AND statements. Since the text will accept a query on “either” a player name, associated acts, famous performance and playing style, the most appropriate thing to do is to change all the ANDs to ORs, so that it will look like this:

SelectCommand="SELECT * FROM [topguitarist] WHERE (([playername] LIKE '%' + @playername + '%') OR ([associatedacts] LIKE '%' + @associatedacts + '%') OR ([famousperformance] LIKE '%' + @famousperformance + '%') OR ([playerstyle] LIKE '%' + @playerstyle + '%'))"> 

This is the correct SQL query, since it can accept any type of search query targeting all columns.

Of course you need to change the text in the Default.aspx from “Search the guitarist database by entering either the first name, last name or full name of the guitarist:” to a more relevant descriptive text:

“Search the guitarist database by entering any keyword (first name, last name, performance title, playing style, rock , etc)”

So let’s test the application using a web browser (File à View in Browser), then say that you need to see all blues guitarists by entering “blues” in search query.

You can download the complete source code (Default.aspx) for this tutorial.