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

using System;
using System.Collections;
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()
	{
		/// <summary>
		/// Retrieves a complete T-SQL statement for a given selection set, where logic, and order by directions
		/// </summary>
		/// <param name="selectionSet"></param>
		/// <param name="whereClause"></param>
		/// <param name="orderBy"></param>
		/// <param name="sqlParameters"></param>
		/// <returns></returns>
		public static string GetTSQLStatement(SelectionSet selectionSet, DBWhere whereClause, OrderBy<T> orderBy, out List<SqlParameter> sqlParameters)
		{
			sqlParameters = null;
			string tableName = CachedAttributes.DBTableNameForClass;
			Debug.Assert(tableName != null);

			StringBuilder sb = new StringBuilder(512);

			// handle selection of columns.  SelSet of null = *, otherwise column names manually added
			sb.Append("SELECT ");
			sb.Append((selectionSet == null) ? "*" : selectionSet.GetSelectClause());

			// add on the table name(s)
			sb.Append(" FROM ");
			sb.Append((selectionSet == null || selectionSet.SelectAll == true) ? tableName : selectionSet.GetFromClause());

			if (whereClause != null)
			{
				// generates the WHERE clause, outputting an array of parameters, if found
				string sWhere = ConstructSqlWhereStatement(whereClause, out sqlParameters);
				if (!string.IsNullOrEmpty(sWhere)) { sb.Append(" WHERE ("); sb.Append(sWhere); sb.Append(")"); }
			}

			// append any ORDER BY requested
			if (orderBy != null) { sb.Append(orderBy.GetOrderByClause()); }

			return sb.ToString();
		}

		/// <summary>
		/// Takes an array of items of type T and creates a SQL IN "(item1, item2, item3, ....)" clause.
		/// </summary>
		/// <typeparam name="T">The type of items being passed in</typeparam>
		/// <param name="itemCollection">The array of items</param>
		/// <param name="itemCount">The number of items</param>
		/// <param name="noDuplicates">True if no duplicate IN items should be generated (note: each item must be convertible to a string)</param>
		/// <param name="wantEncloseInQuotes">Enclose each item in quotes - i.e. IN "('item1', 'item2', item3', ...)"</param>
		/// <returns>Returns a string starting with ( containing the set of items, and ending with a ) - perfect for use in a SQL IN (item1, item2, ...) clause</returns>
		public static string CreateInSet<A>(A[] itemCollection, int itemCount, bool noDuplicates, bool wantEncloseInQuotes)
		{
			Debug.Assert(itemCount <= itemCollection.Length);
			bool fFirst = true;
			StringBuilder sb = new StringBuilder(1024);
			Hashtable ht = new Hashtable(noDuplicates ? itemCount : 1);
			sb.Append("(");
			for (int i = 0; i < itemCount; i++)
			{
				string s = itemCollection[i].ToString();
				if (noDuplicates && ht.ContainsKey(s)) continue;
				if (noDuplicates) ht.Add(s, null);
				if (fFirst) { fFirst = false; }
				else sb.Append(",");
				if (wantEncloseInQuotes) { sb.Append("'" + s + "'"); } else { sb.Append(s); }
			}
			sb.Append(")");
			return sb.ToString();
		}

		/// <summary>
		/// Returns a string that is (slightly more) safe from SQL injection attacks (and handles ')
		/// </summary>		
		public static string DBSafeString(string s)
		{
			string returnString = s.Replace("'", "''");
			// removed for HTML support - NOTE: this may be a security hole...
			// returnString = returnString.Replace("<", "");
			return returnString;
		}
	}
}