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