need an algorithm to create nested categories in c#

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;
    }开发者_Go百科

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.

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.

I would first create a Category class to hold your categories and child Categories. this will enable you to implement recursion read out your infinitely deep child categories. It would look like this: (Disclaimer none of the below was tested against a compiler so there may be some syntax mistakes)

public class Category{
     public int CategoryId { get; private set; }
     public string CategoryName { get; private set; }
     public int ParentId { get; private set; }
     public List<Category> ChildCategories { get; private set; }

     //I would recommend using a parentId of 0 or some other int value that can't occur in your database to describe a category that has no parent.
     public Category(int categoryId, string categoryName, int parentId)
     {
         CategoryId = categoryId;
         CategoryName = categoryName;
         ParentId = parentId;
         ChildCategories = getChildCategories();
     }

     //I didn't have this method setting the ChildCategories directly because I would 
     //recommend refactoring your data access code into a data access class of some sort
     //also depending on the amount of categories you could be generating a lot of database calls.
     //It may make more sense to get the whole table and pass in the resulting dataset, then use recursion to build your nested category structure.
     private List<Category> getChildCategories()
     {
          List<Category> resultingCategories = new List<Category>();
          //Data access logic here to get a dataset assume the variable holding the  
          //data reader is named sdr

          while (sdr.Read()){
              //This assumes categoryId is the first column
              int childCategoryId = Convert.ToInt32(sdr[0]);

              //This assumes categoryName is the second column
              string childCategoryName = sdr[1];

              Category childCategory = new Category(childCategoryId, childCategoryName, CategoryId);
              resultingCategories.Add(childCategory);
          }
          return resultingCategories;
     }
}

Also just a side item even if you control all the data being passed into your data access function please parameterize your query. It will ensure that your app won't fall to the ever so common SQL injection attack.

As I said in comments, I think this is more a sql question.

my previous example had 2 tables. my bad, this looks better: SQL Parent/Child recursive call or union?

Here's a good google search to find more: http://www.google.com/search?q=sql+children+recursive+site%3Astackoverflow.com

Common Table Expressions were made exactly for this purpose: http://msdn.microsoft.com/en-us/library/ms190766.aspx

The accepted answer is fine and brute forcing this in C# is acceptable. However it is only good if your categories are a small list, and WILL continue to be a small list. This is exactly the kind of thing that in 3 years will suffer performance problems as the table grows.

Nested Sets are one well-regarded solution for the SQL end of this equation. It requires a bit more application logic, so you'll want to abstract out the behavior to the extent that you can.

They have some limitations if the elements have frequent inserts or deletes, but that shouldn't apply in your case. The solution when you do have frequent inserts or deletes is usually to create numbering gaps among the children.

http://www.sqlteam.com/article/more-trees-hierarchies-in-sql has references to an early Celko article on the topic, and some contemporary variations on the theme.

you should do your recursion in your code so get you all necessary data with "select * from yourtable" to your memory and then do your recursive method. This will be a lot faster.