How do I databind a DataTable to a List of Custom Objects

Question

I have a class that holds tasks:

class Task{
  public string Description;
  public DateTime StartDate;
  public DateTime EndDate;
}

I have a SQLite Database that has a DataTable named "Task":

DataTable.Rows[0]["Description"].ToString() // "Draw a cat"
DataTable.Rows[0]["BeginDate"].ToString() // "2016-08-17 9:47:22 AM"
DataTable.Rows[0]["EndDate"].ToString() // "2016-08-17 11:22:37 AM"

Can I create List<Task> that is populated from the DataTable?

Could I add a new Task to the List<Task>and have it update the DataTable?

Answer 1

Can I create List that is populated from the DataTable?

You need a code like this to do the needful:

// Creates IEnumerable<DataRow>

var taskDataTableEnumerable = taskDataTable.AsEnumerable();

List<Task> myTaskList =
    (from item in taskDataTableEnumerable
     select new Task{
         Description = item.Field<string>("DescriptionColumnName"),
         StartDate = item.Field<DateTime>("StartDateColumnName"),
         EndDate = item.Field<DateTime>("EndDateColumnName")
    }).ToList();

Could I add a new Task to the Listand have it update the DataTable?

Yes you can, and you have following options:

• Check out this question - Convert generic List/Enumerable to DataTable?, it use a Nuget utility FastMember to achieve the same

Or

• You can simply just create a new DataRow to the existing table as follows:

       DataRow taskDataRow = taskDataTable.NewRow();
  

• Add Data to the taskDataRow from the newly added Task Object, using a code like:

         taskDataRow["DescriptionColumnName"] = taskObject.Description
         taskDataRow["StartDateColumnName"] = taskObject.StartDate
         taskDataRow["EndDateColumnName"] = taskObject.EndDate
  

This way newly added Task object to the list is also added as a DataRow to the DataTable, in case FastMember doesn't work for your use case and you need a different option, then may plan to use custom code like underneath, where all the type T properties will be converted to DataTable with appropriate column name, you may add option for fields if required, currently it is for the properties:

    public static DataTable CreateTable<TDataTable>(this IEnumerable<TDataTable> collection)
    {
        // Fetch the type of List contained in the ParamValue
        var tableType = typeof(TDataTable);

        // Create DataTable which will contain data from List<T>
        var dataTable = new DataTable();

        // Fetch the Type fields count
        var columnCount = tableType.GetProperties().Count();

        var columnNameMappingDictionary = new Dictionary<string, string>();

        // Create DataTable Columns using table type field name and their types
        // Traversing through Column Collection
        for (var counter = 0; counter < columnCount; counter++)
        {
            var propertyInfo = tableType.GetProperties()[counter];                

            // Fetch DataParam attribute
            var dataParameterAttribute = propertyInfo.GetDataParameterAttribute();

            // Datatable column name based on DataParam attribute
            var columnName = (dataParameterAttribute != null) ? dataParameterAttribute.Name : propertyInfo.Name;

            columnNameMappingDictionary.Add(propertyInfo.Name,
                (dataParameterAttribute != null) ? dataParameterAttribute.Name : propertyInfo.Name);

            // Fetch the current type of a property and check whether its nullable type before adding a column
            var currentType = tableType.GetProperties()[counter].PropertyType;

            dataTable.Columns.Add(columnName, Nullable.GetUnderlyingType(currentType) ?? currentType);
        }

        // Return parameter with null value
        if (collection == null)
            return dataTable;

        // Traverse through number of entries / rows in the List
        foreach (var item in collection)
        {
            // Create a new DataRow
            var dataRow = dataTable.NewRow();

            foreach (var columnName in columnNameMappingDictionary.Select(propertyinfo => propertyinfo.Value))
            {
                dataRow[columnName] = item.GetType().GetProperty(columnName).GetValue(item) ?? DBNull.Value;
            }
            // Add Row to Table
            dataTable.Rows.Add(dataRow);
        }

        return (dataTable);
    }

// Data Parameter Attribute

[AttributeUsage(AttributeTargets.Property, AllowMultiple = true)]
    public class DataParamAttribute : Attribute
    {
        /// <summary>
        /// Gets or sets the name.
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// Initializes a new instance of the <see cref="DataParamAttribute"/> class.
        /// </summary>
        /// <param name="name">
        /// The name.
        /// </param>
        public DataParamAttribute(string name)
        {
            this.Name = name;
        }
    }

// Fetch the DataParameter attribute

public static DataParamAttribute GetDataParameterAttribute(this PropertyInfo propertyInfo)
        {
            DataParamAttribute mappedAttribute = null;

            // Get list of Custom Attributes on a property
            var attributeArray = propertyInfo.GetCustomAttributes(false);

            // Column mapping of the ParameterAttribute
            var columnMapping =
                attributeArray.FirstOrDefault(attribute => attribute.GetType() == typeof(DataParamAttribute));

            if (columnMapping != null)
            {
                // Typecast to get the mapped attribute
                mappedAttribute = columnMapping as DataParamAttribute;
            }
            return mappedAttribute;
        }