Beispiel #1
0
        private static string GetDatabaseName(DBEnvironments environment)
        {
            string dbName;

            switch (environment)
            {
            case DBEnvironments.Development:
                dbName = Properties.Settings.Default.Database;
                break;

            case DBEnvironments.Production:
                dbName = Properties.Settings.Default.Production_Database;
                break;

            default:
                dbName = Properties.Settings.Default.Database;
                break;
            }

            if (string.IsNullOrEmpty(dbName))
            {
                throw new ArgumentException("You must supply the file name of a database", dbName);
            }

            return(dbName);
        }
Beispiel #2
0
        /// <summary> Eliminar espacios al final de una columna de tipo string. </summary>
        ///
        /// <param name="dbEnvironment"></param>
        /// <param name="dbVersion"></param>
        /// <param name="dbColumna"></param>
        ///
        /// <returns> Columna sin espacios. </returns>
        ///
        public string GetCvtRTrim(DBEnvironments dbEnvironment, string dbVersion, string dbColumna)
        {
            string strRTrimStrim = "";

            switch (dbEnvironment)
            {
            case DBEnvironments.SQL:
                strRTrimStrim = "RTRIM(" + dbColumna + ")";
                break;

            case DBEnvironments.DB2:
                strRTrimStrim = "RTRIM(" + dbColumna + ")";
                break;

            case DBEnvironments.ORACLE:
                strRTrimStrim = "RTRIM(" + dbColumna + ")";
                break;

            case DBEnvironments.MSACCESS:
                strRTrimStrim = "RTRIM(" + dbColumna + ")";
                break;

            case DBEnvironments.VFP:
                strRTrimStrim = "RTRIM(" + dbColumna + ")";
                break;

            default:
                strRTrimStrim = "RTRIM(" + dbColumna + ")";
                break;
            }

            return(strRTrimStrim);
        }
Beispiel #3
0
        // Obtener la cadena Like - Método.
        public string GetCvtLike(DBEnvironments dbEnvironment, string dbVersion)
        {
            string strLike = "";

            switch (dbEnvironment)
            {
            case DBEnvironments.SQL:
                strLike = "%";
                break;

            case DBEnvironments.DB2:
                strLike = "%";
                break;

            case DBEnvironments.ORACLE:
                strLike = "%";
                break;

            case DBEnvironments.MSACCESS:
                strLike = "*";
                break;

            case DBEnvironments.VFP:
                strLike = "%";
                break;

            default:
                strLike = "%";
                break;
            }

            return(strLike);
        }
Beispiel #4
0
        public static void DeleteDB(DBEnvironments environment)
        {
            string dbName = GetDatabaseName(environment);

            if (!IsExists(environment, dbName))
            {
                MessageOut("{0} not found", dbName);
                return;
            }

            // Create Connection String from Configuration File
            string sqlConStr = BuildConnectionString(environment, "master");

            MessageOut("in {0}", sqlConStr);

            // Uninstall ASP.NET Membership table
            // No need because we can delete just the database
            // SqlServices.Uninstall(dbName, SqlFeatures.All, sqlConStr);

            // Single user to cut current connections.
            string sqlQuery = string.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE; \r\n GO", dbName);

            SqlScriptHelper.ExecuteScript(sqlQuery, sqlConStr);

            // DROP DATABASE in context of master db
            sqlQuery = string.Format("DROP DATABASE {0}; \r\n GO", dbName);
            SqlScriptHelper.ExecuteScript(sqlQuery, sqlConStr);
        }
Beispiel #5
0
        internal static void ViewVersion(DBEnvironments environment)
        {
            string dbName = GetDatabaseName(environment);

            if (!IsExists(environment, dbName))
            {
                MessageOut("{0} not found", dbName);
                return;
            }

            // Create Connection String from Configuration File
            string sqlConStr = BuildConnectionString(environment, dbName);

            MessageOut("in {0}", sqlConStr);

            string versionName = GetLatestSchemaVersionName(sqlConStr);

            if (!string.IsNullOrEmpty(versionName))
            {
                MessageOut("Current Version: {0}", versionName);
            }
            else
            {
                MessageOut("Database is empty");
            }
        }
Beispiel #6
0
        //------------------------------------------------------------------
        // Métodos públicos de conversión de tipos según tipo BBDD.
        //------------------------------------------------------------------

        /// <summary> Obtener el formato para procesar una columna NULL. </summary>
        ///
        /// <param name="dbEnvironment"></param>
        /// <param name="dbVersion"></param>
        /// <param name="dbColumna"></param>
        /// <param name="sustValor"></param>
        ///
        /// <returns> Cadena con formato. </returns>
        ///
        public string GetCvtNull(DBEnvironments dbEnvironment, string dbVersion, string dbColumna, object sustValor)
        {
            string strNullString = "";

            switch (dbEnvironment)
            {
            case DBEnvironments.SQL:
                strNullString = "ISNULL(" + dbColumna + ", " + sustValor + ")";
                break;

            case DBEnvironments.DB2:
                strNullString = sustValor.ToString();
                break;

            case DBEnvironments.ORACLE:
                strNullString = "NVL(" + dbColumna + ", " + sustValor + ")";
                break;

            case DBEnvironments.MSACCESS:
                strNullString = "ISNULL(" + dbColumna + ", " + sustValor + ")";
                break;

            case DBEnvironments.VFP:
                strNullString = sustValor.ToString();
                break;

            default:
                strNullString = "IIF(ISNULL(" + dbColumna + "), " + sustValor + ")";
                break;
            }

            return(strNullString);
        }
Beispiel #7
0
        //------------------------------------------------------------------
        // Métodos públicos de conversión de tipos según tipo BBDD.
        //------------------------------------------------------------------

        // Obtener substring - Método.
        public string GetCvtSubStr(DBEnvironments dbEnvironment, string dbVersion, string dbColumna, int posicionInicial, int lenPosicionFinal)
        {
            string strSubstring = "";

            switch (dbEnvironment)
            {
            case DBEnvironments.SQL:
                strSubstring = "SUBSTRING(" + dbColumna + ", " + posicionInicial.ToString("####") + ", " + lenPosicionFinal.ToString("####") + ")";
                break;

            case DBEnvironments.DB2:
                strSubstring = "SUBSTR(" + dbColumna + ", " + posicionInicial.ToString("####") + ", " + lenPosicionFinal.ToString("####") + ")";
                break;

            case DBEnvironments.ORACLE:
                strSubstring = "SUBSTR(" + dbColumna + ", " + posicionInicial.ToString("####") + ", " + lenPosicionFinal.ToString("####") + ")";
                break;

            case DBEnvironments.MSACCESS:
                strSubstring = "MID(" + dbColumna + ", " + posicionInicial.ToString("####") + ", " + lenPosicionFinal.ToString("####") + ")";
                break;

            case DBEnvironments.VFP:
                strSubstring = "SUBSTR(" + dbColumna + ", " + posicionInicial.ToString("####") + ", " + lenPosicionFinal.ToString("####") + ")";
                break;

            default:
                strSubstring = "SUBSTRING(" + dbColumna + ", " + posicionInicial.ToString("####") + ", " + lenPosicionFinal.ToString("####") + ")";
                break;
            }

            return(strSubstring);
        }
Beispiel #8
0
        public static void CreateDB(DBEnvironments environment)
        {
            string dbName = GetDatabaseName(environment);

            if (IsExists(environment, dbName))
            {
                MessageOut("{0} already exists", dbName);
                return;
            }

            // Create Connection String from Configuration File
            string sqlConStr = BuildConnectionString(environment, "master");

            MessageOut("in {0}", sqlConStr);

            // CREATE DATABASE in context of master db
            string sqlQuery = string.Format("CREATE DATABASE {0}; \r\n GO", dbName);

            SqlScriptHelper.ExecuteScript(sqlQuery, sqlConStr);

            // Create schema_version table
            sqlConStr = BuildConnectionString(environment, dbName);
            MessageOut("in {0}", sqlConStr);
            SqlScriptHelper.ExecuteScriptFromEmbeddedResource("BananaCoding.Tools.Database.DBScripts.create_schema.sql", sqlConStr);
        }
Beispiel #9
0
        // Obtener la cadena de concatenación - Método.
        public string GetCvtCat(DBEnvironments dbEnvironment, string dbVersion)
        {
            string strConcat = "";

            switch (dbEnvironment)
            {
            case DBEnvironments.SQL:
                strConcat = "+";
                break;

            case DBEnvironments.DB2:
                strConcat = "A";
                break;

            case DBEnvironments.ORACLE:
                strConcat = "||";
                break;

            case DBEnvironments.MSACCESS:
                strConcat = "+";
                break;

            case DBEnvironments.VFP:
                strConcat = "+";
                break;

            default:
                strConcat = "+";
                break;
            }

            return(strConcat);
        }
Beispiel #10
0
        public static void DeleteDB(DBEnvironments environment)
        {
            string dbName = GetDatabaseName(environment);

            if (!IsExists(environment, dbName))
            {
                MessageOut("{0} not found", dbName);
                return;
            }

            // Create Connection String from Configuration File
            string sqlConStr = BuildConnectionString(environment, "master");
            MessageOut("in {0}", sqlConStr);

            // Uninstall ASP.NET Membership table
            // No need because we can delete just the database
            // SqlServices.Uninstall(dbName, SqlFeatures.All, sqlConStr);

            // Single user to cut current connections.
            string sqlQuery = string.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE; \r\n GO", dbName);
            SqlScriptHelper.ExecuteScript(sqlQuery, sqlConStr);

            // DROP DATABASE in context of master db
            sqlQuery = string.Format("DROP DATABASE {0}; \r\n GO", dbName);
            SqlScriptHelper.ExecuteScript(sqlQuery, sqlConStr);
        }
Beispiel #11
0
        public static void GrantPermission(DBEnvironments environment, string userToGrant)
        {
            string dbName = GetDatabaseName(environment);

            // Create Connection String from Configuration File
            string sqlConStr = BuildConnectionString(environment, dbName);
            MessageOut("in {0}", sqlConStr);

            userToGrant = userToGrant.Replace("NT_", "NT ").Replace("NETWORK_", "NETWORK ");

            // Grant DATABASE in context of master db
            string sqlQuery = SqlScriptHelper.LoadScriptFromEmbeddedResource("BananaCoding.Tools.Database.DBScripts.grant_permission.sql");
            sqlQuery = sqlQuery.Replace("[DBNAME]", dbName);
            sqlQuery = sqlQuery.Replace("[DBUSER]", userToGrant);
            SqlScriptHelper.ExecuteScript(sqlQuery, sqlConStr);
            MessageOut("Grant Permission '{0}' on {1}", userToGrant, dbName);
        }
Beispiel #12
0
        /// <summary>
        /// Obtener una clave de la lista de numeradores.
        /// </summary>
        ///
        /// <param name="dbEntorno"> Entorno activo de la BBDD</param>
        /// <param name="dbVersion"> Versión de la BBDD</param>
        /// <param name="Numerador"> Código del numerador</param>
        /// <param name="nLongitud"> Nº de caracteres a devolver</param>
        /// <param name="nCuantos"> Incremento numerador (Def: 1) </param>
        ///
        /// <returns> Cadena con el numerador (null si error) </returns>
        ///
        public string NewNum(string Numerador, DBEnvironments dbEntorno = DBEnvironments.NULL, string dbVersion = null, int nLongitud = 10, int nCuantos = 1)
        {
            string strRetorno = null;
            string strSQL     = "";

            try
            {
                dbEntorno = dbEntorno == DBEnvironments.NULL ? _DBEntorno : dbEntorno;
                dbVersion = dbVersion == null ? _DBVersion : dbVersion;

                switch (dbEntorno)
                {
                case DBEnvironments.SQL:
                    strSQL     = "Select NEXT VALUE FOR '" + Numerador + "'";
                    strRetorno = System.Convert.ToString(ExecDirectSQL(strSQL)).PadLeft(nLongitud, '0');
                    break;

                case DBEnvironments.DB2:
                    break;

                case DBEnvironments.ORACLE:
                    break;

                case DBEnvironments.MSACCESS:
                    break;

                case DBEnvironments.VFP:
                    break;

                default:
                    break;
                }
            }

            catch (OdbcException ex)
            {
                UsrError = ex.Message;
            }

            catch (Exception ex)
            {
                UsrError = ex.Message;
            }

            return(strRetorno);
        }
Beispiel #13
0
        internal static void LoadFixtures(Task task)
        {
            DBEnvironments environment = task.Environment;
            string         dbName      = GetDatabaseName(environment);

            if (!IsExists(environment, dbName))
            {
                MessageOut("{0} not found", dbName);
                return;
            }

            // Create Connection String from Configuration File
            string sqlConStr = BuildConnectionString(environment, dbName);

            MessageOut("in {0}", sqlConStr);

            LoadFixtures(sqlConStr, task.FixtureTo);
        }
Beispiel #14
0
        public static void GrantPermission(DBEnvironments environment, string userToGrant)
        {
            string dbName = GetDatabaseName(environment);

            // Create Connection String from Configuration File
            string sqlConStr = BuildConnectionString(environment, dbName);

            MessageOut("in {0}", sqlConStr);

            userToGrant = userToGrant.Replace("NT_", "NT ").Replace("NETWORK_", "NETWORK ");

            // Grant DATABASE in context of master db
            string sqlQuery = SqlScriptHelper.LoadScriptFromEmbeddedResource("BananaCoding.Tools.Database.DBScripts.grant_permission.sql");

            sqlQuery = sqlQuery.Replace("[DBNAME]", dbName);
            sqlQuery = sqlQuery.Replace("[DBUSER]", userToGrant);
            SqlScriptHelper.ExecuteScript(sqlQuery, sqlConStr);
            MessageOut("Grant Permission '{0}' on {1}", userToGrant, dbName);
        }
Beispiel #15
0
        internal static void InstallMembership(DBEnvironments environment)
        {
            string dbName = GetDatabaseName(environment);

            if (!IsExists(environment, dbName))
            {
                MessageOut("{0} not found", dbName);
                return;
            }

            // Create Connection String from Configuration File
            string sqlConStr = BuildConnectionString(environment, dbName);

            // Install ASP.NET membership tables
            SqlServices.Install(dbName, SqlFeatures.All, sqlConStr);

            var versionName = "000_ASPNET_Membership";

            // Update Schema version
            UpdateVersionSchema(sqlConStr, versionName);
        }
Beispiel #16
0
        internal static bool IsExists(DBEnvironments environment, string dbName)
        {
            if (string.IsNullOrEmpty(dbName))
            {
                throw new ArgumentException("You must supply the file name of a database", dbName);
            }

            // Create Connection String from Configuration File
            string sqlConStr = BuildConnectionString(environment, "master");

            using (SqlConnection connection = new SqlConnection(sqlConStr))
            {
                string sqlQuery = string.Format("SELECT name FROM sys.databases WHERE name = '{0}'", dbName);

                SqlCommand cmd = new SqlCommand(sqlQuery, connection);

                connection.Open();
                object result = cmd.ExecuteScalar();

                return(result != null && !Convert.IsDBNull(result));
            }
        }
Beispiel #17
0
        public static void CreateDB(DBEnvironments environment)
        {
            string dbName = GetDatabaseName(environment);

            if (IsExists(environment, dbName))
            {
                MessageOut("{0} already exists", dbName);
                return;
            }

            // Create Connection String from Configuration File
            string sqlConStr = BuildConnectionString(environment, "master");
            MessageOut("in {0}", sqlConStr);

            // CREATE DATABASE in context of master db
            string sqlQuery = string.Format("CREATE DATABASE {0}; \r\n GO", dbName);
            SqlScriptHelper.ExecuteScript(sqlQuery, sqlConStr);

            // Create schema_version table
            sqlConStr = BuildConnectionString(environment, dbName);
            MessageOut("in {0}", sqlConStr);
            SqlScriptHelper.ExecuteScriptFromEmbeddedResource("BananaCoding.Tools.Database.DBScripts.create_schema.sql", sqlConStr);
        }
Beispiel #18
0
        private static string BuildConnectionString(DBEnvironments environment, string dbName)
        {
            SqlConnectionStringBuilder sqlConBuilder = new SqlConnectionStringBuilder();
            bool isDev = environment == DBEnvironments.Development;

            sqlConBuilder.DataSource = isDev ? Properties.Settings.Default.Server : Properties.Settings.Default.Production_Server;
            string userName = isDev ? Properties.Settings.Default.UserName : Properties.Settings.Default.Production_UserName;

            sqlConBuilder.InitialCatalog = dbName;
            sqlConBuilder.Enlist         = false;
            sqlConBuilder.ConnectTimeout = 900;

            if (string.IsNullOrEmpty(userName))
            {
                sqlConBuilder.IntegratedSecurity = true;
            }
            else
            {
                sqlConBuilder.IntegratedSecurity = false;
                sqlConBuilder.UserID             = isDev ? Properties.Settings.Default.UserName : Properties.Settings.Default.Production_UserName;
                sqlConBuilder.Password           = isDev ? Properties.Settings.Default.Password : Properties.Settings.Default.Production_Password;
            }
            return(sqlConBuilder.ConnectionString);
        }
Beispiel #19
0
        internal static void InstallMembership(DBEnvironments environment)
        {
            string dbName = GetDatabaseName(environment);

            if (!IsExists(environment, dbName))
            {
                MessageOut("{0} not found", dbName);
                return;
            }

            // Create Connection String from Configuration File
            string sqlConStr = BuildConnectionString(environment, dbName);

            // Install ASP.NET membership tables
            SqlServices.Install(dbName, SqlFeatures.All, sqlConStr);

            var versionName = "000_ASPNET_Membership";
            // Update Schema version
            UpdateVersionSchema(sqlConStr, versionName);
        }
Beispiel #20
0
        internal static void Migrate(Task task)
        {
            DBEnvironments environment = task.Environment;
            int            migrateTo   = task.MigrateTo;
            bool           aspNET      = task.AspNet;

            string dbName = GetDatabaseName(environment);

            if (!IsExists(environment, dbName))
            {
                MessageOut("{0} not found", dbName);
                return;
            }

            // Create Connection String from Configuration File
            string sqlConStr = BuildConnectionString(environment, dbName);

            MessageOut("in {0}", sqlConStr);

            // Update Schema version
            int?latestVersion = GetLatestSchemaVersion(sqlConStr);

            // Grant Permission
            if (!string.IsNullOrEmpty(task.GrantPermission))
            {
                GrantPermission(environment, task.GrantPermission);
            }

            // Migrate 000 first
            if (!latestVersion.HasValue && aspNET)
            {
                InstallMembership(environment);
            }

            // Get all migration files
            var sw = new Stopwatch();

            string[] files = Directory.GetFiles(@"migrate", "*.sql");
            Array.Sort <string>(files);
            foreach (var sqlscript in files)
            {
                // ignore vim backup file
                if (sqlscript.EndsWith("~"))
                {
                    continue;
                }

                string versionName   = Path.GetFileNameWithoutExtension(sqlscript);
                int    versionNumber = GetVersionNumber(versionName);
                if (latestVersion.HasValue && versionNumber <= latestVersion)
                {
                    continue;
                }
                if (migrateTo != 0 && versionNumber > migrateTo)
                {
                    break;
                }

                using (TransactionScope scope = new TransactionScope())
                {
                    MessageOut(@"Run script ------ {0} --------------------------", versionName);

                    sw.Reset();
                    sw.Start();

                    // Run Sql from file
                    SqlScriptHelper.ExecuteScriptFile(sqlscript, sqlConStr);

                    // Update Schema version
                    UpdateVersionSchema(sqlConStr, versionName);

                    sw.Stop();
                    MessageOut(@"Run script ------ {0} ({1}ms) ------ successfully.", versionName, sw.ElapsedMilliseconds);

                    scope.Complete();
                }
            }

            // Load Fixtures
            if (task.Fixture)
            {
                LoadFixtures(sqlConStr);
            }
        }
Beispiel #21
0
        internal static bool IsExists(DBEnvironments environment, string dbName)
        {
            if (string.IsNullOrEmpty(dbName))
            {
                throw new ArgumentException("You must supply the file name of a database", dbName);
            }

            // Create Connection String from Configuration File
            string sqlConStr = BuildConnectionString(environment, "master");

            using (SqlConnection connection = new SqlConnection(sqlConStr))
            {
                string sqlQuery = string.Format("SELECT name FROM sys.databases WHERE name = '{0}'", dbName);

                SqlCommand cmd = new SqlCommand(sqlQuery, connection);

                connection.Open();
                object result = cmd.ExecuteScalar();

                return (result != null && !Convert.IsDBNull(result));
            }
        }
Beispiel #22
0
        private static string GetDatabaseName(DBEnvironments environment)
        {
            string dbName;
            switch (environment)
            {
                case DBEnvironments.Development:
                    dbName = Properties.Settings.Default.Database;
                    break;
                case DBEnvironments.Production:
                    dbName = Properties.Settings.Default.Production_Database;
                    break;
                default:
                    dbName = Properties.Settings.Default.Database;
                    break;
            }

            if (string.IsNullOrEmpty(dbName))
            {
                throw new ArgumentException("You must supply the file name of a database", dbName);
            }

            return dbName;
        }
Beispiel #23
0
        private static string BuildConnectionString(DBEnvironments environment, string dbName)
        {
            SqlConnectionStringBuilder sqlConBuilder = new SqlConnectionStringBuilder();
            bool isDev = environment == DBEnvironments.Development;
            sqlConBuilder.DataSource = isDev ? Properties.Settings.Default.Server : Properties.Settings.Default.Production_Server;
            string userName = isDev ? Properties.Settings.Default.UserName : Properties.Settings.Default.Production_UserName;
            sqlConBuilder.InitialCatalog = dbName;
            sqlConBuilder.Enlist = false;
            sqlConBuilder.ConnectTimeout = 900;

            if (string.IsNullOrEmpty(userName))
            {
                sqlConBuilder.IntegratedSecurity = true;
            }
            else
            {
                sqlConBuilder.IntegratedSecurity = false;
                sqlConBuilder.UserID = isDev ? Properties.Settings.Default.UserName : Properties.Settings.Default.Production_UserName;
                sqlConBuilder.Password = isDev ? Properties.Settings.Default.Password : Properties.Settings.Default.Production_Password;
            }
            return sqlConBuilder.ConnectionString;
        }
Beispiel #24
0
        internal static void ViewVersion(DBEnvironments environment)
        {
            string dbName = GetDatabaseName(environment);

            if (!IsExists(environment, dbName))
            {
                MessageOut("{0} not found", dbName);
                return;
            }

            // Create Connection String from Configuration File
            string sqlConStr = BuildConnectionString(environment, dbName);
            MessageOut("in {0}", sqlConStr);

            string versionName = GetLatestSchemaVersionName(sqlConStr);
            if (!string.IsNullOrEmpty(versionName))
                MessageOut("Current Version: {0}", versionName);
            else
                MessageOut("Database is empty");
        }