帮助使用 .net 和 SQL 返回数据库行

Question

我正在尝试以这种格式返回数据库行 - 类别和子类别：

类别 1
子类别
子类别
子类别

类别 2
子类别
子类别
SubCategory

换句话说，返回类别及其下面属于该类别的子类别。

我将类别和子类别存储在列表中，如果这是要走的路，我猜我的问题是我的 for 或 foreach 语法

这是完整的代码：

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

Answer 1

首先，循环遍历所有类别。然后，在该循环中，循环遍历属于该类别的所有子类别。这里，我直接将数据输出为HTML；如果您愿意，也可以填充一个列表。

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

当然，如果您只需将 CategoryName 连接到您的 subTable 存储过程，一切都会变得更加容易（并且您不再需要 table ）。一定要ORDER BY CategoryName，这样具有相同类别的子类别就会“分组在一起”，然后你可以使用这样的代码：

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

Answer 2

直接使用 ado.net 比其他方法更复杂且效率更低。

MS 引入了用于数据访问的实体框架，您的数据访问代码可以简化为如下所示：

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

请参阅：http://msdn.microsoft.com/en-us/data/ef.aspx

Answer 3

你让你的生活变得更加困难。

我建议两件事：

1）按照@Shiraz所说的那样 - 使用实体框架

2）如果不可能，为什么不使用单个存储过程，它基本上执行UNION或JOIN（不完全是）确定你的模式/逻辑）。

您用来合并/缝合类别的代码是数据库问题，您不应该在代码中执行此操作。

Answer 4

1.- 创建一个存储过程，如果（仅当所有类别包括所有子类别）按类别名称对它们进行排序，则使两个表交叉连接
2.- 更改您的类别对象以拥有类别子集，以便您可以将子类别添加到您的类别对象中

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

Answer 5

用于调用存储过程的 C# 代码非常简单，该存储过程返回排序的数据列表、填充数据表并循环它。

C# 代码

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

使用 DataTable 的明显好处是：轻量级、可序列化、断开连接 - 所有这些都允许应用程序高性能和可扩展。但是，如果您需要做的话，没有什么可以阻止您循环数据表并填充您自己的集合！

问题最难的部分是编写一个存储过程，该存储过程按您需要的顺序返回数据。就我个人而言，我会使用邻接列表方法结合存储过程和公用表表达式 (CTE) 来实现类别层次结构。这种方法的优点是类别层次结构可以是多级的，编码简单，并且比嵌套集合等其他方法更容易管理和维护。

有关 CTE 的详细信息 - 请参阅 http://msdn.microsoft.com/en-us /library/ms190766.aspx

不幸的是，我现在手头只有一个 MySQL 实现，这有点啰嗦，因为 MySQL 缺乏使此任务在 RDBMS 中变得微不足道的功能，例如 SQL Server (CTE) 和 Oracle（连接经过）。但是，网络上有大量资源可以向您展示如何使用 CTE 和存储过程来完成您需要的操作。如果您对我如何在 MySQL 中实现这一点感兴趣，我在本答案的底部提供了完整源代码的链接。

无论您使用哪种 RDBMS，存储过程的结果都应该如下所示：

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

我的答案的完整源代码可以在这里找到：http ://pastie.org/1331218

希望这能引起人们的兴趣:)