How to search a database with string - ASP.NET Database Tutorial

What Will I Learn?

How to allow users to search a database for matching text.

Requirements

ASP.Net

Difficulty

Basic

Tutorial - How to search a database with string

Searching a website is often taken for granted. Implementing a search facility on a website used to be rather complex. However, as with many things in ASP.NET, it has gotten much easier. This tutorial will show how we can implement a simple search facility to allow users to input text and search a database for matching records.

First, we need to add the following assembly reference:

using System.Data.SqlClient;

In the Web.config, we declare the connection string:

<appSettings>
<add key="ConnString" value="Data Source=CLIENT-TASK2\SQLEXPRESS;Initial Catalog=BasicDataAccess;Integrated Security=True"/>
</appSettings>

The ASPX page will consist of a textbox, a button and a repeater control to display the results. It will look something like this:

<form id="form1" runat="server">
Search: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
<asp:Button ID="Button1" runat="server" Text="Search" OnClick="Button1_Click" /><br /><br />

<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate><table width="100%"><tr><th>Name</th>Cityth></tr>HeaderTemplate>
<ItemTemplate>
<tr><td><%#DataBinder.Eval(Container.DataItem, "theName")%>
<td><%#DataBinder.Eval(Container.DataItem, "theCity")%>
</ItemTemplate>
<FooterTemplate></table>FooterTemplate>
</asp:Repeater>
</form>

In the code-behind, we will reference a Stored Procedure, which will be something like this:
ALTER PROCEDURE spSearchByString
@SearchString varchar(50)
AS
SELECT [tblOne].theName, [tblOne].theCity
FROM [tblOne]
WHERE ([tblOne].theName LIKE '%' + @SearchString + '%' OR [tblOne].theCity LIKE '%' + @SearchString + '%')
RETURN

The code-behind should look something like this:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["searchString"] != null)
{
DisplaySearchResults(Request.QueryString["searchString"]);
}
}

public void DisplaySearchResults(string strSearch)
{
SqlCommand cmd = new SqlCommand("spSearchByString", new SqlConnection(ConfigurationManager.AppSettings["ConnString"]));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SearchString", strSearch);
cmd.Connection.Open();

Repeater1.DataSource = cmd.ExecuteReader();
Repeater1.DataBind();

cmd.Connection.Close();
cmd.Connection.Dispose();
}

protected void Button1_Click(object sender, EventArgs e)
{
Response.Redirect("Default.aspx?searchString=" + Server.UrlEncode(TextBox1.Text));
}
}


Posted on Utopian.io -  Rewarding Open Source Contributors