Using LIKE in SelectCommand of SqlDataSource in ASPNet

In this article I will explain with an example and attached sample code, how to use LIKE Operator in the SQL Query specified in the SelectCommand of SqlDataSource in ASP.Net.

Database

Here I am making use of Microsoft’s Northwind Database. You can download it from here

Download and install Northwind Database

Populating and Filtering the GridView

In the HTML Markup, there is an ASP.Net GridView control, a TextBox and a Button.

The GridView with records from the Customers table of the Northwind database using SqlDataSource. For the SqlDataSource, I have specified FilterExpression along with the SelectCommand, using which the records are filtered based on the value entered in the TextBox using LIKE statement

Search Customer:

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

<asp:Button ID="btnSearch" runat="server" Text="Search" />

<hr />

<asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"

runat="server" AutoGenerateColumns="false" DataSourceID="GridDataSource" AllowPaging="true">

<asp:BoundField DataField="ContactName" HeaderText="Name" ItemStyle-Width="150" />

<asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="150" />

<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="GridDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConStr %>"

SelectCommand="SELECT ContactName, City, Country FROM Customers" FilterExpression="ContactName LIKE '{0}%'">

<asp:ControlParameter Name="Country" ControlID="txtSearch" PropertyName="Text" />

</FilterParameters>

</asp:SqlDataSource>

SelectCommand – Here we need to set the default SQL Query without any WHERE Clause or filter.

FilterExpression – Here we need to set the filter part of the SQL Query with a placeholder {0}. The placeholder {0} will be replaced by the value of the ControlParameter, i.e. the TextBox’s Text

FilterParameters – Here we specify the Control Parameters to be used by the FilterExpression

As soon as some text is entered in the TextBox and the Search Button is clicked, the GridView records are filtered.

Note: SqlDataSource can also filter records in GridView using QueryString Parameters, to know more about it please refer ASP.Net SqlDataSource pass value to SelectParameter using QueryString Parameter Example