Rapid Web Application Development

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;


namespace rasp.SQL.DBInfo {
	/// <summary>
	/// The lookup info class is an Object Oriented way of looking at how data should be queried and bound to a dropdown.
	/// </summary>
	/// <remarks>
	/// The lookup info ultimetly creates a select statement for the data that will be bound to a dropdown. This statement selects two columns
	/// as ID and Name to bind to a dropdown.
	/// </remarks>
	public class LookupInfo {
		private string _SelectStatement;
		private string _SourceTable;
		private string _IDFields;
		private string _ValueFields;
		private string _IDFormula;
		private string _ValueFormula;
		private string _IDValue;

		/// <summary>
		/// Uses the LookupInfoID to query _Lookups and initialize the lookup info with the row specified.
		/// </summary>
		/// <param name="LookupInfoID">The ID from _Lookups</param>
		public LookupInfo(int LookupInfoID) {
			string select = "Select *, (select name from _TABLES where _LOOKUPS._TABLES_ID = _TABLES.ID) as TableName from _LOOKUPS where ID = " + LookupInfoID;
			string ErrorMessage = "Cannot find a lookup info row in _LOOKUPS with the id of: " + LookupInfoID;
			DataSet ds = SQLUtilities.SelectData(select);
			if(ds.Tables.Count > 0) {
				if (ds.Tables[0].Rows.Count > 0) {
					InitializeLookupInfo(ds.Tables[0].Rows[0]);
				} else {					
					throw new Exception(ErrorMessage);
				}
			} else {
				throw new Exception(ErrorMessage);
			}
		}		

		/// <summary>
		/// Creates a LookupInfo with a datarow from the _Lookups table
		/// </summary>
		/// <param name="dr">A datarow from the _Lookups table</param>
		public LookupInfo(DataRow dr) {
			InitializeLookupInfo(dr); 
		}

		/// <summary>
		/// Generates a LookupInfo based off of standard naming conventions. This means that the specified table has an ID and Name field.
		/// If this is true then we will use this info accordingly and create a lookup info object.
		/// </summary>
		/// <param name="TableName">The name of the table to lookup in.</param>
		public LookupInfo(string TableName) {
			_SourceTable = TableName;
			InitializeLookupInfo();
		}

		/// <summary>
		/// Initalizes the LookupInfo with a datarow from the _Lookups table
		/// </summary>
		/// <param name="dr">A datarow from the _Lookups table</param>
		private void InitializeLookupInfo(DataRow dr) {
			_SourceTable = TextUtilities.AssignNullString(dr,"TableName","");

			_IDValue = TextUtilities.AssignNullString(dr,"IDValue","");

			_IDFields = TextUtilities.AssignNullString(dr,"IDFields","ID");			
			_IDFormula = TextUtilities.AssignNullString(dr,"IDFormula","ID as ID");

			_ValueFields = TextUtilities.AssignNullString(dr,"ValueFields","Name");
			_ValueFormula = TextUtilities.AssignNullString(dr,"ValueFormula","Name as [Name]");
			
		}

		/// <summary>
		/// Generates a LookupInfo based off of standard naming conventions. This means that the specified table has an ID and Name field.
		/// If this is true then we will use this info accordingly and create a lookup info object.
		/// </summary>
		private void InitializeLookupInfo() {
			//use the _TableInformation to generate a default deal	
			
			_IDValue = "";

			_IDFields = "ID";
			_IDFormula = "ID as ID";

			_ValueFields = "Name";
			_ValueFormula = "Name as [Option]";

		}

    /// <summary>
    /// Generates the select statement for the lookup. Uses the id and value fields and substitutes them into the
    /// appropriate formula, then orders the results by the field that appears <i>first</i> value field list.
    /// </summary>
		public string SelectStatement {
			get {
				if (_SelectStatement == null) {
					//Prepare the statement
					string select = "select ";
					string idField = TextUtilities.SubOutFields(_IDFormula, _IDFields.Split(',')) + ", ";
					string valueField = TextUtilities.SubOutFields(_ValueFormula, _ValueFields.Split(',')) + " ";          
          string from = "from " + _SourceTable + " ";
					string orderBy = "order by " +  _ValueFields.Split(',')[0];
					_SelectStatement = select + idField + valueField + from + orderBy;
				}
				return _SelectStatement;
			}
		}

		/// <summary>
		/// The source table for the lookup.
		/// </summary>
		public string SourceTable {
			get {
				return _SourceTable;
			}
			set {
				_SourceTable = value;
			}
		}
		/// <summary>
		/// A comma seperated list of fields that will be used for the ID of the lookup.
		/// </summary>
		public string IDFields {
			get {
				return _IDFields;
			}
			set {
				_IDFields = value;
			}
		}
		/// <summary>
		/// A comma seperated list of fields that will be used for the value of the lookup.
		/// </summary>
		public string ValueFields {
			get {
				return _ValueFields;
			}
			set {
				_ValueFields = value;
			}
		}
		/// <summary>
		/// A formula to be used for the ID of the lookup.
		/// </summary>
		/// <example>
		/// {0} as [ID]
		/// </example>
		public string IDFormula {
			get {
				return _IDFormula;
			}
			set {
				_IDFormula = value;
			}
		}
		/// <summary>
		/// A formula to be used for the value of the lookup.
		/// </summary>
		/// <example>
		/// {0} as [Name]
		/// </example>
		public string ValueFormula {
			get {
				return _ValueFormula;
			}
			set {
				_ValueFormula = value;
			}
		}
		/// <summary>
		/// I don't know what this is for, it's used in lots of important things, but at this moment, I can't remember
		/// why I use it.
		/// </summary>
		public string IDValue {
			get {
				return _IDValue;
			}
			set {
				_IDValue = value;
			}
		}
	}
	
}