How to create a Dynamic IEnumerable

Question

I know this is a simple question for you. But I am a beginner in c#. What I want to achieve is to create a method that will store any type of List of Objects from my Model. e.g List<Person>.

I have tried to make something like this..

public IEnumerable<T> GetObjects<T>()
{
    Type type = typeof(T);

    PropertyInfo[] properties = type.GetProperties();

    foreach (PropertyInfo item in properties)
    {
        // store properties
    }


    List<T> objects = new List<T>();

    using (SqlConnection str = GetSqlConnection)
    {
        // retrieve data from db 
        //then store it to list of objects
    }

    return objects;

}

This will enable me to retrieve data using only this method.

EDIT:

I already manage to create this sample code to retrieve a specific table from a database.

public IEnumerable<ItemBrand> getAllBrand
{
    get
    {
        List<ItemBrand> brands = new List<ItemBrand>();

        using (MySqlConnection strConn = getMySqlConnection())
        {
            string query = "SELECT * FROM tblbrand";
            MySqlCommand cmd = new MySqlCommand(query, strConn);

            strConn.Open();
            MySqlDataReader rd = cmd.ExecuteReader();

            while (rd.Read())
            {
                ItemBrand brand = new ItemBrand();

                brand.brandID = Convert.ToInt32(rd["brandID"]);
                brand.Name = rd["brandName"].ToString();

                brands.Add(brand);
            }

            return brands;
        }
    }
}

Currently I have multiple methods of this in my solution. I would love to remove those duplicate codes with your help.

Answer 1

I have written a method that gets the result retrieved from a stored proc and transformes it into a list of objects. The only thing you need to pay attention to is: The class you are mapping to must have the same column names as the ones that are sent from the database.

public IEnumerable <T> ExecuteReaderToList<T>(string storedProcedure, IDictionary parameters = null,
              string connectionString = null)
        {
            ICollection list = new List();


        var properties = typeof(T).GetProperties();           

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            SqlCommand cmd = new SqlCommand();

            cmd.CommandText = storedProcedure;
            cmd.CommandType = CommandType.StoredProcedure;

            if (parameters != null)
            {
                foreach (KeyValuePair<string, object> parameter in parameters)
                {
                    cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
                }
            }

            cmd.Connection = conn;

            conn.Open();

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var element = Activator.CreateInstance<T>();

                    foreach (var f in properties)
                    {
                        var o = reader[f.Name];
                        if (o.GetType() != typeof(DBNull))
                        {
                            f.SetValue(element, o, null);
                        }

                        o = null;
                    }

                    list.Add(element);
                }
            }
            conn.Close();
        }
        return list;
    }

Answer 2

<code> 
  // Create a new type of the entity I want
Type t = typeof(T);

T returnObject = new T();

for (int i = 0; i < dataReader.FieldCount; i++) {
    string colName = string.Empty;
    colName = dataReader.GetName(i);
    // Look for the object's property with the columns name, ignore case
    PropertyInfo pInfo = t.GetProperty(colName.ToLower(), BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);

    // did we find the property ?
    if (pInfo != null) {
        if (dataReader.Read()) {
            object val = dataReader[colName];
            // is this a Nullable<> type
            bool IsNullable = (Nullable.GetUnderlyingType(pInfo.PropertyType) != null);
            if (IsNullable) {
                if (val is System.DBNull) {
                    val = null;
                } else {
                    // Convert the db type into the T we have in our Nullable<T> type
                    val = Convert.ChangeType(val, Nullable.GetUnderlyingType(pInfo.PropertyType));
                }
            } else {
                // Convert the db type into the type of the property in our entity
                val = Convert.ChangeType(val, pInfo.PropertyType);
            }
            // Set the value of the property with the value from the db
            pInfo.SetValue(returnObject, val, null);
        }
    }
}

</code> 

Answer 3

Have a generic method like:

public IEnumerable<T> CreateListOfItems(string tableName,
                                        Func<MySqlDataReader, T> itemCreator)
{
    var items = new List<T>();

    using (var strConn = getMySqlConnection())
    {
        string query = "SELECT * FROM " + tableName;
        var cmd = new MySqlCommand(query, strConn);

        strConn.Open();
        var rd = cmd.ExecuteReader();

        while (rd.Read())
        {
            items.Add(itemCreator(rd));
        }
    }
    return items;
}

Then use like this:

private ItemBrand CreateItemBrandFromDBData(MySqlDataReader rd)
{
    return new ItemBrand
    {
        BrandID = Convert.ToInt32(rd["brandID"]),
        Name = rd["brandName"].ToString()
    };
}

...

var brands = CreateListOfItems<ItemBrand>(tblbrand, CreateItemBrandFromDBData);