In this article we will show if we want to use filter
expression in Gridview using SqlDataSource we can use filter expression in
SqlDataSource for filtering record in Gridview or DataSource.
Here we will take example of country and city for filtering record
in Gridview from SqlDataSource. Here we will cascade filter operation for
searching record in Gridview.
I have taken customer table for bind customer details in
Gridview and for filter record I have took cascade expression in SqlDataSource.
Here I have bind Gridview using SqlDataSource so we don’t
have to code for bind Gridview or directly we can bind data using SqlDataSource
in Gridview.
Create table in Sql
and fill some data for checking.
CREATE TABLE [dbo].[Customers](
[ContactName] [varchar](20) NOT NULL,
[City] [varchar](20) NOT NULL,
[PostalCode] [int] NOT NULL,
[Country] [varchar](20) NOT NULL,
[Phone] [bigint] NOT NULL,
[Fax] [bigint] NOT NULL,
[CustomerId] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT
[PK_Customers] PRIMARY KEY
CLUSTERED
(
[CustomerId] ASC
)WITH
(PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Now using
SqlDataSource bind Gridview and CreateFilter Expression for Filter record from
Gridview and we can see that record in Gridview.
HTML CODE:
<div>
Select Country:
<asp:DropDownList ID="ddlCountries" runat="server" AutoPostBack="true" DataSourceID="DropDownDataSource" DataTextField="Country"
DataValueField="Country"
AppendDataBoundItems="true"><asp:ListItem Text="All Countries" Value="" />
</asp:DropDownList>
<asp:SqlDataSource ID="DropDownDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:UHConnection %>" SelectCommand="SELECT DISTINCT [Country] FROM
[Customers]"></asp:SqlDataSource>
<asp:DropDownList ID="ddlCities" runat="server" AutoPostBack="True" DataSourceID="DropDownDataSource1" DataTextField="City"
DataValueField="City"
AppendDataBoundItems="True">
<asp:ListItem Text="All Cities" Value="" />
</asp:DropDownList>
<asp:SqlDataSource ID="DropDownDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:UHConnection %>" SelectCommand="SELECT DISTINCT [City] FROM [Customers] WHERE
([Country] = @Country)">
<SelectParameters>
<asp:ControlParameter ControlID="ddlCountries" Name="Country" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<hr />
<asp:GridView ID="GridView1"
HeaderStyle-BackColor="#3AC0F2"HeaderStyle-ForeColor="White"
runat="server"
AutoGenerateColumns="False"
DataSourceID="GridDataSource"
BackColor="White"
BorderColor="#CCCCCC"
BorderStyle="None"
BorderWidth="1px"
CellPadding="4"
ForeColor="Black"
GridLines="Horizontal">
<Columns>
<asp:BoundField DataField="ContactName" HeaderText="ContactName" ItemStyle-Width="150" SortExpression="ContactName">
<ItemStyle Width="150px"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="150" SortExpression="City">
<ItemStyle Width="150px"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" SortExpression="Country">
<ItemStyle Width="150px"></ItemStyle>
</asp:BoundField>
</Columns>
<FooterStyle BackColor="#CCCC99" ForeColor="Black" />
<HeaderStyle BackColor="#333333" ForeColor="White" Font-Bold="True"></HeaderStyle>
<PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F7F7F7" />
<SortedAscendingHeaderStyle BackColor="#4B4B4B" />
<SortedDescendingCellStyle BackColor="#E5E5E5" />
<SortedDescendingHeaderStyle BackColor="#242121" />
</asp:GridView>
<asp:SqlDataSource ID="GridDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:UHConnection
%>"
SelectCommand="SELECT [ContactName], [City], [Country] FROM
[Customers] " FilterExpression="Country='{0}' and City='{1}'">
<FilterParameters>
<asp:ControlParameter Name="Country" ControlID="ddlCountries" PropertyName="SelectedValue" />
<asp:ControlParameter Name="City" ControlID="ddlcities" PropertyName="SelectedValue" />
</FilterParameters>
</asp:SqlDataSource>
</div>
Thanks for comments.....