如何从数据库中读取动态属性 [英] How to read dynamic properties from database

问题描述

我会尽我所能解释我的场景.我的数据库中有以下表格:

I will try to explain my scenario the best way I can. I have the following tables in my database:

Products{ProductId, CategoryId ...}
Categories{CategoryId ...}
CategoryProperties{CategoryPropertyId, CategoryId, Name ...}
PropertyValues{ProductId, CategoryPropertyId, Value ...}

因此，我们的目标是列出属于某个类别的产品，并且每个类别可以有n"个属性，其值在PropertyValues"表中.我有一个基于categoryId"返回数据的查询，所以我总是可以从数据库中得到不同的结果:

So the goal is to have list of products which belongs to some category and each category can have 'n' number of properties with values in the 'PropertyValues' table. I have a query that returns data based on 'categoryId' so I can always get different results from the database:

对于 CPU 类别:

intel i7 | CPU | Model | Socket | L1 Cash | L2 Cahs | etc.

对于 HDD 类别:

samsung F3 | HDD | Model | Speed | GB | etc.

因此，根据我的查询中的类别，我总是可以获得不同的列号和名称.对于数据库访问，我使用简单的 ADO.NET 调用来返回结果的存储过程.但是因为查询结果本质上是动态的，所以我不知道什么是读取这些数据的好方法.

So based on categories from my query as a result I always can get different column numbers and names. For database access I use simple ADO.NET call to stored procedure that return result. But because query result is dynamic in it's nature I don't know what is a good way to read this data.

我创建了一个 Product 实体，但 我很困惑如何真正实现它:(我认为我可以制作一个 Product 实体并制作 inherit Product 的其他实体，例如 Cpu、硬盘、相机、PcCase、GraphicCard、MobilePhone、Gps等等

但我认为这很愚蠢，因为我可以用域中的200多个实体来结束它.

在这种情况下你会怎么做?
如何阅读以及将这个动态属性放在哪里?

更新 - 一些解决方案

好吧，基于 @millimoose 建议 Dictionary 和 @Tim Schmelter 想法使用 DataTable 对象我来了一些解决办法.
现在...这可以让我读取数据并显示它们.
但是我仍然需要比我更聪明的人提供关于我做得好吗或者我应该更好地处理这件事还是我制作了一些 spageti 代码的建议.所以这就是我所做的:

I made a Product entity but I am confused how to make it really :( I thought that I can make a Product entity and make other entities which inherit Product like Cpu, Hdd, Camera, PcCase, GraphicCard, MobilePhone, Gps etc.

but I think that it's stupid because I can end this with 200+ entities in domain.

What would you do in this situation?
How to read and where to put this dynamic properties?

UPDATE - some solution

All right based on @millimoose suggestion for Dictionary and @Tim Schmelter idea to use DataTable object I came to some solution.
Now... this works I get data read them and I can display them.
But I still need advice from smarter people than me on am I did this good or should I handle this better or I am made some spageti code. So here what I did:

public class Product
    {
        public Product()
        {
            this.DynamicProperties = new List<Dictionary<string, string>>();
        }

        public List<Dictionary<string, string>> DynamicProperties { get; set; }

    }
...
List<Product> products = new List<Product>();
...
using (SqlDataAdapter a = new SqlDataAdapter(cmd))
                {
                    DataTable t = new DataTable();
                    a.Fill(t);

                    Product p = null;

                    foreach (DataRow row in t.Rows)
                    {
                        p = new Product();
                        foreach (DataColumn col in t.Columns)
                        {
                            string property = col.ColumnName.ToString();
                            string propertyValue = row[col.ColumnName].ToString();

                            Dictionary<string, string> dictionary = new Dictionary<string, string>();

                            dictionary.Add(property, propertyValue);

                            p.DynamicProperties.Add(dictionary);
                        }

                        products.Add(p);
                    }
                }

推荐答案

你有一个产品，一堆类别，每个类别都有一堆属性.

You have a product, a bunch of categories, and each category has a bunch of properties.

class Product
{
    public int Id { get; set; }
    public Category Category { get; set; }
    public List<ProductProperty> Properties { get; set; }
}

class Category
{
  public int Id { get; set; }
  public string Name { get; set; }
}

class ProductProperty
{
   public  int Id { get; set; }
   public  string Name { get; set; }
   public string Value { get; set; }
}

您可以将产品属性存储在列表/字典中

you could store the product properties in a list / dictionary

然后您只需将属性添加到产品中

then you just add properties to the product

Properties.Add(new ProductionProperty() { Name="intel i7"});

或者如果它的名称/值

Properties.Add(new ProductionProperty() { Name="Speed", Value="150MB/s"});

这篇关于如何从数据库中读取动态属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！