Implementing Model-View-Presenter in ASP.NET

using System;
using System.Collections;
using System.Text;
using System.Data;
using SubSonic.Utilities;
using System.Diagnostics;

namespace SubSonic
{
    public class CodeGeneration
    {
        private static string tableList = "*";
        //private const string PROPERTY_TEMPLATE = "propTemplate";
        //private const string CLASS_TEMPLATE = "classTemplate";
        //private const string COLLECTION_TEMPLATE = "collectionTemplate";
        //private const string ODS_INSERT = "odsInsert";
        //private const string ODS_UPDATE = "odsUpdate";
        //private const string DEFAULT_NAMESPACE = "";

        #region Public Methods

        public static void EnsureProviderLoad()
        {
            DataService.LoadProviders();
        }


        #region Usings

        public static string GetUsingStatements(LanguageType languageType)
        {
            if (languageType == LanguageType.CSharp)
            {
                return ResourceHelper.GetString(TemplateName.CLASS_USINGS) + Environment.NewLine + Environment.NewLine;
            }
            else if (languageType == LanguageType.VB)
            {
                return ResourceHelper.GetString(TemplateName.CLASS_USINGS_VB) + Environment.NewLine + Environment.NewLine;
            }
            return String.Empty;
        }

        #endregion

        #region Scaffolds

        public static string GenerateScaffold(string table, LanguageType languageType, DataProvider provider)
        {
            string template = String.Empty;
            if (languageType == LanguageType.CSharp)
            {
                template = ResourceHelper.GetString(TemplateName.SCAFFOLD);
            }
            else if (languageType == LanguageType.VB)
            {
                template = ResourceHelper.GetString(TemplateName.SCAFFOLD_VB);
            }
            string result = template.Replace(TemplateVariable.TABLE_NAME, table);
            result = result.Replace(TemplateVariable.PROVIDER, provider.Name);
            return result;
        }

        public static string GenerateScaffold(string table, string masterPageFile, LanguageType languageType, DataProvider provider)
        {
            string template = String.Empty;
            if (languageType == LanguageType.CSharp)
            {
                template = ResourceHelper.GetString(TemplateName.SCAFFOLD_MASTER_PAGE);
            }
            else if (languageType == LanguageType.VB)
            {
                template = ResourceHelper.GetString(TemplateName.SCAFFOLD_MASTER_PAGE_VB);
            }
            string result = template.Replace(TemplateVariable.TABLE_NAME, table).Replace(TemplateVariable.MASTER_PAGE, masterPageFile);
            result = result.Replace(TemplateVariable.PROVIDER, provider.Name);
            return result;
        }

        #endregion

        #region Classes
        public static string GenerateClass(string tableName, StringBuilder generatedTableList, LanguageType languageType, DataProvider provider)
        {
            tableList = generatedTableList.ToString();

            CodeGenerationArgs args = new CodeGenerationArgs();
            args.TableName = tableName;
            args.Namespace = GetNameSpace(provider);
            args.Language = languageType;
            return GetClass(args, provider) + Environment.NewLine;
        }
        public static string GenerateClasses(DataProvider provider, string tables, LanguageType languageType)
        {
            tableList = tables;

            //make sure everything's loaded properly
            DataService.LoadProviders();

            //set the provider for this class
            //to the provider that was passed in


            //set the SubSonicConfig.ProviderName - this will ensure that we get schema from the proper DB
            StringBuilder sOut = new StringBuilder();
            string[] tbls = DataService.GetTableNames(provider.Name);
            foreach(string tbl in tbls)
            {
                if(isInList(tbl))
                {
                    CodeGenerationArgs args = new CodeGenerationArgs();
                    args.TableName = tbl;
                    args.Namespace = GetNameSpace(provider);
                    args.Language = languageType;

                    sOut.AppendLine(GetClass(args, provider));
                }
                
            }
            string result = sOut.ToString();
            return result;
            
        }

        public static string GenerateClasses(string tables, LanguageType languageType, DataProvider provider)
        {
            tableList = tables;

            //make sure everything's loaded properly
            DataService.LoadProviders();

            string result = GenerateClasses(provider, tableList, languageType);

            return result;
        }

        public static string GenerateClass(string table, LanguageType languageType, DataProvider provider)
        {
            return GenerateClasses(table, languageType, provider);
        }

        //TODO: Why are there two similar GenerateClass methods?
        //      One should be an override of the other if necessary.
        public static string GenerateClass(string table, string nameSpace, LanguageType languageType, DataProvider provider)
        {
            tableList = table;
            CodeGenerationArgs args = new CodeGenerationArgs();
            args.TableName = table;
            args.Namespace = nameSpace;
            args.Language = languageType;
            return GetClass(args, provider) + Environment.NewLine;
        }

        /// <summary>
        /// this method can't be used with the build provider nor class generators since it would cause a doubling of using statements
        /// </summary>
        public static string GenerateClass(string table, string className, string nameSpace, LanguageType languageType, DataProvider provider)
        {
            tableList = table;
            string usings = GetUsingStatements(languageType);
            CodeGenerationArgs args = new CodeGenerationArgs();
            args.TableName = table;
            args.Namespace = nameSpace;
            args.Language = languageType;
            args.ClassName = className;

            string classCode = GetClass(args, provider);
            string result = usings + Environment.NewLine + Environment.NewLine + classCode;
            return result;
        }

        #endregion

        #region Enums
        public static string GenerateEnums(LanguageType languageType)
        {
            string enums = String.Empty;//GetEnums();//GetEnums_VB();
            return enums;
        }

        #endregion

        #region SPs

        public static string GenerateSPs(LanguageType languageType, string nameSpace, DataProvider provider)
        {
            return GenerateSPs(languageType, nameSpace, true, provider);
        }

        public static string GenerateSPs(LanguageType languageType, DataProvider provider)
        {
            return GenerateSPs(languageType, true, provider);
        }

        public static string GenerateSPs(LanguageType languageType, bool useConfig, DataProvider provider)
        {
            return GenerateSPs(languageType, provider.GeneratedNamespace, useConfig, provider);
        }

        public static string GenerateSPs(LanguageType languageType, string nameSpace, bool useConfig, DataProvider provider)
        {
            string find = provider.SPStartsWith;
            string replaceWith = provider.AppendWith;
            string spClassName = provider.SPClassName.Replace(" ", String.Empty).Trim();
            if (String.IsNullOrEmpty(spClassName))
            {
                spClassName = ClassName.STORED_PROCEDURES;
            }
            StringBuilder sOut = new StringBuilder();
            // If useConfig is false, we don't use the configuration 
            // setting and always generate the code. Used for
            // dynamic code generation that doesn't rely on web.config
            if (!useConfig || provider.UseSPs)
            {
                if (languageType == LanguageType.CSharp)
                {
                    if (!String.IsNullOrEmpty(nameSpace))
                    {
                        sOut.AppendLine();
                        sOut.AppendLine("namespace " + nameSpace);
                        sOut.AppendLine("{");
                        sOut.AppendLine();
                        sOut.AppendLine();
                    }

                    sOut.AppendLine("public class " + spClassName);
                    sOut.AppendLine("{");
                    sOut.AppendLine(GetSPs(find, replaceWith, languageType, provider));
                    sOut.AppendLine("}");

                    if (!String.IsNullOrEmpty(nameSpace))
                    {
                        sOut.AppendLine();
                        sOut.Append("}");
                    }
                }
                else if (languageType == LanguageType.VB)
                {
                    if (!String.IsNullOrEmpty(nameSpace))
                    {
                        sOut.AppendLine();
                        sOut.AppendLine("Namespace " + nameSpace);
                        sOut.AppendLine();
                        sOut.AppendLine();
                    }
                    sOut.AppendLine("Public Class " + spClassName);
                    sOut.AppendLine();
                    sOut.Append(GetSPs(find, replaceWith, languageType, provider));
                    sOut.AppendLine("End Class");

                    if (!String.IsNullOrEmpty(nameSpace))
                    {
                        sOut.AppendLine();
                        sOut.AppendLine("End Namespace");
                    }
                }
            }
            return sOut.ToString();
        }
        #endregion

        #region Views
        /// <summary>
        /// Returns the Views as ReadOnly classes
        /// </summary>
        /// <returns></returns>
        public static string GenerateViews(LanguageType languageType, DataProvider provider)
        {
            string sOut = String.Empty;


            string[] views = DataService.GetViewNames(provider.Name);

            foreach (string view in views)
            {
                if (Utility.StartsWith(view, provider.ViewStartsWith))
                {
                    CodeGenerationArgs args = new CodeGenerationArgs();
                    args.TableName = view;
                    args.Namespace = GetNameSpace(provider);
                    args.Language = languageType;
                    args.IsReadOnly = true;
                    sOut += GetView(args, provider) + Environment.NewLine;
                }
            }

            return sOut;
        }

        public static string GenerateView(string viewName, LanguageType languageType, DataProvider provider)
        {
            return GenerateView(GetNameSpace(provider), viewName, languageType, provider);
        }

        public static string GenerateView(string nameSpace, string viewName, LanguageType languageType, DataProvider provider)
        {
            CodeGenerationArgs args = new CodeGenerationArgs();
            args.TableName = viewName;
            args.Namespace = nameSpace;
            args.Language = languageType;

            return GetView(args, provider) + Environment.NewLine;
        }

        #endregion

        #region Structs
        public static string GenerateTableStruct(LanguageType languageType, DataProvider provider)
        {
            return GenerateTableStruct(languageType, provider.GeneratedNamespace, provider);
        }

        public static string GenerateTableStruct(LanguageType languageType, string nameSpace, DataProvider provider)
        {
            string[] tables = DataService.GetTableNames(provider.Name);
            return BuildTablesStruct(tables, nameSpace, languageType, provider);
        }

        public static string GenerateViewStruct(LanguageType languageType, DataProvider provider)
        {
            return GenerateViewStruct(languageType, provider.GeneratedNamespace, provider);
        }

        public static string GenerateViewStruct(LanguageType languageType, string nameSpace, DataProvider provider)
        {
            string[] views = DataService.GetViewNames(provider.Name);
            //check for multiple providers
            string sOut = BuildViewsStruct(views, nameSpace, languageType, provider);
            return sOut;
        }

        #endregion

        #endregion

        #region Private Methods
        #region Namespace
        static string GetNameSpace(DataProvider provider)
        {
            string result = null;

            try
            {
                result = provider.GeneratedNamespace;
            }
            catch (Exception)
            {
                //TODO: We shouldn't swallow all exceptions! 
                //Isn't it best to fail if we can't do this?
                //What exceptions can be throw here? P.H. -- 2007.02.16
            }

            if (String.IsNullOrEmpty(result))
            {
                result = "Subsonic.Generated";
            }
            return result;
        }

        #endregion

        #region Bools
        public static bool IsManyToMany(TableSchema.Table schema)
        {
            int keyCount = 0;
            bool bOut = false;

            foreach (TableSchema.TableColumn col in schema.Columns)
            {
                if (col.IsPrimaryKey)
                    keyCount++;
            }

            if (keyCount > 1)
            {
                bOut = true;
            }
            return bOut;
        }

        private static bool isInList(string tableName)
        {
            if (tableList.Trim() == "*")
            {
                return true;
            }
            else
            {
                //thanks chegan!
                return (Environment.NewLine + tableList.ToLower() + Environment.NewLine).Contains(Environment.NewLine + tableName.ToLower() + Environment.NewLine);

            }
        }

        private static string ReplaceTemplateValues(string template, string summary, string methodName, string methodType, string methodBody)
        {
            template = template.Replace(TemplateVariable.SUMMARY, summary);
            template = template.Replace(TemplateVariable.METHOD_NAME, methodName);
            template = template.Replace(TemplateVariable.METHOD_BODY, methodBody);
            template = template.Replace(TemplateVariable.METHOD_TYPE, methodType) + Environment.NewLine;
            return template;
        }

        #endregion

        #region SPs
        private static string GetSPs(string find, string replaceWith, LanguageType languageType, DataProvider provider)
        {
            //get the SP list from the DB
            string[] SPs = DataService.GetSPList(provider.Name);

            StringBuilder spList = new StringBuilder();
            string spTemplate = String.Empty;

            if (languageType == LanguageType.CSharp)
            {
                spTemplate = ResourceHelper.GetString(TemplateName.STORED_PROCEDURE);
            }
            else if (languageType == LanguageType.VB)
            {
                spTemplate = ResourceHelper.GetString(TemplateName.STORED_PROCEDURE_VB);
            }

            foreach (string sp in SPs)
            {
                if (!sp.StartsWith("dt_") && Utility.StartsWith(sp, provider.SPStartsWith))
                {
                    string methodName;
                    Convention convention = new Convention(provider);
                    if (string.IsNullOrEmpty(find))
                    {
                        methodName = convention.SPName(sp, replaceWith);
                    }
                    else
                    {
                        methodName = convention.SPName(sp, find, replaceWith);
                    }
                    StringBuilder paramList = new StringBuilder();
                    StringBuilder argList = new StringBuilder();
                    //grab the parameters
                    IDataReader paramReader = DataService.GetSPParams(sp, provider.Name);
                    bool firstPass = true;
                    while (paramReader.Read())
                    {
                        //loop the params, pulling out the names and dataTypes
                        DbType dbType = DataService.GetDbType(paramReader[SqlSchemaVariable.DATA_TYPE].ToString(), provider.Name);
                        string Type = Utility.GetVariableType(dbType, languageType);
                        string paramName = paramReader[SqlSchemaVariable.NAME].ToString();
                        string arg = convention.ParameterName(paramName);
                        string dbTypeName = Enum.GetName(typeof(DbType), dbType);
                        string sMode = paramReader[SqlSchemaVariable.MODE].ToString();


                        //append to the arg list
                        if (!firstPass)
                        {

                            argList.Append(", ");
                        }

                        if (languageType == LanguageType.CSharp)
                        {
                            argList.Append(Type + " " + arg);
                            if (sMode == SqlSchemaVariable.MODE_INOUT)
                            {
                                //thanks to dmcgiv for this!
                                paramList.AppendLine("\tsp.Command.AddOutputParameter(\"" + paramName + "\",DbType." + dbTypeName + ");");
                            }
                            else
                            {
                                paramList.AppendLine("\tsp.Command.AddParameter(\"" + paramName + "\"," + arg + ", DbType." + dbTypeName + ");");
                            }
                        }
                        else if (languageType == LanguageType.VB)
                        {
                            argList.Append("ByVal " + arg + " As " + Type);

                            if (sMode == SqlSchemaVariable.MODE_INOUT)
                            {
                                paramList.AppendLine("\tsp.Command.AddOutputParameter(\"" + paramName + "\", DbType." + dbTypeName + ")");
                            }
                            else
                            {
                                paramList.AppendLine("\tsp.Command.AddParameter(\"" + paramName + "\"," + arg + ", DbType." + dbTypeName + ")");
                            }
                        }
                        firstPass = false;
                    }


                    paramReader.Close();

                    //put it all together
                    string spMethod;
                    spMethod = spTemplate.Replace(TemplateVariable.STORED_PROCEDURE_NAME, sp);
                    spMethod = spMethod.Replace(TemplateVariable.METHOD_NAME, methodName);
                    spMethod = spMethod.Replace(TemplateVariable.PARAMETERS, paramList.ToString());
                    spMethod = spMethod.Replace(TemplateVariable.ARGUMENT_LIST, argList.ToString());
                    spMethod = spMethod.Replace(TemplateVariable.PROVIDER, provider.Name);

                    spList.AppendLine(spMethod);
                    spList.AppendLine();
                }
            }
            return spList.ToString();
        }
        #endregion

        #region Views
        private static string GetView(CodeGenerationArgs args, DataProvider provider)//string nameSpace, string viewName, LanguageType languageType, DataProvider provider)
        {
            //this is here for reverse-compat
            return GetClass(args, provider);
        }
        #endregion

        #region Classes
        //an override for className

        private static string GetClass(CodeGenerationArgs args, DataProvider provider)
        {
            string namespaceStart = String.Empty;
            string namespaceEnd = String.Empty;
            string classTemplate = String.Empty;

            if (args.Language == LanguageType.CSharp)
            {
                namespaceStart = Environment.NewLine + "namespace " + args.Namespace + Environment.NewLine + "{" + Environment.NewLine;
                namespaceEnd = Environment.NewLine + "}" + Environment.NewLine + Environment.NewLine;
                if (args.IsReadOnly)
                {
                    classTemplate = ResourceHelper.GetString(TemplateName.READ_ONLY_CLASS);

                }
                else
                {
                    classTemplate = ResourceHelper.GetString(TemplateName.CLASS);

                }
            }
            if (args.Language == LanguageType.VB)
            {
                if (args.IsReadOnly)
                {
                    classTemplate = ResourceHelper.GetString(TemplateName.READ_ONLY_CLASS_VB);

                }
                else
                {
                    classTemplate = ResourceHelper.GetString(TemplateName.CLASS_VB);

                }
                namespaceStart = Environment.NewLine + "Namespace " + args.Namespace + Environment.NewLine + Environment.NewLine;
                namespaceEnd = "End Namespace" + Environment.NewLine + Environment.NewLine;
            }

            string sOut = String.Empty;

            

            TableSchema.Table tbl = DataService.GetTableSchema(args.TableName, provider.Name);
            
            string className = tbl.ClassName;
            //Debug.Assert(!tbl.ClassName.Contains(" "), args.TableName);
            bool isReadOnly = args.IsReadOnly;
            LanguageType languageType = args.Language;

            if (!String.IsNullOrEmpty(args.Namespace))
            {
                sOut += namespaceStart;
            }

            //create the collection
            string collectionClass = GenerateCollection(className, isReadOnly, languageType);

            //load the properties
            string propList = GenerateProperties(tbl, languageType);

            
            //add the collection first
            string classOut = collectionClass + Environment.NewLine + Environment.NewLine;
            classOut += classTemplate.Replace(TemplateVariable.CLASS_NAME, className).Replace(TemplateVariable.PROPERTY_LIST, propList);
            classOut = classOut.Replace(TemplateVariable.TABLE_NAME, tbl.Name);

            //place holder for now - don't know what this does...
            //foreign key lookups

            string methodList = GenerateForeignKeyMethods(className, tbl, languageType);

            methodList += GeneratePKMethods(tbl, languageType);
            
            classOut = classOut.Replace(TemplateVariable.METHOD_LIST, methodList);

            //column struct
            string columnStruct = GenerateColumnsStruct(tbl, languageType);
            classOut = classOut.Replace(TemplateVariable.COLUMNS_STRUCT, columnStruct);

            if (!isReadOnly)
            {
                string odsInsertMethod = GenerateODSInsertMethod(className, tbl, languageType);
                string odsUpdateMethod = GenerateODSUpdateMethod(className, tbl, languageType);
                classOut = classOut.Replace(TemplateVariable.INSERT, odsInsertMethod);
                classOut = classOut.Replace(TemplateVariable.UPDATE, odsUpdateMethod);
            }


            string schema = GenerateSchema(tbl, languageType);
            classOut = classOut.Replace(TemplateVariable.TABLE_SCHEMA, schema);
            

            //Many To Many
            string manyMethodList = GenerateManyToManyMethods(className, tbl, languageType);
            sOut += classOut.Replace(TemplateVariable.MANY_METHODS, manyMethodList);


            if (!String.IsNullOrEmpty(args.Namespace))
            {
                sOut += namespaceEnd;
            }
            return sOut;
        }

        #endregion

        #region Generators
        /// <summary>
        /// Generates the schema in the class itself, so we don't need to hit the DB at runtime
        /// </summary>
        /// <param name="tbl"></param>
        /// <param name="languageType"></param>
        /// <returns></returns>
        private static string GenerateSchema(TableSchema.Table tbl, LanguageType languageType)
        {


            string eol = ";";
            if (languageType == LanguageType.VB)
            {
                eol = String.Empty;
            }

            StringBuilder sb = new StringBuilder();

            sb.AppendLine("BaseSchema = new TableSchema.Table(\"" + tbl.Name + "\", DataService.GetInstance(\"" + tbl.Provider.Name + "\"))" + eol);
            if (languageType == LanguageType.VB)
            {
                //sb.AppendLine("If Schema Is Nothing");
                
                sb.AppendLine("\t\t" + "Dim columns As TableSchema.TableColumnCollection = New TableSchema.TableColumnCollection()");
            }
            else
            {
                //sb.AppendLine("if(Schema == null)");
                //sb.AppendLine("\t\t{");
                //sb.AppendLine("BaseSchema = new TableSchema.Table()" + eol);
                sb.AppendLine("\t\t" + "TableSchema.TableColumnCollection columns = new TableSchema.TableColumnCollection()" + eol);
            }
            sb.AppendLine("\t\t" + "BaseSchema.Name = \"" + tbl.Name + "\"" + eol);
            //sb.AppendLine("\t\t" + "BaseSchema.ProviderName = \"" + tbl.Provider.Name + "\"" + eol);
            sb.AppendLine("\t\t" + "BaseSchema.SchemaName = \"" + tbl.SchemaName + "\"" + eol);
            sb.AppendLine(String.Empty);
            foreach (TableSchema.TableColumn column in tbl.Columns)
            {
                string varName = "col" + column.ArgumentName;
                if (languageType == LanguageType.VB)
                {
                    sb.AppendLine("\t\t" + "Dim " + varName + " As TableSchema.TableColumn = New TableSchema.TableColumn(BaseSchema)");
                }
                else
                {
                    sb.AppendLine("\t\t" + "TableSchema.TableColumn " + varName + " = new TableSchema.TableColumn(BaseSchema);");
                }

                sb.AppendLine("\t\t" + varName + ".ColumnName = \"" + column.ColumnName + "\"" + eol);
                sb.AppendLine("\t\t" + varName + ".DataType = DbType." + column.DataType + eol);

                sb.AppendLine("\t\t" + varName + ".MaxLength = " + column.MaxLength + eol);
                //sb.AppendLine("\t\t" + varName + ".PropertyName = \"" + column.PropertyName + "\"" + eol);
                //sb.AppendLine("\t\t" + varName + ".DisplayName = \"" + Utility.ParseCamelToProper(column.PropertyName) + "\"" + eol);
                //sb.AppendLine("\t\t" + varName + ".ParameterName = \"" + column.ParameterName + "\"" + eol);
                //sb.AppendLine("\t\t" + varName + ".ArgumentName = \"" + column.ArgumentName + "\"" + eol);
                if (languageType == LanguageType.VB)
                {
                    sb.AppendLine("\t\t" + varName + ".AutoIncrement = " + column.AutoIncrement + eol);
                    sb.AppendLine("\t\t" + varName + ".IsNullable = " + column.IsNullable + eol);
                    sb.AppendLine("\t\t" + varName + ".IsPrimaryKey = " + column.IsPrimaryKey + eol);
                    sb.AppendLine("\t\t" + varName + ".IsForeignKey = " + column.IsForeignKey + eol);
                    sb.AppendLine("\t\t" + varName + ".IsReadOnly = " + column.IsReadOnly + eol);
                }
                else
                {
                    sb.AppendLine("\t\t" + varName + ".AutoIncrement = " + column.AutoIncrement.ToString().ToLower() + eol);
                    sb.AppendLine("\t\t" + varName + ".IsNullable = " + column.IsNullable.ToString().ToLower() + eol);
                    sb.AppendLine("\t\t" + varName + ".IsPrimaryKey = " + column.IsPrimaryKey.ToString().ToLower() + eol);
                    sb.AppendLine("\t\t" + varName + ".IsForeignKey = " + column.IsForeignKey.ToString().ToLower() + eol);
                    sb.AppendLine("\t\t" + varName + ".IsReadOnly = " + column.IsReadOnly.ToString().ToLower() + eol);
                }
                sb.AppendLine("\t\t" + "columns.Add(" + varName + ")" + eol);
                sb.AppendLine(String.Empty);
            }
            sb.AppendLine("\t\t" + "BaseSchema.Columns = columns" + eol);
            if (languageType == LanguageType.VB)
            {
                //sb.AppendLine("\t\t" + "End If" + eol);
                //sb.AppendLine("\t\t" + "Return Schema" + eol);
            }
            else
            {
                //sb.AppendLine("\t\t" + "}" + eol);
                //sb.AppendLine("\t\t" + "return Schema" + eol);
            }
            return sb.ToString();
        }

        private static string GetNamespaceStart(string nameSpace, LanguageType langType)
        {
            if (!String.IsNullOrEmpty(nameSpace))
            {
                if (langType == LanguageType.VB)
                {
                    return Environment.NewLine + "Namespace " + nameSpace + Environment.NewLine + Environment.NewLine;
                }
                return Environment.NewLine + "namespace " + nameSpace + Environment.NewLine + "{" + Environment.NewLine;
            }
            return String.Empty;
        }

        private static string GetNamespaceEnd(string nameSpace, LanguageType langType)
        {
            if (!String.IsNullOrEmpty(nameSpace))
            {
                if (langType == LanguageType.VB)
                {
                    return Environment.NewLine + "End Namespace" + Environment.NewLine + Environment.NewLine;
                }

                return Environment.NewLine + "}" + Environment.NewLine + Environment.NewLine;
            }
            return String.Empty;
        }

        private static string WrapCodeInNamespace(string code, string nameSpace, LanguageType langType)
        {
            return GetNamespaceStart(nameSpace, langType) + code + GetNamespaceEnd(nameSpace, langType);
        }


        private static string ReplaceStructVariables(string code, string declareList, string assignList, LanguageType langType)
        {
            if (langType == LanguageType.VB)
            {
                code = code.Replace(TemplateVariable.STRUCT_LIST, declareList);
                code = code.Replace(TemplateVariable.STRUCT_ASSIGNMENTS, assignList);
            }
            else
            {
                code = code.Replace(TemplateVariable.STRUCT_LIST, assignList);
            }
            return code;
        }


        static string BuildViewsStruct(string[] views, string nameSpace, LanguageType languageType, DataProvider provider)
        {
            string sOut;

            //pull the template
            if (languageType == LanguageType.VB)
            {
                sOut = ResourceHelper.GetString(TemplateName.VIEW_LIST_VB);
            }
            else
            {
                sOut = ResourceHelper.GetString(TemplateName.VIEW_LIST);
            }
            //create the table list
            StringBuilder assignList = new StringBuilder();
            StringBuilder declareList = new StringBuilder();
            foreach (string view in views)
            {
                string alteredName = DataService.GetTableSchema(view, provider.Name).ClassName;
                if (languageType == LanguageType.VB)
                {
                    assignList.AppendLine(alteredName + " = @\"" + view + "\"");
                    declareList.AppendLine("\t\tPublic Shared " + alteredName + " As String");
                }
                else
                {
                    assignList.AppendLine("\t\tpublic static string " + alteredName + " = @\"" + view + "\";");
                }
            }

            sOut = ReplaceStructVariables(sOut, declareList.ToString(), assignList.ToString(), languageType);
            return WrapCodeInNamespace(sOut, nameSpace, languageType);
        }


        static string BuildTablesStruct(string[] tables, string nameSpace, LanguageType languageType, DataProvider provider)
        {
            string sOut;
            //pull the template
            if (languageType == LanguageType.VB)
            {
                sOut = ResourceHelper.GetString(TemplateName.TABLE_LIST_VB);
            }
            else
            {
                sOut = ResourceHelper.GetString(TemplateName.TABLE_LIST);
            }
            //create the table list
            StringBuilder assignList = new StringBuilder();
            StringBuilder declareList = new StringBuilder();
            foreach (string table in tables)
            {
                string alteredName = DataService.GetTableSchema(table, provider.Name).ClassName;
                if (languageType == LanguageType.VB)
                {
                    assignList.AppendLine(alteredName + " = @\"" + table + "\"");
                    declareList.AppendLine("\t\tPublic Shared " + alteredName + " As String");
                }
                else
                {
                    assignList.AppendLine("\t\tpublic static string " + alteredName + " = @\"" + table + "\";");
                }
            }

            sOut = ReplaceStructVariables(sOut, declareList.ToString(), assignList.ToString(), languageType);
            return WrapCodeInNamespace(sOut, nameSpace, languageType);
        }

        static string GenerateColumnsStruct(TableSchema.Table table, LanguageType languageType)
        {
            string sOut;

            if (languageType == LanguageType.VB)
            {
                sOut = ResourceHelper.GetString(TemplateName.COLUMN_STRUCT_VB);
            }
            else
            {
                sOut = ResourceHelper.GetString(TemplateName.COLUMN_STRUCT);
            }

            StringBuilder assignList = new StringBuilder();
            StringBuilder declareList = new StringBuilder();
            foreach (TableSchema.TableColumn col in table.Columns)
            {
                string alteredName = col.PropertyName;
                if (languageType == LanguageType.VB)
                {
                    assignList.AppendLine("\t\t" + alteredName + " = \"" + col.ColumnName + "\"");
                    declareList.AppendLine("\t\tPublic Shared " + alteredName + " As String");
                }
                else
                {
                    assignList.AppendLine("\t\tpublic static string " + alteredName + " = @\"" + col.ColumnName + "\";");
                }
            }
            sOut = ReplaceStructVariables(sOut, declareList.ToString(), assignList.ToString(), languageType);
            return sOut;
        }


        static string GenerateCollection(string className, bool isReadOnly, LanguageType languageType)
        {
            //pull the template
            string sOut;
            if (languageType == LanguageType.VB)
            {
                if (isReadOnly)
                {
                    sOut = ResourceHelper.GetString(TemplateName.READ_ONLY_COLLECTION_VB);
                }
                else
                {
                    sOut = ResourceHelper.GetString(TemplateName.COLLECTION_VB);
                }
            }
            else
            {
                if (isReadOnly)
                {
                    sOut = ResourceHelper.GetString(TemplateName.READ_ONLY_COLLECTION);
                }
                else
                {
                    sOut = ResourceHelper.GetString(TemplateName.COLLECTION);
                }
            }
            sOut = sOut.Replace(TemplateVariable.CLASS_NAME, className);
            return sOut;
        }

        static string GenerateODSUpdateMethod(string className, TableSchema.Table tbl, LanguageType languageType)
        {
            string updateMethod;
            string updateSetList = String.Empty;
            string updateArgList = String.Empty;

            foreach (TableSchema.TableColumn col in tbl.Columns)
            {
                string varType = Utility.GetVariableType(col.DataType, col.IsNullable, languageType);
                string propertyName = col.PropertyName;
                string argName = col.ArgumentName;


                if (!Utility.IsAuditField(col.ColumnName))
                {
                    updateSetList += updateSetList.Length == 0 ? String.Empty : (Environment.NewLine + "\t\t");

                    if (languageType == LanguageType.CSharp)
                    {
                        string argument = varType + " " + argName;
                        updateSetList += "item." + propertyName + " = " + argName + ";";
                        updateArgList += updateArgList.Length == 0 ? argument : (", " + argument);
                    }
                    else if (languageType == LanguageType.VB)
                    {
                        string argument = "ByVal " + argName + " As " + varType;
                        updateSetList += "item." + propertyName + " = " + argName;
                        updateArgList += updateArgList.Length == 0 ? argument : (", " + argument);
                    }
                }
            }


            string updateTemplate;
            if (languageType == LanguageType.CSharp)
            {
                updateTemplate = ResourceHelper.GetString(TemplateName.ODS_UPDATE);
            }
            else
            {
                updateTemplate = ResourceHelper.GetString(TemplateName.ODS_UPDATE_VB);
            }

            updateMethod = updateTemplate.Replace(TemplateVariable.ARGUMENT_LIST, updateArgList);
            updateMethod = updateMethod.Replace(TemplateVariable.CLASS_NAME, className);
            updateMethod = updateMethod.Replace(TemplateVariable.SET_LIST, updateSetList);
            return updateMethod;
        }


        static string GenerateODSInsertMethod(string className, TableSchema.Table tbl, LanguageType languageType)
        {
            string insertSetList = String.Empty;
            string insertArgList = String.Empty;
            string insertMethod;

            foreach (TableSchema.TableColumn col in tbl.Columns)
            {
                string varType = Utility.GetVariableType(col.DataType, col.IsNullable, languageType);
                string propertyName = col.PropertyName;
                string argName = col.ArgumentName;

                if (!Utility.IsAuditField(col.ColumnName))
                {
                    insertSetList += insertSetList.Length == 0 ? String.Empty : (Environment.NewLine + "\t\t");

                    if (languageType == LanguageType.CSharp)
                    {
                        string argument = varType + " " + argName;
                        if (!col.AutoIncrement)
                        {
                            insertArgList += insertArgList.Length == 0 ? argument : (", " + argument);
                            insertSetList += "item." + propertyName + " = " + argName + ";";
                        }
                    }
                    else if (languageType == LanguageType.VB)
                    {
                        string argument = "ByVal " + argName + " As " + varType;
                        if (!col.AutoIncrement)
                        {
                            insertArgList += insertArgList.Length == 0 ? argument : (", " + argument);
                            insertSetList += "item." + propertyName + " = " + argName;
                        }
                    }
                }
            }

            string insertTemplate;
            if (languageType == LanguageType.CSharp)
            {
                insertTemplate = ResourceHelper.GetString(TemplateName.ODS_INSERT);
            }
            else
            {
                insertTemplate = ResourceHelper.GetString(TemplateName.ODS_INSERT_VB);
            }
            insertMethod = insertTemplate.Replace(TemplateVariable.ARGUMENT_LIST, insertArgList);
            insertMethod = insertMethod.Replace(TemplateVariable.CLASS_NAME, className);
            insertMethod = insertMethod.Replace(TemplateVariable.SET_LIST, insertSetList);
            return insertMethod;
        }

        static string GeneratePKMethods(TableSchema.Table tbl, LanguageType languageType)
        {
            StringBuilder methodList = new StringBuilder();
            ArrayList usedMethodNames = new ArrayList();

            foreach (TableSchema.TableColumn col in tbl.Columns)
            {
                if (col.IsPrimaryKey)
                {
                    string propFKTable = GetPrimaryKeyTableSetter(tbl, col, usedMethodNames, languageType);

                    if (!string.IsNullOrEmpty(propFKTable))
                    {
                        methodList.AppendLine(propFKTable);
                    }
                }
            }


            return methodList.ToString();
        }


        static string GenerateForeignKeyMethods(string className, TableSchema.Table tbl, LanguageType languageType)
        {
            //string methodList = string.Empty;
      
            StringBuilder methodList = new StringBuilder();
            //StringBuilder output = new StringBuilder();
            //if (languageType == LanguageType.VB) {
            //    output.AppendLine("Public Property " + propertyName + "() As " + fkTableNameSingular);
            //    output.AppendLine("\tGet" + Environment.NewLine + "\t\t return " + fkTableNameSingular + ".FetchByID(" + pkPropertyName + ")" + Environment.NewLine + "\tEnd Get");
            //    output.AppendLine("\tSet" + Environment.NewLine + "\t\t MarkDirty()" + Environment.NewLine + "\t\tSetColumnValue(\"" + pkPropertyName + "\", Value." + Convention.PropertyName(tblFK.PrimaryKey.ColumnName, table.Name) + ")" + Environment.NewLine + "\tEnd Set" + Environment.NewLine + "End Property");
            //} else {
            //    output.AppendLine("public " + fkTableNameSingular + " " + propertyName + Environment.NewLine + "{");
            //    output.AppendLine("\tget { return " + fkTableNameSingular + ".FetchByID(" + pkPropertyName + "); }");
            //    output.Append("\tset" + Environment.NewLine + "\t{" + Environment.NewLine + "\t\tMarkDirty();" + Environment.NewLine + "\t\tSetColumnValue(\"" + pkPropertyName + "\", value." + Convention.PropertyName(tblFK.PrimaryKey.ColumnName, table.Name) + ");" + Environment.NewLine + "\t}" + Environment.NewLine + "}");
            //}
            //return output.ToString();
            
            
            ArrayList usedPropertyNames = new ArrayList();

            foreach (TableSchema.TableColumn col in tbl.Columns)
            {
                if (col.IsForeignKey)
                {
                    string propFKTable = GetForeignKeyTableSetter(tbl, col, className, usedPropertyNames, languageType);
                    if (!string.IsNullOrEmpty(propFKTable))
                    {
                        methodList.AppendLine(propFKTable);
                    }
                }
            }

            
            return methodList.ToString();
        }

        static string GenerateManyToManyMethods(string className, TableSchema.Table tbl, LanguageType languageType)
        {
            string manyMethods = String.Empty;
            StringBuilder manyMethodList = new StringBuilder();
            string[] manyList = DataService.GetManyToMany(tbl.Provider.Name, tbl.Name);
            foreach (string mapTable in manyList)
            {
                if (mapTable.EndsWith(tbl.Provider.ManyToManySuffix))
                {
                    string template;
                    if (languageType == LanguageType.CSharp)
                    {
                        template = ResourceHelper.GetString(TemplateName.MANY_TO_MANY_METHODS);
                    }
                    else
                    {
                        template = ResourceHelper.GetString(TemplateName.MANY_TO_MANY_METHODS_VB);
                    }
                    string foreignPK = String.Empty;
                    //pull map table
                    TableSchema.Table mtbl = DataService.GetTableSchema(mapTable, tbl.Provider.Name);

                    //there should be two keys on this table - ignore the one that's for 
                    //this class
                    foreach (TableSchema.TableColumn col in mtbl.Columns)
                    {
                        if (col.IsPrimaryKey && !Utility.IsMatch(col.ColumnName, tbl.PrimaryKey.ColumnName))
                        {
                            foreignPK = col.ColumnName;
                            break;
                        }
                    }

                    if (!String.IsNullOrEmpty(foreignPK))
                    {
                        //now, get foreign table
                        try
                        {
                            string fTableName = DataService.GetForeignKeyTableName(foreignPK, tbl.Name, tbl.Provider.Name);
                            if(isInList(fTableName))
                            {
                                TableSchema.Table ftbl = DataService.GetTableSchema(fTableName, tbl.Provider.Name);
                                string fClassName = ftbl.ClassName;
                                string pkPropertyName = tbl.PrimaryKey.DisplayName;
                                //now that we have the schema, we can set the template
                                manyMethods = template.Replace(TemplateVariable.CLASS_NAME, className);
                                manyMethods = manyMethods.Replace(TemplateVariable.PK_PROP, pkPropertyName);
                                manyMethods = manyMethods.Replace(TemplateVariable.PK, tbl.PrimaryKey.ColumnName);
                                //manyMethods = manyMethods.Replace(TemplateVariable.PK_VAR, tbl.PrimaryKey.ColumnName.ToLower());
                                manyMethods = manyMethods.Replace(TemplateVariable.PK_VAR, tbl.PrimaryKey.ColumnName);
                                manyMethods = manyMethods.Replace(TemplateVariable.MAP_TABLE, mtbl.Name);
                                manyMethods = manyMethods.Replace(TemplateVariable.FOREIGN_PK, ftbl.PrimaryKey.ColumnName);
                                //manyMethods = manyMethods.Replace(TemplateVariable.FK_VAR, ftbl.PrimaryKey.ColumnName.ToLower());
                                manyMethods = manyMethods.Replace(TemplateVariable.FK_VAR, ftbl.PrimaryKey.ColumnName);
                                manyMethods = manyMethods.Replace(TemplateVariable.FOREIGN_TABLE, ftbl.Name);
                                manyMethods = manyMethods.Replace(TemplateVariable.FOREIGN_CLASS, fClassName);
                            }
                        }
                        catch
                        {
                            manyMethods = "";
                        }
                    }
                }
                else
                {
                }

                manyMethodList.AppendLine(manyMethods);
            }
            return manyMethods;
        }



        static string GenerateProperties(TableSchema.Table tbl, LanguageType languageType)
        {
            StringBuilder sOut = new StringBuilder();

            string propTemplate;
            if (languageType == LanguageType.CSharp)
            {
                propTemplate = ResourceHelper.GetString(TemplateName.PROPERTY);
            }
            else
            {
                propTemplate = ResourceHelper.GetString(TemplateName.PROPERTY_VB);
            }

            foreach (TableSchema.TableColumn col in tbl.Columns)
            {
                string varType = Utility.GetVariableType(col.DataType, col.IsNullable, languageType);
                string propertyName = col.PropertyName;
                string getLine = GetPropertyGetter(col, languageType);
                string setLine = GetPropertySetter(col, languageType);

                sOut.AppendLine(propTemplate.Replace(TemplateVariable.PROPERTY_NAME, propertyName)
                                    .Replace(TemplateVariable.GETTER, getLine)
                                    .Replace(TemplateVariable.SETTER, setLine)
                                    .Replace(TemplateVariable.PROPERTY_TYPE, varType));
            }

            return sOut.ToString();
        }

        #endregion

        #region Props

        private static string GetPrimaryKeyTableSetter(TableSchema.Table table, TableSchema.TableColumn column, ArrayList usedMethodNames, LanguageType languageType)
        {
            StringBuilder allMethods = new StringBuilder();
            TableSchema.Table[] pkTables = DataService.GetPrimaryKeyTables(table.Name, table.Provider.Name);
            if (pkTables != null)
            {
                foreach(TableSchema.Table pkTable in pkTables)
                {
                    if(isInList(pkTable.Name) && (pkTable.PrimaryKey != null))
                    {
                        string methodFKTableTemplate;
                        if(languageType == LanguageType.VB)
                        {
                            methodFKTableTemplate = ResourceHelper.GetString(TemplateName.METHOD_VB);
                        }
                        else
                        {
                            methodFKTableTemplate = ResourceHelper.GetString(TemplateName.METHOD);
                        }

                        //if (String.IsNullOrEmpty(pkTable[0]) || String.IsNullOrEmpty(pkTable[1]))
                        //{
                        //    return String.Empty;
                        //}
                        string tblNameFK = pkTable.ClassName;
                        string tblNameFKPlural = pkTable.ClassNamePlural;
                        string tblNameFKCollection = tblNameFK + "Collection";
                        string structNameFK = pkTable.PrimaryKey.PropertyName;
                        string propertyName = column.PropertyName;

                        string methodBody;
                        if(languageType == LanguageType.VB)
                        {
                            methodBody = "\t\tReturn New " + tblNameFKCollection + "().Where(" + tblNameFK + ".Columns." +
                                         structNameFK + ", " + propertyName + ").Load()";
                        }
                        else
                        {
                            methodBody = "\t\treturn new " + tblNameFKCollection + "().Where(" + tblNameFK + ".Columns." +
                                         structNameFK + ", " + propertyName + ").Load();";
                        }

                        string methodName = table.Provider.RelatedTableLoadPrefix + tblNameFKPlural;
                        string summary = "One to Many - Returns " + tblNameFKPlural;

                        if(Utility.IsMatch(tblNameFK, tblNameFKPlural))
                        {
                            methodName = methodName + "Records";
                        }

                        if(tblNameFK == table.Name)
                        {
                            methodName = "Child" + methodName;
                        }
                        if(usedMethodNames.Contains(methodName))
                        {
                            methodName += "From" + propertyName;
                        }
                        if(!usedMethodNames.Contains(methodName))
                        {
                            usedMethodNames.Add(methodName);
                            methodFKTableTemplate = ReplaceTemplateValues(methodFKTableTemplate, summary, methodName, tblNameFKCollection, methodBody);
                            allMethods.Append(methodFKTableTemplate);
                        }
                    }
                }
            }
            return allMethods.ToString();
        }

        private static string GetForeignKeyTableSetter(TableSchema.Table table, TableSchema.TableColumn column, string className, ArrayList usedPropertyNames, LanguageType languageType)
        {
            TableSchema.Table fkTable = DataService.GetForeignKeyTable(column, table);

            if (fkTable == null || !isInList(fkTable.Name))
            {
                return String.Empty;
            }

            //TableSchema.Table tblFK = null;
            //try
            //{
            //    tblFK = DataService.GetTableSchema(fkTableName, SubSonicConfig.ProviderName);//Query.BuildTableSchema(fkTableName,SubSonicConfig.ProviderName);
            //}
            //catch
            //{
            //    Debug.WriteLine("Failed for " + fkTableName);
            //    return null;
            //}

            bool isManyToMany = IsManyToMany(fkTable);

            string methodType = fkTable.ClassName;
            string methodTypeCollection = methodType +  "Collection";
            string fkTableNameSingular = fkTable.ClassName;
            string pkStructName = fkTable.PrimaryKey.PropertyName;
            string pkPropertyName = column.PropertyName;

            if (String.IsNullOrEmpty(pkStructName))
            {
                if (fkTable.Columns.Count > 0)
                {
                    pkStructName = fkTable.Columns[0].ColumnName;
                }
            }

            if (!isManyToMany)
            {
                if (!IsManyToMany(table))
                {
                    string propertyName = table.Provider.RelatedTableLoadPrefix + fkTableNameSingular;

                    if (fkTable.Name == table.Name)
                    {
                        propertyName = "Parent" + propertyName;
                    }
                    if (usedPropertyNames.Contains(propertyName))
                    {
                        propertyName += "From" + pkPropertyName;
                    }
                    usedPropertyNames.Add(propertyName);

                    StringBuilder output = new StringBuilder();
                    if (languageType == LanguageType.VB)
                    {
                        output.AppendLine("Public Property " + propertyName + "() As " + fkTableNameSingular);
                        output.AppendLine("\tGet" + Environment.NewLine + "\t\t return " + fkTableNameSingular + ".FetchByID(" + pkPropertyName + ")" + Environment.NewLine + "\tEnd Get");
                        output.AppendLine("\tSet" + Environment.NewLine + "\t\t MarkDirty()" + Environment.NewLine + "\t\tSetColumnValue(\"" + pkPropertyName + "\", Value." + fkTable.PrimaryKey.PropertyName + ")" + Environment.NewLine + "\tEnd Set" + Environment.NewLine + "End Property");
                    }
                    else
                    {
                        output.AppendLine("public " + fkTableNameSingular + " " + propertyName + Environment.NewLine + "{");
                        output.AppendLine("\tget { return " + fkTableNameSingular + ".FetchByID(" + pkPropertyName + "); }");
                        output.Append("\tset" + Environment.NewLine + "\t{" + Environment.NewLine + "\t\tMarkDirty();" + Environment.NewLine + "\t\tSetColumnValue(\"" + pkPropertyName + "\", value." + fkTable.PrimaryKey.PropertyName + ");" + Environment.NewLine + "\t}" + Environment.NewLine + "}");
                    }
                    return output.ToString();

                }
                else
                {
                    string methodFKTableTemplate;
                    string methodBody;

                    if (languageType == LanguageType.VB)
                    {
                        methodFKTableTemplate = ResourceHelper.GetString(TemplateName.METHOD_VB);
                        methodBody = "\t\tReturn New " + fkTableNameSingular + "Collection().Where(" + fkTableNameSingular + ".Columns." + pkStructName + ", " + pkPropertyName + ").Load()";
                    }
                    else
                    {
                        methodFKTableTemplate = ResourceHelper.GetString(TemplateName.METHOD);
                        methodBody = "\t\treturn new " + fkTableNameSingular + "Collection().Where(" + fkTableNameSingular + ".Columns." + pkStructName + ", " + pkPropertyName + ").Load();";
                    }
                    string methodName = table.Provider.RelatedTableLoadPrefix + table.ClassNamePlural;
                    if (usedPropertyNames.Contains(methodName) || Utility.IsMatch(methodName, className))
                    {
                        methodName += "From" + pkPropertyName;
                    }
                    if (!usedPropertyNames.Contains(methodName))
                    {
                        string summary = "Returns " + fkTableNameSingular;
                        string methodFKTable = ReplaceTemplateValues(methodFKTableTemplate, summary, methodName, methodTypeCollection, methodBody);
                        usedPropertyNames.Add(methodName);
                        return methodFKTable;
                    }
                }
            }
            return String.Empty;
        }

        private static string GetPropertyGetter(TableSchema.TableColumn column, LanguageType languageType)
        {
            StringBuilder sb = new StringBuilder();

            string varType = Utility.GetVariableType(column.DataType, column.IsNullable, languageType);

            if (languageType == LanguageType.VB)
            {
                sb.AppendFormat("\t\t\tReturn GetColumnValue(Of {0})(\"{1}\")", varType, column.ColumnName);
            }
            else
            {
                sb.AppendFormat("\t\t\treturn GetColumnValue<{0}>(\"{1}\");", varType, column.ColumnName);
            }
            return sb.ToString();
        }

        private static string GetPropertySetter(TableSchema.TableColumn column, LanguageType languageType)
        {
            StringBuilder sb = new StringBuilder();

            if (languageType == LanguageType.VB)
            {
                sb.AppendLine("\t\t\tMarkDirty()");
                sb.AppendLine("\t\t\tSetColumnValue(\"" + column.ColumnName + "\", Value)");
            }
            else
            {
                sb.AppendLine("\t\t\tMarkDirty();");
                sb.AppendLine("\t\t\tSetColumnValue(\"" + column.ColumnName + "\", value);");
            }
            return sb.ToString();
        }

        #endregion

        #endregion

    }
    /// <summary>
    /// Helper class to hold arguments for passing to methods
    /// </summary>
    class CodeGenerationArgs
    {
        public string Template = string.Empty;
        public TableSchema TableSchema = null;
        public LanguageType Language = LanguageType.CSharp;
        public string[] TableNames = null;
        public bool IsReadOnly = false;
        public string ClassName = string.Empty;
        public string TableName = string.Empty;
        public string SPName = string.Empty;
        public string Namespace = string.Empty;
    }
}