need an algorithm to create nested categories in c#

Question

I have a table structure like below :

categoryID bigint , primary key , not null
categoryName nvarchar(100) 
parentID bigint, not null

where as categoryID and parentID has an one-to-many relation to each other

and I want to create a nested categories with unlimited depth out of this table in my program.

I have a solution but it is not work so good and only returns the root please see the code :

    private static string createlist(string catid, string parent)
    {

        string sql = "SELECT categoryID , categoryName FROM category WHERE parentID = " + parent;
        SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=sanjab;Integrated Security=True");
        cn.Open();
        SqlCommand cmd = new SqlCommand(sql, cn);
        SqlDataReader sdr = cmd.ExecuteReader();

        while (sdr.Read())
        {
            if (catid != "")
                catid += ", ";
            catid += sdr[1].ToString();
            createlist(catid, sdr[0].ToString());


        }
        return catid;
    }

although the code is not very efficient cause it is opening a lots of connection at the same time but with the help of above code and little bit of tweaking I can manage 2-levels depth categories but more than that means lots of trouble for me.

is there any easier method or algorithm?

regards.

Answer 1

Assuming you're wanting to deal with the entire Category hierarchical object graph all at once, and assuming you're dealing with a reasonable number Categories (deliberate emphasis), your best bet is to probably load all the data from your SQL database in one go, then build up your tree object graph in memory, rather than hit the database for each node in the tree, which could result in hundreds of database look-ups.

For instance, if there were 5 Categories and each had 5 Sub-Categories, and each Sub-Category had 5 Sub-Sub-Categories you're looking at 31 database hits to load that up using recursion from the database point-of-view, even though you'll only dealing with 125 actual database records. Selecting all 125 records in one go isn't going to break the bank where 31+ database hits might.

To do this I would first return your Categories as a flattened list (pseudo code):

public IList<FlattenedCategory> GetFlattenedCategories()
{
    string sql = "SELECT categoryID, categoryName, parentID FROM category";
    SqlConnection cn = // open connection dataReader etc. (snip)

    while (sdr.Read())
    {
        FlattenedCategory cat = new FlattenedCategory();
        // fill in props, add the 'flattenedCategories' collection (snip)
    }

    return flattenedCategories;
}

With the FlattenedCategory class loking something like this:

public class FlattenedCategory
{
    public int CategoryId { get; set; }
    public string Name { get; set; }
    public int? ParentId { get; set; }
}

Now we have an in-memory collection of all the Categories, we build up the tree like this:

public IList<Category> GetCategoryTreeFromFlattenedCollection(
    IList<FlattenedCategory> flattenedCats, int? parentId)
{
    List<Category> cats = new List<Category>();

    var filteredFlatCats = flattenedCats.Where(fc => fc.ParentId == parentId);

    foreach (FlattenedCategory flattenedCat in filteredFlatCats)
    {
        Category cat = new Category();
        cat.CategoryId = flattenedCat.CategoryId;
        cat.Name = flattenedCat.Name;

        Ilist<Category> childCats = GetCategoryTreeFromFlattenedCollection(
            flattenedCats, flattenedCat.CategoryId);

        cat.Children.AddRange(childCats);

        foreach (Category childCat in childCats)
        {
            childCat.Parent = cat;
        }

        cats.Add(cat);
    }

    return cats;
}

And call it like this:

IList<FlattenedCategory> flattenedCats = GetFlattenedCategories();
Ilist<Category> categoryTree = GetCategoryTreeFromFlattenedCollection(flattenedCats, null);

Note: in this example we're using a Nullable INT for the ParentCategoryId, a nullable value would mean it's a root (top-level) category (no parent). I'd recommend you make your parentID field in your database nullable too.

Warning: Code not tested, just pseudo code, so use at your own risk. It's just to demonstrate the general idea.