Converting a Collection into a DataTable using Generics

If you've worked on creating a Data Access Layer you know how frustrating it can be to determine the type of data your DAL will be handling. For that reason, creating a converter that works with generics can be the long term answer.

The first thing to do is to create a reference to the following namespaces:

//custom references
using System.Data.SqlClient;
using System.Data;
using System.Collections.ObjectModel;
using System.Reflection









Since the converters use generics make sure that the class also used generics.

public class toDataTable<T> where T : class, new()








Before converting anything, you need to create the table and fill it with data

private DataTable CreateTable(T genericItem)
        {
            Type type = genericItem.GetType();
            PropertyInfo[] properties = type.GetProperties(); //all the properties in the type

            //DataTable
            DataTable dt = new DataTable();
            foreach (PropertyInfo p in properties)
            {
                dt.Columns.Add(p.Name); //add all columns according to the property Name in the Type T
            }
            return dt;
        }




After creating the Table, fill it with the items returned from the Database. The Execute method handles the connection to the database, retrieving the data, and returning it as a Collection of Generic Objects/items. Since there are articles around explaining how to create the collection of items I won’t mention any details about that process.

private DataTable FillTable(DataTable table, Collection<T> items, bool AllowExceptions)
        {
            if (items.Count != 0)
            {
                for (int i = 0; i < items.Count; i++)//Each element in the collection is added as a row
                {
                    T t1 = items[i];
                    Type type1 = t1.GetType();
                    object[] values = new object[type1.GetProperties().Length];

                    for (int l = 0; l < type1.GetProperties().Length; l++) //for each property, add the value for the member
                    {
                        PropertyInfo prop = type1.GetProperty(table.Columns[l].ColumnName);
                        values[l] = (object)prop.GetValue(t1, null); //to add the row, a collection of objects need to be created
                    }

                    table.Rows.Add(values); //add each row to the table
                }
            }

            else //if there are no items returned, either throw a new exception or add a NO RESULTS row
            {
                if (AllowExceptions) //if user wants to handle the empty table, throw an exception
                {
                    throw new Exception("No Results found, Empty DataTable not allowed, try the AllowExceptions overload instead");
                }
                else //create a new row with the default values of each property
                {
                    T t2 = new T();
                    Type type2 = t2.GetType();
                    PropertyInfo[] properties = type2.GetProperties();
                    object[] values = new object[type2.GetProperties().Length];
                    for (int i = 0; i < values.Length; i++)
                    {
                        Type t = properties[i].PropertyType; //create new type from the property                    
                        if (t == typeof(System.String)) values[i] = "No results found";
                        else values[i] = System.Activator.CreateInstance(t); //create an instance to use the default value
                    }
                    table.Rows.Add(values); //add the row
                }
            }
            return table;
        }

Notice that the AllowExceptions exists because you may not know exactly whether the developer will want to handle exceptions on their own or if he/she will want to have a default table created.
Therefore, below are the three coded methods for converting the collection into a DataTable. The code also handles for exceptions (empty collection).

public DataTable ToDataTable()
        {
            T t = new T();
            DataTable dt = CreateTable(t);

            try //fill the table
            {
                dt = FillTable(dt, Execute(), false);
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }
        public DataTable ToDataTable(Collection<T> Items)
        {
            DataTable dt = null;

            if (Items.Count != 0) //only create from items if it's not empty
            {
                dt = CreateTable(Items[0]); //create the Table from the collection inserted
            }
            else //if items collection is empty, create an empty data table with the column names
            {
                T t = new T();
                dt = CreateTable(t);
            }

            try //fill the Table
            {
                dt = FillTable(dt, Items, false);
            }
            catch (Exception)
            {
                throw;
            }

            return dt;
        }
        public DataTable ToDataTable(bool AllowExceptions)
        {
            T t = new T();
            DataTable dt = CreateTable(t);

            try //fill the table
            {
                dt = FillTable(dt, Execute(), AllowExceptions);
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }
        
        public DataTable ToDataTable(Collection<T> Items, bool AllowExceptions)
        {
            DataTable dt = null;

            if (Items.Count != 0) //only create from items if it's not empty
            {
                dt = CreateTable(Items[0]); //create the Table from the collection inserted
            }
            else //if items collection is empty, create an empty data table with the column names
            {
                T t = new T(); //instantiate the type
                dt = CreateTable(t);
            }

            try //fill the Table
            {
                dt = FillTable(dt, Items, AllowExceptions);
            }
            catch (Exception)
            {
                throw;
            }

            return dt;
        }


You may download the source code here