A generic database class
This is a class I use frequently for running SQL commands / queries:
public class Database : IDatabase { public Database(DatabaseConfiguration config) { this.config = config; } public object[][] RunQuery(string query, params object[] args) { using (var con = Connect()) using (var cmd = Prepare(con, query, args)) return cmd.ExecuteReader().Pipe(ReadData); } public void RunNonQuery(string command, params object[] args) { using (var con = Connect()) using (var cmd = Prepare(con, command, args)) cmd.ExecuteNonQuery(); } // private static readonly ITableReader TABLE_READER = new TableReader(); private readonly DatabaseConfiguration config; private SqlConnection Connect() => new SqlConnection(config.ConnectionString).Apply(it => it.Open()); private static object[][] ReadData(IDataReader reader) => TABLE_READER.ReadData(reader); private static SqlCommand Prepare(SqlConnection connection, string sql, params object[] args) => new SqlCommand(sql, connection).Apply(it => AddParams(it, args)); private static void AddParams(SqlCommand cmd, IEnumerable<object> args) => ToParams(args).Apply(cmd.Parameters.AddRange); private static SqlParameter[] ToParams(IEnumerable<object> args) => args.Select(CreateParameter).ToArray(); private static SqlParameter CreateParameter(object value, int index) => new SqlParameter($"@p{index + 1}", value); }This is using two helper classes: the
TableReader
class
internal class TableReader : ITableReader { public object[][] ReadData(IDataReader reader) { var list = new List<object[]>(); while (reader.Read()) { var values = new object[reader.FieldCount]; reader.GetValues(values); list.Add(values); } return list.ToArray(); } }and the
DatabaseConfiguration
class
public class DatabaseConfiguration { public string ConnectionString { get; set; } }and also a couple of extension methods:
public static B Pipe<A, B>(this A a, Func<A, B> func) => func(a); public static A Apply<A>(this A a, Action<A> action) { action(a); return a; }I am quite happy with the way this code looks - small classes and methods, everything should be pretty self-explanatory.
Comments