Developing Next Generation Smart Clients using .NET 2.0 working with Existing .NET 1.1 SOA-based XML Web Services

/*
 * $Id: CommonSqlCode.cs,v 1.19 2005/03/05 21:00:15 jroland Exp $
 * Last modified by $Author: jroland $
 * Last modified at $Date: 2005/03/05 21:00:15 $
 * $Revision: 1.19 $
 */
 
/*
	Common SQL related code generation methods
	Created: 12/30/03 by Oskar Austegard
	
	9/17/2004 - Dave Kekish 
	Changed sql to c# conversion for decimal type from Single to a Decimal.
	You cannot implicitly convert a objet to a Single.  
	see http://www.gotdotnet.com/Community/MessageBoard/Thread.aspx?id=263704
	
	01/26/05 - ab
	added isIntXX(), a convenience method	
*/



using CodeSmith.Engine;
using SchemaExplorer;
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;
using System.Diagnostics;

namespace MoM.Templates

{
	/// <summary>
	/// Common code-behind class used to simplify SQL Server based CodeSmith templates
	/// </summary>
	public class CommonSqlCode : CodeTemplate
	{
		
// [ab 012605] convenience array for checking if a datatype is an integer 
		private readonly static DbType[] aIntegerDbTypes = new DbType[] {DbType.Int16,DbType.Int32, DbType.Int64 };
		
		/// <summary>
		/// Return a specified number of tabs
		/// </summary>
		/// <param name="n">Number of tabs</param>
		/// <returns>n tabs</returns>
		public string Tab(int n)
		{
			return new String('\t', n);
		}

		/// <summary>
		/// Get the safe name for a data object by determining if it contains spaces or other illegal
		/// characters - if so wrap with []
		/// </summary>
		/// <param name="schemaObject">Database schema object (e.g. a table, stored proc, etc)</param>
		/// <returns>The safe name of the object</returns>
		public string GetSafeName(SchemaObjectBase schemaObject)
		{
			return GetSafeName(schemaObject.Name);
		}

		/// <summary>
		/// Get the safe name for a data object by determining if it contains spaces or other illegal
		/// characters - if so wrap with []
		/// </summary>
		/// <param name="objectName">The name of the database schema object</param>
		/// <returns>The safe name of the object</returns>
		public string GetSafeName(string objectName)
		{
			return objectName.IndexOfAny(new char[]{' ', '@', '-', ',', '!'}) > -1 ? "[" + objectName + "]" : objectName;
		}

		/// <summary>
		/// Get the camel cased version of a name.  
		/// If the name is all upper case, change it to all lower case
		/// </summary>
		/// <param name="name">Name to be changed</param>
		/// <returns>CamelCased version of the name</returns>
		public string GetCamelCaseName(string name)
		{
			if (name.Equals(name.ToUpper()))
				return name.ToLower();
			else
				return name.Substring(0, 1).ToLower() + name.Substring(1);
		}
		
		/// <summary>
		/// Get the Pascal cased version of a name.  
		/// </summary>
		/// <param name="name">Name to be changed</param>
		/// <returns>PascalCased version of the name</returns>
		public string GetPascalCaseName(string name)
		{		
			return name.Substring(0, 1).ToUpper() + name.Substring(1);
		}
		
		/// <summary>
		/// Remove any non-word characters from a SchemaObject's name (word characters are a-z, A-Z, 0-9, _)
		/// so that it may be used in code
		/// </summary>
		/// <param name="schemaObject">DB Object whose name is to be cleaned</param>
		/// <returns>Cleaned up object name</returns>
		public string GetCleanName(SchemaObjectBase schemaObject)
		{
			return GetCleanName(schemaObject.Name);
		}
		
		
		
		
		#region Business object class name		
		public string GetAbstractClassName(string tableName)
		{
			return string.Format("{0}Base", GetClassName(tableName));
		}

		public string GetPartialClassName(string tableName)
		{
			return string.Format("{0}.generated", GetClassName(tableName));
		}
		
		public string GetEnumName(string tableName)
		{
			return string.Format("{0}", GetClassName(tableName).Replace("Enum", ""));
		}
		
		// Create a class name from a table name, for a business object
		public string GetClassName(string tableName)
		{
			// 1.remove space or bad characters
			string name = GetCleanName(tableName);
			
			// 2. Set Pascal case
			name = GetPascalCaseName(name);
			
			// 3. Remove any plural - Experimental, need more grammar analysis//ref: http://www.gsu.edu/~wwwesl/egw/crump.htm
			ArrayList invariants = new ArrayList();
			invariants.Add("alias");
			
			
			if (invariants.Contains(name.ToLower()))
			{
				return name;
			}
			else if (name.EndsWith("ies"))
			{
				return name.Substring(0, name.Length-3) + "y";
			}
			else if (name.EndsWith("s") && !(name.EndsWith("ss") || name.EndsWith("us")))
			{
				return name.Substring(0, name.Length-1);
			}
			else
				return name;			
		}		
		#endregion
		
		
		# region collection class name
		public string GetAbstractCollectionClassName(string tableName)
		{
			return string.Format("{0}Base", GetCollectionClassName(tableName));
		}
		public string GetCollectionClassName(string tableName)
		{
			return string.Format("{0}Collection", GetClassName(tableName));
		}
		#endregion

		
		#region Factory class name
		public string GetAbstractRepositoryClassName(string tableName)
		{
			return string.Format("{0}Base", GetRepositoryClassName(tableName));
		}
		
		public string GetRepositoryClassName(string tableName)
		{
			return string.Format("{0}Repository", GetClassName(tableName));
		}
		public string GetRepositoryInterfaceName(string tableName)
		{
			return string.Format("I{0}Repository", GetClassName(tableName));
		}
		public string GetRepositoryTestClassName(string tableName)
		{
			return string.Format("{0}RepositoryTest", GetClassName(tableName));
		}
		#endregion
		

		/// <summary>
		/// Remove any non-word characters from a name (word characters are a-z, A-Z, 0-9, _)
		/// so that it may be used in code
		/// </summary>
		/// <param name="name">name to be cleaned</param>
		/// <returns>Cleaned up object name</returns>
		public string GetCleanName(string name)
		{
			return Regex.Replace(name, @"[\W]", "");
		}

		/// <summary>
		/// Get the cleaned many to many relationship name camelcased version of a name
		/// </summary>
		/// <param name="name">name to be cleaned</param>
		/// <returns>the cleaned, camelcased name </returns>
		public string GetManyToManyName(string table1, string table2)
		{
			string manyToManyTableSeperator = "_From_";
			return string.Format("{1}{0}{2}", manyToManyTableSeperator, GetClassName(table1), GetClassName(table2));
		}

		/// <summary>
		/// Get the cleaned, camelcased name of a parameter
		/// </summary>
		/// <param name="par">Command Parameter</param>
		/// <returns>the cleaned, camelcased name </returns>
		public string GetCleanParName(ParameterSchema par)
		{
			return GetCleanParName(par.Name);
		}

		/// <summary>
		/// Get the cleaned, camelcased version of a name
		/// </summary>
		/// <param name="name">name to be cleaned</param>
		/// <returns>the cleaned, camelcased name </returns>
		public string GetCleanParName(string name)
		{
			return GetCamelCaseName(GetCleanName(name));
		}

		/// <summary>
		/// Get the member variable styled version of a name
		/// </summary>
		/// <param name="name">name to be cleaned</param>
		/// <returns>the cleaned, camelcased name with a _ prefix</returns>
		public string GetMemberVariableName(string name)
		{
			return "_" + GetCleanParName(name);
		}

		/// <summary>
		/// Get the description ext. property of a column and return as inline SQL comment
		/// </summary>
		/// <param name="schemaObject">Any database object, but typically a column</param>
		/// <returns>Object description, as inline SQL comment</returns>
		public string GetColumnSqlComment(SchemaObjectBase schemaObject)
		{
			return schemaObject.Description.Length > 0 ? "-- " + schemaObject.Description : "";
		}

		/// <summary>
		/// Check if a column is an identity column
		/// </summary>
		/// <param name="column">DB table column to be checked</param>
		/// <returns>Identity?</returns>
		public bool IsIdentityColumn(ColumnSchema column)
		{
			return (bool)column.ExtendedProperties["CS_IsIdentity"].Value;
		} 
		
		public bool IsReadOnlyColumn(ColumnSchema column)
		{
			return (bool)column.ExtendedProperties["CS_ReadOnly"].Value;
		}

		/// <summary>
		/// Get the owner of a table
		/// </summary>
		/// <param name="table">The table to check</param>
		/// <returns>The safe name of the owner of the table</returns>
		public string GetOwner(TableSchema table)
		{
			return (table.Owner.Length > 0) ? GetSafeName(table.Owner) + "." : "";
		}

		/// <summary>
		/// Get the owner of a command
		/// </summary>
		/// <param name="table">The command to check</param>
		/// <returns>The safe name of the owner of the command</returns>
		public string GetOwner(CommandSchema command)
		{
			return (command.Owner.Length > 0) ? GetSafeName(command.Owner) + "." : "";
		}

		/// <summary>
		/// Does the command have a resultset?
		/// </summary>
		/// <param name="cmd">Command in question</param>
		/// <returns>Resultset?</returns>
		public bool HasResultset(CommandSchema cmd)
		{
			return cmd.CommandResults.Count > 0;
		}

		/// <summary>
		/// Get a SqlParameter statement for a column
		/// </summary>
		/// <param name="column">Column for which to get the Sql parameter statement</param>
		/// <returns>Sql Parameter statement</returns>
		public string GetSqlParameterStatement(ColumnSchema column)
		{
			return GetSqlParameterStatement(column, false);
		}
		
		/// <summary>
		/// Get a SqlParameter statement for a column
		/// </summary>
		/// <param name="column">Column for which to get the Sql parameter statement</param>
		/// <param name="isOutput">Is this an output parameter?</param>
		/// <returns>Sql Parameter statement</returns>
		public string GetSqlParameterStatement(ColumnSchema column, bool isOutput)
		{
			string param = "@" + column.Name + " " + column.NativeType;
			
			switch (column.DataType)
			{
				case DbType.Decimal:
				{
					if (column.NativeType != "real")
						param += "(" + column.Precision + ", " + column.Scale + ")";
				
					break;
				}
				case DbType.AnsiString:
				case DbType.AnsiStringFixedLength:
				case DbType.String:
				case DbType.StringFixedLength:
				{
					if (column.NativeType != "text" && column.NativeType != "ntext")
					{
						if (column.Size > 0)
						{
							param += "(" + column.Size + ")";
						}
					}
					break;
				}
			}
			
			if (isOutput)
			{
				param += " OUTPUT";
			}
			
			return param;
		}
		/// <summary>
		/// Get a SqlParameter statement for a column
		/// </summary>
		/// <param name="column">Column for which to get the Sql parameter statement</param>
		/// <param name="isOutput">Is this an output parameter?</param>
		/// <returns>Sql Parameter statement</returns>
		public string GetSqlParameterStatement(ColumnSchema column, string Name)
		{
			string param = "@" + Name + " " + column.NativeType;
			
			switch (column.DataType)
			{
				case DbType.Decimal:
				{
					param += "(" + column.Precision + ", " + column.Scale + ")";
					break;
				}
				case DbType.AnsiString:
				case DbType.AnsiStringFixedLength:
				case DbType.String:
				case DbType.StringFixedLength:
				{
					if (column.NativeType != "text" && column.NativeType != "ntext")
					{
						if (column.Size > 0)
						{
							param += "(" + column.Size + ")";
						}
					}
					break;
				}
			}	
			return param;
		}

		/// <summary>
		/// Parse the text of a stored procedure to retrieve any comment prior to the CREATE PROC construct
		/// </summary>
		/// <param name="commandText">Command Text of the procedure</param>
		/// <returns>The procedure header comment</returns>
		public string GetSqlProcedureComment(string commandText)
		{
			string comment = "";
			// Find anything upto the CREATE PROC statement
			Regex regex = new Regex(@"CREATE[\s]*PROC", RegexOptions.IgnoreCase);	
			comment = regex.Split(commandText)[0];
			//remove comment characters
			regex = new Regex(@"(-{2,})|(/\*)|(\*/)");
			comment = regex.Replace(comment, string.Empty);
			//trim and return
			return comment.Trim();
		}

		/// <summary>
		/// Get any in-line SQL comments on the same lines as parameters
		/// </summary>
		/// <param name="commandText">Command Text of the procedure</param>
		/// <returns>Hashtable of parameter comments, with parameter names as keys</returns>
		public Hashtable GetSqlParameterComments(string commandText)
		{
			Hashtable paramComments = new Hashtable();
			//Get parameter names and comments
			string pattern = @"(?<param>@\w*)[^@]*--(?<comment>.*)";
			//loop through the matches and extract the parameter and the comment, ignoring duplicates
			foreach (Match match in Regex.Matches(commandText, pattern))
				if (!paramComments.ContainsKey(match.Groups["param"].Value))
					paramComments.Add(match.Groups["param"].Value, match.Groups["comment"].Value.Trim());
			//return the hashtable
			return paramComments;
		}
		
		
		#region "Stored procedures input transformations"
		
		/// <summary>
		/// Transform the list of sql parameters to a list of method parameters.
		/// </summary>
		public string TransformStoredProcedureInputsToMethod(ParameterSchemaCollection inputParameters)
		{
			return TransformStoredProcedureInputsToMethod(false, inputParameters);
		}
		
		/// <summary>
		/// Transform the list of sql parameters to a list of method parameters.
		/// </summary>
		public string TransformStoredProcedureInputsToMethod(bool startWithComa, ParameterSchemaCollection inputParameters)
		{
			string temp = string.Empty;
			for(int i=0; i<inputParameters.Count; i++)
			{
				temp += (i>0) || startWithComa ? ", " : "";
				temp += GetCSType(inputParameters[i]) + " " + inputParameters[i].Name.Substring(1, inputParameters[i].Name.Length-1 );
			}
			
			return temp;
		}
		
		/// <summary>
		/// Transform the list of sql parameters to a list of ExecuteXXXXX parameters.
		/// </summary>
		public string TransformStoredProcedureInputsToDataAccess(ParameterSchemaCollection inputParameters)
		{
			return TransformStoredProcedureInputsToDataAccess(false, inputParameters);
		}
		
		/// <summary>
		/// Transform the list of sql parameters to a list of ExecuteXXXXX parameters.
		/// </summary>
		public string TransformStoredProcedureInputsToDataAccess(bool alwaysStartWithaComa, ParameterSchemaCollection inputParameters)
		{
			string temp = string.Empty;
			for(int i=0; i<inputParameters.Count; i++)
			{
				temp += (i>0) || alwaysStartWithaComa ? ", " : "";
				temp += inputParameters[i].Name.Substring(1, inputParameters[i].Name.Length-1 );
			}
			
			return temp;
		}
						
		/// <summary>
		/// Transform the list of sql parameters to a list of comment param for a method
		/// </summary>
		public string TransformStoredProcedureInputsToMethodComments(ParameterSchemaCollection inputParameters)
		{
			string temp = string.Empty;
			for(int i=0; i<inputParameters.Count; i++)
			{
				temp += string.Format("{2}\t/// <param name=\"{0}\"> A <c>{1}</c> instance.</param>", inputParameters[i].Name.Substring(1, inputParameters[i].Name.Length-1 ), GetCSType(inputParameters[i]), Environment.NewLine);
			}
			
			return temp;
		}

		#endregion
/*

		/// <summary>
		/// Convert database types to C# types
		/// </summary>
		/// <param name="dataType">Column or parameter data type</param>
		/// <param name="useNullableTypes">Are NullableTypes used?</param>
		/// <returns>The C# (rough) equivalent of the field's data type</returns>
		public string GetCSType(DbType dataType, bool useNullableTypes)
		{
			if (useNullableTypes)
				return GetNullableType(dataType);
			else
				return GetCSType(dataType);
		}

		/// <summary>
		/// Convert database types to C# types
		/// </summary>
		/// <param name="field">Column or parameter</param>
		/// <param name="useNullableTypes">Are NullableTypes used?</param>
		/// <returns>The C# (rough) equivalent of the field's data type</returns>
		public string GetCSType(DataObjectBase field, bool useNullableTypes)
		{
			return GetCSType(field.DataType, useNullableTypes);
		}

		/// <summary>
		/// Convert database types to C# types
		/// </summary>
		/// <param name="dataType">Column or parameter data type, as a string</param>
		/// <param name="useNullableTypes">Are NullableTypes used?</param>
		/// <returns>The C# (rough) equivalent of the field's data type</returns>
		public string GetCSType(string dataType, bool useNullableTypes)
		{
			try { return GetCSType((DbType)Enum.Parse(typeof(DbType), dataType), useNullableTypes); }
			catch { return "object"; }
		}

		/// <summary>
		/// Convert database types to C# types
		/// </summary>
		/// <param name="dataType">Column or parameter data type</param>
		/// <returns>The C# (rough) equivalent of the field's data type</returns>
		public string GetCSType(DbType dataType)
		{
			switch (dataType)
			{
				case DbType.AnsiString: 
					return "string";
				
				case DbType.AnsiStringFixedLength: 
					return "string";
					
				case DbType.Binary: 
					return "byte[]";
					
				case DbType.Boolean: 
					return "bool";
				
				case DbType.Byte: 
					return "byte";
					
				case DbType.Currency: 
					return "decimal";
				
				case DbType.Date: 
					return "DateTime";
				
				case DbType.DateTime: 
					return "DateTime";
				
				case DbType.Decimal: 
					//return "Single";
					return "decimal";
				
				case DbType.Double: 
					return "double";
				
				case DbType.Guid: 
					return "Guid";
				
				case DbType.Int16: 
					return "short";
					
				case DbType.Int32: 
					return "int";
					
				case DbType.Int64: 
					return "long";
					
				case DbType.Object: 
					return "object";
					
				case DbType.Single: 
					return "decimal";
					
				case DbType.String: 
					return "string";
					
				case DbType.StringFixedLength: 
					return "string";
					
				case DbType.Time: 
					return "DateTime";

				
					
				case DbType.VarNumeric: 
					return "decimal";
					
					//the following won't be used
					//			case DbType.SByte: return "sbyte";
					//			case DbType.UInt16: return "ushort";
					//			case DbType.UInt32: return "uint";
					//			case DbType.UInt64: return "ulong";
				default: return "object";
			}
		}
*/
		/// <summary>
		/// Convert database types to C# types
		/// </summary>
		/// <param name="field">Column or parameter</param>
		/// <returns>The C# (rough) equivalent of the field's data type</returns>
		public string GetCSType(DataObjectBase field)
		{
			//return field.NativeType;
			if (field.NativeType.ToLower() == "real")
				return "System.Single";
			else
				return field.SystemType.ToString();
			//return GetCSType(field.DataType);
		}

/*
		/// <summary>
		/// Convert database types to C# types
		/// </summary>
		/// <param name="dataType">Column or parameter data type, as a string</param>
		/// <returns>The C# (rough) equivalent of the field's data type</returns>
		public string GetCSType(string dataType)
		{
			try { return GetCSType((DbType)Enum.Parse(typeof(DbType), dataType)); }
			catch { return "object"; }
		}
*/
		/// <summary>
		/// Convert db types to NullableTypes
		/// </summary>
		/// <param name="dataType">Column or parameter data type</param>
		/// <returns>The NullableType (rough) equivalent of the field's data type</returns>
		public string GetNullableType(DbType dataType)
		{
			switch (dataType)
			{
				case DbType.AnsiString: return "NullableString";
				case DbType.AnsiStringFixedLength: return "NullableString";
				case DbType.Binary: return "NullableByte[]";
				case DbType.Boolean: return "NullableBoolean";
				case DbType.Byte: return "NullableByte";
				case DbType.Currency: return "NullableDecimal";
				case DbType.Date: return "NullableDateTime";
				case DbType.DateTime: return "NullableDateTime";
				case DbType.Decimal: return "NullableDecimal";
				case DbType.Double: return "NullableDouble";
				case DbType.Guid: return "NullableGuid";
				case DbType.Int16: return "NullableInt16";
				case DbType.Int32: return "NullableInt32";
				case DbType.Int64: return "NullableInt64";
				case DbType.Object: return "object";
				case DbType.Single: return "NullableSingle";
				case DbType.String: return "NullableString";
				case DbType.StringFixedLength: return "NullableString";
				case DbType.Time: return "NullableDateTime";
				case DbType.VarNumeric: return "NullableDecimal";
					//the following won't be used
					//		case DbType.SByte: return "NullableSByte";
					//		case DbType.UInt16: return "NullableUShort";
					//		case DbType.UInt32: return "NullableUInt";
					//		case DbType.UInt64: return "NullableULong";
				default: return "object";
			}
		}


		/// <summary>
		/// Convert db types to NullableTypes
		/// </summary>
		/// <param name="field">Column or parameter</param>
		/// <returns>The NullableType (rough) equivalent of the field's data type</returns>
		public string GetNullableType(DataObjectBase field)
		{
			return GetNullableType(field.DataType);
		}

		/// <summary>
		/// Convert db types to NullableTypes
		/// </summary>
		/// <param name="dataType">Column or parameter data type, as a string</param>
		/// <returns>The NullableType (rough) equivalent of the field's data type</returns>
		public string GetNullableType(string dataType)
		{
			try { return GetNullableType((DbType)Enum.Parse(typeof(DbType), dataType)); }
			catch { return "object"; }
		}


/*
		/// <summary>
		/// Get a default value for a given field's data type
		/// </summary>
		/// <param name="field">The field for which to get the default value</param>
		/// <returns>A string representation of the default value</returns>
		public string GetDefaultByType(DataObjectBase field)
		{
			return GetDefaultByType(field.DataType);
		}

		/// <summary>
		/// Get a default value for a given data type name
		/// </summary>
		/// <param name="dataType">String name of the data type for which to get the default value<</param>
		/// <returns>A string representation of the default value</returns>
		public string GetDefaultByType(string dataType)
		{
			try { return GetDefaultByType((DbType)Enum.Parse(typeof(DbType), dataType)); }
			catch { return "null"; }
		}

		/// <summary>
		/// Get a default value for a given data type
		/// </summary>
		/// <param name="dataType">Data type for which to get the default value<</param>
		/// <returns>A string representation of the default value</returns>
		public string GetDefaultByType(DbType dataType)
		{
			switch (dataType)
			{
				case DbType.AnsiString: return "string.Empty";
				case DbType.AnsiStringFixedLength: return "string.Empty";
				//Answer modified was just 0
				case DbType.Binary: return "0";
				case DbType.Boolean: return "false";
				
				//Answer modified was just 0
				case DbType.Byte: 
					return "(byte)0"; 
					//return "{ 0 }"; 
				
				case DbType.Currency: return "0";
				case DbType.Date: return "DateTime.MaxValue";
				case DbType.DateTime: return "DateTime.MaxValue";
				case DbType.Decimal: return "0";
				case DbType.Double: return "0";

				case DbType.Guid: 
					return "0";

				case DbType.Int16: 
					return "0";

				case DbType.Int32: 
					return "0";

				case DbType.Int64: return "0";
				case DbType.Object: return "null";
				case DbType.Single: return "0";
				case DbType.String: return "0";
				case DbType.StringFixedLength: return "string.Empty";
				case DbType.Time: return "DateTime.MaxValue";
				case DbType.VarNumeric: 
					return "0";
					//the following won't be used
					//		case DbType.SByte: return "0";
					//		case DbType.UInt16: return "0";
					//		case DbType.UInt32: return "0";
					//		case DbType.UInt64: return "0";
				default: return "null";
			}
		}
	
*/
		
		public string GetCSDefaultByType(DataObjectBase column)
		{
			if (column.NativeType.ToLower() == "real")
				return "0.0F";
			else
			{
				DbType dataType = column.DataType;
				switch (dataType)
				{
					case DbType.AnsiString: 
						return "string.Empty";
						
					case DbType.AnsiStringFixedLength: 
						return "string.Empty";
					
					case DbType.String: 
						return "string.Empty";
						
					case DbType.Boolean: 
						return "false";
					
					case DbType.StringFixedLength: 
						return "string.Empty";
						
					case DbType.Guid: 
						return "Guid.Empty";
					
					
					//Answer modified was just 0
					case DbType.Binary: 
						return "new byte[] {}";
					
					//Answer modified was just 0
					case DbType.Byte:
						return "(byte)0";
						//return "{ 0 }";
					
					case DbType.Currency: 
						return "0";
					
					case DbType.Date: 
						return "DateTime.MinValue";
					
					case DbType.DateTime: 
						return "DateTime.MinValue";
					
					case DbType.Decimal: 
						return "0.0m";
						//return "0M";
						//return "0.0M";
					
					case DbType.Double: 
						return "0.0f";
					
					case DbType.Int16: 
						return "(short)0";
						
					case DbType.Int32: 
						return "(int)0";
						
					case DbType.Int64: 
						return "(long)0";
					
					case DbType.Object: 
						return "null";
					
					case DbType.Single: 
						return "0F";
					
					//case DbType.Time: return "DateTime.MaxValue";
					case DbType.Time: return "new DateTime(1900,1,1,0,0,0,0)";
					case DbType.VarNumeric: return "0";
						//the following won't be used
						//		case DbType.SByte: return "0";
						//		case DbType.UInt16: return "0";
						//		case DbType.UInt32: return "0";
						//		case DbType.UInt64: return "0";
					default: return "null";
				}
			}
		}
		
		/*
		/// <summary>
		/// Get a default value for a given data type
		/// </summary>
		/// <param name="dataType">Data type for which to get the default value<</param>
		/// <returns>A string representation of the default value</returns>
		public string GetCSDefaultByType(DbType dataType)
		{			
			
		}
*/
			/// <summary>
		/// Get a mock value for a given data type. Used by the unit test classes.
		/// </summary>
		/// <param name="dataType">Data type for which to get the default value.</param>
		/// <returns>A string representation of the default value.</returns>
		public string GetCSMockValueByType(DataObjectBase column, string stringValue, bool bValue, Guid guidValue, int numValue, DateTime dtValue)
		{	
			if (column.NativeType.ToLower() == "real")
				return numValue.ToString() + "F";
			else
			{
				switch (column.DataType)
				{
					case DbType.AnsiString: 
						return "\"" + stringValue + "\"";
						
					case DbType.AnsiStringFixedLength: 
					return "\"" + stringValue + "\"";
					
					case DbType.String: 
						return "\"" + stringValue + "\"";
						
					case DbType.Boolean: 
						return bValue.ToString().ToLower();
					
					case DbType.StringFixedLength: 
						return "\"" + stringValue + "\"";
						
					case DbType.Guid: 
						return "new Guid(\"" + guidValue.ToString() + "\")"; 
					
					
					//Answer modified was just 0
					case DbType.Binary: 
						return "new byte[] {" + numValue.ToString() + "}";
					
					//Answer modified was just 0
					case DbType.Byte:
						return "(byte)" + numValue.ToString() + "";
						//return "{ 0 }";
					
					case DbType.Currency: 
						return numValue.ToString();
					
					case DbType.Date: 
						return string.Format("new DateTime({0}, {1}, {2}, 0, 0, 0, 0)", dtValue.Date.Year, dtValue.Date.Month, dtValue.Date.Day);
					
					case DbType.DateTime: 
						return string.Format("new DateTime({0}, {1}, {2}, {3}, {4}, {5}, {6})", dtValue.Year, dtValue.Month, dtValue.Day, dtValue.Hour, dtValue.Minute, dtValue.Second, dtValue.Millisecond);
					
					case DbType.Decimal: 
						return numValue.ToString() + "m";
						//return "0M";
						//return "0.0M";
					
					case DbType.Double: 
						return numValue.ToString() + ".0f";
					
					case DbType.Int16: 
						return "(short)" + numValue.ToString();
						
					case DbType.Int32: 
						return "(int)" + numValue.ToString();
						
					case DbType.Int64: 
						return "(long)" + numValue.ToString();
					
					case DbType.Object: 
						return "null";
					
					case DbType.Single: 
						return numValue.ToString() + "F";
					
					//case DbType.Time: return "DateTime.MaxValue";
					case DbType.Time: 
						return string.Format("new DateTime({0}, {1}, {2}, {3}, {4}, {5}, {6})", dtValue.Year, dtValue.Month, dtValue.Day, dtValue.Hour, dtValue.Minute, dtValue.Second, dtValue.Millisecond);
						
					case DbType.VarNumeric: 
						return numValue.ToString();
						//the following won't be used
						//		case DbType.SByte: return "0";
						//		case DbType.UInt16: return "0";
						//		case DbType.UInt32: return "0";
						//		case DbType.UInt64: return "0";
					default: return "null";
				}
			}
		}


		/// <summary>
		/// Get the Sql Data type of a column
		/// </summary>
		/// <param name="column">Column for which to get the type</param>
		/// <returns>String representing the SQL data type</returns>
		public string GetSqlDbType(DataObjectBase column)	
		{
			switch (column.NativeType)
			{
				case "bigint": return "BigInt";
				case "binary": return "Binary";
				case "bit": return "Bit";
				case "char": return "Char";
				case "datetime": return "DateTime";
				case "decimal": return "Decimal";
				case "float": return "Float";
				case "image": return "Image";
				case "int": return "Int";
				case "money": return "Money";
				case "nchar": return "NChar";
				case "ntext": return "NText";
				case "numeric": return "Decimal";
				case "nvarchar": return "NVarChar";
				case "real": return "Real";
				case "smalldatetime": return "SmallDateTime";
				case "smallint": return "SmallInt";
				case "smallmoney": return "SmallMoney";
				case "sql_variant": return "Variant";
				case "sysname": return "NChar";
				case "text": return "Text";
				case "timestamp": return "Timestamp";
				case "tinyint": return "TinyInt";
				case "uniqueidentifier": return "UniqueIdentifier";
				case "varbinary": return "VarBinary";
				case "varchar": return "VarChar";
				default: return "__UNKNOWN__" + column.NativeType;
			}
		}
		
		public string FKColumnName(TableKeySchema fkey)
		{
			string Name = String.Empty;
			for(int x=0;x < fkey.ForeignKeyMemberColumns.Count;x++)
			{
				Name += fkey.ForeignKeyMemberColumns[x].Name;
			}
			return Name;
		}
		
		public string IXColumnName(IndexSchema index)
		{
			string Name = String.Empty;
			for(int x=0;x < index.MemberColumns.Count;x++)
			{
				Name += index.MemberColumns[x].Name;
			}
			return Name;
		}
		
		public string GetKeysName(ColumnSchemaCollection keys)
		{	
			string result = String.Empty;
			for(int x=0; x < keys.Count;x++)
			{
				result += keys[x].Name;
			}
			return result;
		}

		public bool IsMultiplePrimaryKeys(ColumnSchemaCollection keys)
		{
			if(keys.Count > 1)
				return true;
			return false;
		}
	

		/// <summary>
		///	Indicates if a column is an int.
		/// </summary>
		/// <author>ab</author>
		/// <date>01/26/05</date>
		public bool isIntXX(ColumnSchema column)
		{
			bool result = false;

			for(int i = 0; i < aIntegerDbTypes.Length; i++)
			{
				if (aIntegerDbTypes[i] == column.DataType) result=true;
			}
			
			return result;		
		}
		
		// [ab 013105] column name sorting comparer
		public class columnSchemaComparer : IComparer  
		{
	      	int IComparer.Compare( Object x, Object y )  
			{
				if (x is ColumnSchema && y is ColumnSchema)
	          		return( (new CaseInsensitiveComparer()).Compare( ((ColumnSchema)x).Name,  ((ColumnSchema)y).Name ) );
					
				throw new ArgumentException("one or both object(s) are not of type ColumnSchema");
			}
				
      	}
      	
      	
      	#region Execute sql file

		public void ExecuteSqlInFile(string pathToScriptFile, string connectionString ) 
		{
			SqlConnection connection;

			StreamReader _reader			= null;

			string sql	= "";

			if( false == System.IO.File.Exists( pathToScriptFile )) 
			{
				throw new Exception("File " + pathToScriptFile + " does not exists");
			}
			using( Stream stream = System.IO.File.OpenRead( pathToScriptFile ) ) 
			{
				_reader = new StreamReader( stream );

				connection = new SqlConnection(connectionString);

				SqlCommand	command = new SqlCommand();

				connection.Open();
				command.Connection = connection;
				command.CommandType	= System.Data.CommandType.Text;

				while( null != (sql = ReadNextStatementFromStream( _reader ) )) 
				{
					command.CommandText = sql;

					command.ExecuteNonQuery();
				}

				_reader.Close();
			}
			connection.Close();			
		}


		private static string ReadNextStatementFromStream( StreamReader _reader ) 
		{			
			StringBuilder sb = new StringBuilder();

			string lineOfText;

			while(true) 
			{
				lineOfText = _reader.ReadLine();
				if( lineOfText == null ) 
				{

					if( sb.Length > 0 ) 
					{
						return sb.ToString();
					}
					else 
					{
						return null;
					}
				}

				if( lineOfText.TrimEnd().ToUpper() == "GO" ) 
				{
					break;
				}
			
				sb.Append(lineOfText + Environment.NewLine);
			}

			return sb.ToString();
		}

		#endregion
      	
      	
      	public class TableSchemaExtended : TableSchema 
      	{
			ColumnSchemaCollection nonKeysUpdatable;
			ColumnSchemaCollection colsUpdatable;
						
			public TableSchemaExtended(DatabaseSchema p0,string p1, string p2, DateTime p3) : base (p0, p1, p2, p3)
			{
			} 
			
			public TableSchemaExtended(DatabaseSchema p0, string p1, string p2, DateTime p3, ExtendedProperty[] p4) : base (p0, p1, p2, p3)
			{
			}
			
			private void init()
			{
				colsUpdatable = new ColumnSchemaCollection();
				foreach (ColumnSchema column in this.Columns)
				{
					if (((bool)column.ExtendedProperties["CS_IsComputed"].Value) == false && column.NativeType.ToLower() != "timestamp" )
						colsUpdatable.Add(column);
				}
				// [ab 012605] nonKeys sans computed/read-only columns. This is for Insert/Update operations
				nonKeysUpdatable = new ColumnSchemaCollection();
				foreach (ColumnSchema column in this.NonPrimaryKeyColumns)
				{
					if (((bool)column.ExtendedProperties["CS_IsComputed"].Value) == false && column.NativeType.ToLower() != "timestamp" )
						nonKeysUpdatable.Add(column);
				}
				// [ab 013105] alpha sort the collections, to guarantee the same order between entity props and sp params when they are being assigned
				// Debugger.Break();
				IComparer colNameComparer = new columnSchemaComparer();
				colsUpdatable.Sort(colNameComparer); 
				nonKeysUpdatable.Sort(colNameComparer);
				
				/*
				// updatecols
				string output = string.Empty;
	
				for (int i = 0; i < keys.Count; i++)
				{
					output += ", " + GetCSType(keys[i]) + " " +  keys[i].Name;
				}
				
				for (int i = 0; i < nonKeysUpdatable.Count; i++)
				{
					output += ", " + GetCSType(nonKeysUpdatable[i]) + " " +  nonKeysUpdatable[i].Name;
				}
				
				// if the primary keys are not readonly, we propagate theme throw the differents layers
				if(!IsPkeyReadOnly)
				{
					for (int i = 0; i < keys.Count; i++)
					{
						output += ", " + GetCSType(keys[i]) + " Original" +  keys[i].Name;
					}
				}
				return output.Remove(0,2);
				*/
			}
			
			/*
			public ColumnSchemaCollection UpdateColumns
			{
				get
				{
					return keys + nonKeysUpdatable + if(!IsPkeyReadOnly)
				}
			}
			*/

      	}

		
		#region Children Collections
		
		/////////////////////////////////////////////////////////////////////////////////////
		/// Begin Children Collection 
		/////////////////////////////////////////////////////////////////////////////////////
		
		///<summary>
		///  An ArrayList of all the child collections for this table.
		///</summary>
		private System.Collections.ArrayList _collections = new System.Collections.ArrayList();
		
		///<summary>
		///  An ArrayList of all the properties rendered.  
		///  Eliminate Dupes through common junction tables and fk relationships
		///</summary>
		private System.Collections.ArrayList _renderedChildren = new System.Collections.ArrayList();
		
		///<summary>
		///  Holds the current table of the children collections being collected
		///</summary>
		private string _currentTable = string.Empty;


	
		///<summary>
		///	Returns an array list of Child Collections of the object
		///</summary>
		public System.Collections.ArrayList GetChildrenCollections(SchemaExplorer.TableSchema table, SchemaExplorer.TableSchemaCollection tables) 
		{
			//System.Diagnostics.Debugger.Break();
			//CleanUp
			if(CurrentTable != table.Name)
			{
				_collections.Clear();
				_renderedChildren.Clear();
				CurrentTable = table.Name;
			}
			
			if (_collections.Count > 0)
				return _collections;
			

			//Provides Informatoin about the foreign keys
			TableKeySchemaCollection fkeys = new TableKeySchemaCollection(table.ForeignKeys);
			//Provides information about the indexes contained in the table. 
			IndexSchemaCollection indexes = new IndexSchemaCollection(table.Indexes);

			TableKeySchemaCollection primaryKeyCollection = new TableKeySchemaCollection(table.PrimaryKeys);

			foreach(TableKeySchema keyschema in primaryKeyCollection)
			{
				// add the relationship only if the linked table is part of the selected tables (ie: omit tables without primary key)
				if (!tables.Contains(keyschema.ForeignKeyTable.Owner, keyschema.ForeignKeyTable.Name))
				{
					continue;
				}
						
				//Add 1-1 relations
				if(IsRelationOneToOne(keyschema))
				{
					CollectionInfo collectionInfo = new CollectionInfo();
					collectionInfo.PkColName = table.PrimaryKey.MemberColumns[0].Name;
					collectionInfo.PkIdxName = keyschema.Name;
					collectionInfo.PrimaryTable = table.Name;
					collectionInfo.SecondaryTable = keyschema.ForeignKeyTable.Name;
					collectionInfo.SecondaryTablePkColName = keyschema.ForeignKeyTable.PrimaryKey.MemberColumns[0].Name;
					collectionInfo.CollectionRelationshipType = RelationshipType.OneToOne;
					collectionInfo.CleanName = keyschema.ForeignKeyTable.Name;//GetClassName(keyschema.ForeignKeyTable.Name);		
					collectionInfo.CollectionName = GetCollectionClassName(collectionInfo.CleanName);
					collectionInfo.CallParams = GetFunctionRelationshipCallParameters(keyschema.ForeignKeyMemberColumns);
					collectionInfo.GetByKeysName = "GetBy" + GetKeysName(keyschema.ForeignKeyMemberColumns);
					
					_collections.Add(collectionInfo);
			  	}
				//Add 1-N,N-1 relations
				else
				{
					CollectionInfo collectionInfo = new CollectionInfo();
					collectionInfo.PkColName = table.PrimaryKey.MemberColumns[0].Name;
					collectionInfo.PkIdxName = keyschema.Name;
					collectionInfo.PrimaryTable = table.Name;
					collectionInfo.SecondaryTable = keyschema.ForeignKeyTable.Name;
					collectionInfo.SecondaryTablePkColName = keyschema.ForeignKeyTable.PrimaryKey.MemberColumns[0].Name;
					collectionInfo.CollectionRelationshipType = RelationshipType.OneToMany;
					collectionInfo.CleanName = keyschema.ForeignKeyTable.Name; //GetClassName(keyschema.ForeignKeyTable.Name);
					collectionInfo.CollectionName = GetCollectionClassName(collectionInfo.CleanName);
					collectionInfo.CallParams = GetFunctionRelationshipCallParameters(table.PrimaryKey.MemberColumns);
					//collectionInfo.CallParams = GetFunctionRelationshipCallParameters(keyschema.ForeignKeyMemberColumns);
					collectionInfo.GetByKeysName = "GetBy" + GetKeysName(keyschema.ForeignKeyMemberColumns);
					//collectionInfo.GetByKeysName = "GetBy" + GetKeysName(keyschema.ForeignKeyTable.PrimaryKey.MemberColumns);
				
					_collections.Add(collectionInfo);
				}
		    }
			
			//Add N-N relations
			TableSchema junctionTable;
			foreach(TableKeySchema primarykey in primaryKeyCollection)
			{
				// add the relationship only if the linked table is part of the selected tables (ie: omit tables without primary key)
				if (!tables.Contains(primarykey.ForeignKeyTable.Owner, primarykey.ForeignKeyTable.Name))
				{
					continue;
				}
				
				if(IsJunctionTable(primarykey.ForeignKeyTable))
				{
				
					CollectionInfo collectionInfo = new CollectionInfo();
					junctionTable = primarykey.ForeignKeyTable;
					foreach(TableKeySchema t in junctionTable.ForeignKeys)
					{   
						//Warning: Assumes 1st column is primary key.
						if((t.ForeignKeyMemberColumns[0] != primarykey.ForeignKeyMemberColumns[0]) 
							&& ( t.ForeignKeyMemberColumns[0].IsPrimaryKeyMember ))
						{
							collectionInfo.PkColName = table.PrimaryKey.MemberColumns[0].Name;
							collectionInfo.PkIdxName = t.Name;
							collectionInfo.PrimaryTable = table.Name;
							collectionInfo.SecondaryTable = t.PrimaryKeyTable.Name;
							collectionInfo.SecondaryTablePkColName = t.PrimaryKeyTable.PrimaryKey.MemberColumns[0].Name;
							collectionInfo.JunctionTable = junctionTable.Name;
							collectionInfo.CollectionName = GetManyToManyName(GetCollectionClassName(collectionInfo.SecondaryTable), collectionInfo.JunctionTable);
							collectionInfo.CollectionRelationshipType = RelationshipType.ManyToMany;
							collectionInfo.CallParams = "entity." + collectionInfo.PkColName;
							collectionInfo.GetByKeysName = "GetBy" + GetManyToManyName(collectionInfo.PrimaryTable, collectionInfo.JunctionTable);
							
							///Find FK junc table key, used for loading scenarios
							if(junctionTable.PrimaryKey.MemberColumns[0] == t.ForeignKeyMemberColumns[0])
								collectionInfo.FkColName = junctionTable.PrimaryKey.MemberColumns[1].Name;
							else
								collectionInfo.FkColName = junctionTable.PrimaryKey.MemberColumns[0].Name;

							collectionInfo.CleanName = GetManyToManyName(t.PrimaryKeyTable.Name, junctionTable.Name);
							_collections.Add(collectionInfo);
						}
					}
				}
			}// end N-N relations
		return _collections; 
		}

		public string GetFunctionRelationshipCallParameters(ColumnSchemaCollection columns)
		{
			string output = "";
			for (int i = 0; i < columns.Count; i++)
			{
				output +=  "entity." + columns[i].Name;
				if (i < columns.Count - 1)
				{
					output += ", ";
				}
			}
			return output;
		}



		///<summary>
		/// returns true all primary key columns have is a foreign key relationship
		/// </summary>
		public bool IsJunctionTable(TableSchema table)
		{
			if (table.PrimaryKey == null || table.PrimaryKey.MemberColumns.Count == 0)
			{
				//Response.WriteLine(string.Format("IsJunctionTable: The table {0} doesn't have a primary key.", table.Name));
				return false;
				
			}
			if (table.PrimaryKey.MemberColumns.Count == 1)
			{
				return false;				
			}
			
			// TODO tables with primary key = 1 foreign key
			
			for (int i=0;i < table.PrimaryKey.MemberColumns.Count; i++){
				if (!table.PrimaryKey.MemberColumns[i].IsForeignKeyMember)
					return false;
			}
			return true;			
		}

/*
		public bool IsJunctionTable(TableSchema table)
			{
				bool RetValue;
				ColumnSchemaCollection keys;
				RetValue = false;
				if(table.PrimaryKey.MemberColumns.Count > 1)
				{
					keys = new ColumnSchemaCollection(SourceTable.PrimaryKey.MemberColumns);
					foreach(ColumnSchema primarykey in keys)
					{
						if(primarykey.IsForeignKeyMember)
						{
							RetValue = true;
						}
						else
						{
							RetValue = false;
							break;
						}
					} 
				}
				return RetValue;
			}
*/
		///<summary>
		///	Returns whether or not a table key is a one to one 
		/// relationship with another table.
		/// WARNING: Assumes first column is the FK.
		///</summary>
		public bool IsRelationOneToOne(TableKeySchema keyschema)
		{
			foreach(IndexSchema i in keyschema.ForeignKeyTable.Indexes)
			{
				if((i.MemberColumns[0].Name == keyschema.ForeignKeyMemberColumns[0].Name) && (!IsJunctionTable(keyschema.ForeignKeyTable)))
				{
					if(i.IsUnique || i.IsPrimaryKey)
					{
						return true;
					}
					else
					{
						return false;
					}
				}	
			}
			return false;
		}
		
		public ColumnSchemaCollection GetRelationKeyColumns(TableKeySchemaCollection fkeys, IndexSchemaCollection indexes)
		{
			System.Diagnostics.Debugger.Break();
			for (int j=0; j < fkeys.Count; j++)
			{
				bool skipkey = false;
				foreach(IndexSchema i in indexes)
				{
					if(i.MemberColumns.Contains(fkeys[j].ForeignKeyMemberColumns[0]))
						skipkey = true;			
				}
				if(skipkey)
					continue;

				return fkeys[j].ForeignKeyMemberColumns;
			}
			return new ColumnSchemaCollection();
		}
		
		/*
		///<summary>
		///	TODO : Returns any string mutations that will be used for a string.
		/// Ex. singular string to be used within the template 
		///     All spaces from table or column names removed
		///</summary>
		public static string CleanName(string s){
			return s.Replace(" ", string.Empty);
		}
		*/
		

		///<summary>
		///  Store the most recent <see cref"SourceTable" /> of the templates,
		///  Used to clean up upon new SourceTable execution.  
		///</summary>
		[BrowsableAttribute(false)]
		public  string CurrentTable {
			get{return _currentTable;}
			set {_currentTable = value;}
		}
		
		///<summary>
		///  Store the most recent
		///  Used to keep track of which childcollections have been rendered
		///  Eliminates the Dupes.
		///</summary>
		[BrowsableAttribute(false)]
		public  System.Collections.ArrayList RenderedChildren {
			get{return _renderedChildren;}
			set {_renderedChildren = value;}
		}
		
		///<summary>
		///  Store the most recent <see cref"SourceTable" /> of the templates,
		///  Used to clean up upon new SourceTable execution.  
		///</summary>
		[BrowsableAttribute(false)]
		///<summary>
		/// Child Collection RelationshipType Enum
		///</summary>
		public enum RelationshipType{
			None = 0,
			OneToOne = 1,
			OneToMany = 2,
			ManyToOne = 3,
			ManyToMany = 4
		}
		
		///<summary>
		///	Child relationship structure information and their <see cref="RelationshipType" />
		/// to store in the <see cref="ChildCollections" /> ArrayList
		///</summary>
		public class CollectionInfo {
			public string CleanName;
			public string PkColName;
			public string PkIdxName;
			public string FkColName;
			public string FkIdxName;
			public string PrimaryTable;
			public string SecondaryTable;
			public string SecondaryTablePkColName;
			public string JunctionTable;
			public string CollectionName = string.Empty;
			public string CallParams = string.Empty;
			public string PropertyString = string.Empty;
			public string GetByKeysName = string.Empty;
			public RelationshipType CollectionRelationshipType;	
		}
	#endregion
		
	}
}