Inject record into DataList

Question

Hello,

I would like suggestions on how to inject a record into my DataList to give an "All" option. Here is my code, data coming from the Northwind database.

<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1" 
        RepeatLayout="Flow" ShowFooter="False" ShowHeader="False" 
        RepeatDirection="Horizontal" 
        onitemcommand="DataList1_ItemCommand">
        <ItemStyle CssClass="datalist" />
    <ItemTemplate>
        <%#(((DataListItem)Container).ItemIndex+1).ToString() %>
        <asp:LinkButton ID="lbtnRegion" runat="server" 
          Text='<%# Eval("RegionDescription").ToString().Trim() %>'
        CommandName='<%# DataBinder.Eval(Container.DataItem,"RegionID")%>' />                    
    </ItemTemplate>       
    </asp:DataList>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
    SelectCommand="SELECT [RegionID], [RegionDescription] FROM [Region]" 
        ondatabinding="SqlDataSource1_Databinding" 
    onselected="SqlDataSource1_Selected">
</asp:SqlDataSource>

I am using the Link button in the Datalist to filter the territories and display them in a GridView. What I would like to do is at some in the databinding process, add an Item in the DataList that will act as the ALL option, any suggestions would be appreciated.

protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
{
    LinkButton lbtn;

    foreach (DataListItem dli in DataList1.Items)
    {
        lbtn = (LinkButton)dli.FindControl("lbtnRegion");
        if (lbtn != null)
            lbtn.ForeColor = System.Drawing.Color.White;
    }
    string command = e.CommandName;
    lbtn = (LinkButton)e.Item.FindControl("lbtnRegion");
    if (lbtn != null)
        lbtn.ForeColor = System.Drawing.Color.YellowGreen;

    DataView dv = GetData(ref command); // Pass the RegionId
    gvTerritory.DataSource = dv;
    gvTerritory.DataBind();
}

Thanks

Answer 1

One way is to UNION ALL a 'All' value to the query fetching the list of drop down items.

SELECT 'All', 'All Regions' 
UNION ALL 
SELECT [RegionID], [RegionDescription] FROM [Region]

But if you have a lot of lists (or dropdowns) like this, it is better practice to create a custom control that injects an 'All' record for you.

Answer 2

It worked with the following SQL:

SELECT '-1' AS 'RegionID', 'All Regions' AS 'RegionDescription' 
UNION ALL SELECT [RegionID], [RegionDescription] FROM [Region]

Answer 3

Used this on a drop down, may work the same on a datalist

  Protected Sub ddlDataSources_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDataSources.DataBound
    ddlDataSources.Items.Insert(0, New ListItem("All Data Sources", 0))
  End Sub