SqlDataSource provide FilterExpression property used to filter rows returned by the control. FilterExpression reducing the amount of code needed to build condition and logical filter expression. Many advantage of filter expression is that you can search data from multiple tables. SqlDataSqource control use SelectCommand statement to retrieve data from database.
In Default.aspx, I have used FilterExpression manually in SqlDataSource and in Dynamic.aspx page I dynamically set the FilterExpression and FilterParameters. FilterParameters is read only property. If you click on search button, GridView shows the filter data. Both Default.aspx and Dynamic.aspx return same result.
Default.aspx
<%@ Page Title="Home Page" Language="VB" MasterPageFile="~/Site.Master" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Search" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:Master %>"
SelectCommand="SELECT [EMP_FNAME], [EMP_LNAME], [EMP_COUNTRY] FROM [EMPLOYEE1]"
FilterExpression="EMP_FNAME like '{0}%' or EMP_LNAME like '{0}%'">
<FilterParameters>
<asp:ControlParameter ControlID="TextBox1" Name="Name" PropertyName="Text" />
</FilterParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="EMP_FNAME" HeaderText="EMP_FNAME"
SortExpression="EMP_FNAME" />
<asp:BoundField DataField="EMP_LNAME" HeaderText="EMP_LNAME"
SortExpression="EMP_LNAME" />
<asp:BoundField DataField="EMP_COUNTRY" HeaderText="EMP_COUNTRY"
SortExpression="EMP_COUNTRY" />
</Columns>
</asp:GridView>
</asp:Content>
Output:
Dynamic.aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Dynamic.aspx.vb" Inherits="Dynamic" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Search" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:Master %>"
SelectCommand="SELECT EMP_FNAME, EMP_LNAME, EMP_COUNTRY FROM EMPLOYEE1" >
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="EMP_FNAME" HeaderText="EMP_FNAME"
SortExpression="EMP_FNAME" />
<asp:BoundField DataField="EMP_LNAME" HeaderText="EMP_LNAME"
SortExpression="EMP_LNAME" />
<asp:BoundField DataField="EMP_COUNTRY" HeaderText="EMP_COUNTRY"
SortExpression="EMP_COUNTRY" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
Dynamic.aspx.vb
Partial Class Dynamic
Inherits System.Web.UI.Page
Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click
SqlDataSource1.FilterExpression = "EMP_FNAME like '{0}%' or EMP_LNAME like'{0}%'"
SqlDataSource1.FilterParameters.Add("EMP_FNAME", TextBox1.Text)
End Sub
End Class
Output: