Convert rows from a data reader into typed results

Question

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

Answer 1

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

Answer 2

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();

Answer 3

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();

Answer 4

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 */)

Answer 5

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

Answer 6

Like Magic

I personally HATE doing manual mapping in constructors, I'm also not a fan of doing my own reflection. So here's another solution courtesy of the wonderful (and fairly ubiquitous) Newtonsoft JSON lib.

It will only work if your property names exactly match the datareader column names, but it worked well for us.

...assumes you've got a datareader name "yourDataReader"...

        var dt = new DataTable();
        dt.Load(yourDataReader);
        // creates a json array of objects
        string json = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
        // this is what you're looking for right??
        List<YourEntityType> list = 
Newtonsoft.Json.JsonConvert
.DeserializeObject<List<YourEntityType>>(json);

Answer 7

Latest versions of C#/.NET5 offer a new awesome feature called "source generators", that I urge everyone to explore.

In a nutshell it allows you to generate C# code at compilation time, the code that will do manual mapping for you. It's obviously 20-30 times faster than any kind of reflection (even with caching and similar speed hacks) - because it literally just assigns properties to IDataReader fields.

There's no short code snippet I could share here, since source generators are still a bit complicated (it should reside in a separate assembly and you will have to learn Roslyn API's etc.) but I do believe it's an awesome feature worth exploring for everyone in the ORM world and around.

I started experimenting with source generators and loving it: https://github.com/jitbit/MapDataReader feel free to "steal" some code from my repo.

disclaimer: I shared a link to my own github repo

Answer 8

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.

Answer 9

For .NET Core 2.0:

Here is an extension method that works with .NET CORE 2.0 to execute RAW SQL and map results to LIST of arbitrary types:

USAGE:

 var theViewModel = new List();
 string theQuery = @"SELECT * FROM dbo.Something";
 theViewModel = DataSQLHelper.ExecSQL(theQuery,_context);

 using Microsoft.EntityFrameworkCore;
 using System.Data;
 using System.Data.SqlClient;
 using System.Reflection;

public static List ExecSQL(string query, myDBcontext context)
 {
 using (context)
 {
 using (var command = context.Database.GetDbConnection().CreateCommand())
 {
 command.CommandText = query;
 command.CommandType = CommandType.Text;
 context.Database.OpenConnection();
                using (var result = command.ExecuteReader())
                {
                    List<T> list = new List<T>();
                    T obj = default(T);
                    while (result.Read())
                    {
                        obj = Activator.CreateInstance<T>();
                        foreach (PropertyInfo prop in obj.GetType().GetProperties())
                        {
                            if (!object.Equals(result[prop.Name], DBNull.Value))
                            {
                                prop.SetValue(obj, result[prop.Name], null);
                            }
                        }
                        list.Add(obj);
                    }
                    return list;

                }
            }
        }
    }

Answer 10

My version

Usage:

var Q = await Reader.GetTable<DbRoom>("SELECT id, name FROM rooms");

PgRoom is

public class DbRoom
{
    [Column("id")]
    public int Id { get; set; }

    [Column("name")]
    public string Name { get; set; }
}

Reader.GetTable contains:

            using (var R = await Q.ExecuteReaderAsync())
            {
                List<T> Result = new List<T>();

                Dictionary<int, PropertyInfo> Props = new Dictionary<int, PropertyInfo>();

                foreach (var p in typeof(T).GetProperties())
                {
                    for (int i = 0; i < R.FieldCount; i++)
                    {
                        if (p.GetCustomAttributes<ColumnAttribute>().FirstOrDefault(t => t.Name == R.GetName(i)) != null
                            && p.PropertyType == R.GetFieldType(i))
                        {
                            Props.Add(i, p);
                        }
                    }
                }

                while (await R.ReadAsync())
                {
                    T row = new T();

                    foreach (var kvp in Props)
                    {
                        kvp.Value.SetValue(row, R[kvp.Key]);
                    }

                    Result.Add(row);
                }

                return Result;
            }

Answer 11

I found this solution.

var cmd = ctx.Connection.CreateCommand();

T result = DbDataReaderdHelper.Fill<T>(cmd)

public static class DbDataReaderdHelper
{
    public static List<T> Fill<T>(DbCommand dbCommand) where T : new()
    {
        List<T> result = new List<T>();
        var reader = dbCommand.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Type type = typeof(T);
                T obj = (T)Activator.CreateInstance(type);
                PropertyInfo[] properties = type.GetProperties();

                foreach (PropertyInfo property in properties)
                {
                    var value = reader[property.Name];

                    try
                    {
                        if (value != null)
                        {
                            var convertedValue = TypeDescriptor.GetConverter(property.PropertyType).ConvertFromInvariantString(value.ToString());

                            property.SetValue(obj, convertedValue);
                        }
                    }
                    catch {}
                }
                result.Add(obj);
            }
        }

        reader.Close();

        return result;
    }
}

Answer 12

Please check this answer

Simple, Fast IDataReader to IEnumerable<T> conversion using reflection + lambda expressions

https://stackoverflow.com/a/70321210/3343007