Simple DataAccess layer base class which maps value from database to generic type POCO class instance

DataAccessLayer

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using log4net;


namespace SimpleData.DataAccessLayer
{
    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
    public class DataColumn : Attribute
    {
        #region Fields
        private string columnName;
        #endregion

        #region Properties
        public string ColumnName
        {
            get
            {
                return this.columnName;
            }
            set
            {
                this.columnName = value;
            }
        }
        #endregion

        #region Constructors
        public DataColumn(string columnName)
        {
            this.columnName = columnName;
        }
        #endregion

    }


    public abstract class Base : IDisposable
    {
        private static ILog Log = LogManager.GetLogger(typeof(Base));

        #region Fields
        private SqlConnection connection = null;
        #endregion

        #region Properties
        public SqlConnection Connection
        {
            get
            {

                return this.connection;
            }
            set
            {
                this.connection = value;
            }
        }
        #endregion

        #region Constructors
        public Base()
        {

        }

        public Base(string connectionString)
        {
            this.connection = new SqlConnection(connectionString);
        }

        public Base(SqlConnection connection)
        {
            this.connection = connection;
        }
        #endregion

        #region Data operations

        /// <summary>
        /// Returns SqlDataReader for stored procedure with optional paramets list
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string procedureName, Dictionary<string, IConvertible> parameters = null)
        {
            return this.GetCommand(procedureName, parameters).ExecuteReader();
        }

        /// <summary>
        /// Returns DataTable for stored procedure with optional paramets list
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string procedureName, Dictionary<string, IConvertible> parameters = null)
        {
            DataTable dataTable = new DataTable();
            this.GetAdapter(procedureName, parameters).Fill(dataTable);
            return dataTable;
        }

        /// <summary>
        /// Returns DataSet for stored procedure with optional paramets list
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string procedureName, Dictionary<string, IConvertible> parameters = null)
        {
            DataSet dataSet = new DataSet();
            this.GetAdapter(procedureName, parameters).Fill(dataSet);
            return dataSet;
        }

        /// <summary>
        /// Creates single instance of POCO class of type T
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public T ExecuteProcedureSingleModel<T>(string procedureName, Dictionary<string, IConvertible> parameters = null) where T : class, new()
        {
            T item = null;
            IDataRecord record = this.GetCommand(procedureName, parameters).ExecuteScalar() as IDataRecord;
            if (record != null)
            {
                var objectType = typeof(T);
                item = new T();
                for (int columnIndex = 0; columnIndex < record.FieldCount; columnIndex++)
                {
                    var objectProperty = objectType.GetProperties()
                            .Where(p => p.GetCustomAttributes(typeof(DataColumn), true)
                                .Where(a => ((DataColumn)a).ColumnName == record.GetName(columnIndex))
                                .Any()
                                ).FirstOrDefault();
                    if (objectProperty != null)
                    {
                        var dataValue = record.GetValue(columnIndex);
                        objectProperty.SetValue(item, DBNull.Value.Equals(dataValue) ? null : dataValue);
                    }
                }
            }
            return item;
        }

        /// <summary>
        /// Return IEnumerable of POCO class instances of type T for the mutiple results from database
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public IEnumerable<T> ExecuteProcedureMultipleModel<T>(string procedureName, Dictionary<string, IConvertible> parameters = null) where T : class, new()
        {
            var reader = this.ExecuteReader(procedureName, parameters);
            if (reader.HasRows)
            {
                var objectType = typeof(T);
                while (reader.Read())
                {
                    T item = new T();
                    for (int columnIndex = 0; columnIndex < reader.FieldCount; columnIndex++)
                    {
                        var objectProperty = objectType.GetProperties()
                            .Where(p => p.GetCustomAttributes(typeof(DataColumn), true)
                                .Where(a => ((DataColumn)a).ColumnName == reader.GetName(columnIndex))
                                .Any()
                                ).FirstOrDefault();
                        if (objectProperty != null)
                        {
                            var dataValue = reader.GetValue(columnIndex);
                            objectProperty.SetValue(item, DBNull.Value.Equals(dataValue) ? null : dataValue);
                        }
                    }
                    yield return item;
                }
            }
            reader.Close();
        }

        #endregion

        #region Command preparing methods

        /// <summary>
        /// Creates SqlAdapter instance for the stored procedure with optional paramets
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private SqlDataAdapter GetAdapter(string procedureName, Dictionary<string, IConvertible> parameters = null)
        {
            return new SqlDataAdapter(this.GetCommand(procedureName, parameters));
        }

        /// <summary>
        /// Creates SqlAdapter instance for the command
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        private SqlDataAdapter GetAdapter(SqlCommand command)
        {
            return new SqlDataAdapter(command);
        }

        /// <summary>
        /// Prepares SqlCommand for stored procedure with oprional parameters list
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private SqlCommand GetCommand(string procedureName, Dictionary<string, IConvertible> parameters = null)
        {
            SqlCommand command = new SqlCommand(procedureName);
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            command.Connection = this.connection;
            if (parameters != null && parameters.Any())
            {
                foreach (var param in parameters)
                {
                    command.Parameters.Add(new SqlParameter(param.Key, param.Value));
                }
            }
            return command;
        }

        #endregion

        public void Dispose()
        {
            if (this.connection != null)
            {
                this.connection.Close();
                this.connection.Dispose();
            }
        }
    }
}