Adding Show All option to a DropDownList GridView data filter with LinqDataSource

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

1. 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.
2. 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

1. 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.



2. 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

1. 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

1. 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.NET

<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.