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

Question

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 1

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

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

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

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.

Answer 2

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.

Answer 3

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

Answer 4

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.

1. Create some custom attribute class that can hold a column name for each public property of your class.

2. After retrieving record from database instantiate an object of Product class and enumerate properties. For each property that has you custom attribute - use SetValue of PropertyInfo to change value according to column name defined in custom attribute.

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

Answer 5

One possible solution:

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

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

It also works very well on large nested SQL queries.

Answer 6

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

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

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

Answer 7

Using Entity Framework you could use the SqlQuery method to populate a class that is not a table or view: context.Database.SqlQuery(sql, parameterList)

Offcourse you need to use EF. :-)

Answer 8

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