Convert array of objects into a DataTable in C#

DEFINE YOUR CLASS

The class must correspond to the structure of the SQL table, including the correct order of the fields as they appear in the database. If the field order doesn’t match, SQL inserts will fail when using the DataTable.

Here’s an example of an EmployeeDTO class, though your class will differ based on your needs.

public class EmployeeDTO
{
    public int EmployeeId { get; set; } 
    public string FirstName { get; set; } 
    public string LastName { get; set; } 
    public DateTime HireDate { get; set; } 
    public decimal Salary { get; set; } 
    public bool IsActive { get; set; } 
}

IMPLEMENT THE EXTENSION METHOD

Now, implement the following extension method that will allow you to convert your list of objects into a DataTable.

using System.ComponentModel;
using System.Data;

namespace MyCode
{
    public static class IEnumerableExtensions
    {
        public static DataTable ToDataTable<T>(this IEnumerable<T> data)
        {
            // Get the properties of the object type
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            
            // Add columns to the DataTable based on the object properties
            foreach (PropertyDescriptor prop in properties)
            {
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            }

            // Add rows to the DataTable
            foreach (T item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                {
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                }
                table.Rows.Add(row);
            }

            return table;
        }
    }
}

HOW TO USE THE EXTENSION METHOD

Now, you can use this extension method to convert a list of EmployeeDTO objects into a DataTable.

List<EmployeeDTO> employees = new List<EmployeeDTO>();
employees.Add(new EmployeeDTO() { EmployeeId = 1, FirstName = "John", LastName = "Doe", HireDate = DateTime.Now, Salary = 50000, IsActive = true });
employees.Add(new EmployeeDTO() { EmployeeId = 2, FirstName = "Jane", LastName = "Smith", HireDate = DateTime.Now, Salary = 55000, IsActive = true });
employees.Add(new EmployeeDTO() { EmployeeId = 3, FirstName = "Bob", LastName = "Brown", HireDate = DateTime.Now, Salary = 47000, IsActive = false });

var dataTable = employees.ToDataTable();

In this example, a list of EmployeeDTO objects is created, representing employees with their details like ID, name, hire date, salary, and status. Then, the ToDataTable method is called to convert the list into a DataTable for potential SQL insert operations.