如何从C＃中的SQL查询的结果填充类？ [英] How can I populate a class from the results of an SQL query in C#?

问题描述

我有这样的类：

public class Product
{
    public int ProductId { get; private set; }
    public int SupplierId { get; private set; }

    public string Name { get; private set; }
    public decimal Price { get; private set; }
    public int Stock { get; private set; }
    public int PendingStock { get; private set; }
}

我可以从我的数据库中获取这些细节：

I can fetch those details from my database like this:

SELECT product_id, supplier_id, name, price, total_stock, pending_stock 
FROM products
WHERE product_id = ?

我不想手动运行 DataSet 或 DataTable 来设置值。

我确定有一种方法来填充类使用某种类型的绑定/映射机制，但我唯一可以找到的东西是绑定到winforms组件或使用XAML。

I'm sure there's a way to populate the class using some kind of binding / mapping mechanism, but the only stuff I could find was for binding to winforms components or using XAML.

是否有某种属性

推荐答案

我决定提出另一个答案，这实际上扩展了由Alex提供的答案（所以所有信用给他），但它引入属性为列名2属性名映射。

I've decided to propose another answer, which actually extension to the answer provided by Alex (so all credits to him), but it introduces attributes for the sake of column-name-2-property-name mapping.

首先需要保存列名称的自定义属性：

First of all custom attribute to hold column name is needed:

[AttributeUsage(AttributeTargets.Property, Inherited = true)]
[Serializable]
public class MappingAttribute : Attribute
{
    public string ColumnName = null;
}

属性必须应用于类的那些属性从数据库行填充：

The attribute must be applied to those properties of the class, that are to be populated from database row:

public class Product
{
    [Mapping(ColumnName = "product_id")]
    public int ProductId { get; private set; }

    [Mapping(ColumnName = "supplier_id")]
    public int SupplierId { get; private set; }

    [Mapping(ColumnName = "name")]
    public string Name { get; private set; }
    [Mapping(ColumnName = "price")]
    public decimal Price { get; private set; }
    [Mapping(ColumnName = "total_stock")]
    public int Stock { get; private set; }
    [Mapping(ColumnName = "pending_stock")]
    public int PendingStock { get; private set; }
}

除了属性用于检索栏名称：

And rest goes as Alex proposed, except that the attribute is used to retrieve column name:

T MapToClass<T>(SqlDataReader reader) where T : class
{
        T returnedObject = Activator.CreateInstance<T>();
        PropertyInfo[] modelProperties = returnedObject.GetType().GetProperties();
        for (int i = 0; i < modelProperties.Length; i++)
        {
            MappingAttribute[] attributes = modelProperties[i].GetCustomAttributes<MappingAttribute>(true).ToArray();

            if (attributes.Length > 0 && attributes[0].ColumnName != null)
                modelProperties[i].SetValue(returnedObject, Convert.ChangeType(reader[attributes[0].ColumnName], modelProperties[i].PropertyType), null);
        }
        return returnedObject;
}

这篇关于如何从C＃中的SQL查询的结果填充类？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！