Searching a Database with a String in C#
SetupIf you have not already done so, open Visual Studio and start a new web project.
The Concept…Databases usually contain a large amount of information. This information often needs to be filtered before viewed. There are many ways to sort and filter database information. In our example we will be searching through a database of cities. The visitor will be allowed to enter in text which will be used to filter through the different cities in our database.
Step 1.The first step we will take is creating the database. You will need a database with a simple three column table that contains an ID, City, and a Name column. The definition used to create this database table is as follows:
Step 2.The next step we will take is creating our user interface. All we want for our user is the ability to type in a letter or two and click search. Drag and drop a label, a textbox next to that label, a button underneath the label/textbox, a repeater control underneath that group and a SqlDataSource and Label underneath the repeater control.
Modify your code so it appears like the code below:
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
<asp:Button ID="Button1" runat="server" Text="Search" OnClick="Button1_Click" /><br />
<asp:Repeater ID="Repeater1" runat="server"
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [People]"></asp:SqlDataSource>
<asp:Label ID="lblStatus" runat="server"></asp:Label>
Step 3.Now, we will need to add our stored procedure to the database. Switch to the server explorer and right click on the Stored Procedures folder located within the Database files. You will select, “Add New Stored Procedure”.
Copy and paste the following code there:
ALTER PROCEDURE dbo.spSearchByString
SELECT [People].Name, [People].City
WHERE ([People].Name LIKE '%' + @SearchString + '%' OR [People].City LIKE '%' + @SearchString + '%')
Step 4.The last step, but most definitely not least is the functionality of the interface. Double click on your Search button so that Visual Studio creates the on_click method in the code behind. Within the code behind, we will need to have the appropriate binding statements added so that our stored procedure is used. To do this, we are to make a DisplaySearchResults method that takes one string parameter called strSearch. The code is as follows for this particular method:
public void DisplaySearchResults(string strSearch)
SqlCommand cmd = new SqlCommand("spSearchByString", new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.mdf;Integrated Security=True;User Instance=True"));
cmd.CommandType = CommandType.StoredProcedure;
Repeater1.DataSource = cmd.ExecuteReader();
Save your changes and switch over to your design view of the project. Double click on the repeater control so that you may have the Item Command method created in the code behind. We aren’t going to include anything within the method, but if you intend to do anything specific with the repeater control you will need this. Now that you are back to the code behind, we are going to add some items to the button on_click method.
Modify the method so that it contains the following code:
protected void Button1_Click(object sender, EventArgs e)
if (TextBox1.Text == "" | TextBox1.Text == null)
lblStatus.Text = "Please enter valid search criteria";
lblStatus.Text = "";
TextBox1.Text = "Request Submitted...";
Save and run the program. When the user enters b into the text box and hits enter, notice how it selects all possible entries containing a b either in the name or city field, or even both.
A Few Last Words…There are various applications to today’s lesson; it all depends on what you are looking for in terms of functionality and results. Learning how to control and manipulate a database is easy with ASP.NET and we intend to be there every step of the way to help! Thank you for being a valued reader and join us next time for additional database tutorials!