Introduction
This is a very quick way (kind of a workaround) to implement a "Show all" option to a dropdownlist filter of a GridView
, through a LinqDataSource
,
using the Visual Studio Designer mode.
In this example, we are using LINQ to SQL entity classes (shown in the picture below) which are contained in a .dbml file named VehiclesData.dbml.
The vehicle models are going to be listed in the GridView
and filtered with the dropdownlist by its manufacturer.
Setting up the GridView
- Add a
LinqDataSource
control to the GridView
.
- Expand the
GridView
's 'Smart Tasks' and choose New Data Source.
- For data type, select LINQ (we'll name it
GridViewDataSource
in this example).
- Click OK.
- Configure the
GridViewDataSource
to use the VehicleModels table.
- Expand the 'Smart Tasks' of the
GridView
- Choose the appropriate
DataContext
(in our case, VehiclesDataDataContext
), click Next. - Select the VehicleModel table.
Note: to have the Edit, Update, Delete functions automatically available in the GridView
, all fields from the table must be selected.
- Click Finish.
We'll setup the GridView
to show only the Name column and allow sorting and paging.
Setting up the DropDownList
- Configure the
DropDownList
to list the Manufacturer column from the VehicleManufacturers table:
- Add another
LinqDataSource
(with name DropDownDataSource
here). - Expand the 'Smart Tasks' of the DropDownDataSource, select Configure Data Source, choose
VehiclesDataDataContext
. - Now select the VehicleManufacturers table, with only the Manufacturer and ID columns. We need the Manufacturer column to show it in the list
and the ID to bind everything here together.
- Configure the
DropDownList
to show the data in the Manufacturer column.
- Open the 'Smart Tasks' of the
DropDownList
, select Choose Data Source. - Choose the
DropDownDataSource
. For the data field (this is shown on the list), choose Manufacturer, and the data value, we need the ID.
Setup the DropDownList to filter the data displayed in the GridView
- Setup the '
Where
' clause of the GridViewDataSource
.
- Open the 'Smart Tasks' of the
GridViewDataSource
, select Configure Data Source, click Next, and then Where.
- VehicleManufacturerID is the foreign key in the VehicleModels table, which needs to be associated to the ID column from the VehicleManufacturer table.
"How to" is shown in the picture below:
- Click Add.
- Click OK.
- Set AutoPostBack="true" to the
DropDownList
.
Up until now, we have everything working as expected, but the GridView
shows only the filtered selection from the DropDownList
.
Adding 'Show All' option to the DropDownList filter
- Add the 'Show All' list item:
- Expand 'Smart Tasks' of the
DropDownList
, select Edit Items. - Add new item, name it 'Show All', and for value, put -1.
- Click OK.
- Set
AppendDataBoundItems="true"
to the DropDownList
. - Go to the
GridViewDataSource
source and make the changes like shown below:
<asp:linqdatasource id="GridViewDataSource" runat="server"
contexttypename="WebApplication1.VehiclesDataDataContext"
tablename="VehicleModels"
where="(VehicleManufacturerID ==
@VehicleManufacturerID) || (@VehicleManufacturerID == @ShowAll)">
<WhereParameters>
<asp:ControlParameter ControlID="ddManufacturers" Name="VehicleManufacturerID"
PropertyName="SelectedValue" Type="Int32" />
<asp:Parameter Name="ShowAll" Type="Int32" DefaultValue="-1"/>
</WhereParameters>
</asp:linqdatasource>
So what happens is that we add a helper parameter to extend the Where
clause of the LINQ query.
where="(VehicleManufacturerID
== @VehicleManufacturerID) || (@VehicleManufacturerID == @ShowAll)"
with the help of the additional parameter:
<asp:Parameter Name="ShowAll" Type="Int32" DefaultValue="-1" />
But remember that its value must never have a value that the @VehicleManufacturerID
or whatever parameter we equal to it, might have.