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

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

namespace BrainTechLLC.DAL
{
	public partial class DBLayer<T> : INotifyPropertyChanged where T : DBLayer<T>, IDBLayer<T>, new()
	{
		// Functionality related to retrieving specific data from the database

		/// <summary>
		/// Routes a database query to the appropriate transport-specific method, depending on the class' data transport type
		/// </summary>		
		internal static ResultSet<T> RouteDBQuery(string sqlStatement, SelectionSet selectionSet, List<SqlParameter> sqlParameters, out Exception exOut, out string message)
		{
			exOut = null;
			message = "";

			DALQueryInfo.AddMessage("FROM DB: " + sqlStatement);

			if (CacheAndPerformance._trackConcurrentDBQueries) CacheAndPerformance._databaseHitCount++;
			return ConnectionProvider.SQLLoadRows(sqlStatement, selectionSet, (sqlParameters == null) ? null : sqlParameters.ToArray(), out exOut, out message);
		}

		/// <summary>
		/// Retrieves data from the database (or level-1 WHERE cache)
		/// </summary>		
		internal static ResultSet<T> GetRowsFromDB(SelectionSet selectionSet, DBWhere whereClause, OrderBy<T> orderBy, out bool fetchedFromQueryCache, string prebuiltSqlStatement, bool populatingCache)
		{
			EnsureInitialized();

			List<SqlParameter> sqlParameters = null;
			ResultSet<T> results = null;

			string sqlStatement = (prebuiltSqlStatement != null) ? prebuiltSqlStatement : GetTSQLStatement(selectionSet, whereClause, orderBy, out sqlParameters);

			if (sqlParameters == null && !populatingCache)
			{
				// Note - the BeforeDALRead event may be raised several times... TODO: fix this oversight in some elegant manner
				OnBeforeDALRead(whereClause);

				// Look in level-1 "where" cache
				results = WhereCache.FindInWhereCache(sqlStatement);

				if (results != null)
				{
					fetchedFromQueryCache = true;
					return results;
				}
			}

			// Not found in level 1 cache - go to the database
			fetchedFromQueryCache = false;
			Exception exOut = null;
			string message = "";

			results = RouteDBQuery(sqlStatement, selectionSet, sqlParameters, out exOut, out message);
			
			// Look at any exception that was thrown
			if (exOut != null)
			{
				DALQueryInfo.AddMessage("Exception executing " + sqlStatement + ": " + exOut.Message);
			}
			else if (sqlParameters == null && !WhereCache.IsStatementInCache(sqlStatement))
			{
				// if the WHERE clause has non-standard (additional) requirements that were not handled by the SQL statement,
				// handle them here.  Better solution would probably be to enhance T-SQL statement generation to handle all possible where logic cases
				if (whereClause != null && whereClause._hasNonStandardRequirements)
				{
					string recommendedSort = null;
					results = DBLayer<T>.QueryResultSet(results, whereClause, false, ref recommendedSort);
				}

				if (!populatingCache)
				{
					// If we did not execute this read specifically to populate the cache...
					int nResultRows = results.RowCount;

					// Add the exact SQL select statement to the level 1 cache (if there were no parameters)
					DBLayer<T>.WhereCache.RecordInWhereCache(sqlStatement, results, nResultRows);

					if (Cache.CacheType == CacheType.GrowingCache && !Cache.RetrievedAllRows &&
						DBLayer<T>.CacheAndPerformance._trackConcurrentDBQueries &&
						!DBLayer<T>.Cache.RetrievedAllRows &&
						DBLayer<T>.CacheAndPerformance._cacheHitCount == 0 &&
						DBLayer<T>.CacheAndPerformance._databaseHitCount > DBLayer<T>.CacheAndPerformance._dbHitsBeforeFullPopulate)
					{
						bool retrFromC;
						ResultSet<T> rgAll = DBLayer<T>.GetRowsFromDB(SelectionSet.All, null, null, out retrFromC, null, true);

						Cache.LockCache();
						try { Cache.HandleGrowingCache(rgAll, null); }
						finally { Cache.UnlockCache(); }
					}
				}
			}

			return results;
		}
	}
}