Design and Implementation of an Attribute-Driven, Caching Data Abstraction Layer

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Diagnostics;
using System.Security.Permissions;
using System.Text;
using System.Web;

namespace BrainTechLLC.DAL
{
    /// <summary>
    /// Interface for all selection sets.  Contains a boolean value specifying whether
    /// or not to select all values from a table, and a GetSelectClause() method which
    /// is responsible for returning the appropriate SQL "SELECT" clause for the selection set.
    /// </summary>
    interface ICreateSelect
    {
        bool SelectAll { get; set;}
        string GetSelectClause();
    }

    /// <summary>
    /// Stores a DB table name and corresponding business object Type.
    /// Also stores a selection set for the table, specifying which columns are
    /// to be retrieved.
    /// </summary>
    [Serializable, DesignTimeVisible(true)]
    internal class TableNameAndType
    {
        /// <summary>
        /// DB table name
        /// </summary>
        public string _tableName;
        /// <summary>
        /// Business object type corresponding to the table name
        /// </summary>
        public Type _tableType;
        /// <summary>
        /// A selection set used to select specific columns to be returned from the table
        /// </summary>
        public ICreateSelect _tableSelectionSet;
        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="o"></param>
        public TableNameAndType(ICreateSelect o) { _tableSelectionSet = o; }
    }

    /// <summary>
    /// Represents a single selection set entry
    /// </summary>
    [Serializable]
    internal class SelectionEntry
    {
        /// <summary>
        /// The actual DB column name (or statement to be returned, such as COUNT(*)
        /// </summary>
        internal string _dbColumnName;
        /// <summary>
        /// Any "AS" statement used to rename the results into a more readable form
        /// </summary>
        internal string _resultAS;
        /// <summary>
        /// The cached property descriptor used to fill in result values
        /// </summary>
        internal PropertyDescriptor _cachedPropertyDescriptor;
        /// <summary>
        /// The name of the property that will be filled in by the column associated with this selection set entry
        /// </summary>
        internal string _propertyName;
        /// <summary>
        /// A reference to the DB table name and class type
        /// </summary>
        internal TableNameAndType _tableNameAndType;
    }

    /// <summary>
    /// Selection criteria for retrieving specific columns from a single table
	/// Not very well tested/not frequently utilized - may not be completely functional
    /// </summary>
    /// <typeparam name="SS"></typeparam>
    [Serializable]
    [DesignTimeVisible(true)]
    [AspNetHostingPermission(SecurityAction.Demand, Level = AspNetHostingPermissionLevel.Medium)]
    public class TableSelectionSet<SS> : ICreateSelect where SS : DBLayer<SS>, IDBLayer<SS>, new()
    {
        /// <summary>
        /// Select * from this table?
        /// </summary>
        private bool _selectAll = true;

        /// <summary>
        /// Reference to the table from which values will be retrieved
        /// </summary>
        internal TableNameAndType _tableInfo;

        /// <summary>
        /// A list of columns (selection entries) to select
        /// </summary>
        internal List<SelectionEntry> _selectionSet;
        
        /// <summary>
        /// Constructor - fills in the table name based on the table selection set generic class type
        /// </summary>
        public TableSelectionSet()
        {
            _tableInfo = new TableNameAndType(this);
            _tableInfo._tableName = DBLayer<SS>.CachedAttributes.DBTableNameForClass;
            _tableInfo._tableType = typeof(SS);
        }

        /// <summary>
        /// Returns true if all columns in the table are to be selected, false if only
        /// specific columns will be selected.
        /// </summary>
        public bool SelectAll
        {
            get { return _selectAll; }
            set { if (value == true) { _selectionSet = null; } _selectAll = value; }
        }

        /// <summary>
        /// Returns the actual T-SQL SELECT clause for retrieving certain columns from the table
        /// </summary>
        /// <returns></returns>
        public string GetSelectClause()
        {
            if (_selectAll) return _tableInfo._tableName + ".*";
            if (_selectionSet == null) return "";
            StringBuilder sb = new StringBuilder(256);
            bool fFirst = true;
            for (int i = 0; i < _selectionSet.Count; i++)
            {
                SelectionEntry sel = _selectionSet[i];
                if (fFirst) { fFirst = false; }
                else { sb.Append(", "); }
                if (sel._cachedPropertyDescriptor != null) { sb.Append(_tableInfo._tableName + "." + sel._dbColumnName); }
                else { sb.Append(sel._dbColumnName); }

                // Support for AS  (i.e. COUNT(*) AS CNT)
                if (!string.IsNullOrEmpty(sel._resultAS)) { sb.Append(" AS " + sel._resultAS); }
            }
            return sb.ToString();
        }

        /// <summary>
        /// Add a column to be retrieved from the table
        /// </summary>
        /// <param name="propertyName"></param>
        /// <returns></returns>
        public bool Add(string propertyName)
        {
			string columnName = DBLayer<SS>.CachedAttributes.GetDBColumnName(propertyName);
            if (string.IsNullOrEmpty(columnName)) { Debug.Assert(false, "No ColumnName attribute has been set for the property " + propertyName); return false; }
            return Add(propertyName, null, true);
        }

        /// <summary>
        /// Add a compound statement (i.e. COUNT(*)) and an "AS" clause (i.e. AS RowCount)
        /// </summary>
        /// <param name="sCompoundStatement"></param>
        /// <param name="sResultAS"></param>
        /// <returns></returns>
        public bool Add(string compoundStatement, string resultAS)
        {
			string columnName = DBLayer<SS>.CachedAttributes.GetDBColumnName(compoundStatement);
            if (!string.IsNullOrEmpty(columnName)) { return Add(compoundStatement, resultAS, true); }
            else return Add(compoundStatement, resultAS, false);
        }

        /// <summary>
        /// Add a compound statement (i.e. COUNT(*)) and an "AS" clause (i.e. AS RowCount), with
        /// the option to specify whether or not to try to put the result (RowCount) into a property
        /// or field called "RowCount" on the business object class type
        /// </summary>
        /// <param name="sCompoundStatement"></param>
        /// <param name="sResultAS"></param>
        /// <param name="fIpropertyName"></param>
        /// <returns></returns>
        internal bool Add(string compoundStatement, string resultAS, bool compoundStatementIsPropertyNameName)
        {
            if (_selectAll) _selectAll = false;
            if (_selectionSet == null) _selectionSet = new List<SelectionEntry>(8);
            SelectionEntry sel;
            for (int i = 0; i < _selectionSet.Count; i++)
            {
                sel = _selectionSet[i];
                if ((sel._dbColumnName != null && compoundStatement.Equals(sel._dbColumnName, StringComparison.OrdinalIgnoreCase)) ||
                    (sel._resultAS != null && compoundStatement.Equals(sel._resultAS, StringComparison.OrdinalIgnoreCase)))
                {
                    // duplicate entry
                    return false;
                }
            }
            sel = new SelectionEntry();
            sel._dbColumnName = compoundStatement;
            if (compoundStatementIsPropertyNameName)
            {
                sel._propertyName = compoundStatement;
                sel._cachedPropertyDescriptor = DBLayer<SS>.CachedAttributes.GetPropertyDescriptor(compoundStatement);
            }
            sel._resultAS = resultAS;
            sel._tableNameAndType = _tableInfo;
            _selectionSet.Add(sel);
            return true;
        }
    }

    /// <summary>
    /// Selection set allows queries to return a subset of columns and also allows for computed columns
    /// (i.e. COUNT(*) AS CurrentCount, MAX(UserNumber) AS MaxUserNum).  This class also generates
    /// the SQL "FROM tbl1, tbl2, etc" statement.
    /// NOTE: There is preliminary support for multi-table selects, but it is not yet fully implemented
    /// </summary>
    [Serializable, DesignTimeVisible(true), AspNetHostingPermission(SecurityAction.Demand, Level = AspNetHostingPermissionLevel.Medium)]
    public class SelectionSet : ICreateSelect
    {
        private bool _selectAll = false;
        private static SelectionSet SharedSelectAll = new SelectionSet(true);
        public static SelectionSet All { get { return SharedSelectAll; } set { SharedSelectAll = value; } }
        public SelectionSet(bool wantSelectAll) { _selectAll = wantSelectAll; }
        public static SelectionSet SelectAllColumns<T>() where T : DBLayer<T>, IDBLayer<T>, new()
        {
            SelectionSet ss = new SelectionSet();
            ss.Add<T>(true);
            return ss;
        }
        public bool SelectAll
        {
            get { return _selectAll; }
            set { _selectAll = value; }
        }

        /// <summary>
        /// Prelim support for multi-table selects
        /// </summary>
        internal List<TableNameAndType> _tableSelectionSets = new List<TableNameAndType>(1);

        /// <summary>
        /// Returns a FROM clause based on the tables needed to populate the selection set
        /// </summary>
        /// <returns></returns>
        internal string GetFromClause()
        {
            StringBuilder sb = new StringBuilder(256);
            bool fFirst = true;
            for (int i = 0; i < _tableSelectionSets.Count; i++)
            {
                if (fFirst) { fFirst = false; } else { sb.Append(", "); }
                sb.Append(_tableSelectionSets[i]._tableName);
            }
            return sb.ToString();
        }

        /// <summary>
        /// Returns the full selection clause of the T-SQL statement
        /// </summary>
        /// <returns></returns>
        public string GetSelectClause()
        {
            StringBuilder sb = new StringBuilder(256);
            bool fFirst = true;
            if (SelectAll) return "*";
            for (int i = 0; i < _tableSelectionSets.Count; i++)
            {
                ICreateSelect makeSelect = _tableSelectionSets[i]._tableSelectionSet;
                string s = makeSelect.GetSelectClause();
                if (!string.IsNullOrEmpty(s))
                {
                    if (fFirst) { fFirst = false; } else { sb.Append(", "); }
                    sb.Append(s);
                }
            }
            return sb.ToString();
        }

        public SelectionSet() { }

        /// <summary>
        /// Finds the selection set used to select columns from a specific table
        /// </summary>
        /// <typeparam name="ABCD"></typeparam>
        /// <returns></returns>
        public TableSelectionSet<ABCD> FindTableSelectionSet<ABCD>() where ABCD : DBLayer<ABCD>, IDBLayer<ABCD>, new()
        {
            TableSelectionSet<ABCD> selectionSet = null;
            for (int i = 0; i < _tableSelectionSets.Count; i++)
            {
                TableNameAndType tnt = _tableSelectionSets[i];
                if (tnt._tableType == typeof(ABCD))
                {
                    selectionSet = (TableSelectionSet<ABCD>)tnt._tableSelectionSet;
                    return selectionSet;
                }
            }
            return selectionSet;
        }

        /// <summary>
        /// Adds a selection set used to select columns from a specific table
        /// </summary>
        /// <typeparam name="ABCD"></typeparam>
        /// <returns></returns>
        public TableSelectionSet<ABCD> Add<ABCD>() where ABCD : DBLayer<ABCD>, IDBLayer<ABCD>, new()
        {
            TableSelectionSet<ABCD> selectionSet = FindTableSelectionSet<ABCD>();
            if (selectionSet == null) { selectionSet = new TableSelectionSet<ABCD>(); _tableSelectionSets.Add(selectionSet._tableInfo); }
            return selectionSet;
        }

        /// <summary>
        /// Select * from a specific table
        /// </summary>
        /// <typeparam name="ABCD"></typeparam>
        /// <param name="fSelectAll"></param>
        /// <returns></returns>
        public TableSelectionSet<ABCD> Add<ABCD>(bool fSelectAll) where ABCD : DBLayer<ABCD>, IDBLayer<ABCD>, new()
        {
            TableSelectionSet<ABCD> selectionSet = FindTableSelectionSet<ABCD>();
            if (selectionSet == null) { selectionSet = new TableSelectionSet<ABCD>(); _tableSelectionSets.Add(selectionSet._tableInfo); }
            selectionSet.SelectAll = fSelectAll;
            return selectionSet;
        }

        /// <summary>
        /// Select a property from a specific table
        /// </summary>
        /// <typeparam name="ABCD"></typeparam>
        /// <param name="propertyName"></param>
        /// <returns></returns>
        public TableSelectionSet<ABCD> Add<ABCD>(string propertyName) where ABCD : DBLayer<ABCD>, IDBLayer<ABCD>, new()
        {
            TableSelectionSet<ABCD> selectionSet = FindTableSelectionSet<ABCD>();
            if (selectionSet == null) { selectionSet = new TableSelectionSet<ABCD>(); _tableSelectionSets.Add(selectionSet._tableInfo); }
            selectionSet.Add(propertyName);
            return selectionSet;
        }

        /// <summary>
        /// Use a compound statement to return a column from a SQL query and
        /// store the results in column named "sResultAS"
        /// </summary>
        /// <typeparam name="ABCD"></typeparam>
        /// <param name="sCompoundStatement"></param>
        /// <param name="sResultAS"></param>
        /// <returns></returns>
        public TableSelectionSet<ABCD> Add<ABCD>(string sCompoundStatement, string sResultAS) where ABCD : DBLayer<ABCD>, IDBLayer<ABCD>, new()
        {
            TableSelectionSet<ABCD> selectionSet = FindTableSelectionSet<ABCD>();
            if (selectionSet == null) { selectionSet = new TableSelectionSet<ABCD>(); _tableSelectionSets.Add(selectionSet._tableInfo); }
            selectionSet.Add(sCompoundStatement, sResultAS);
            return selectionSet;
        }
    }
}