How to convert a DataReader to a DTO/List properly?

Question

I'm trying to find an easy way to convert a DataReader to a DTO (wich got properties just like the column names). But I'm wondering how the reflection would affect the performace and maybe there's an easier/cleaner way to do it.

I was looking at SO but haven't found a decent solution (even on Duplicated questions).

Here's what I did for "homework"

 public static T ConvertSPToDTO<T>(string procName, List<SqlParameter> parameters) where T : new()
    {
        T t = new T();
        SqlDataReader rs = null;
        try
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(procName, conn))
                {
                    foreach (SqlParameter param in parameters)
                    {
                        cmd.Parameters.Add(param);
                    }
                    cmd.CommandType = CommandType.StoredProcedure;
                    rs = cmd.ExecuteReader();
                    List<string> columns = Enumerable.Range(0, rs.FieldCount).Select(rs.GetName).ToList();
                    while (rs.Read())
                    {
                        foreach (string column in columns)
                        {
                            if (rs[column] != System.DBNull.Value)
                            {
                                t.GetType().GetProperty(column).SetValue(t, Convert.ChangeType(rs[column], rs[column].GetType()), null);
                            }

                        }
                    }
                    rs.Close();
                    return t;
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return default(T);
        }
        finally
        {
            rs.Dispose();
        }
    }

you can use AutoMapper (https://github.com/AutoMapper/AutoMapper). — kmatyaszek, Sep 21 '13 at 07:35

score 1 · Answer 1 · answered Sep 21 '13 at 07:41

To do this efficiently requires meta-programming. You can use libraries to help. For example, "FastMember" includes a TypeAccessor which provides fast access to instance creation and member-access by name. However, this example is also basically exactly how "dapper" works, so you could just use dapper:

int id = ...
var data = connection.Query<Order>(
    "select * from Orders where CustomerId = @id",
    new { id }).ToList();

You can also open up the "dapper" code to see what it does.

How to convert a DataReader to a DTO/List properly?

1 Answers1