CODEWITHSUNDEEP
c#sql-serverdatabasewinformsorm
Polynomial
Polynomial

Reputation: 28316

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

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?

Upvotes: 15

Views: 37925

Answers (9)

Mark
Mark

Reputation: 439

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. :-)

Upvotes: 0

Kuba Wyrostek
Kuba Wyrostek

Reputation: 6221

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

Upvotes: 18

Gautam
Gautam

Reputation: 45

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

Upvotes: -1

Miro Malek
Miro Malek

Reputation: 269

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.

Upvotes: 1

Alex
Alex

Reputation: 23300

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.

Upvotes: 7

madd0
madd0

Reputation: 9323

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.

Upvotes: 15

Kuba Wyrostek
Kuba Wyrostek

Reputation: 6221

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

Upvotes: 1

Tom Gullen
Tom Gullen

Reputation: 61735

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

Upvotes: 2

JohnnBlade
JohnnBlade

Reputation: 4327

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

Upvotes: 0

Related Questions