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.