Database abstraction layer for multiple providers

Question

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 bottom

---

Core

The 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);
}

---

SqlClientProvider

This 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;
 }
 }
 }
}

---

Helpers

If 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 configuration

public 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 builders

public 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 builders

public 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 & Usage

Test models

public 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 repository

This 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();

• Tolani: I love your questions. I'm still trying to finish reviewing your Custom string formatters. Will be coming shortly for this one wink
• t3chb0t: @TolaniJaiye-Tikolo thx ;-) I'm trying to make them as short as I can but it's not easy. This is the least working code.

Answer 1

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.

• t3chb0t: I purposely removed the CommandType from the signature because I'm going to write a second method called ExecuteStoredProcedure. I agree with the rest ;-) For sql validation I will add a sanitizer where you can write the query like select [name] from {schema}.{table} where [name] = @name where the {schema} and {table} will be passed via parameters and sanitized internally then injected into to the string, this should work for other names too.
• Heslacher: because I'm going to write a second method called ExecuteStoredProced‌​ure ... and then you will write a ExecuteReaderStoredProcedure ? Both will lead to some code duplication.
• t3chb0t: not necessarily, I can have a third one that requires the command-type parameter but me as a user wouldn't like to specify it everytime... I'll probably go with ExecuteQuery & ExecuteStoredProcedure and leave the parameter as an internal matter for the third method ExecuteReader... tough decisions ;-)
• Heslacher: but me as a user wouldn't like to specify it everytime this is an argument I can understand. Usually I would suggest having the CommandType as an optional parameter, but having two optional parameters for one method just smells.
• t3chb0t: I aborted the project... and I must say it was stupid :-P although a nice exercise and I've learned a few things but it's practically rewriting entity framework. We can as well create code-first models, apply the appropriate attributes and Execute a Parameterized Query. If you don't use EF it means the project is so tiny that for the two or three queries it's not worth to use EF and you write ad-hoc sql or it's so large that you use EF anyway.