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