Display Hierarchy in a DropDown List

I have a data hierarchy that I currently display in a treeview. I was wondering what would be the easiest way to convert this hierarchy into a dropdown list as well. In a treeview I can find a specific node and add an item under that node. I'm not sure how to do that with a drop down list. Belo开发者_JAVA百科w is the code I have for the dropdown so far:

DropDown Hierarchy

**Solved

    public void DropDownTree(DropDownList ddl)
    {
        ddl.Items.Clear(); 
        using (SqlConnection connection = new SqlConnection())
        {
            // Data Connection
            connection.ConnectionString = (ConfigurationManager.ConnectionStrings["AssetWhereConnectionString"].ConnectionString);
            connection.Open();
            string getLocations = @"
                With hierarchy (id, [location id], name, depth, [path])
                As (

                    Select ID, [LocationID], Name, 1 As depth,
                        Cast(Null as varChar(max)) As [path]
                    From dbo.Locations
                    Where ID = [LocationID]

                    Union All

                    Select child.id, child.[LocationID], child.name,
                        parent.depth + 1 As depth,
                        IsNull(
                            Cast(parent.id As varChar(max)),
                            Cast(parent.id As varChar(max))
                        ) As [path]
                    From dbo.Locations As child
                    Inner Join hierarchy As parent
                        On child.[LocationID] = parent.ID
                    Where child.ID != parent.[Location ID])

                Select *
                From hierarchy
                Order By [depth] Asc";

            using (SqlCommand cmd = new SqlCommand(getLocations, connection))
            {
                cmd.CommandType = CommandType.Text;
                using (SqlDataReader rs = cmd.ExecuteReader())
                {
                    while (rs.Read())
                    {
                        string id = rs.GetGuid(0).ToString();
                        int depth = rs.GetInt32(3);
                        string text = rs.GetString(2);
                        string locationID = rs.GetGuid(1).ToString();
                        string padding = String.Concat(Enumerable.Repeat("- ", 2 * depth));


                        if (id == locationID)
                        {
                            ddl.Items.Add(new ListItem(padding + text, id));
                        }
                        else
                        {
                            int index = ddl.Items.IndexOf(ddl.Items.FindByValue(rs.GetString(4).ToString().ToLower()));

                            // Fix the location where the item is inserted. 
                            index = index + 1;

                            ddl.Items.Insert(index, new ListItem(padding + text, id));

                        }
                    }
                }
            }
        }
    }

Your code looks fine but from your comment under your question, it sounds like you are getting a negative value for the index on this line:

int index =  ddl.Items.IndexOf(ddl.Items.FindByValue(rs.GetString(4).ToString().ToLower()));

A negative value indicates that the dropdownlist item that you were searching for was not found. This would certainly be the case if the dropdownlist is empty. You would be searching for an item that doesn't exist.

Below is an update to your code that will produce the following (which I believe is what you're looking for):

Try changing your code to the following:

if (id == locationID)
{
    ddl.Items.Add(new ListItem(padding + text, id));
}
else
{
    int index =  ddl.Items.IndexOf(ddl.Items.FindByValue(rs.GetString(4).ToString().ToLower()));

    //Check to see if this item exists before trying to insert
    if (index == -1) 
    {
        //Add the item if it doesn't exist
        ddl.Items.Add(new ListItem(padding + text, id));
    }
    else
    {
        ddl.Items.Insert(index, new ListItem(padding + text, id));
    }
}

The only way you have is contatenate a string to the Node Description: it might me a white space just to indent the child node against its parent or something graphically better

With standard HTML select lists there are two ways to display hierarchy:

• If root nodes are not selectable, you can use optgroup elements. Unfortunately, ASP.NET DropDownList does not support optgroups.

• If root nodes are selectable, use whitespace to indent child nodes

I would say that the easiest way is to use the depth value in your resultset:

    DropDownList ddl = new DropDownList();
    while (rs.Read())
    {
        string id = rs.GetGuid(0).ToString();
        int depth = rs.GetInt32(3);
        string text = rs.GetString(2);
        string padding = String.Concat(Enumerable.Repeat(" ", 4 * depth));
        ddl.Items.Add(new ListItem(padding + text, id));
    }