Shin Shin - 3 months ago 7
C# Question

How to dynamically specify type of List<> function?

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

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.

Comments