Alex Rivera | Logout

How can I populate a class from the results of a SQL query in C#?

Asked 2012-07-27T11:05:43.753
15

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?

c# sql-server database winforms orm
Edit
Report

2 Answers

7

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

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".

WARNING I 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.

answered 2012-07-27T11:30:43.597
2

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

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;
    }
}
answered 2012-07-27T11:11:40.003

Your Answer