help returning database rows using .net and SQL

Question

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();
        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>";
    }

Uhm, and what is your question? Is there a problem with your code that you want help with? — Albin Sunnanbo
– Albin Sunnanbo, Commented Nov 28, 2010 at 21:48
Oh, boy, you have a whole bunch of problems. Start by creating a SubCategryClass with an CategoryId, SubCategoryId, Name and change the Categories class to CategoryId, Name. If you start there, the rest will follow quite naturally. — Albin Sunnanbo
– Albin Sunnanbo, Commented Nov 28, 2010 at 21:55
How are your categories and subcategories linked, i.e., what's the foreign key in the subcategory table called? — Heinzi
– Heinzi, Commented Nov 28, 2010 at 22:38
@Heinzi, My subCategory table has a column named CategoryID which is linked to CategoryID on the Category Table. — The Muffin Man
– The Muffin Man, Commented Nov 28, 2010 at 22:43

Heinzi · Accepted Answer · 2010-11-29 01:48:42Z

1

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>";

edited Nov 29, 2010 at 1:48

answered Nov 29, 2010 at 0:41

Heinzi

173k61 gold badges386 silver badges554 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

The Muffin Man Over a year ago

Heinzi, this works except that it returns the same category multiple times according to how many sub categories are displayed. E.g. If 5 sub categories are displayed it shows category 1(items 1-5) 5 times(instances). My stored procedure is using an inner join, rather than each table being stored in a seperate datatable by the way.

Heinzi Over a year ago

@nick: Is it possible that your stored procedure Category_Admin_GetAll accidentally returns Category1 five times instead of only one time? In fact, if you already use a stored procedure with an inner join, wouldn't it be easier to just join the category name to the subcategory in the subcategory SP and just use one datatable?

Shiraz Bhaiji · Accepted Answer · 2010-11-28 22:23:05Z

0

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

answered Nov 28, 2010 at 22:23

Shiraz Bhaiji

65.7k37 gold badges149 silver badges260 bronze badges

Comments

RPM1984 · Accepted Answer · 2010-11-28 22:35:57Z

0

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.

answered Nov 28, 2010 at 22:35

RPM1984

73.3k62 gold badges241 silver badges364 bronze badges

Comments

Oscar Cabrero · Accepted Answer · 2010-11-28 23:00:10Z

0

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);

edited Nov 28, 2010 at 23:00

answered Nov 28, 2010 at 22:53

Oscar Cabrero

4,1698 gold badges33 silver badges49 bronze badges

1 Comment

The Muffin Man Over a year ago

Would it be easier to do this with LINQ?

Jon Black · Accepted Answer · 2010-11-29 02:42:24Z

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 :)

Collectives™ on Stack Overflow

help returning database rows using .net and SQL

5 Answers 5

2 Comments

Comments

Comments

1 Comment

C# code

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

1 Comment

C# code

Comments

Your Answer

Sign up or log in

Post as a guest

Related