How to dynamically indicate the type of the function List <>?

Question

I have several classes that represent database tables, for loading the rows of each table into a DataGridView , I have a List<> function that inside the loop gets all the rows from this 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 to create a single function List<> , where I can dynamically specify the return type List<> or how to convert, for example, from List<object> to List<myClass> .

Answer 1

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

You can do it further. Using convention, the entire data hydration code can be centralized and abstracted.

I'm going to assume that your class property names and column names are the same. If they are not, then the following code can be extended to add alias attributes to property names. Sometimes a property is calculated from other values ​​of an object; this property cannot be hydrated. The Ignore attribute can be created and implemented in the underlying 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 of caveats in the above code. The types DBNulls and Nullable are special cases and require special code from them. Usually I convert DBNull to null. I have never encountered a situation where I needed to distinguish between different ones. For Nullalbe types, simply define the Nullable type and process the code accordingly.

ORM will remove most of the headaches when working with data access. The downside is that you are connected to the DTO and the database schema. Of course, this problem may lie in the use of abstractions. Many companies still use strictly stored procedures; most ORMs fall when they have to consume stored procedures. They are simply not designed to work with stored procedures.

I wrote a data access structure called " Hypersonic ." This is on GitHub, it is specifically designed to work with stored procedures. The above code is an easy implementation of it.

Answer 2

You may have an interface that all your data classes should implement

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

Then change your method as follows

 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(); } 

Thus, each class will be responsible for filling in its own fields.

The where T : IData, new() for a type parameter is critical. It tells the method that T must implement the IData interface. This is necessary to be able to call the FillFromReader method without casting. Data classes must have a default constructor (this is defined by new() . This allows you to instantiate using new T() .

---

I surrounded the code using a connection, command, and data reader with using statements. The using statement closes and frees 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 prematurely returned. For instance,

See using Statement (C # Reference)