C# Database Mapper

Question

I was looking to map my database query results to strongly type objects in my c# code. So i wrote a quick and dirty helper method on the SqlConnection class which runs the query on the database and uses reflection to map the record columns to the object properties. The code is below:

 public static T Query<T>(this SqlConnection conn, string query) where T : new()
    {
        T obj = default(T);

        using (SqlCommand command = new SqlCommand(query, conn))
        {
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    obj = new T();

                    PropertyInfo[] propertyInfos;
                    propertyInfos = typeof(T).GetProperties();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        var name = reader.GetName(i);

                        foreach (var item in propertyInfos)
                        {
                            if (item.Name.Equals(name, StringComparison.InvariantCultureIgnoreCase) && item.CanWrite)
                            {
                                item.SetValue(obj, reader[i], null);
                            }
                        }

                    }
                }
            }
        }

        return obj;
    }

---

  public class User
    {
        public int id { get; set; }
        public string firstname { get; set; }
        public string lastname { get; set; }
        public DateTime signupDate { get; set; }
        public int age { get; set; }
        public string gender { get; set; }
    }


   var user = conn.Query<User>("select id,firstname,lastname from users");      

I just wanted a second opinion on my approach above of using reflection to tie the values together, if there's anything i can do better in the code above. Or if there's some other totally different approach i can take to get the same result?

I think i can probably improve the code in the helper method by removing the loop for propertyInfos and using a dictionary instead. Is there anything else that needs to be tweaked?

P.S: i'm aware of Dapper, i just wanted to implement something similar on my own to help me learn better.

Answer 1

There are a few of things I can think of:

1. I think that in order to skip the loop you can use:

   reader[item.Name]
   

2. I've done something similar myself, but I never ran into dapper. I'm not sure if it uses reflection, but it's always a good idea to read someone else's code to sharpen your skill (Scott Hanselman frequently recommends doing so).

3. You can also look at: http://www.codeproject.com/KB/database/metaquery_part1.aspx

4. You can implement an attribute that maps a field to a database column, but that's just for fun.

Edit:

5: You can also skip the while loop over the reader and just take the first row, and document the fact that your query only returns one object, so it doesn't pull a thousand rows if the query returns a thousand rows.

Answer 2

What you've done is basically what linq-to-sql or other OR-mappers do under the hood. To learn the details of how it works it's always a good idea to write something from scratch.

If you want more inspiration or want to have something that's ready for production use out-of-the-box I'd recommend reading up on linq-to-sql. It is lightweight, yet competent.