There's been a lot of questions lately about database provider and repository design especially without entity framework or alike.I thought I try myself to create a reusable framework for this kind of stuff. So let's begin ;-)TL;DR - There's an example at the bottomCoreThe main part of the framework is the DbProvider class with a corresponding interface. It encapsulates the most repetitive common tasks like managing a connection, executing queries and adding parameters. There is one thing that this early version lacks namely sql injection protection. The parameters need to be sanitized later.I used generics to make it strongly typed and avoid magic strings later (like table or column names). With this design it supports each provider's own data types.public interface IDbProvider{    int ExecuteNonQuery(string sql, Action<ParameterBuilder> parameters = null);    IEnumerable<Dictionary<string, object>> ExecuteReader(        string sql,         Action<ParameterBuilder> parameters = null);}public abstract class DbProvider<TConnection, TCommand, TData> : IDbProvider    where TConnection : DbConnection    where TCommand : DbCommand{    private readonly DbProviderFactory _dbProviderFactory;    protected DbProvider(        string providerInvariantName,         string connectionString,         Action<DbConfigurationBuilder<TData>> dbConfiguration)    {        _dbProviderFactory = DbProviderFactories.GetFactory(providerInvariantName);        var dbConfigurationBuilder = new DbConfigurationBuilder<TData>(connectionString);        dbConfiguration(dbConfigurationBuilder);        DbConfiguration = dbConfigurationBuilder.Build();    }    public DbConfiguration<TData> DbConfiguration { get; }    public abstract string ParameterPrefix { get; }    public int ExecuteNonQuery(string sql, Action<ParameterBuilder> parameters = null)    {        var parameterBuilder = new ParameterBuilder();        parameters?.Invoke(parameterBuilder);        if (string.IsNullOrWhiteSpace(sql))        {            throw new ArgumentException(Value may not be null or whitespace, sql);        }        using (var connection = _dbProviderFactory.CreateConnection())        {            connection.ConnectionString = DbConfiguration.ConnectionString;            connection.Open();            using (var command = connection.CreateCommand())            {                command.CommandType = CommandType.Text;                AddParameters((TCommand)command, parameterBuilder.Tables);                return command.ExecuteNonQuery();            }        }    }    public IEnumerable<Dictionary<string, object>> ExecuteReader(        string sql,         Action<ParameterBuilder> parameters = null)    {        var parameterBuilder = new ParameterBuilder();        parameters?.Invoke(parameterBuilder);        using (var connection = _dbProviderFactory.CreateConnection())        {            connection.ConnectionString = DbConfiguration.ConnectionString;            connection.Open();            using (var command = connection.CreateCommand())            {                command.CommandType = CommandType.Text;                command.CommandText = sql;                AddParameters((TCommand)command, parameterBuilder.Tables);                using (var reader = command.ExecuteReader())                {                    var columns = Enumerable.Range(0, reader.FieldCount)                        .Select(reader.GetName).ToList();                    var result = new List<Dictionary<string, object>>();                    while (reader.Read())                    {                        result.Add(columns.ToDictionary(c => c, c => reader[c]));                    }                    return result;                }            }        }    }    protected abstract void AddParameters(        TCommand command,         IReadOnlyDictionary<string, IReadOnlyDictionary<string, object>> parameters);}SqlClientProviderThis is an example of a sql-client-provider. It overrides the AddParameters method to use its own types and the parameter prefix.public class SqlClientProvider : DbProvider<SqlConnection, SqlCommand, SqlDbType>{    public SqlClientProvider(        string connectionString,         Action<DbConfigurationBuilder<SqlDbType>> dbConfiguration)        : base(System.Data.SqlClient, connectionString, dbConfiguration)    { }    public override string ParameterPrefix => @;    protected override void AddParameters(        SqlCommand command,         IReadOnlyDictionary<string, IReadOnlyDictionary<string, object>> parameters)    {        foreach (var table in parameters)        {            foreach (var column in table.Value)            {                command.Parameters.Add(                    ${ParameterPrefix}{column.Key},                    DbConfiguration.TableConfigurations[table.Key].ColumnConfigurations[column.Key].DataType,                    DbConfiguration.TableConfigurations[table.Key].ColumnConfigurations[column.Key].Length                ).Value = column.Value;            }        }    }}HelpersIf someone would like to test it I include here the whole set of supporting types that make the entire thing work and provide a strongly typed fluent api.I will not comment on this. They are just a bunch of builders to make it easier to use and to eliminate magic strings.Database configurationpublic class DbConfiguration<TData>{    public DbConfiguration(string connectionString)    {        ConnectionString = connectionString;        TableConfigurations = new Dictionary<string, TableConfiguration<TData>>();    }    public string ConnectionString { get; }    public string SchemaName { get; internal set; }    public Dictionary<string, TableConfiguration<TData>> TableConfigurations { get; }}public class TableConfiguration<TData>{    public TableConfiguration()    {        ColumnConfigurations = new Dictionary<string, ColumnConfiguration<TData>>();    }    public Dictionary<string, ColumnConfiguration<TData>> ColumnConfigurations { get; }}public class ColumnConfiguration<TDataType>{    public int Length { get; set; }    public TDataType DataType { get; set; }}Database configuration builderspublic class DbConfigurationBuilder<TData>{    private readonly DbConfiguration<TData> _dbConfiguration;    public DbConfigurationBuilder(string nameOrConnectionString)    {        _dbConfiguration = new DbConfiguration<TData>(nameOrConnectionString);    }    public DbConfigurationBuilder<TData> SchemaName(string schemaName)    {        _dbConfiguration.SchemaName = schemaName;        return this;    }    public DbConfigurationBuilder<TData> Table<TTable>(Action<TableConfiguraitonBuilder<TTable, TData>> tableConfiguration)    {        var tableConfigurationBuilder = new TableConfiguraitonBuilder<TTable, TData>();        tableConfiguration(tableConfigurationBuilder);        _dbConfiguration.TableConfigurations[tableConfigurationBuilder.Name] = tableConfigurationBuilder.TableConfiguraion;        return this;    }    public DbConfiguration<TData> Build() => _dbConfiguration;}public class TableConfiguraitonBuilder<TTable, TData>{    private readonly TableConfiguration<TData> _tableConfiguration;    public TableConfiguraitonBuilder()    {        _tableConfiguration = new TableConfiguration<TData>();    }    public TableConfiguraitonBuilder<TTable, TData> Column<TColumn>(        Expression<Func<TTable, TColumn>> expression,        Action<ColumnConfigurationBuilder<TData>> columnConfiguration)    {        var columnConfigurationBuilder = new ColumnConfigurationBuilder<TData>();        columnConfiguration(columnConfigurationBuilder);        _tableConfiguration.ColumnConfigurations[ExpressionHelper.GetMemberName(expression)] = columnConfigurationBuilder.ColumnConfiguration;        return this;    }    internal string Name => typeof(TTable).Name;    internal TableConfiguration<TData> TableConfiguraion => _tableConfiguration;}public class ColumnConfigurationBuilder<TData>{    private readonly ColumnConfiguration<TData> _columnConfiguration;    public ColumnConfigurationBuilder()    {        _columnConfiguration = new ColumnConfiguration<TData>();    }    public ColumnConfigurationBuilder<TData> HasLength(int length)    {        _columnConfiguration.Length = length;        return this;    }    public ColumnConfigurationBuilder<TData> HasDataType(TData dataType)    {        _columnConfiguration.DataType = dataType;        return this;    }    internal ColumnConfiguration<TData> ColumnConfiguration => _columnConfiguration;}static class ExpressionHelper{    public static string GetMemberName<T, P>(Expression<Func<T, P>> expression)    {        var memberExpression = expression.Body as MemberExpression;        if (memberExpression != null)        {            return memberExpression.Member.Name;        }        throw new ArgumentException(Invalid expression type., nameof(expression));    }}Parameter builderspublic class ParameterBuilder{    private readonly Dictionary<string, IReadOnlyDictionary<string, object>> _tables;    public ParameterBuilder()    {        _tables = new Dictionary<string, IReadOnlyDictionary<string, object>>();    }    internal IReadOnlyDictionary<string, IReadOnlyDictionary<string, object>> Tables => _tables;    public ParameterBuilder Table<TTable>(Action<TableBuilder<TTable>> table)    {        var tableBuilder = new TableBuilder<TTable>();        table(tableBuilder);        _tables[typeof(TTable).Name] = tableBuilder.Columns;        return this;    }}public class TableBuilder<TTable>{    private readonly Dictionary<string, object> _columns;    public TableBuilder()    {        _columns = new Dictionary<string, object>();    }    internal IReadOnlyDictionary<string, object> Columns => _columns;    public TableBuilder<TTable> Column<TColumn>(Expression<Func<TTable, TColumn>> expression, object value)    {        _columns[ExpressionHelper.GetMemberName(expression)] = value;        return this;    }}Example & UsageTest modelspublic class Setting{    public string Name { get; set; }    public object Value { get; set; }}public class User{    public int Id { get; set; }    public string Login { get; set; }    public string Password { get; set; }}Test repositoryThis repository creates a sql-client-provider and initializes the tables and columns with types and lengths.It also provides a test method for selecting settings GetValues that uses the framework.public class FooRepository{    private readonly IDbProvider _dbProvider;    public FooRepository(IDbProvider dbProvider)    {        _dbProvider = dbProvider;    }    public static FooRepository CreateWithSqlClient(string connectionString)    {        return new FooRepository(new SqlClientProvider(connectionString, b =>        {            // These settings are used when adding parameters.            b.Table<Setting>(t =>            {                t.Column(c => c.Name, c => c.HasLength(200).HasDataType(SqlDbType.NVarChar));                t.Column(c => c.Value, c => c.HasLength(-1).HasDataType(SqlDbType.NVarChar));            });            b.Table<User>(t =>            {                t.Column(c => c.Id, c => c.HasDataType(SqlDbType.Int));                t.Column(c => c.Login, c => c.HasLength(30).HasDataType(SqlDbType.NVarChar));                t.Column(c => c.Password, c => c.HasLength(30).HasDataType(SqlDbType.NVarChar));            });        }));    }    public List<string> GetValues(string name)    {        return _dbProvider.ExecuteReader(            select [value] from [setting] where [name] = @name,            p => p.Table<Setting>(t => t.Column(s => s.Name, name)) // needs caching        )        .Select(r => r.Values.First())        .Cast<string>()        .ToList();    }}Using the repository:var fooRepo = FooRepository.CreateWithSqlClient(connectionString);var results = fooRepo.GetValues(Foo).Dump();
Database abstraction layer for multiple providers
c#;database;generics;framework;fluent interface
DbProvider<TConnection, TCommand, TData>In the ExecuteNonQuery() method you are doing stuff before you check the passed in parameter sql for validness. You should place the validation at the top of the method.  public int ExecuteNonQuery(string sql, Action<ParameterBuilder> parameters = null){    if (string.IsNullOrWhiteSpace(sql))    {        throw new ArgumentException(Value may not be null or whitespace, sql);    }  If the connection couldn't be opened because the passed connectionstring is wrong or the DBMS couldn't be reached, the work which may be done by the passed in Action<ParameterBuilder> parameters will be unnecessary/lost. So placing this inside the using block may be better like so      using (var connection = _dbProviderFactory.CreateConnection())    {        connection.ConnectionString = DbConfiguration.ConnectionString;        connection.Open();        var parameterBuilder = new ParameterBuilder();        parameters?.Invoke(parameterBuilder);        using (var command = connection.CreateCommand())        {            command.CommandType = CommandType.Text;            AddParameters((TCommand)command, parameterBuilder.Tables);            return command.ExecuteNonQuery();        }    }Right now this method takes two parameters, hence having a third which takes a CommandType enum wouldn't hurt and you wouldn't need to change this if a stored procedure should be used. This results in  public int ExecuteNonQuery(string sql, CommendType commandType, Action<ParameterBuilder> parameters = null){    if (string.IsNullOrWhiteSpace(sql))    {        throw new ArgumentException(Value may not be null or whitespace, sql);    }    using (var connection = _dbProviderFactory.CreateConnection())    {        connection.ConnectionString = DbConfiguration.ConnectionString;        connection.Open();        var parameterBuilder = new ParameterBuilder();        parameters?.Invoke(parameterBuilder);        using (var command = connection.CreateCommand())        {            command.CommandType = commandType;            AddParameters((TCommand)command, parameterBuilder.Tables);            return command.ExecuteNonQuery();        }    }}  In the ExecuteReader() method you don't check the validness of the sql parameter. You really should do this. The mentioned points about the ExecuteNonQuery() method apply to this method as well.  