Polynomial Polynomial - 3 months ago 14
C# Question

How can I populate a class from the results of an 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 = ?


I don't want to have to manually run through a
DataSet
or
DataTable
to set the values.

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?

Answer

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