jeudi 15 janvier 2015

Mapping SqlDataReader result to object

I'm trying to create a generic method that will convert results from a SqlDataReader into a POCO object based on the property names. This is what I got so far:


POCO object:



public class Order
{
[DbField]
public int OrderId { get; set; }
[DbField]
public string OrderName { get; set; }
[DbField]
public DateTime OrderDate { get; set; }

public int NonDbProperty { get; set; }
}


attribute definition:



public class DbFieldAttribute : Attribute { }


method to retrieve all property with the DbField attribute of an object:



public static IEnumerable<string> GetPropertiesWithDbAttribute<T>()
{
var allProps = typeof(T).GetProperties();
var props = allProps.Where(p => Attribute.IsDefined(p, typeof(DbFieldAttribute))).Select(n => n.Name);
return props.ToList();
}


and the actual SELECT operation:



public object GetFirstOrder()
{
try
{
Order temp = new Order();
using (SqlConnection con = new SqlConnection(GetConnection()))
using (var cmd = con.CreateCommand())
{
con.Open();
cmd.CommandText = "SELECT * FROM [Order] WHERE OrderId = 1";

using (SqlDataReader sdr = cmd.ExecuteReader())
{
if (sdr.HasRows)
{
var props = Utils.GetPropertiesWithDbAttribute<Order>();
while (sdr.Read())
{

foreach (string item in props)
{
temp.GetType().GetProperty(item).SetValue(temp, sdr[item]);
}
}
}
}
}
return temp;
}
catch (Exception ex)
{
return null;
}
}


this works in the tests that I've made so far, however I wonder if the automatic conversion between the object retrieved in sdr[item] is enough or should I use an explicit conversion based on the type of the property, for example:



//DateTime? property:
temp.GetType().GetProperty(item).SetValue(temp, sdr[item] == DBNull.Value? null: (DateTime?)sdr[item]);





Aucun commentaire:

Enregistrer un commentaire