Generic List linked with Database using Reflection

Question

I have a class

public class UserInfo
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
}

And I need to make a link between the database, with this code:

using (SqlDataReader reader = cmd.ExecuteReader())
{
   while (reader.HasRows)
   {

   }
}

Using Reflection on all the lines of the database.

And store them into a generic List:

List<UserInfo> users = new List<UserInfo>();

I GOT IT !!

why not use one of the [many available ORMs](http://en.wikipedia.org/wiki/List_of_object-relational_mapping_software#.NET) instead of rolling your own? — Cristian Lupascu, Jul 10 '13 at 06:22
Is this what you mean? http://stackoverflow.com/questions/12662318/how-to-convert-datatable-to-listt-using-reflections — Winks, Jul 10 '13 at 06:23

score 1 · Accepted Answer · answered Jul 18 '13 at 12:55

I GOT IT !!

This is the result, maybe someone needs it !!

public List<UserInfo> GetAllUsers()
{
    List<UserInfo> users = new List<UserInfo>();

    try
    {
        using (SqlConnection sqlConnection = connectionString)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "dbo.GetAllUsers";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Connection = sqlConnection;
                sqlConnection.Open();

                using (SqlDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.HasRows)
                    {
                        while (dataReader.Read())
                        {
                            UserInfo user = new UserInfo();
                            PropertyInfo[] pList = typeof(UserInfo).GetProperties();
                            foreach (PropertyInfo pi in pList)
                            {
                                object value = dataReader[pi.Name];
                                if (!value.GetType().Equals(typeof(DBNull)))
                                {
                                    users.GetType().GetProperty(pi.Name, BindingFlags.Public | BindingFlags.Instance).SetValue(user, value, null);
                                }
                            }
                            users.Add(user);
                        }
                    }
                    else
                    {
                        users = null;
                    }
                }
            }
            sqlConnection.Close();
        }
    }


    catch (Exception)
    {
        return null;
    }

    return users;
}

Generic List linked with Database using Reflection

1 Answers1