When reading data from SqlDataReader you have to know your select statement’s columns order to read and map data from database to your model. Often it looks somehow like this:
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
return new AnimalModel
{
AnimalId = reader.GetInt32(0),//or better with enum like that: AnimalId = reader.GetInt32((int)AnimalSelect.AnimalId)
BirthDate = reader.GetDateTime(1),
Name = reader.GetString(2)
//etc...
};
else return null;
}
It’s a good practice to create enum representing select’s columns and using it instead of int indexes.
But wouldn’t it be better if you could just do it like that:
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
return reader.ReadObject();
else return null;
}
I got tired of this old way, so one weekend I’ve tried to implement new solution. I got inspired and encouraged by some MVC 3.0 tutorials that I watched recently, in witch they used attributes in wide variety of situations.
The main concept is that instead of representing structure as enum we can do that directly in model’s class using attributes on it’s properties and class itself, like that:
namespace SQLStructureAttribute.Models
{
[SelectStructureDescribed]
public class AnimalModel
{
[SelectStructure(0)]
public int AnimalId {get;set;}
[SelectStructure(1)]
public string Name { get; set; }
[SelectStructure(2)]
public int NumberOfLegs { get; set; }
[SelectStructure(3)]
public string OwnerName {get;set;}
[SelectStructure(4)]
public int? OwnerAddressId { get; set; }
}
}
It is very common that data represented by model is red in different order within application depending on particular case. To identify order that you would like to use just specify variant name while applying attributes and while reading from reader.
namespace SQLStructureAttribute.Models
{
[SelectStructureDescribed]
[SelectStructureDescribed("simpleReversed")]
public class AnimalModel
{
[SelectStructure(0)]
[SelectStructure(5, "simpleReversed")]
public int AnimalId {get;set;}
[SelectStructure(1)]
[SelectStructure(4, "simpleReversed")]
public string Name { get; set; }
[SelectStructure(2)]
[SelectStructure(3, "simpleReversed")]
public int NumberOfLegs { get; set; }
[SelectStructure(3)]
[SelectStructure(2, "simpleReversed")]
public string OwnerName {get;set;}
[SelectStructure(4)]
[SelectStructure(1, "simpleReversed")]
public int? OwnerAddressId { get; set; }
[SelectStructure(5, true)]
[SelectStructure(0, "simpleReversed")]
public DateTime BirthDate { get; set; }
}
}
And then reading your model looks like this:
reader.ReadObject("simpleReversed");
Of course it’s not obligatory that all properties of given model are present in result set, then you can skip attribute on such properties.
Some attributes can have complex types, i.e. be instances of classes, they can also be read from reader if result set has been constructed accordingly, for example:
namespace SQLStructureAttribute.Models
{
[SelectStructureDescribed("withOneAddressWithoutCity")]
public class AnimalModel
{
[SelectStructure(0, "withOneAddressWithoutCity")]
public int AnimalId {get;set;}
[SelectStructure(1, "withOneAddressWithoutCity")]
public string Name { get; set; }
[SelectStructure(2, "withOneAddressWithoutCity")]
public int NumberOfLegs { get; set; }
[SelectStructure(3, "withOneAddressWithoutCity")]
public string OwnerName {get;set;}
[SelectStructure(4, "withOneAddressWithoutCity")]
public int? OwnerAddressId { get; set; }
[SelectStructure(5, "withOneAddressWithoutCity")]
public DateTime BirthDate { get; set; }
[SelectStructure(6, "withOneAddressWithoutCity")]
public AddressModel OwnerAddress { get; set; }
}
}
namespace SQLStructureAttribute.Models
{
[SelectStructureDescribed("withOneAddressWithoutCity")]
public class AddressModel
{
[SelectStructure(0, "withOneAddressWithoutCity")]
public int AddressModelId { get; set; }
[SelectStructure(1, "withOneAddressWithoutCity")]
public int? CityId { get; set; }
[SelectStructure(2, "withOneAddressWithoutCity")]
public string StreetName { get; set; }
[SelectStructure(3, "withOneAddressWithoutCity")]
public int? FlatNumber { get; set; }
[SelectStructure(4, "withOneAddressWithoutCity")]
public string HouseNumber { get; set; }
[SelectStructure(5, "withOneAddressWithoutCity")]
public string PostalCode { get; set; }
public CityModel City { get; set; }
}
}
This solution will work recursively reaching deepest property decorated with attribute (it’s mapping objects tree to list of columns going to the deepest leaf, respecting order given in attributes).
Assumptions, limitations and exceptions
Ground assumption/limitation is that all classes used as models must have parameterless constructors.
Furthermore models containing properties of the same types as itself(or descending type) will cause runtime error. Otherwise it will lead to infinite recursion while mapping objects tree to columns list. So for example this won’t work:
namespace SQLStructureAttribute.Models
{
[SelectStructureDescribed]
public class AnimalModel
{
[SelectStructure(0)]
public int AnimalId {get;set;}
[SelectStructure(1)]
public string Name { get; set; }
[SelectStructure(2)]
public int NumberOfLegs { get; set; }
[SelectStructure(3)]//this will cause InvalidOperationException exceptions
public AnimalModel BoxMate {get;set;}
}
}
This rule goes even deeper. In code example one before the last if AddressModel would have property of type AnimalModel decorated with SelectStructure attribute it would led to the same situation.
Other exception that can be thrown is InconsistentAttributesException. It is caused by inconsistent use of attributes, for example lets say that class A defines property of type B, where B is type of some class, witch is not decorated with attribute SelectStructure(with according variant). So this wont work:
namespace SQLStructureAttribute.Models
{
[SelectStructureDescribed]
public class A
{
[SelectStructure(0)]
public B b {get;set;}
}
//won't work, missing [SelectStructureDescribed]
public class B
{
[SelectStructure(0)]
public int b {get;set;}
}
}
Disclaimer
Main disadvantage of this solution is that it mixes-up models classes with information how they are retrieved from data source, and I’m aware of this disadvantage. But in the other hand it is non-obtrusive, easily integrated, and considering amount of saved code lines it can be a fair trade.
There is some more…
I didn’t describe all of options. There are custom null-data handling methods and initial translation option(start reading not from first result’s column) to allow re-usage of SQL statements and stored procedures. Feel encouraged to try it on your own.
Download source here: ReadTypeExtender
Please comment what you think about it, comments and critique are very welcome.