C# Dal abstraction layer | C# Online Compiler

C# Dal abstraction layer by Maslow

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Foundation.Helpers
{
    internal static class SqlHelper
    {

        public static int ExecuteSpNonQuery(string cmdText, string connectionString, IDictionary<string, object> parameters = null)
        {
            return Sql<int>(connectionString, cmd => { PrepareCommand(cmd, cmdText, CommandType.StoredProcedure, parameters); return cmd.ExecuteNonQuery(); });
        }

        public static T LoadOneOrNone<T>(string cmdText, CommandType commandType, string connectionString, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
            return ExecuteReader<T>(cmdText, connectionString, commandType, r =>
            {
                if (r.Read())
                    return readFunc(r);
                return default(T);
            }, parameters);
        }

        public static T LoadOneOrDie<T>(string cmdText, CommandType commandType, string connectionString, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
            return ExecuteReader<T>(cmdText, connectionString, commandType, r =>
            {
                r.Read();
                return readFunc(r);
            }, parameters);
        }

        public static T LoadOneOrNone<T>(string cmdText, CommandType commandType, IDbConnection conn, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
            return ExecuteReader<T>(cmdText, conn, commandType, r =>
            {
                if (r.Read())
                    return readFunc(r);
                return default(T);
            }, parameters);
        }

        #region bytecode

        public static byte[] LoadSpAllBytes(string cmdText, string connectionString, IDictionary<string, object> parameters = null)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                return LoadSpAllBytes(cmdText, conn, parameters);
            }
        }

        public static byte[] LoadSpAllBytes(string cmdText, IDbConnection conn, IDictionary<string, object> parameters = null)
        {
            byte[] blob = null;
            if (conn.State != ConnectionState.Open)
                conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = cmdText;
                LoadParameters(cmd, parameters);

                using (IDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        blob = new Byte[(reader.GetBytes(0, 0, null, 0, int.MaxValue))];
                        reader.GetBytes(0, 0, blob, 0, blob.Length);
                    }

                    return blob;
                }
            }
        }

        #endregion
        internal static void LoadParameters(IDbCommand cmd, IDictionary<string, object> parameters)
        {
            if (parameters == null)
                return;

            foreach (var kvp in parameters)
            {
                var p = cmd.CreateParameter();
                p.Value = kvp.Value;
                p.ParameterName = kvp.Key;
                cmd.Parameters.Add(p);
            }

            foreach (IDataParameter p in cmd.Parameters)
            {
                if (p.Value == null) p.Value = DBNull.Value;
            }
        }

        internal static object ExecuteScalar(string cmdText, CommandType commandType, string connectionString, IDictionary<string, object> parameters = null)
        {
            return Sql(connectionString, cmd => ExecuteCommand(cmd, cmdText, commandType, () => cmd.ExecuteScalar(), parameters));
        }

        private static T ExecuteReader<T>(string cmdText, IDbConnection conn, CommandType commandType, Func<IDataReader, T> readerFunc, IDictionary<string, object> parameters = null)
        {
            return Sql(conn, cmd => ExecuteCommand(cmd, cmdText, commandType, () =>
            {
                using (var reader = cmd.ExecuteReader())
                    return readerFunc(reader);
            }, parameters));
        }
        private static T ExecuteReader<T>(string cmdText, string connectionString, CommandType commandType, Func<IDataReader, T> readerFunc, IDictionary<string, object> parameters = null)
        {
            return Sql(connectionString, cmd => ExecuteCommand(cmd, cmdText, commandType, () =>
            {
                using (var reader = cmd.ExecuteReader())
                    return readerFunc(reader);
            }, parameters));
        }

        private static TResult ExecuteCommand<T, TResult>(T cmd, string cmdText, CommandType commandType, Func<TResult> mapFunc, IDictionary<string, object> parameters = null, Action<T> extraPrep = null)
            where T : IDbCommand
        {
            PrepareCommand(cmd, cmdText, commandType, parameters, extraPrep);
            return mapFunc();
        }

        private static void PrepareCommand<T>(T cmd, string cmdText, CommandType commandType, IDictionary<string, object> parameters, Action<T> extraPrep = null)
            where T : IDbCommand
        {
            if (cmd.Connection.State != ConnectionState.Open)
                cmd.Connection.Open();
            cmd.CommandText = cmdText;
            cmd.CommandType = commandType;
            LoadParameters(cmd, parameters);
            if (extraPrep != null)
                extraPrep(cmd);
        }

        private static T Sql<T>(string connectionString, Func<IDbCommand, T> commandExecutor)
        {
            using (var conn = new SqlConnection(connectionString))
                return Sql(conn, commandExecutor);
        }

        private static T Sql<T>(IDbConnection conn, Func<IDbCommand, T> commandExecutor)
        {
            using (var cmd = conn.CreateCommand())
            {
                return commandExecutor(cmd);
            }
        }
    }

    public static class SqlIterationHelper
    {

        public static IEnumerable<T> IterateSqlOnConn<T>(string cmdText, IDbConnection conn, Func<IDataRecord, T> readIterator, IDictionary<string, object> parameters = null)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = cmdText;
                SqlHelper.LoadParameters(cmd, parameters);
                foreach (var item in IterateCmd<T>(cmd, readIterator))
                    yield return item;

            }
        }

        internal static IEnumerable<T> IterateSql<T>(string connectionString, Action<IDbCommand> commandSetup, Func<IDataRecord, T> mapFunc)
        {
            using (var conn = new SqlConnection(connectionString))
                foreach (var item in IterateSql(conn, commandSetup, mapFunc))
                    yield return item;
        }

        private static IEnumerable<T> LoadAll<T>(string cmdText, string connectionString, Func<string, IDbConnection, Func<IDataRecord, T>, IDictionary<string, object>, IEnumerable<T>> loadFunc, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {

            using (var conn = new SqlConnection(connectionString))
            {
                foreach (var item in loadFunc(cmdText, conn, readFunc, parameters))
                    yield return item;
            }
        }

        public static IEnumerable<T> LoadSqlAll<T>(string sql, IDbConnection conn, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
            return IterateSqlOnConn(sql, conn, readFunc, parameters);
        }



        public static IEnumerable<T> LoadSpAll<T>(string cmdText, IDbConnection conn, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
            return IterateSpOnConn(cmdText, conn, readFunc, parameters);
        }

        public static IEnumerable<T> LoadSqlAll<T>(string sql, string connectionString, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
            foreach (var item in LoadAll(sql, connectionString, LoadSqlAll, readFunc, parameters))
                yield return item;
        }

        public static IEnumerable<T> LoadSpAll<T>(string cmdText, string connectionString, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
            foreach (var item in LoadAll(cmdText, connectionString, LoadSpAll, readFunc, parameters))
                yield return item;

        }

        public static IEnumerable<T> IterateSpOnConn<T>(string cmdText, IDbConnection conn, Func<IDataRecord, T> readIterator, IDictionary<string, object> parameters = null)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = cmdText;
                SqlHelper.LoadParameters(cmd, parameters);
                foreach (var item in IterateCmd<T>(cmd, readIterator).Where(i => i != null))
                    yield return item;

            }
        }

        private static IEnumerable<T> IterateSql<T>(IDbConnection conn, Action<IDbCommand> commandSetup, Func<IDataRecord, T> mapFunc)
        {
            using (var cmd = conn.CreateCommand())
            {
                commandSetup(cmd);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                        yield return mapFunc(reader);
                }
            }
        }

        private static IEnumerable<T> IterateCmd<T>(IDbCommand cmd, Func<IDataRecord, T> readFunc)
        {
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                    yield return readFunc(reader);
            }
        }
    }
}

​x
 
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
​
namespace Foundation.Helpers
{
    internal static class SqlHelper
    {
​
        public static int ExecuteSpNonQuery(string cmdText, string connectionString, IDictionary<string, object> parameters = null)
        {
            return Sql<int>(connectionString, cmd => { PrepareCommand(cmd, cmdText, CommandType.StoredProcedure, parameters); return cmd.ExecuteNonQuery(); });
        }
​
        public static T LoadOneOrNone<T>(string cmdText, CommandType commandType, string connectionString, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
            return ExecuteReader<T>(cmdText, connectionString, commandType, r =>
            {
                if (r.Read())
                    return readFunc(r);
                return default(T);
            }, parameters);
        }
​
        public static T LoadOneOrDie<T>(string cmdText, CommandType commandType, string connectionString, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
            return ExecuteReader<T>(cmdText, connectionString, commandType, r =>
            {
                r.Read();
                return readFunc(r);
            }, parameters);
        }
​
        public static T LoadOneOrNone<T>(string cmdText, CommandType commandType, IDbConnection conn, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
            return ExecuteReader<T>(cmdText, conn, commandType, r =>
            {
                if (r.Read())
                    return readFunc(r);
                return default(T);
            }, parameters);
        }
​
        #region bytecode
​
        public static byte[] LoadSpAllBytes(string cmdText, string connectionString, IDictionary<string, object> parameters = null)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                return LoadSpAllBytes(cmdText, conn, parameters);
            }
        }
​
        public static byte[] LoadSpAllBytes(string cmdText, IDbConnection conn, IDictionary<string, object> parameters = null)
        {
            byte[] blob = null;
            if (conn.State != ConnectionState.Open)
                conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = cmdText;
                LoadParameters(cmd, parameters);
​
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        blob = new Byte[(reader.GetBytes(0, 0, null, 0, int.MaxValue))];
                        reader.GetBytes(0, 0, blob, 0, blob.Length);
                    }
​
                    return blob;
                }
            }
        }
​
        #endregion
        internal static void LoadParameters(IDbCommand cmd, IDictionary<string, object> parameters)
        {
            if (parameters == null)
                return;
​
            foreach (var kvp in parameters)
            {
                var p = cmd.CreateParameter();
                p.Value = kvp.Value;
                p.ParameterName = kvp.Key;
                cmd.Parameters.Add(p);
            }
​
            foreach (IDataParameter p in cmd.Parameters)
            {
                if (p.Value == null) p.Value = DBNull.Value;
            }
        }
​
        internal static object ExecuteScalar(string cmdText, CommandType commandType, string connectionString, IDictionary<string, object> parameters = null)
        {
            return Sql(connectionString, cmd => ExecuteCommand(cmd, cmdText, commandType, () => cmd.ExecuteScalar(), parameters));
        }
​
        private static T ExecuteReader<T>(string cmdText, IDbConnection conn, CommandType commandType, Func<IDataReader, T> readerFunc, IDictionary<string, object> parameters = null)
        {
            return Sql(conn, cmd => ExecuteCommand(cmd, cmdText, commandType, () =>
            {
                using (var reader = cmd.ExecuteReader())
                    return readerFunc(reader);
            }, parameters));
        }
        private static T ExecuteReader<T>(string cmdText, string connectionString, CommandType commandType, Func<IDataReader, T> readerFunc, IDictionary<string, object> parameters = null)
        {
            return Sql(connectionString, cmd => ExecuteCommand(cmd, cmdText, commandType, () =>
            {
                using (var reader = cmd.ExecuteReader())
                    return readerFunc(reader);
            }, parameters));
        }
​
        private static TResult ExecuteCommand<T, TResult>(T cmd, string cmdText, CommandType commandType, Func<TResult> mapFunc, IDictionary<string, object> parameters = null, Action<T> extraPrep = null)
            where T : IDbCommand
        {
            PrepareCommand(cmd, cmdText, commandType, parameters, extraPrep);
            return mapFunc();
        }
​
        private static void PrepareCommand<T>(T cmd, string cmdText, CommandType commandType, IDictionary<string, object> parameters, Action<T> extraPrep = null)
            where T : IDbCommand
        {
            if (cmd.Connection.State != ConnectionState.Open)
                cmd.Connection.Open();
            cmd.CommandText = cmdText;
            cmd.CommandType = commandType;
            LoadParameters(cmd, parameters);
            if (extraPrep != null)
                extraPrep(cmd);
        }
​
        private static T Sql<T>(string connectionString, Func<IDbCommand, T> commandExecutor)
        {
            using (var conn = new SqlConnection(connectionString))
                return Sql(conn, commandExecutor);
        }
​
        private static T Sql<T>(IDbConnection conn, Func<IDbCommand, T> commandExecutor)
        {
            using (var cmd = conn.CreateCommand())
            {
                return commandExecutor(cmd);
            }
        }
    }
​
    public static class SqlIterationHelper
    {
​
        public static IEnumerable<T> IterateSqlOnConn<T>(string cmdText, IDbConnection conn, Func<IDataRecord, T> readIterator, IDictionary<string, object> parameters = null)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = cmdText;
                SqlHelper.LoadParameters(cmd, parameters);
                foreach (var item in IterateCmd<T>(cmd, readIterator))
                    yield return item;
​
            }
        }
​
        internal static IEnumerable<T> IterateSql<T>(string connectionString, Action<IDbCommand> commandSetup, Func<IDataRecord, T> mapFunc)
        {
            using (var conn = new SqlConnection(connectionString))
                foreach (var item in IterateSql(conn, commandSetup, mapFunc))
                    yield return item;
        }
​
        private static IEnumerable<T> LoadAll<T>(string cmdText, string connectionString, Func<string, IDbConnection, Func<IDataRecord, T>, IDictionary<string, object>, IEnumerable<T>> loadFunc, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
​
            using (var conn = new SqlConnection(connectionString))
            {
                foreach (var item in loadFunc(cmdText, conn, readFunc, parameters))
                    yield return item;
            }
        }
​
        public static IEnumerable<T> LoadSqlAll<T>(string sql, IDbConnection conn, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
            return IterateSqlOnConn(sql, conn, readFunc, parameters);
        }
​
​
​
        public static IEnumerable<T> LoadSpAll<T>(string cmdText, IDbConnection conn, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
            return IterateSpOnConn(cmdText, conn, readFunc, parameters);
        }
​
        public static IEnumerable<T> LoadSqlAll<T>(string sql, string connectionString, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
            foreach (var item in LoadAll(sql, connectionString, LoadSqlAll, readFunc, parameters))
                yield return item;
        }
​
        public static IEnumerable<T> LoadSpAll<T>(string cmdText, string connectionString, Func<IDataRecord, T> readFunc, IDictionary<string, object> parameters = null)
        {
            foreach (var item in LoadAll(cmdText, connectionString, LoadSpAll, readFunc, parameters))
                yield return item;
​
        }
​
        public static IEnumerable<T> IterateSpOnConn<T>(string cmdText, IDbConnection conn, Func<IDataRecord, T> readIterator, IDictionary<string, object> parameters = null)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = cmdText;
                SqlHelper.LoadParameters(cmd, parameters);
                foreach (var item in IterateCmd<T>(cmd, readIterator).Where(i => i != null))
                    yield return item;
​
            }
        }
​
        private static IEnumerable<T> IterateSql<T>(IDbConnection conn, Action<IDbCommand> commandSetup, Func<IDataRecord, T> mapFunc)
        {
            using (var cmd = conn.CreateCommand())
            {
                commandSetup(cmd);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                        yield return mapFunc(reader);
                }
            }
        }
​
        private static IEnumerable<T> IterateCmd<T>(IDbCommand cmd, Func<IDataRecord, T> readFunc)
        {
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                    yield return readFunc(reader);
            }
        }
    }
}
​

800 iterations executed.
1.8400
1.7800
1.6900
0.6700
0.1200
0.9400
0.9500
0.9700
0.8800
1.6700
0.6900
1.2300
1.7900
0.6700
1.2400
1.2600
1.9200
1.5600
0.3400
0.7000
0.5600
1.9300
0.2400
1.3500
0.2300
0.7800
0.3800
0.8200

Cached Result
Last Run:	8:44:33 am
Compile:	0.126s
Execute:	0s
Memory:	856kb
CPU:	0s

View IL Code