时间戳

首页 > 解决方案 > 将相关数据转换为列表

c# - 将相关数据转换为列表

问题描述

我目前正在使用 SQL 管理工作室在 windows 窗体 c# 应用程序中工作，我有两个表

+-------------------------------+
|           Category            |
+-------------------------------+
| CategoryId (UNIQUEIDENTIFIER) |
| Name (VARCHAR(255))           |
| CreatedBy(VARCHAR(255))       |
| CreationDate(DATETIME)        |
+-------------------------------+


+-----------------------------+
|           Product           |
+-----------------------------+
| ProductId(UNIQUEIDENTIFIER) |
| CategoryId (FK)             |
| Name(VARCHAR(255))          |
| CreatedBy(VARCHAR(255))     |
| CreationDate(DATETIME)      |
| IsDeleted(BIT)              |
+-----------------------------+

在 c# 中，我从数据表中获取数据：

var data = db.GetTableBySQL("usp_RedMarks_Get");

我想将此数据表转换为列表以获取每个类别中的产品列表，如下所示：

  private List<Category> GetData()
        {
            // Simulate a database...
            Category c1 = new Category("Fruit", new List<string>() { "Banana", "Apple" });
            Category c2 = new Category("Vegetables", new List<string>() { "Avocado", "Tomato" });
            Category c3 = new Category("Programming Languages", new List<string>() { "C#", "Visual Basic" });
            Category c4 = new Category("Stars", new List<string>() { "Venus", "Mars" });

            List<Category> result = new List<Category>();
            result.Add(c1);
            result.Add(c2);
            result.Add(c3);
            result.Add(c4);

            return result;
        }
    }

class Category
    {
        public string Name;
        public List<string> Items;
        public Category(string name,List<string> items)
        {
            this.Name = name;
            this.Items = items;
        }
    }

存储过程：

SELECT C.Name AS 'categoryName', P.Name AS 'productName'
FROM Category AS C
INNER JOIN Product AS P ON C.CategoryId = P.CategoryId
ORDER BY C.Name

我怎样才能做到这一点？问候

标签： c#

解决方案

如果您出于某种原因不想使用实体框架。您可以使用更轻量级且更容易上手的库，例如 Dapper。然后你可以很容易地映射它，如下所示：

var productWithCategories = 
       conn.Query<Product, Category>(@"
            SELECT p.*, c.*
            FROM Product p
            INNER JOIN Category c ON c.Id = p.CategoryId                    
            ", (product, category) => {
                 category.Products = category.Products ?? new List<Product>();
                 category.Products.Add(product); 
                 return category;
             }).AsQueryable();

使用实体框架是这样的：

public class Product
{
   ...
   public List<Category> Categories {get;set;}
}
public class Category
{
   ...
   public int ProductId {get;set;}
   public Product Product {get;set;}
}
public class MyDataContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }
}
using (MyDataContext db = new MyDataContext())
{
    var categories = (from p in db.Products
                  join c in db.Categories
                  on p.CategoryId equals c.Id
                  select c).ToList();
     string productName = categories.First().Product.Name;

}

推荐阅读