如何从C#中的SQL查询结果填充类? [英] How can I populate a class from the results of a SQL query in C#?
问题描述
我有一个像这样的课程:
I've got a class like this:
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
来设置值.
I don't want to have to manually run through a DataSet
or DataTable
to set the values.
我确定有一种使用某种绑定/映射机制填充类的方法,但是我唯一能找到的就是绑定到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.
是否可以将某些属性应用于我的属性/类,以使该类从查询行中自动填充?
Is there some kind of attribute I can apply to my properties / class to have the class automatically populated from a query row?
推荐答案
我已决定提出另一个答案,它实际上是对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; }
}
除Alex所提议的以外,其余部分都使用该属性,只是该属性用于检索列名:
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屋!