SQL Conditional filter with DropDownList

The following can be used to filter a parameterised SQL query using a drop down list to provide the parameter, or if left blank, will return all results. To keep this quick, im not returning/binding the data to a grid etc...
There are 3 parts required:

• A DropDownList 
• SQLDataSource 
• SQLQuery/Stored Procedure

DropDownList

This DropDownList will be populated from a list of Countries provided by a SQL table and will be used to filter the results set

The important things to note for the DropDown are:


AppendDataBoundItems="true" - Lets you append additional items into a SQL result set
<asp:ListItem Text="" Value="" /> = Creates a blank row at the top of the databound results set

<asp:DropDownList ID="ddlCountryList" runat="server" DataSourceID="dsCountryList"

        DataTextField="BKCountryName" DataValueField="BKCountryName" Width="150px"  
       AutoPostBack="True" AppendDataBoundItems="true">
        <asp:ListItem Text="" Value="" />
</asp:DropDownList>




SQLDataSource
The DataSource is a standard SQLDataSource that is connecting through to my Test Database and wil try and execute a stored Procedure called "usp_ReturnTestData". this proc takes an arguement "Country", which is being provided by the DropDownList.

There are two key things with the DataSource:

1. The property ConvertEmptyStringToNull="true". This ensures that the empty value that were passing from the DropDown will reach the database as a NULL value, and not an empty string.
2. The propery CancelSelectOnNullParameter="false". This will make sure the SQLDatasource doesnt stop as soon as it finds a null value as the parameter

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:cnTestDatabase %>"                SelectCommand="usp_ReturnTestData" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="false">

                <SelectParameters>

                    <asp:ControlParameter ControlID="ddlCountryList" Name="Country" PropertyName="SelectedValue"  

                             ConvertEmptyStringToNull="true" Type="String" />

                </SelectParameters>

</asp:SqlDataSource>

SQL Query
The final part is the SQL Query. In a normal SQL query where we want to return only the value specified in the parameter we could use :

SELECT * FROM Products

WHERE Country = @Country

But in this example, we want to be able to bring back all records if a country is not specified. To do this, we can use the SQL ISNULL() method to evaluate the parameter.

SELECT * FROM Products
WHERE Country = ISNULL(@Country,Country)

The ISNULL function will check to see if the parameter pass is null and if so, return all rows and if if is not, it will filter by the value.