I have two tables like this:
Category:
Id Name
------------------
1 Cat1
2 Cat2
Feature:
Id Name CategoryId
--------------------------------
1 F1 1
2 F2 1
3 F3 2
4 F4 2
5 F5 2
In my .Net classes, i have two POCO classes like this:
public class Category
{
public int Id {get;set;}
public int Name {get;set;}
public IList<Feature> Features {get;set;}
}
public class Feature
{
public int Id {get;set;}
public int CategoryId {get;set;}
public int Name {get;set;}
}
I am using a stored proc that returns me a result set by joining these 2 tables.
This is how my Stored Proc returns the result set.
SELECT
c.CategoryId, c.Name Category, f.FeatureId, f.Name Feature
FROM Category c
INNER JOIN
Feature f
ON c.CategoryId = f.CategoryId
ORDER BY c.Name
--Resultset produced by the above query
CategoryId CategoryName FeatureId FeatureName
---------------------------------------------------
1 Cat1 1 F1
1 Cat1 2 F2
2 Cat2 3 F3
2 Cat2 4 F4
2 Cat2 5 F5
Now if i want to build the list of categories in my .Net code, i have to loop thru the result set and add features unless the category changes.
This is how my .Net code looks like that builds Categories and Features.
List<Category> categories = new List<Category>();
Int32 lastCategoryId = 0;
Category c = new Category();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
//Check if the categoryid is same as previous one.
//If Not, add new category.
//If Yes, dont add the category.
if (lastCategoryId != Convert.ToInt32(reader["CategoryId"]))
{
c = new Category
{
Id = Convert.ToInt32(reader["CategoryId"]),
Name = reader["CategoryName"].ToString()
};
c.Features = new List<Feature>();
categories.Add(c);
}
lastCategoryId = Convert.ToInt32(reader["CategoryId"]);
//Add Feature
c.Features.Add(new Feature
{
Name = reader["FeatureName"].ToString(),
Id = Convert.ToInt32(reader["FeatureId"])
});
}
return categories;
}
I was wondering if there is a better way to do build the list of Categories?
