Convert rows from a data reader into typed results

0 votes
asked Jul 29, 2009 by anthony

I'm using a third party library which returns a data reader. I would like a simple way and as generic as possible to convert it into a List of objects.
For example, say I have a class 'Employee' with 2 properties EmployeeId and Name, I would like the data reader (which contains a list of employees) to be converted into List< Employee>.
I guess I have no choice but to iterate though the rows of the data reader and for each of them convert them into an Employee object that I will add to the List. Any better solution? I'm using C# 3.5 and ideally I would like it to be as generic as possible so that it works with any classes (the field names in the DataReader match the property names of the various objects).

6 Answers

0 votes
answered Jul 29, 2009 by mehrdad-afshari

You could build an extension method like:

public static List<T> ReadList<T>(this IDataReader reader, 
                                  Func<IDataRecord, T> generator) {
     var list = new List<T>();
     while (reader.Read())
         list.Add(generator(reader));
     return list;
}

and use it like:

var employeeList = reader.ReadList(x => new Employee {
                                               Name = x.GetString(0),
                                               Age = x.GetInt32(1)
                                        });

Joel's suggestion is a good one. You can choose to return IEnumerable<T>. It's easy to transform the above code:

public static IEnumerable<T> GetEnumerator<T>(this IDataReader reader, 
                                              Func<IDataRecord, T> generator) {
     while (reader.Read())
         yield return generator(reader);
}

If you want to automatically map the columns to properties, the code idea is the same. You can just replace the generator function in the above code with a function that interrogates typeof(T) and sets the properties on the object using reflection by reading the matched column. However, I personally prefer defining a factory method (like the one mentioned in Joel's answer) and passing a delegate of it into this function:

 var list = dataReader.GetEnumerator(Employee.Create).ToList();
0 votes
answered Jul 29, 2009 by joel-coehoorn

Do you really need a list, or would IEnumerable be good enough?

I know you want it to be generic, but a much more common pattern is to have a static Factory method on the target object type that accepts a datarow (or IDataRecord). That would look something like this:

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }

    public static Employee Create(IDataRecord record)
    {
        return new Employee
        {
           Id = record["id"],
           Name = record["name"]
        };
    }
}

.

public IEnumerable<Employee> GetEmployees()
{
    using (var reader = YourLibraryFunction())
    {
       while (reader.Read())
       {
           yield return Employee.Create(reader);
       }
    }
}

Then if you really need a list rather than an IEnumerable you can call .ToList() on the results. I suppose you could also use generics + a delegate to make the code for this pattern more re-usable as well.

Update: I saw this again today and felt like writing the generic code:

public IEnumerable<T> GetData<T>(IDataReader reader, Func<IDataRecord, T> BuildObject)
{
    try
    {
        while (reader.Read())
        {
            yield return BuildObject(reader);
        }
    }
    finally
    {
         reader.Dispose();
    }
}

//call it like this:
var result = GetData(YourLibraryFunction(), Employee.Create);
0 votes
answered Jul 29, 2009 by dan-diplo

Whilst I wouldn't recommend this for production code, but you can do this automatically using reflection and generics:

public static class DataRecordHelper
{
    public static void CreateRecord<T>(IDataRecord record, T myClass)
    {
        PropertyInfo[] propertyInfos = typeof(T).GetProperties();

        for (int i = 0; i < record.FieldCount; i++)
        {
            foreach (PropertyInfo propertyInfo in propertyInfos)
            {
                if (propertyInfo.Name == record.GetName(i))
                {
                    propertyInfo.SetValue(myClass, Convert.ChangeType(record.GetValue(i), record.GetFieldType(i)), null);
                    break;
                }
            }
        }
    }
}

public class Employee
{
    public int Id { get; set; }
    public string LastName { get; set; }
    public DateTime? BirthDate { get; set; }

    public static IDataReader GetEmployeesReader()
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

        conn.Open();
        using (SqlCommand cmd = new SqlCommand("SELECT EmployeeID As Id, LastName, BirthDate FROM Employees"))
        {
            cmd.Connection = conn;
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
    }

    public static IEnumerable GetEmployees()
    {
        IDataReader rdr = GetEmployeesReader();
        while (rdr.Read())
        {
            Employee emp = new Employee();
            DataRecordHelper.CreateRecord<Employee>(rdr, emp);

            yield return emp;
        }
    }
}

You can then use CreateRecord<T>() to instantiate any class from the fields in a data reader.

<asp:GridView ID="GvEmps" runat="server" AutoGenerateColumns="true"></asp:GridView>

GvEmps.DataSource = Employee.GetEmployees();
GvEmps.DataBind();
0 votes
answered Jul 13, 2016 by airn5475

We have implemented the following solution and feel it works pretty well. It's pretty simple and requires a bit more wiring up then what a mapper would do. However, sometimes it is nice to have the manual control and honestly, you wire up once and you're done.

In a nutshell: Our domain models implement an interface that has a method that takes in an IDataReader and populates the model properties from it. We then use Generics and Reflection to create an instance of the model and call the Parse method on it.

We considered using a constructor and passing IDataReader to it, but the basic performance checks we did seemed to suggest the interface was consistently faster (if only by a little). Also, the interface route provides instant feedback via compilation errors.

One of the things I like, is that you can utilize private set for properties like Age in the example below and set them straight from the database.

public interface IDataReaderParser
{
    void Parse(IDataReader reader);
}

public class Foo : IDataReaderParser
{
    public string Name { get; set; }
    public int Age { get; private set; }

    public void Parse(IDataReader reader)
    {
        Name = reader["Name"] as string;
        Age = Convert.ToInt32(reader["Age"]);
    }
}

public class DataLoader
{
    public static IEnumerable<TEntity> GetRecords<TEntity>(string connectionStringName, string storedProcedureName, IEnumerable<SqlParameter> parameters = null)
                where TEntity : IDataReaderParser, new()
    {
        using (var sqlCommand = new SqlCommand(storedProcedureName, Connections.GetSqlConnection(connectionStringName)))
        {
            using (sqlCommand.Connection)
            {
                sqlCommand.CommandType = CommandType.StoredProcedure;
                AssignParameters(parameters, sqlCommand);
                sqlCommand.Connection.Open();

                using (var sqlDataReader = sqlCommand.ExecuteReader())
                {
                    while (sqlDataReader.Read())
                    {
                        //Create an instance and parse the reader to set the properties
                        var entity = new TEntity();
                        entity.Parse(sqlDataReader);
                        yield return entity;
                    }
                }
            }
        }
    }
}

To call it, you simply provide the type parameter

IEnumerable<Foo> foos = DataLoader.GetRecords<Foo>(/* params */)
0 votes
answered Sep 15, 2017 by mohsen

The simplest Solution :

var dt=new DataTable();
dt.Load(myDataReader);
list<DataRow> dr=dt.AsEnumerable().ToList();

Then select them in order to map them to any type.

0 votes
answered Sep 15, 2017 by pim

NOTE: This is .NET Core code

A stupidly performant option, should you not mind an external dependency (the amazing Fast Member nuget package):

public static T ConvertToObject<T>(this SqlDataReader rd) where T : class, new()
{

    Type type = typeof(T);
    var accessor = TypeAccessor.Create(type);
    var members = accessor.GetMembers();
    var t = new T();

    for (int i = 0; i < rd.FieldCount; i++)
    {
        if (!rd.IsDBNull(i))
        {
            string fieldName = rd.GetName(i);

            if (members.Any(m => string.Equals(m.Name, fieldName, StringComparison.OrdinalIgnoreCase)))
            {
                accessor[t, fieldName] = rd.GetValue(i);
            }
        }
    }

    return t;
}

To use:

public IEnumerable<T> GetResults<T>(SqlDataReader dr) where T : class, new()
{
    while (dr.Read())
    {
        yield return dr.ConvertToObject<T>());
    }
}
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...