help returning database rows using .net and SQL

I'm trying to return database rows - category and sub category in this format:

Category 1

SubCategory

SubCategory

SubCategory

Category 2

SubCategory

SubCategory

SubCategory

In other words return the category with the sub categories that belong to it underneath.

I am storing the category and sub category in a List, if this is the way to go, I'm guessing my problem is my for or foreach syntax

Here is the full code:

 public class Categories
    {
        public string Name { get; set; }
        public string SubName { get; set; }
    }
    public void Category()
    {
        DataTable table = new DataTable();
        DataTable subTable = new DataTable();
     开发者_JAVA技巧   List<Categories> category = new List<Categories>();
        using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["localConnectionString"].ConnectionString))
        {
            using (SqlCommand comm = new SqlCommand())
            {
                comm.Connection = conn;
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = "Category_Admin_GetAll";
                conn.Open();
                SqlDataReader reader = comm.ExecuteReader();
                table.Load(reader);
                reader.Close();
                conn.Close();
            }
            using(SqlCommand comm = new SqlCommand())
            {
                comm.Connection=conn;
                comm.CommandType= CommandType.StoredProcedure;
                comm.CommandText = "SubCategory_Admin_GetAll";
                conn.Open();
                SqlDataReader reader = comm.ExecuteReader();
                subTable.Load(reader);
                reader.Close();
                conn.Close();
            }
        }
        foreach (DataRow dRow in table.Rows)
        {
            category.Add(new Categories { Name = dRow["Name"].ToString() });
        }
        foreach (DataRow dRow in subTable.Rows)
        {
            category.Add(new Categories { SubName = dRow["SubCategoryName"].ToString() });
        }
        int t = category.Count;
        resultSpan.InnerHtml += "<table class='table_category'>";
        resultSpan.InnerHtml += "<tr><td>ACTIVE</td><td>NAME</td></tr>";
        resultSpan.InnerHtml +="<tr><td></td><td>"+ category[0].Name+"</td></tr>";
            for (int i = 0; i < t; i++)
            {
                resultSpan.InnerHtml += "<tr><td></td><td>" + category[i].SubName + "</td></tr>";
            }
        resultSpan.InnerHtml += "</table>";
    }

First, loop through all categories. Then, within that loop, loop through all subcategories beloging to that category. Here, I output the data directly into HTML; you can also populate a list instead, if you want to do that.

resultSpan.InnerHtml += "<table class='table_category'>";
resultSpan.InnerHtml += "<tr><td>ACTIVE</td><td>NAME</td></tr>";

foreach (DataRow dRow in table.Rows)
{
    // category
    resultSpan.InnerHtml +="<tr><td></td><td>"+ dRow.Field<string>("Name") +"</td></tr>";

    var id = dRow.Field<int>("CategoryID");
    var subcategories = from row in subTable.AsEnumerable()
                        where row.Field<int>("CategoryID") = id
                        select row.Field<string>("SubCategoryName");

    foreach (string subcategory in subcategories) {
         // subcategory
         resultSpan.InnerHtml += "<tr><td></td><td>" + subcategory + "</td></tr>";
    }
}

resultSpan.InnerHtml += "</table>";

Of course, if you can just join the CategoryName to your subTable stored procedure, everything gets much easier (and you don't need table anymore). Be sure to ORDER BY CategoryName, so that subcategories with the same category are "grouped together", then you can use code like this:

resultSpan.InnerHtml += "<table class='table_category'>";
resultSpan.InnerHtml += "<tr><td>ACTIVE</td><td>NAME</td></tr>";

string lastCategory = null;

foreach (DataRow dRow in subTable.Rows)
{
    // category, if new one
    var category = dRow.Field<string>("CategoryName");
    if (category != lastCategory) {
        lastCategory = category;
        resultSpan.InnerHtml +="<tr><td></td><td>"+ category +"</td></tr>";
    }

    // subcategory
    resultSpan.InnerHtml += "<tr><td></td><td>" + dRow.Field<string>("SubCategoryName") + "</td></tr>";
}

resultSpan.InnerHtml += "</table>";

Using ado.net directly is more complicated and less productive than other methods.

MS has introduced Entity Framework for data access, your data access code could be reduced to something like this:

var CategoryQuery = from d in context.Category.Include("SubCategory")
                  select d;

see: http://msdn.microsoft.com/en-us/data/ef.aspx

Your making your life more difficult than it has to be.

I'd recommend two things:

1) Do what @Shiraz says - use Entity Framework

2) If that's not possible, why not use a single stored procedure, that basically performs a UNION or JOIN (not entirely sure about your schema/logic).

The code your doing to marry up/stitch the categories is a database concern, you shouldn't be doing it in the code.

1.- make a single store procedure that brings both tables cross joined if(only if all categories include all subcategories) order them by category name 2.- change your category object to have a subset of categories so you can add the subcategories into your category object

IEnumerable<DataRow> rows = dt.Rows.AsEnumerable();

var categories = rows.Select(dr=>dr["fieldname"].ToString()).Distinct().Select(name=> new Category(){Name=name});
var subcategories rows.Select(dr=> new Category(){ SubName =dr["subname"]});
categories.ForEach(c=>c.SubcCategories = subcategories); 

The C# code for calling a stored procedure which returns a sorted list of data, populates a datatable and loops it, is simple in the extreme.

C# code

const string DB_CONN_STR = "Server=127.0.0.1;Uid=foo_dbo;Pwd=pass;Database=foo_db;";

MySqlConnection cn = new MySqlConnection(DB_CONN_STR);

MySqlDataAdapter adr = new MySqlDataAdapter("category_hierarchy", cn);

adr.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable dt = new DataTable();
adr.Fill(dt); //opens and closes the DB connection automatically !!

foreach(DataRow dr in dt.Rows){
    Console.WriteLine(string.Format("{0} {1}", 
        dr.Field<uint>("cat_id").ToString(), dr.Field<string>("cat_name").ToString()));
}

cn.Dispose();

The obvious benefits of using a DataTable are that it is: lightweight, serialisable, disconnected - all things that allow applications to be performant and to scale. However, there's nothing stopping you looping the datatable and populating your own collection if that's what you need to do !

The hardest part of problem is coding a single stored procedure that returns the data in the order that you require it. Personally I would implement a category hierarchy using the adjacency list method in conjunction with a stored procedure and common table expressions (CTE). The advantages of this approach are that the category hierarchy can be multi-level, it's simple to code and it's much easier to manage and maintain than other methods such as nested sets.

More info on CTE - see http://msdn.microsoft.com/en-us/library/ms190766.aspx

Unfortunately I only have a MySQL implementation to hand right now which is a little long winded as MySQL lacks the features that makes this task trivial in RDBMS such as SQL Server (CTE) and Oracle (connect by). However, there are plenty of resources on the web that will show you how to use CTE and stored procedures to do what you need. If you're interested in how I've implemented this in MySQL I've included a link to the full source at the bottom of this answer.

Whatever RDBMS you use the results of the stored procedure should be something like as follows:

call category_hierarchy();

cat_id  cat_name    parent_cat_id   parent_cat_name depth
======  ========    =============   =============== =====
2        Cat 1          1            Categories        1
3        Cat 1-1        2            Cat 1             2
4        Cat 1-2        2            Cat 1             2
5        Cat 1-2-1      4            Cat 1-2           3
7        Cat 2          1            Categories        1
8        Cat 2-1        7            Cat 2             2
9        Cat 2-2        7            Cat 2             2
10      Cat 2-2-1      9            Cat 2-2           3

Full source code for my answer can be found here : http://pastie.org/1331218

Hope this proves of interest :)