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); }
/// <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); }
// 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); }
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); }
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"); } }
//------------------------------------------------------------------ // 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); }
//------------------------------------------------------------------ // 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); }
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); }
// 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); }
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); }
/// <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); }
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); }
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); }
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)); } }
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); }
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); } }
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)); } }
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; }
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; }
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"); }