Implementing Model-View-Presenter in ASP.NET

using System;
using System.Collections;
using System.Text;
using System.Configuration.Provider;
using System.Configuration;
using System.Web.Configuration;
using System.Data.Common;
using System.Data;
using System.Collections.Generic;
using SubSonic.Utilities;

namespace SubSonic
{
    public static class DataService
    {
        public static SubSonicSection ConfigSectionSettings;
        //public static string ConnectionString = String.Empty;

        //public static DBCollection Databases;

        #region Provider-specific bits
        
        public static int ProviderCount
        {
            get
            {
                if (_providers != null)
                {
                    return _providers.Count;
                }
                return 0;
            }
        }
        private static SubSonicSection section;

        public static SubSonicSection ConfigSection {
            get { return section; }
            set { section = value; }
        }
	
        private static DataProvider defaultProvider;
        private static DataProviderCollection _providers;
        private static object _lock = new object();

        public static DataProvider Provider
        {
            get
            {
                if(defaultProvider == null)
                {
                    LoadProviders();
                }
                return defaultProvider;
            }
            set { defaultProvider = value; }
        }

        public static DataProviderCollection Providers
        {
            get
            {
                if(_providers == null)
                {
                    LoadProviders();
                }
                return _providers;
            }
            set { _providers = value; }
        }

        public static string GetNamespace(string providerName)
        {
            return Providers[providerName].GeneratedNamespace;
        }

        public static string[] GetProviderNames()
        {
            if(Providers != null)
            {
                int providerCount = Providers.Count;
                string[] providerNames = new string[providerCount];
                int i = 0;
                foreach (DataProvider provider in Providers)
                {
                    providerNames[i] = provider.Name;
                    i++;
                }
                return providerNames;
            }
            return new string[]{};
        }

        public static DataProvider GetInstance(string providerName)
        {
            //ensure load
            LoadProviders();

            //ensure it's instanced
            if(String.IsNullOrEmpty(providerName) || String.IsNullOrEmpty(providerName.Trim()))
            {
                return defaultProvider;
            }
            else
            {
                DataProvider provider = _providers[providerName];
                if(provider != null)
                {
                    return provider;
                }
                else
                {
                    throw new Exception("No provider is defined with the name " + providerName);
                }
            }
        }

        static DataProvider GetInstance()
        {
            return GetInstance(null);
        }

        public static void LoadProviders()
        {
            // Avoid claiming lock if providers are already loaded
            if (defaultProvider == null)
            {
                lock (_lock)
                {
                    // Do this again to make sure DefaultProvider is still null
                    if (defaultProvider == null)
                    {
                        //we allow for passing in a configuration section
                        //check to see if one's been passed in
                        if (section == null) {
                            
                            if (ConfigSectionSettings != null) {
                                section = ConfigSectionSettings;
                            } else {
                                //if not, check the local app space
                                section = (SubSonicSection)ConfigurationManager.GetSection(ConfigurationSectionName.SUB_SONIC_SERVICE);

                            }

                            //if it's still null, throw an exception
                            if (section == null)
                                throw new ConfigurationErrorsException("Can't find the SubSonicService in your application's config");
                        }
                        //set the builder's template directory
                        CodeService.TemplateDirectory = section.TemplateDirectory;

                        // Load registered providers and point DefaultProvider
                        // to the default provider
                        _providers = new DataProviderCollection();
                        ProvidersHelper.InstantiateProviders(section.Providers, _providers, typeof (DataProvider));

                        defaultProvider = _providers[section.DefaultProvider];
                        
                        if (defaultProvider == null)
                            throw new ConfigurationErrorsException("Invalid Default Provider");

                        //ResetDatabases();

                        if (defaultProvider == null)
                        {
                            throw new ProviderException ("Unable to load default DataProvider");
                        }
                    }
                }
            }
        }

        public static void ResetDatabases()
        {
            
            /*
            //add in the providers, and their databases
            Databases = new DBCollection();
            if(_providers != null)
            {
                foreach(DataProvider prov in _providers)
                {
                    DB db = new DB(prov.Name);
                    db.ConnectionString = prov.ConnectionString;
                    db.ProviderName = prov.Name;
                    db.Provider = prov;
                    Databases.Add(db);
                }
            }
             * */
        }

        public static void AddProvider(DataProvider provider)
        {
            if (_providers == null)
            {
                _providers = new DataProviderCollection();
            }
            _providers.Add(provider);
        }

        #endregion


        #region Scripting
        
        public static string ScriptTableData(string tableName)
        {
            return GetInstance().ScriptData(tableName);
        }

        public static string ScriptTableData(string tableName, string providerName)
        {
            return GetInstance(providerName).ScriptData(tableName);
        }

        public static string ScriptData() 
        {
            return ScriptData(String.Empty);
        }

        public static string ScriptData(string providerName)
        {
            string[] tables = GetTableNames(providerName);

            StringBuilder sb = new StringBuilder();

            foreach(string table in tables)
            {
                sb.Append(GetInstance(providerName).ScriptData(table,providerName) + Environment.NewLine + Environment.NewLine);
            }
            return sb.ToString();
        }
        public static string ScriptData(string tableName,string providerName) {

            return GetInstance(providerName).ScriptData(tableName,providerName);
        }
        public static string ScriptSchema()
        {
            return GetInstance().ScriptSchema();
        }

        public static string ScriptSchema(string providerName)
        {
            return GetInstance(providerName).ScriptSchema();
        }

        #endregion


        #region Database Interaction

        public static List<StoredProcedure> GetSPSchemaCollection(string providerName) {
            //DataService.ResetDatabases();
            List<StoredProcedure> _sps = new List<StoredProcedure>();
            string[] sps = GetSPList(providerName);

            //System.Data.IDataReader rdr = null;
            DataProvider provider = Providers[providerName];
            foreach (string s in sps) {

                if (String.IsNullOrEmpty(provider.SPStartsWith) || s.StartsWith(provider.SPStartsWith))
                {
                    if (CodeService.ShouldGenerate(s, provider.IncludeProcedures, provider.ExcludeProcedures, provider))
                    {
                        //declare the sp
                        StoredProcedure sp = new StoredProcedure(s, provider);

                        //get the params
                        IDataReader rdr = GetSPParams(s, providerName);
                        while(rdr.Read())
                        {
                            StoredProcedure.Parameter par = new StoredProcedure.Parameter();
                            provider.SetParameter(rdr, par);
                            par.QueryParameter = provider.MakeParam(par.Name);
                            par.DisplayName = Utility.GetParameterName(par.Name, provider);
                            sp.Parameters.Add(par);
                        }
                        rdr.Close();
                        _sps.Add(sp);
                    }
                }
            }
            return _sps;
        }

        public static string GetSql(Query qry)
        {
            return GetInstance(qry.ProviderName).GetSql(qry);
        }

        public static QueryCommand BuildCommand(Query qry)
        {
            QueryCommand cmd = GetInstance(qry.ProviderName).BuildCommand(qry);
            cmd.ProviderName = qry.ProviderName;
            cmd.Provider = qry.Provider;
            cmd.CommandTimeout = qry.CommandTimeout;
            return cmd;
        }

        /// <summary>
        /// Executes a transaction of the passed-in commands. 
        /// </summary>
        /// <param name="commands"></param>
        public static void ExecuteTransaction(QueryCommandCollection commands)
        {   
            if (commands == null || commands.Count == 0)
                return;
            commands[0].Provider.ExecuteTransaction(commands);
            //GetInstance().ExecuteTransaction(commands);
        }

        /// <summary>
        /// Executes a transaction of the passed-in commands. 
        /// </summary>
        /// <param name="commands"></param>
        /// <param name="providerName"></param>
        public static void ExecuteTransaction(QueryCommandCollection commands, string providerName)
        {
            GetInstance(providerName).ExecuteTransaction(commands);
        }

        public static int GetRecordCount(Query qry)
        {
            return GetInstance(qry.ProviderName).GetRecordCount(qry);
        }

        /// <summary>
        /// Returns an IDataReader using the passed-in command
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns>IDataReader</returns>
        public static IDataReader GetReader(QueryCommand cmd)
        {
            return GetInstance(cmd.ProviderName).GetReader(cmd);
        }

        /// <summary>
        /// Returns a DataSet based on the passed-in command
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(QueryCommand cmd)
        {
			return GetInstance(cmd.ProviderName).GetDataSet(cmd);
        }

        /// <summary>
        /// Returns a DataSet based on the passed-in command
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public static T GetDataSet<T>(QueryCommand cmd) where T : DataSet, new()
        {
            return GetInstance(cmd.ProviderName).GetDataSet<T>(cmd);
        }

        /// <summary>
        /// Returns a scalar object based on the passed-in command
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public static object ExecuteScalar(QueryCommand cmd)
        {
            return GetInstance(cmd.ProviderName).ExecuteScalar(cmd);
        }

        /// <summary>
        /// Executes a pass-through query on the DB
        /// </summary>
        /// <param name="cmd"></param>
        public static int ExecuteQuery(QueryCommand cmd)
        {
            return GetInstance(cmd.ProviderName).ExecuteQuery(cmd);
        }

        #endregion

        #region Schema

        //public static TableSchema.Table GetSchema(string tableName, string providerName)
        //{
        //    string[] views = GetViewNames(providerName);
        //    foreach (string view in views)
        //    {
        //        if (Utilities.Utility.IsMatch(view, tableName))
        //        {
        //            return GetSchema(tableName, providerName, TableType.View);
        //        }
        //    }
        //    return GetSchema(tableName, providerName, TableType.Table);
        //}
        /// <summary>
        /// Reverse-compat overload; this defaults to Tables. if you need to specify a view, use the 
        /// other method call.
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public static TableSchema.Table GetSchema(string tableName, string providerName) {
            return GetSchema(tableName, providerName, TableType.Table);
        }
        public static TableSchema.Table GetSchema(string tableName, string providerName, TableType tableType)
        {
            TableSchema.Table result;
            
            //the schema could be held in memory
            //if it is, use that; else go to the DB

            DataProvider provider = GetInstance(providerName);

            if (provider.schemaCollection.ContainsKey(tableName)) {
                result = provider.schemaCollection[tableName];
            
            } else {
                //look it up
                //when done, add it to the collection
                result = provider.GetTableSchema(tableName, tableType);
                provider.AddSchema(tableName,result);

            }
            
            return result;
        }



        public static TableSchema.Table GetTableSchema(string tableName, string providerName)
        {

            //try tables first
            TableSchema.Table result = GetSchema(tableName, providerName, TableType.Table);
            
            //try views
            if(result==null)
                result = GetSchema(tableName, providerName, TableType.View);
            
            return result;
        }

        public static TableSchema.Table GetTableSchema(string tableName, string providerName, TableType tableType)
        {
            return GetSchema(tableName, providerName, tableType);
        }

        public static string[] GetTableNames(string providerName)
        {
            return GetInstance(providerName).GetTableNameList();
        }

        public static string[] GetOrderedTableNames(string providerName)
        {
            List<string> addedTables = new List<string>();
            TableSchema.Table[] tbls = GetTables(providerName);
            for(int i = 0; i < tbls.Length; i++)
            {
                int fkCount = tbls[i].ForeignKeys.Count;
                if(fkCount == 0)
                {
                    addedTables.Add(tbls[i].TableName);
                }
                else
                {
                    List<string> fkTables = new List<string>();
                    foreach (TableSchema.TableColumn col in tbls[i].Columns)
                    {
                        if(col.IsForeignKey)
                        {
                            fkTables.Add(col.ForeignKeyTableName);
                        }
                    }

                    bool allDependenciesAdded = true;
                    foreach(string fkTableName in fkTables)
                    {
                        if (!addedTables.Contains(fkTableName) && !Utility.IsMatch(fkTableName, tbls[i].TableName))
                        {
                            allDependenciesAdded = false;
                            break;
                        }
                    }

                    if(allDependenciesAdded && !addedTables.Contains(tbls[i].TableName))
                    {
                        addedTables.Add(tbls[i].TableName);
                    }
                }
            }

            for (int i = 0; i < tbls.Length; i++)
            {
                if(tbls[i].PrimaryKeys.Length < 2 && !addedTables.Contains(tbls[i].TableName))
                {
                    addedTables.Add(tbls[i].TableName);
                }
            }

            for (int i = 0; i < tbls.Length; i++)
            {
                if (tbls[i].PrimaryKeys.Length > 1 && !addedTables.Contains(tbls[i].TableName))
                {
                    addedTables.Add(tbls[i].TableName);
                }
            }

            return addedTables.ToArray();
        }

        public static TableSchema.Table[] GetTables(string providerName)
        {
            string[] tableNames = GetTableNames(providerName);
            TableSchema.Table[] tables = new TableSchema.Table[tableNames.Length];
            for(int i=0; i<tables.Length; i++)
            {
                tables[i] = GetSchema(tableNames[i], providerName, TableType.Table);
            }
            return tables;
        }

        public static string[] GetForeignKeyTables(string tableName, string providerName)
        {
            return GetInstance().GetForeignKeyTables(tableName);
        }

        //public static string[] GetManyToMany(string providerName, string tableName)
        //{
        //    return GetInstance(providerName).GetManyToManyTables(tableName);
        //}
        
        public static string[] GetViewNames(string providerName)
        {
            return GetInstance(providerName).GetViewNameList();
        }


        public static TableSchema.Table[] GetViews(string providerName)
        {
            string[] viewNames = GetViewNames(providerName);
            TableSchema.Table[] views = new TableSchema.Table[viewNames.Length];
            for (int i = 0; i < views.Length; i++)
            {
                views[i] = GetSchema(viewNames[i], providerName, TableType.View);
            }
            return views;
        }


        public static string[] GetSPList(string providerName)
        {
            return GetInstance(providerName).GetSPList();
        }

        public static ArrayList GetPrimaryKeyTableNames(string tableName, string providerName)
        {
            return GetInstance(providerName).GetPrimaryKeyTableNames(tableName);
        }

        public static TableSchema.Table[] GetPrimaryKeyTables(string tableName, string providerName)
        {
            return GetInstance(providerName).GetPrimaryKeyTables(tableName);
        }

        public static string GetForeignKeyTableName(string fkColumn, string tableName, string providerName)
        {
            return GetInstance(providerName).GetForeignKeyTableName(fkColumn, tableName);
        }

        public static TableSchema.Table GetForeignKeyTable(TableSchema.TableColumn fkColumn, TableSchema.Table table)
        {
            return GetInstance(table.Provider.Name).GetForeignKeyTable(fkColumn, table);
        }

        public static IDataReader GetSPParams(string spName, string providerName)
        {
            return GetInstance(providerName).GetSPParams(spName);
        }

        public static DbType GetDbType(string dataType, string providerName)
        {
            return GetInstance(providerName).GetDbType(dataType);
        }

        public static string GetClientType(string providerName)
        {
            return GetInstance(providerName).GetType().Name;
        }

        #endregion

        #region DBCommand Helpers
        
        internal static IDbCommand GetIDbCommand(QueryCommand qry)
        {
            return GetInstance(qry.ProviderName).GetCommand(qry);
        }

        internal static DbCommand GetDbCommand(QueryCommand qry)
        {
            return GetInstance(qry.ProviderName).GetDbCommand(qry);
        }

        #endregion
    }
}