How to dynamically specify type of List<> function?

Question

I have some classes that represent database tables, to load the rows of each table on a DataGridView, I've a List<> function that inside a loop gets all rows from that table.

public List<class_Table1> list_rows_table1()
{
    // class_Table1 contains each column of table as public property
    List<class_Table1> myList = new List<class_Table1>();

    // sp_List_Rows: stored procedure that lists data
    //   from Table1 with some conditions or filters
    Connection cnx = new Connection;
    Command cmd = new Command(sp_List_Rows, cnx);

    cnx.Open;
    IDataReader dr = cmd.ExecuteReader();

    while (dr.Read())
    {
        class_Table1 ct = new class_Table1();

        ct.ID   = Convert.ToInt32(dr[ID_table1]);
        ct.Name = dr[name_table1].ToString();
        //... all others wanted columns follow here

        myList.Add(ct);
    }
    dr.Close();
    cnx.Close();

    // myList contains all wanted rows; from a Form fills a dataGridView
    return myList();
}

And for other tables, some other functions: list_rows_table2, list_rows_table3... My question is: How do I create a only List<> function, where I can dynamically specify the type of List<> returned, or how to convert, for example a List<object> to List<myClass> before returning.

Answer 1

You could have an interface that all your data classes must implement

public interface IData
{
    void FillFromReader(IDataReader dr);
}

Then change your method like this

public List<T> GetList<T>(string sqlText)
    where T : IData, new()
{
    List<T> myList = new List<T>();

    using (Connection cnx = new Connection(connString))
    using (Command cmd = new Command(sqlText, cnx)) {
        cnx.Open();
        using (IDataReader dr = cmd.ExecuteReader()) {
            while (dr.Read())
            {
                T item = new T();
                item.FillFromReader(dr);
                myList.Add(item);
            }
        }
    }
    return myList();
}

So basically each class would be responsible for filling its own fields.

The constraint where T : IData, new() for the generic type parameter is crucial. It tells the method, that T must implement the interface IData. This is necessary for being able to call the method FillFromReader without casting. The data classes must have a default constructor (this is specified by new(). This enables you to instantiate one with new T().

---

I surrounded the code using the connection, the command and the data reader with using statements. The using statement closes and releases the resources automatically at the end of the block and ensures that this happens, even if an exception should be thrown or the statement block should be left prematurely with a return-statement for instance.

See using Statement (C# Reference)

Answer 2

Olivier's implementation is good. It uses generics and interfaces giving each entity it's own implementation of FillFromDataReader().

You can take it farther. By using convention all the data hydration code can be centralized and abstracted away.

I am going to assume that your class property names and your columns names are the same. If they are not then the following code can be extended to add aliases attributes to the property names. Sometimes a property is calculated from other values in the object, this property can not be hydrated. An Ignore attribute can be created and implemented in the below class.

public class DataAccess
{
    /// <summary>
    /// Hydrates the collection of the type passes in.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql">The SQL.</param>
    /// <param name="connection">The connection.</param>
    /// <returns>List{``0}.</returns>
    public List<T> List<T>(string sql, string connection) where T: new()
    {
        List<T> items = new List<T>();

        using (SqlCommand command = new SqlCommand(sql, new SqlConnection(connection)))
        {
            string[] columns = GetColumnsNames<T>();
            var reader = command.ExecuteReader(CommandBehavior.CloseConnection);

            while (reader.Read())
            {
                T item = new T();

                foreach (var column in columns)
                {
                    object val = reader.GetValue(reader.GetOrdinal(column));
                    SetValue(item, val, column);
                }

                items.Add(item);
            }

            command.Connection.Close();

        }

        return items;
    }

    /// <summary>
    /// Sets the value.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="item">The item.</param>
    /// <param name="value">The value.</param>
    /// <param name="column">The column.</param>
    private void SetValue<T>(T item, object value, string column)
    {
        var property = item.GetType().GetProperty(column);
        property.SetValue(item, value, null);
    }

    /// <summary>
    /// Gets the columns names.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns>System.String[][].</returns>
    private string[] GetColumnsNames<T>() where T : new()
    {
        T item = new T();

        return (from i in item.GetType().GetProperties()
                select i.Name).ToArray();
    }
}

There are a couple caveats in the above code. DBNulls and Nullable types are special cases and will require custom code to deal with them. I usually convert DBNull's to null. I have never ran into a case where I needed to distinguish the different between the two. For Nullalbe types, simply detect the Nullable type and handle the code accordingly.

An ORM would remove much of the headache of dealing with data access. The downside, is many times you are coupled to the DTO's and the database schema. Of course this issue can be contained by using abstractions. Many companies still use strictly stored procedures, most ORMs fall down when they are forced to consume stored procedures. They are just not designed to work with stored procedures.

I wrote a data access framework called "Hypersonic." It's on GitHub, it's specifically designed to work with stored procedures. The above code is a light implementation of the it.