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><th>City</th></tr></HeaderTemplate>
<ItemTemplate>
<tr><td><%#DataBinder.Eval(Container.DataItem, "theName")%></a></td>
<td><%#DataBinder.Eval(Container.DataItem, "theCity")%></td></tr>
</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
Thank you for your post. :) I have voted for you: 🎁! To call me just write @contentvoter in a comment.
Congratulation
Today one year ago you joined SteemItThank you, for making SteemIt great and Steem on for more years to come!
(You are being celebrated here)
Congratulations @evariste! You have received a personal award!
1 Year on Steemit
Click on the badge to view your own Board of Honor on SteemitBoard.