Olivier's implementation is good. It uses generics and interfaces giving each entity it's own implementation of FillFromDataReader().
You can take it farther. By using convention all the data hydration code can be centralized and abstracted away.
I am going to assume that your class property names and your columns names are the same. If they are not then the following code can be extended to add aliases attributes to the property names. Sometimes a property is calculated from other values in the object, this property can not be hydrated. An Ignore attribute can be created and implemented in the below class.
public class DataAccess
{
/// <summary>
/// Hydrates the collection of the type passes in.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">The SQL.</param>
/// <param name="connection">The connection.</param>
/// <returns>List{``0}.</returns>
public List<T> List<T>(string sql, string connection) where T: new()
{
List<T> items = new List<T>();
using (SqlCommand command = new SqlCommand(sql, new SqlConnection(connection)))
{
string[] columns = GetColumnsNames<T>();
var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
T item = new T();
foreach (var column in columns)
{
object val = reader.GetValue(reader.GetOrdinal(column));
SetValue(item, val, column);
}
items.Add(item);
}
command.Connection.Close();
}
return items;
}
/// <summary>
/// Sets the value.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="item">The item.</param>
/// <param name="value">The value.</param>
/// <param name="column">The column.</param>
private void SetValue<T>(T item, object value, string column)
{
var property = item.GetType().GetProperty(column);
property.SetValue(item, value, null);
}
/// <summary>
/// Gets the columns names.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns>System.String[][].</returns>
private string[] GetColumnsNames<T>() where T : new()
{
T item = new T();
return (from i in item.GetType().GetProperties()
select i.Name).ToArray();
}
}
There are a couple caveats in the above code. DBNulls and Nullable types are special cases and will require custom code to deal with them. I usually convert DBNull's to null. I have never ran into a case where I needed to distinguish the different between the two. For Nullalbe types, simply detect the Nullable type and handle the code accordingly.
An ORM would remove much of the headache of dealing with data access. The downside, is many times you are coupled to the DTO's and the database schema. Of course this issue can be contained by using abstractions. Many companies still use strictly stored procedures, most ORMs fall down when they are forced to consume stored procedures. They are just not designed to work with stored procedures.
I wrote a data access framework called "Hypersonic." It's on GitHub, it's specifically designed to work with stored procedures. The above code is a light implementation of the it.