Populate a DataTable

A DataTable is often used for holding the results of a report that are then displayed in a data grid of some sort. It is primarily used when the columns returned are dynamically chosen or when creating custom classes for each report is consider to be onerous.

A DataTable may also be used as a staging area before performing a bulk insert operation.

Scenario Prototype

public interface IPopulateDataTableScenario

{
    /// <summary>
    /// Gets a filtered list of EmployeeClassification rows.
    /// </summary>
    DataTable FindByFlags(bool isExempt, bool isEmployee);

    /// <summary>
    /// Gets all EmployeeClassification rows.
    /// </summary>
    DataTable GetAll();
}

ADO.NET

DataTable.Load can be provided with an IDataReader.

public class PopulateDataTableScenario : SqlServerScenarioBase, IPopulateDataTableScenario
{
    public PopulateDataTableScenario(string connectionString) : base(connectionString)
    { }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec WHERE ec.IsExempt = @IsExempt AND ec.IsEmployee = @IsEmployee;";

        var result = new DataTable();

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@IsExempt", isExempt);
            cmd.Parameters.AddWithValue("@IsEmployee", isEmployee);
            using (var reader = cmd.ExecuteReader())
                result.Load(reader);
        }
        return result;
    }

    public DataTable GetAll()
    {
        const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec;";

        var result = new DataTable();

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        using (var reader = cmd.ExecuteReader())
            result.Load(reader);

        return result;
    }
}

Chain

Chain natively supports DataTable.

public class PopulateDataTableScenario : IPopulateDataTableScenario
{
    const string TableName = "HR.EmployeeClassification";
    readonly SqlServerDataSource m_DataSource;

    public PopulateDataTableScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        return m_DataSource.From(TableName, new { isExempt, isEmployee }).ToDataTable().Execute();
    }

    public DataTable GetAll()
    {
        return m_DataSource.From(TableName).ToDataTable().Execute();
    }
}

Dapper

DataTable.Load can be provided with an IDataReader.

public class PopulateDataTableScenario : ScenarioBase, IPopulateDataTableScenario
{
    public PopulateDataTableScenario(string connectionString) : base(connectionString)
    {
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec WHERE ec.IsExempt = @IsExempt AND ec.IsEmployee = @IsEmployee;";

        var result = new DataTable();

        using (var con = OpenConnection())
        using (var reader = con.ExecuteReader(sql, new { isExempt, isEmployee }))
            result.Load(reader);

        return result;
    }

    public DataTable GetAll()
    {
        var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec;";

        var result = new DataTable();

        using (var con = OpenConnection())
        using (var reader = con.ExecuteReader(sql))
            result.Load(reader);

        return result;
    }
}

DbConnector

public class PopulateDataTableScenario : ScenarioBase, IPopulateDataTableScenario
{
    public PopulateDataTableScenario(string connectionString) : base(connectionString)
    {
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec WHERE ec.IsExempt = @isExempt AND ec.IsEmployee = @isEmployee;";

        return DbConnector.ReadToDataTable(sql, new { isExempt, isEmployee }).Execute();
    }

    public DataTable GetAll()
    {
        var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec;";

        return DbConnector.ReadToDataTable(sql).Execute();
    }
}

Entity Framework 6

EF Core does not support DataTable.

public class PopulateDataTableScenario : IPopulateDataTableScenario
{
    private Func<OrmCookbookContext> CreateDbContext;

    public PopulateDataTableScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var dt = new DataTable();
        dt.Columns.Add("EmployeeClassificationKey", typeof(int));
        dt.Columns.Add("EmployeeClassificationName", typeof(string));
        dt.Columns.Add("IsExempt", typeof(bool));
        dt.Columns.Add("IsEmployee", typeof(bool));

        using (var context = CreateDbContext())
        {
            foreach (var row in context.EmployeeClassification.Where(x => x.IsExempt == isExempt && x.IsEmployee == isEmployee))
                dt.Rows.Add(row.EmployeeClassificationKey, row.EmployeeClassificationName, row.IsExempt, row.IsEmployee);
        }
        return dt;
    }

    public DataTable GetAll()
    {
        var dt = new DataTable();
        dt.Columns.Add("EmployeeClassificationKey", typeof(int));
        dt.Columns.Add("EmployeeClassificationName", typeof(string));
        dt.Columns.Add("IsExempt", typeof(bool));
        dt.Columns.Add("IsEmployee", typeof(bool));

        using (var context = CreateDbContext())
        {
            foreach (var row in context.EmployeeClassification)
                dt.Rows.Add(row.EmployeeClassificationKey, row.EmployeeClassificationName, row.IsExempt, row.IsEmployee);
        }
        return dt;
    }
}

You can generalize this using a reflection library.

public class PopulateDataTableScenario2 : IPopulateDataTableScenario
{
    private Func<OrmCookbookContext> CreateDbContext;

    public PopulateDataTableScenario2(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var propertyList = MetadataCache.GetMetadata<EmployeeClassification>().Properties.Where(p => p.CanRead).ToList();

        var dt = BuildGenericDataTable(propertyList);

        using (var context = CreateDbContext())
        {
            var buffer = new object[propertyList.Count];
            foreach (var row in context.EmployeeClassification.Where(x => x.IsExempt == isExempt && x.IsEmployee == isEmployee))
            {
                for (var i = 0; i < propertyList.Count; i++)
                    buffer[i] = propertyList[i].InvokeGet(row) ?? DBNull.Value;

                dt.Rows.Add(buffer);
            }
        }
        return dt;
    }

    public DataTable GetAll()
    {
        var propertyList = MetadataCache.GetMetadata<EmployeeClassification>().Properties.Where(p => p.CanRead).ToList();

        var dt = BuildGenericDataTable(propertyList);

        using (var context = CreateDbContext())
        {
            var buffer = new object[propertyList.Count];
            foreach (var row in context.EmployeeClassification)
            {
                for (var i = 0; i < propertyList.Count; i++)
                    buffer[i] = propertyList[i].InvokeGet(row) ?? DBNull.Value;

                dt.Rows.Add(buffer);
            }
        }
        return dt;
    }

    static DataTable BuildGenericDataTable(List<PropertyMetadata> propertyList)
    {
        var dt = new DataTable();
        foreach (var property in propertyList)
        {
            var propertyType = MetadataCache.GetMetadata(property.PropertyType);
            if (propertyType.IsNullable && propertyType.TypeInfo.IsValueType) //Special handling for Nullable<T>
            {
                var underlyingType = propertyType.TypeInfo.GenericTypeArguments[0];
                var col = dt.Columns.Add(property.Name, underlyingType);
                col.AllowDBNull = true;
            }
            else
            {
                var col = dt.Columns.Add(property.Name, property.PropertyType);
                col.AllowDBNull = property.IsReferenceNullable ?? propertyType.IsNullable;
            }
        }

        return dt;
    }
}

Entity Framework Core

EF Core does not support DataTable.

public class PopulateDataTableScenario : IPopulateDataTableScenario
{
    private Func<OrmCookbookContext> CreateDbContext;

    public PopulateDataTableScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var dt = new DataTable();
        dt.Columns.Add("EmployeeClassificationKey", typeof(int));
        dt.Columns.Add("EmployeeClassificationName", typeof(string));
        dt.Columns.Add("IsExempt", typeof(bool));
        dt.Columns.Add("IsEmployee", typeof(bool));

        using (var context = CreateDbContext())
        {
            foreach (var row in context.EmployeeClassifications.Where(x => x.IsExempt == isExempt && x.IsEmployee == isEmployee))
                dt.Rows.Add(row.EmployeeClassificationKey, row.EmployeeClassificationName, row.IsExempt, row.IsEmployee);
        }
        return dt;
    }

    public DataTable GetAll()
    {
        var dt = new DataTable();
        dt.Columns.Add("EmployeeClassificationKey", typeof(int));
        dt.Columns.Add("EmployeeClassificationName", typeof(string));
        dt.Columns.Add("IsExempt", typeof(bool));
        dt.Columns.Add("IsEmployee", typeof(bool));

        using (var context = CreateDbContext())
        {
            foreach (var row in context.EmployeeClassifications)
                dt.Rows.Add(row.EmployeeClassificationKey, row.EmployeeClassificationName, row.IsExempt, row.IsEmployee);
        }
        return dt;
    }
}

You can generalize this using a reflection library.

public class PopulateDataTableScenario2 : IPopulateDataTableScenario
{
    private Func<OrmCookbookContext> CreateDbContext;

    public PopulateDataTableScenario2(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var propertyList = MetadataCache.GetMetadata<EmployeeClassification>().Properties.Where(p => p.CanRead).ToList();

        var dt = BuildGenericDataTable(propertyList);

        using (var context = CreateDbContext())
        {
            var buffer = new object[propertyList.Count];
            foreach (var row in context.EmployeeClassifications.Where(x => x.IsExempt == isExempt && x.IsEmployee == isEmployee))
            {
                for (var i = 0; i < propertyList.Count; i++)
                    buffer[i] = propertyList[i].InvokeGet(row) ?? DBNull.Value;

                dt.Rows.Add(buffer);
            }
        }
        return dt;
    }

    public DataTable GetAll()
    {
        var propertyList = MetadataCache.GetMetadata<EmployeeClassification>().Properties.Where(p => p.CanRead).ToList();

        var dt = BuildGenericDataTable(propertyList);

        using (var context = CreateDbContext())
        {
            var buffer = new object[propertyList.Count];
            foreach (var row in context.EmployeeClassifications)
            {
                for (var i = 0; i < propertyList.Count; i++)
                    buffer[i] = propertyList[i].InvokeGet(row) ?? DBNull.Value;

                dt.Rows.Add(buffer);
            }
        }
        return dt;
    }

    static DataTable BuildGenericDataTable(List<PropertyMetadata> propertyList)
    {
        var dt = new DataTable();
        foreach (var property in propertyList)
        {
            var propertyType = MetadataCache.GetMetadata(property.PropertyType);
            if (propertyType.IsNullable && propertyType.TypeInfo.IsValueType) //Special handling for Nullable<T>
            {
                var underlyingType = propertyType.TypeInfo.GenericTypeArguments[0];
                var col = dt.Columns.Add(property.Name, underlyingType);
                col.AllowDBNull = true;
            }
            else
            {
                var col = dt.Columns.Add(property.Name, property.PropertyType);
                col.AllowDBNull = property.IsReferenceNullable ?? propertyType.IsNullable;
            }
        }

        return dt;
    }
}

LINQ to DB

TODO

LLBLGen Pro

LLBLGen Pro natively supports DataTable.

public class PopulateDataTableScenario : IPopulateDataTableScenario
{
    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        using (var adapter = new DataAccessAdapter())
        {
            var q = new QueryFactory().EmployeeClassification
                          .Where(EmployeeClassificationFields.IsEmployee.Equal(isEmployee)
                                 .And(EmployeeClassificationFields.IsExempt.Equal(isExempt)))
                                      .Select(Projection.Full);
            return adapter.FetchAsDataTable(q);
        }
    }

    public DataTable GetAll()
    {
        using (var adapter = new DataAccessAdapter())
        {
            return adapter.FetchAsDataTable(new QueryFactory().EmployeeClassification.Select(Projection.Full));
        }
    }
}

NHibernate

NHibernate does not support DataTable, but you can add it using an IResultTransformer.

[SuppressMessage("Design", "CA1001")]
public class DataTableResultTransformer : IResultTransformer
{
    readonly DataTable m_DataTable = new DataTable();
    readonly Type?[] m_DataTypeOverrides = Array.Empty<Type?>();

    /// <summary>
    /// Initializes a new instance of the <see cref="DataTableResultTransformer"/> class.
    ///
    /// Only use this constructor if none of the columns are nullable.
    /// </summary>
    /// <remarks>Warning: If a field is NULL in the first row, that entire column will be cast as a String.</remarks>
    public DataTableResultTransformer()
    {
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="DataTableResultTransformer"/> class.
    ///
    /// 1. If dataTypeOverrides for a given column is not null, it is used.
    /// 2. If the field is not null for the first row, then that field's data type if used.
    /// 3. If both the dataTypeOverride and the field in the first row are null, the column's data type is String.
    /// </summary>
    /// <param name="dataTypeOverrides">The expected data types.</param>
    public DataTableResultTransformer(params Type?[] dataTypeOverrides)
    {
        m_DataTypeOverrides = dataTypeOverrides;
    }

    public IList TransformList(IList collection)
    {
        return new List<DataTable> { m_DataTable };
    }

    public object TransformTuple(object[] tuple, string[] aliases)
    {
        if (tuple == null || tuple.Length == 0)
            throw new ArgumentException($"{nameof(tuple)} is null or empty.", nameof(tuple));

        if (aliases == null || aliases.Length == 0)
            throw new ArgumentException($"{nameof(aliases)} is null or empty.", nameof(aliases));

        if (m_DataTable.Columns.Count == 0)
        {
            //Create the DataTable if this is the first row
            for (var i = 0; i < aliases.Length; i++)
            {
                var col = m_DataTable.Columns.Add(aliases[i]);
                if (i < m_DataTypeOverrides.Length && m_DataTypeOverrides[i] != null)
                    col.DataType = m_DataTypeOverrides[i]!;
                else if (tuple[i] != null && tuple[i] != DBNull.Value)
                    col.DataType = tuple[i].GetType();
            }
        }

        return m_DataTable.Rows.Add(tuple);
    }
}

Note that inline SQL must be used inconjunction with the IResultTransformer.

public class PopulateDataTableScenario : IPopulateDataTableScenario
{
    readonly ISessionFactory m_SessionFactory;

    public PopulateDataTableScenario(ISessionFactory sessionFactory)
    {
        m_SessionFactory = sessionFactory;
    }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var session = m_SessionFactory.OpenSession())
        {
            session.Save(classification);
            session.Flush();
            return classification.EmployeeClassificationKey;
        }
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        using (var session = m_SessionFactory.OpenSession())
        {
            session.Delete(new EmployeeClassification() { EmployeeClassificationKey = employeeClassificationKey });
            session.Flush();
        }
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        //Note that this uses ":ParameterName" instead of SQL Server's normal "@ParameterName".
        var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec WHERE ec.IsExempt = :IsExempt AND ec.IsEmployee = :IsEmployee;";

        using (var session = m_SessionFactory.OpenStatelessSession())
        {
            var sqlQuery = session.CreateSQLQuery(sql);
            sqlQuery.SetParameter("IsExempt", isExempt);
            sqlQuery.SetParameter("IsEmployee", isEmployee);
            var transformedQuery = sqlQuery.SetResultTransformer(new DataTableResultTransformer());
            return transformedQuery.List().OfType<DataTable>().Single();
        }
    }

    public DataTable GetAll()
    {
        var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec;";

        using (var session = m_SessionFactory.OpenStatelessSession())
        {
            var sqlQuery = session.CreateSQLQuery(sql);
            var transformedQuery = sqlQuery.SetResultTransformer(new DataTableResultTransformer());
            return transformedQuery.List().OfType<DataTable>().Single();
        }
    }
}

RepoDb

In RepoDb, the DataTable.Load can be provided with an IDataReader object from ExecuteReader method.

public class PopulateDataTableScenario : IPopulateDataTableScenario
{
    readonly string m_ConnectionString;

    public PopulateDataTableScenario(string connectionString)
    {
        m_ConnectionString = connectionString;
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec WHERE ec.IsExempt = @IsExempt AND ec.IsEmployee = @IsEmployee;";
        var table = new DataTable();

        using (var connection = new SqlConnection(m_ConnectionString))
        {
            using (var reader = connection.ExecuteReader(sql, new { isExempt, isEmployee }))
            {
                table.Load(reader);
            }
        }

        return table;
    }

    public DataTable GetAll()
    {
        var sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec;";
        var table = new DataTable();

        using (var connection = new SqlConnection(m_ConnectionString))
        {
            using (var reader = connection.ExecuteReader(sql))
            {
                table.Load(reader);
            }
        }

        return table;
    }
}

ServiceStack

public class PopulateDataTableScenario : IPopulateDataTableScenario
{
    private readonly IDbConnectionFactory _dbConnectionFactory;
    
    public PopulateDataTableScenario(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var dt = new DataTable();
        dt.Columns.AddRange( ModelDefinition<EmployeeClassification>.Definition.FieldDefinitions.Select(k =>
            new DataColumn(k.FieldName, k.FieldType)).ToArray());
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            var sql = db.From<EmployeeClassification>().Where(x => x.IsExempt == isExempt && x.IsEmployee == isEmployee);
            foreach (var row in db.SelectLazy(sql))
                dt.Rows.Add(row.Id, row.EmployeeClassificationName, row.IsExempt, row.IsEmployee);
        }

        return dt;
    }

    public DataTable GetAll()
    {
        var dt = new DataTable();
        dt.Columns.AddRange( ModelDefinition<EmployeeClassification>.Definition.FieldDefinitions.Select(k =>
            new DataColumn(k.FieldName, k.FieldType)).ToArray());

        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            foreach (var row in db.SelectLazy(db.From<EmployeeClassification>()))
                dt.Rows.Add(row.Id, row.EmployeeClassificationName, row.IsExempt, row.IsEmployee);
        }

        return dt;
    }
}