Read from an Arbitrary Table

This scenario demonstrate how to read all of the rows/columns of a table when the table name is only known at run time.

Scenario Prototype

public interface IArbitraryTableReadScenario<T>
{
    T GetAll(string schema, string tableName);
}

ADO.NET

public class ArbitraryTableReadScenario : SqlServerScenarioBase, IArbitraryTableReadScenario<DataTable>
{
    public ArbitraryTableReadScenario(string connectionString) : base(connectionString)
    { }

    public DataTable GetAll(string schema, string tableName)
    {
        //WARNING: Verify that the schema and table name exist to avoid SQL injection attacks.
        string sql = $"SELECT * FROM [{schema}].[{tableName}];";

        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 will verify the table exists before executing the query to ensure that SQL injection is not possible.

public class ArbitraryTableReadScenario : IArbitraryTableReadScenario<DataTable>
{
    readonly SqlServerDataSource m_DataSource;

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

    public DataTable GetAll(string schemaName, string tableName)
    {
        return m_DataSource.From(schemaName + "." + tableName).ToDataTable().Execute();
    }
}

public class ArbitraryTableReadScenario2 : IArbitraryTableReadScenario<IReadOnlyList<IReadOnlyDictionary<string, object?>>>
{
    readonly SqlServerDataSource m_DataSource;

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

    //This version returns a lightweight object known as a "Table". It is an alternative to .NET's DataTable.
    public IReadOnlyList<IReadOnlyDictionary<string, object?>> GetAll(string schemaName, string tableName)
    {
        return m_DataSource.From(schemaName + "." + tableName).ToTable().Execute().Rows;
    }
}

Dapper

public class ArbitraryTableReadScenario : ScenarioBase, IArbitraryTableReadScenario<DataTable>
{
    public ArbitraryTableReadScenario(string connectionString) : base(connectionString)
    { }

    public DataTable GetAll(string schema, string tableName)
    {
        //WARNING: Verify that the schema and table name exist to avoid SQL injection attacks.
        string sql = $"SELECT * FROM [{schema}].[{tableName}];";

        var result = new DataTable();

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

        return result;
    }
}

DbConnector

TODO

Entity Framework 6

TODO

Entity Framework Core

public class ArbitraryTableReadScenario : IArbitraryTableReadScenario<DataTable>
{
    private readonly Func<OrmCookbookContext> CreateDbContext;

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

    public DataTable GetAll(string schema, string tableName)
    {
        using OrmCookbookContext context = CreateDbContext();
        using DbCommand commnd = context.Database.GetDbConnection().CreateCommand();
        string sql = $"SELECT * FROM [{schema}].[{tableName}];";
        commnd.CommandText = sql;
        commnd.Connection!.Open();
        using DbDataReader reader = commnd.ExecuteReader(CommandBehavior.CloseConnection);
        var result = new DataTable();
        result.Load(reader);
        return result;
    }
}

LINQ to DB

TODO

LLBLGen Pro

TODO

NHibernate

TODO

RepoDb

TODO

ServiceStack

TODO