如何从 C# 中的 SQL 查询结果填充一个类？

Question

提问by Polynomial

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 = ?

I don't want to have to manually run through a DataSetor DataTableto set the values.

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

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.

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

Is there some kind of attribute I can apply to my properties / class to have the class automatically populated from a query row?

是否有某种属性可以应用于我的属性/类以使该类从查询行自动填充？

Answer 1

采纳答案by Kuba Wyrostek

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.

我决定提出另一个答案，它实际上是对 Alex 提供的答案的扩展（因此所有功劳都归功于他），但它为了 column-name-2-property-name 映射引入了属性。

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

Answer 2

回答by JohnnBlade

Then you should be using Entity Framework or Linq To SQL and if you dont want to use that, then you need to map/fill it yr self

那么你应该使用 Entity Framework 或 Linq To SQL，如果你不想使用它，那么你需要自己映射/填充它

more info on Entity Framework http://msdn.microsoft.com/en-us/data/ef.aspx

有关实体框架的更多信息 http://msdn.microsoft.com/en-us/data/ef.aspx

more info on Linq to SQL http://msdn.microsoft.com/en-us/library/bb386976.aspx

有关 Linq to SQL 的更多信息 http://msdn.microsoft.com/en-us/library/bb386976.aspx

Answer 3

回答by Tom Gullen

I would use Linq to SQL and do it as follows:

我会使用 Linq to SQL 并按如下方式执行：

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

    public Product(int id)
    {
        using(var db = new MainContext())
        {
            var q = (from c in product where c.ProductID = id select c).SingleOrDefault();
            if(q!=null)
                LoadByRec(q);           
        }
    }
    public Product(product rec)
    {
        LoadByRec(q);
    }
    public void LoadByRec(product rec)
    {
        ProductId = rec.product_id;
        SupplierID = rec.supplier_id;
        Name = rec.name;
        Price = rec.price;
        Stock = rec.total_stock;
        PendingStock = rec.pending_stock;
    }
}

Answer 4

回答by madd0

You need to either map the properties yourself or use an ORM (Object relational mapper).

您需要自己映射属性或使用 ORM（对象关系映射器）。

Microsoft provides Entity Framework, but Dapperrequires less overhead and might be a viable option depending on your requirements.

Microsoft 提供Entity Framework，但Dapper需要较少的开销，并且可能是一个可行的选择，具体取决于您的要求。

In your case, the Dapper code would look something like:

在您的情况下，Dapper 代码如下所示：

var query = @"SELECT product_id, supplier_id, name, price, total_stock, pending_stock 
FROM products
WHERE product_id = @id";

var product = connection.Query<Product>(query, new {?id = 23 });

For the sake of completeness, it's important to point out that I'm talking about Dapper here because the question concerns mapping SQL results to objects. EF and Linq to SQL will do this too, but they will also do additional stuff, like translating Linq queries into SQL statements, which might also be useful.

为了完整起见，重要的是要指出我在这里谈论的是 Dapper，因为问题涉及将 SQL 结果映射到对象。EF 和 Linq to SQL 也将执行此操作，但它们还会执行其他操作，例如将 Linq 查询转换为 SQL 语句，这也可能很有用。

Answer 5

回答by Kuba Wyrostek

There is no such functionality by default in raw .NET Framework. You could use Entity Framework, but if it's not a good solution for you, then an alternative would be reflection mechanism.

默认情况下，原始 .NET Framework 中没有此类功能。您可以使用实体框架，但如果它对您来说不是一个好的解决方案，那么另一种选择是反射机制。

Create some custom attribute class that can hold a column name for each public property of your class.
After retrieving record from database instantiate an object of Productclass and enumerate properties. For each property that has you custom attribute - use SetValueof PropertyInfoto change value according to column name defined in custom attribute.

创建一些自定义属性类，可以为类的每个公共属性保存一个列名。
从数据库中检索记录后，实例化一个Product类对象并枚举属性。对于有你的自定义属性的每个属性-使用SetValue的PropertyInfo以变化值根据自定义属性定义的列名。

Take the following into consideration:

请考虑以下因素：

the solution is quite and overhead to simple assignments; it only makes sense if you have many tables and many classes like Product- and wish to write one code to automatically initialize all of them
reflection is an overhead itself - so some caching would be required in the long run

解决方案对于简单的分配来说是相当和开销的；只有当你有很多表和很多类时才有意义Product- 并且希望编写一个代码来自动初始化所有这些
反射本身就是一种开销 - 因此从长远来看需要一些缓存

Answer 6

回答by Alex

If you don't want to leverage an ORM framework (Entity Framework etc.) you can do it by hand:

如果您不想利用 ORM 框架（实体框架等），您可以手动完成：

T MapToClass<T>(SqlDataReader reader) where T : class
{
        T returnedObject = Activator.CreateInstance<T>();
        List<PropertyInfo> modelProperties = returnedObject.GetType().GetProperties().OrderBy(p => p.MetadataToken).ToList();
        for (int i = 0; i < modelProperties.Count; i++)
            modelProperties[i].SetValue(returnedObject, Convert.ChangeType(reader.GetValue(i), modelProperties[i].PropertyType), null);
        return returnedObject;
}

you use it like this:

你像这样使用它：

Product P = new Product(); // as per your example
using(SqlDataReader reader = ...)
{
while(reader.Read()) { P = MapToClass<Product(reader); /* then you use P */ }
}

Only thing to take care of, is the order of the fields in the query (it MUST match the order of the properties as they are defined in your class).

唯一需要注意的是查询中字段的顺序（它必须与类中定义的属性的顺序相匹配）。

All you need to do is build the class, write a query, then it will take care of the "mapping".

您需要做的就是构建类，编写查询，然后它会处理“映射”。

WARNINGI use this method a lot and never had any issue, but it doesn't work properly for partial classes. If you come to partial models you're much better off using an ORM framework anyway.

警告我经常使用这种方法并且从来没有遇到过任何问题，但是对于部分类它不能正常工作。如果您使用部分模型，无论如何最好使用 ORM 框架。

Answer 7

回答by Miro Malek

One possible solution:

一种可能的解决方案：

In SQL query you can use "PATH Mode with FOR XML "clause.

在 SQL 查询中，您可以使用“PATH Mode with FOR XML”子句。

The result of the query will be an XML, which you can deserialize directly to C# objects.

查询的结果将是一个 XML，您可以将其直接反序列化为 C# 对象。

It also works very well on large nested SQL queries.

它也适用于大型嵌套 SQL 查询。

Answer 8

回答by Gautam

select 'public ' + case DATA_TYPE  
when 'varchar' then 'string'
when 'nvarchar' then 'string'
when 'DateTime' then 'DateTime'
when 'bigint' then 'long' 
else DATA_TYPE end +' '+ COLUMN_NAME + ' {get; set;}' 
from INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME ='YOUR TABLE NAME'  ORDER BY ORDINAL_POSITION

如何从 C# 中的 SQL 查询结果填充一个类？

提问by Polynomial

采纳答案by Kuba Wyrostek

回答by JohnnBlade

回答by Tom Gullen

回答by madd0

回答by Kuba Wyrostek

回答by Alex

回答by Miro Malek

回答by Gautam

相关推荐

最近更新

标签

如何从 C# 中的 SQL 查询结果填充一个类？

提问by Polynomial

采纳答案by Kuba Wyrostek

回答by JohnnBlade

回答by Tom Gullen

回答by madd0

回答by Kuba Wyrostek

回答by Alex

回答by Miro Malek

回答by Gautam

相关推荐

C# 使用 Try/Catch 检查文件是否存在

C# 如何从具有日期的对象列表中获取最小日期？

C# 如何设置 web.config 文件以显示完整的错误消息

C# 从数据表中删除列

相关推荐

最近更新

标签