How to get the ListItem based on the column name in ASP.net?

Question

I have a drop down list box like following. Under some condition, in the databound event I want to remove the items whose bitActive is set to 0 (inactive). I did not put a WHERE bitAcive!=0 in the selectCommand, because I only want to remove them under some conditions. Is there any way I could iterate the items and check the value of bitActive?

<tr>
            <td width="30%" align="right">Location<span class="littlefont">*</span></td>
            <td width="70%" align="left">
                <asp:DropDownList ID="ddlLocation" runat="server" 
                    DataSourceID="SqlDSLocation" DataTextField="txtRefLocation_Name" 
                    DataValueField="intRefLocation_ID" ondatabound="ddlLocation_DataBound">
                </asp:DropDownList>
                <asp:SqlDataSource ID="SqlDSLocation" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:SPRConnectionString %>" 
                    SelectCommand="SELECT DISTINCT [intRefLocation_ID], [txtRefLocation_Name], [location], [bitActive] FROM [tblRefLocation] ORDER BY [intRefLocation_ID]">
                </asp:SqlDataSource>
            </td>
        </tr> 

Answer 1

In codebehind you can call the SQLDataSource.Select() method:

System.Data.DataView dv = (System.Data.DataView)SqlDSLocation.Select(DataSourceSelectArguments.Empty);

And then iterate through the rows returned, finding the "bitActive" rows who are set to zero and removing them from your DropDownList (code sort of hacked from the example linked above):

foreach(System.Data.DataRow row in dv.Table.Rows)
{
    // This is approximate logic, tailor this to fit your actual data
    if (row["bitActive"].ToString() == "False")
    {
        ddlLocation.Items.Remove(row["intRefLocation_ID"].ToString());
    }
}

Note that this is not removing these rows from your SQL table. Make sure you don't databind your DropDownList again after this - otherwise all the stuff you just removed will return.

EDIT: For a more efficient and elegant solution, see James Johnson's answer.

Answer 2

Instead of removing items in the ItemDataBound event, why not just filter the datasource before binding it?:

var table = new DataTable("MyTable"); //assume it's populated
if (table.Rows.Count > 0)
{
    var results = table.AsEnumerable().Where(r => r.bitActive).AsDataView().ToTable();
    if (!results.HasErrors)
    {
        DropDownList1.DataSource = results;
        DropDownList1.DataBind();
    }        
}