Sdaleo/DBResources.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.IO;

namespace Sdaleo
{
    /// <summary>
    /// For SQL Server you can fill all locations, for SQLCE there are no Views,Sprocs
    /// FullResourceLocationContainingScripts, is the full resource folder where the sql scripts are located
    /// f.e. Application.DatabaseLayer.SQLScripts
    /// SQLScript_FileExt specifies which files to look for (f.e. .sql or .sqlce)
    /// </summary>
    public struct DBResource
    {
        public Assembly AssemblyContainingResources;
        public string FullResourceLocationContaingScripts_Tables;
        public string FullResourceLocationContaingScripts_Views;
        public string FullResourceLocationContaingScripts_Sprocs;
        public string SQLScript_FileExt;

        /// <summary>
        /// Useful for SQLCE DBResources, which only contains Tables
        /// </summary>
        /// <param name="asm">the assembly that contains the resources</param>
        /// <param name="FRLCS_Tables">The Location of where the table .sqlce scripts are located, can be blank, if not used, but that would not make sense</param>
        /// <param name="FileExt">file extension of the scripts, like .sqlce</param>
        public DBResource(Assembly asm, string FRLCS_Tables, string FileExt = ".sqlce")
        {
            this.AssemblyContainingResources = asm;
            this.FullResourceLocationContaingScripts_Tables = FRLCS_Tables;
            this.FullResourceLocationContaingScripts_Views = String.Empty;
            this.FullResourceLocationContaingScripts_Sprocs = String.Empty;
            this.SQLScript_FileExt = FileExt;
        }

        /// <summary>
        /// Useful for SQLServer DBResources, which can contain Tables, Views, Sprocs
        /// </summary>
        /// <param name="asm">the assembly that contains the resources</param>
        /// <param name="FRLCS_Tables">The Location of where the table .sql scripts are located, can be blank, if not used</param>
        /// <param name="FRLCS_Views">The Location of where the views .sql scripts are located, can be blank, if not used</param>
        /// <param name="FRLCS_Sprocs">The Location of where the sprocs .sql scripts are located, can be blank, if not used</param>
        /// <param name="FileExt">file extension of the scripts, like .sql</param>
        public DBResource(Assembly asm, string FRLCS_Tables, string FRLCS_Views, string FRLCS_Sprocs, string FileExt = ".sql")
        {
            this.AssemblyContainingResources = asm;
            this.FullResourceLocationContaingScripts_Tables = FRLCS_Tables;
            this.FullResourceLocationContaingScripts_Views = FRLCS_Views;
            this.FullResourceLocationContaingScripts_Sprocs = FRLCS_Sprocs;
            this.SQLScript_FileExt = FileExt;
        }
    }

    /// <summary>
    /// This class allows external callers to set SQL Resource Scripts,
    /// for easy table, view, sprocs creation and updating
    /// </summary>
    public static class DBResources
    {
        #region Private Statics

        private static Dictionary<string, DBResource> _DBResources = new Dictionary<string, DBResource>();

        #endregion

        #region DBResource Add/Get/Delete

        /// <summary>
        /// Used to Add a DBResource into DatabaseLayer
        /// </summary>
        /// <param name="uniqueName">Unique Name to identify the Resource by</param>
        /// <param name="dbResource">a DBResource structure that must contain the assembly and at least one resource, and the file extension for the scripts</param>
        /// <returns>true if added, false otherwise</returns>
        public static bool AddDBResource(string uniqueName, DBResource dbResource)
        {
            if (!String.IsNullOrEmpty(uniqueName) && !_DBResources.ContainsKey(uniqueName))
            {
                _DBResources.Add(uniqueName, dbResource);
                return true;
            }
            return false;
        }

        /// <summary>
        /// Used to Delete a DBResource from DatabaseLayer
        /// </summary>
        /// <param name="uniqueName">Unique Name to identify the Resource by</param>
        /// <returns>true if deleted, false otherwise</returns>
        public static bool DeleteDBResource(string uniqueName)
        {
            if (!String.IsNullOrEmpty(uniqueName) && _DBResources.ContainsKey(uniqueName))
            {
                _DBResources.Remove(uniqueName);
                return true;
            }
            return false;
        }

        /// <summary>
        /// Used to Retrieve a DBResource from DatabaseLayer
        /// </summary>
        /// <param name="uniqueName">Unique Name to identify the Resource by</param>
        /// <returns>the DBResource that was previously added by AddDBResource(), otherwise throws an error</returns>
        public static DBResource GetDBResource(string uniqueName)
        {
            if (!String.IsNullOrEmpty(uniqueName) && _DBResources.ContainsKey(uniqueName))
                return _DBResources[uniqueName];
            else
                throw new Exception("DBResource not found. Call AddDBResource First");
        }

        #endregion

        #region Internal Table, View, Sprocs, Resource Getter Functions

        /// <summary>
        /// Returns the Table Scripts for a specified DBResource
        /// </summary>
        /// <param name="uniqueName">Unique Name to identify the Resource by</param>
        /// <returns>an array of FRLCS Script Names</returns>
        internal static string[] GetTableDBResourceScripts(string uniqueName)
        {
            if (!String.IsNullOrEmpty(GetDBResource(uniqueName).FullResourceLocationContaingScripts_Tables))
                return GetScriptsFromResourceLocation(GetDBResource(uniqueName).AssemblyContainingResources, GetDBResource(uniqueName).FullResourceLocationContaingScripts_Tables, GetDBResource(uniqueName).SQLScript_FileExt);
            return null;
        }

        /// <summary>
        /// Returns the Views Scripts for a specified DBResource
        /// </summary>
        /// <param name="uniqueName">Unique Name to identify the Resource by</param>
        /// <returns>an array of FRLCS Script Names</returns>
        internal static string[] GetViewsDBResourceScripts(string uniqueName)
        {
            if (!String.IsNullOrEmpty(GetDBResource(uniqueName).FullResourceLocationContaingScripts_Views))
                return GetScriptsFromResourceLocation(GetDBResource(uniqueName).AssemblyContainingResources, GetDBResource(uniqueName).FullResourceLocationContaingScripts_Views, GetDBResource(uniqueName).SQLScript_FileExt);
            return null;
        }

        /// <summary>
        /// Returns the Sprocs Scripts for a specified DBResource
        /// </summary>
        /// <param name="uniqueName">Unique Name to identify the Resource by</param>
        /// <returns>an array of FRLCS Script Names</returns>
        internal static string[] GetSprocsDBResourceScripts(string uniqueName)
        {
            if(!String.IsNullOrEmpty(GetDBResource(uniqueName).FullResourceLocationContaingScripts_Sprocs))
                return GetScriptsFromResourceLocation(GetDBResource(uniqueName).AssemblyContainingResources, GetDBResource(uniqueName).FullResourceLocationContaingScripts_Sprocs, GetDBResource(uniqueName).SQLScript_FileExt);
            return null;
        }

        #endregion

        #region Internal ReadScript

        /// <summary>
        /// Use this to read the contents of a Script from a specified Resource
        /// </summary>
        /// <param name="uniqueName">Unique Name to identify the Resource by</param>
        /// <param name="FRLCS_Script">the Full Resource Location Name N Path of the Script to read</param>
        /// <returns>the contents of the script, split by '\n', or null if not found</returns>
        internal static string[] Read_FRLCS_Script(string uniqueName, string FRLCS_Script_Name)
        {
            using (StreamReader stream = new StreamReader(GetDBResource(uniqueName).AssemblyContainingResources.GetManifestResourceStream(FRLCS_Script_Name)))
            {
                if (stream != null)
                {
                    string txtContent = stream.ReadToEnd();

                    // Get Rid of carriage returns and new lines
                    string[] retVal = txtContent.Replace("\r","").Split('\n');

                    // iterate each line and trim whitespaces
                    for (int i = 0; i < retVal.Length; ++i)
                         retVal[i] = retVal[i].Trim();

                    return retVal;
                }
            }
            return null;
        }

        #endregion

        #region Private Static Helper Functions

        /// <summary>
        /// Returns the Scripts found from the specified Resource Location
        /// </summary>
        /// <param name="ResourceLocation">a FRLCS Path to where the Scripts are located</param>
        /// <param name="FileExt">File Extension used by the scripts</param>
        /// <returns>the FRLCS names of the Scripts, or null if none found</returns>
        private static string[] GetScriptsFromResourceLocation(Assembly asm, string ResourceLocation, string FileExt)
        {
            List<string> scriptsFRLCS = new List<String>();
            string[] resourceNames = asm.GetManifestResourceNames();
            foreach (string ResourceName in resourceNames)
            {
                if (ResourceName.StartsWith(ResourceLocation) && ResourceName.EndsWith(FileExt))
                    scriptsFRLCS.Add(ResourceName); // found
            }
            scriptsFRLCS.Sort();
            return scriptsFRLCS.ToArray();
        }

        #endregion
    }
}