public static void DropTableIfExists(
this IExecuteExpressionRoot self,
string tableName
)
{
self.Sql($"DROP TABLE IF EXISTS {tableName}");
}
public static void ResourceScript(this IExecuteExpressionRoot root, Assembly assembly, string resourceName)
{
using (var stream = assembly.GetManifestResourceStream(resourceName))
{
if (stream == null)
{
throw new Exception(string.Format("Error retrieving resource {0} from assembly {1}", resourceName, assembly.FullName));
}
try
{
using (var reader = new StreamReader(stream))
{
//The method IExecuteExpressionRoot.Sql calls a string format, therefor we have to escape
//the { and } in some declarations.
var script = reader.ReadToEnd().Replace("{", "{{").Replace("}", "}}");
var statements = SplitScriptByGo(script);
foreach (var statement in statements)
{
root.Sql(statement.Statement);
}
}
}
catch (Exception ex)
{
throw new Exception(string.Format("Error using resource {0} from assembly {1}", resourceName, assembly.FullName), ex);
}
}
}
public static void AdicionarPagina(this IExecuteExpressionRoot execute, string campoReferencia,
TipoDeExpressaoAdicionarPagina tipoDeExpressaoAdicionarPagina, string sqlNovaPagina,
string caminhoNovaPagina)
{
string expressao;
switch (tipoDeExpressaoAdicionarPagina)
{
case TipoDeExpressaoAdicionarPagina.PeloCaminhoDoPai:
expressao = $"SELECT QT_Right FROM TB_Pagina WHERE TX_CaminhoRelativo LIKE '{campoReferencia}'";
break;
case TipoDeExpressaoAdicionarPagina.PeloNomeDoPai:
expressao = $"SELECT QT_Right FROM TB_Pagina WHERE TX_Nome LIKE '{campoReferencia}'";
break;
case TipoDeExpressaoAdicionarPagina.PeloCaminhoDoIrmao:
expressao = $"SELECT QT_Right + 1 FROM TB_Pagina WHERE TX_CaminhoRelativo LIKE '{campoReferencia}'";
break;
default:
throw new Exception("Tipo de expressão não implementada.");
}
var script =
BasicScriptsResources.AdicionarPaginaScript.Replace("#EXPRESSAO#", expressao)
.Replace("#CAMINHO_NOVA_PAGINA#", caminhoNovaPagina);
// Abrir espaço pra nova página.
execute.Sql(script);
// Incluir nova página.
execute.Sql(sqlNovaPagina);
}
public static void DropViewIfExist(
this IExecuteExpressionRoot execute,
string tableName
)
{
execute.Sql($"DROP VIEW IF EXISTS {tableName}");
}
public static void AppScript(this IExecuteExpressionRoot executeExpressionRoot, string path)
{
var parameters = new Dictionary <string, string>
{
{ "ScriptParameter", Program.Configuration.GetValue <string>("ScriptParameter") }
};
executeExpressionRoot.Script(path, parameters);
}
public static void RemovePrimaryKey(this IExecuteExpressionRoot execute, string tableName)
{
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("Table name can't be null or empty");
}
var script = BasicScriptsResources.RemovePrimaryKeyScript.Replace("#TABLE_NAME#", tableName);
execute.Sql(script);
}
public static void RemoverPagina(this IExecuteExpressionRoot execute, string caminhoRelativo)
{
var left = $"SELECT TOP 1 QT_Left FROM TB_Pagina WHERE TX_CaminhoRelativo LIKE '{caminhoRelativo}'";
var script = BasicScriptsResources.RemoverPaginaScript.Replace("#EXPRESSAO#", left);
// Tirar o espaço da página removida.
execute.Sql(script);
// Remover página e sub-páginas.
execute.Sql($"DELETE FROM TB_Pagina WHERE TX_CaminhoRelativo LIKE '{caminhoRelativo}'");
}
public static string GerarScriptInsercaoPagina(this IExecuteExpressionRoot execute, string nome, string titulo,
string caminhoRelativo, TipoDeExpressaoAdicionarPagina tipoDeExpressaoAdicionarPagina,
string campoReferencia,
string descricao = null)
{
var script = "INSERT INTO TB_Pagina (#COLUMN#) VALUES (#VALUES#)";
AdicionarColuna("TX_Nome", nome, ref script);
AdicionarColuna("TX_Titulo", titulo, ref script);
AdicionarColuna("TX_CaminhoRelativo", caminhoRelativo, ref script);
if (string.IsNullOrWhiteSpace(descricao) == false)
{
AdicionarColuna("TX_Descricao", descricao, ref script);
}
string expressaoLeft, expressaoRight;
switch (tipoDeExpressaoAdicionarPagina)
{
case TipoDeExpressaoAdicionarPagina.PeloCaminhoDoPai:
expressaoLeft =
$"SELECT TOP 1 QT_Right FROM TB_Pagina WHERE TX_CaminhoRelativo LIKE '{campoReferencia}'";
expressaoRight =
$"SELECT TOP 1 QT_Right + 1 FROM TB_Pagina WHERE TX_CaminhoRelativo LIKE '{campoReferencia}'";
break;
case TipoDeExpressaoAdicionarPagina.PeloNomeDoPai:
expressaoLeft = $"SELECT TOP 1 QT_Right FROM TB_Pagina WHERE TX_Nome LIKE '{campoReferencia}'";
expressaoRight = $"SELECT TOP 1 QT_Right + 1 FROM TB_Pagina WHERE TX_Nome LIKE '{campoReferencia}'";
break;
case TipoDeExpressaoAdicionarPagina.PeloCaminhoDoIrmao:
expressaoLeft =
$"SELECT TOP 1 QT_Right + 1 FROM TB_Pagina WHERE TX_CaminhoRelativo LIKE '{campoReferencia}'";
expressaoRight =
$"SELECT TOP 1 QT_Right + 2 FROM TB_Pagina WHERE TX_CaminhoRelativo LIKE '{campoReferencia}'";
break;
default:
throw new Exception("Tipo de expressão não implementada.");
}
AdicionarLeftRight(expressaoLeft, expressaoRight, ref script);
InserirDadosPadrao(ref script);
return(script.Replace(",#COLUMN#", "").Replace(",#VALUES#", ""));
}
public static string GerarScriptInsercaoAgrupador(this IExecuteExpressionRoot execute, string nome, string icone,
string descricao = null)
{
var script = "INSERT INTO (#COLUMN#) VALUES (#VALUES#)";
AdicionarColuna("TX_Nome", nome, ref script);
AdicionarColuna("TX_IconeMenu", icone, ref script);
if (string.IsNullOrWhiteSpace(descricao) == false)
{
AdicionarColuna("TX_Descricao", descricao, ref script);
}
InserirDadosPadrao(ref script);
return(script.Replace(",#COLUMN#", "").Replace(",#VALUES#", ""));
}
public static string GerarScriptInsercaoMenu(this IExecuteExpressionRoot execute, bool paginaInicial,
string estilo,
string nome, string titulo, string caminhoRelativo, string icone, string descricao = null)
{
var script = "INSERT INTO (#COLUMN#) VALUES (#VALUES#)";
AdicionarColuna("IN_PaginaInicial", paginaInicial, ref script);
AdicionarColuna("TX_Estilo", estilo, ref script);
AdicionarColuna("TX_Nome", nome, ref script);
AdicionarColuna("TX_Titulo", titulo, ref script);
AdicionarColuna("TX_CaminhoRelativo", caminhoRelativo, ref script);
AdicionarColuna("TX_IconeMenu", icone, ref script);
if (string.IsNullOrWhiteSpace(descricao) == false)
{
AdicionarColuna("TX_Descricao", descricao, ref script);
}
InserirDadosPadrao(ref script);
return(script.Replace(",#COLUMN#", "").Replace(",#VALUES#", ""));
}
public static void Procedure(this IExecuteExpressionRoot execute, string procedureName)
{
if (string.IsNullOrWhiteSpace(procedureName))
{
throw new Exception($"Procedure name can't be null or empty");
}
var preScriptProcedure = BasicScriptsResources.PreProcedureScript.Replace("#SCRIPT_NAME#", procedureName);
var rm = StoredProceduresResources.ResourceManager;
var script = rm.GetString(procedureName);
if (string.IsNullOrWhiteSpace(script))
{
throw new Exception(
$"Procedure \"{procedureName}\" not found in {StoredProceduresResources.ResourceManager.BaseName}");
}
var finalScript = string.Concat(preScriptProcedure, script);
execute.Sql(finalScript);
}
public static void Function(this IExecuteExpressionRoot execute, string functionName)
{
if (string.IsNullOrWhiteSpace(functionName))
{
throw new Exception($"Function name can't be null or empty");
}
var preScriptFunction = BasicScriptsResources.PreFunctionScript.Replace("#SCRIPT_NAME#", functionName);
var rm = FunctionResources.ResourceManager;
var script = rm.GetString(functionName);
if (string.IsNullOrWhiteSpace(script))
{
throw new Exception(
$"Function \"{functionName}\" not found in {FunctionResources.ResourceManager.BaseName}");
}
var finalScript = string.Concat(preScriptFunction, script);
execute.Sql(finalScript);
}
public static void DropTableIfExists(this IExecuteExpressionRoot execute, string tableName)
{
execute.Sql(string.Format("IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}')) BEGIN DROP TABLE [{0}] END", tableName));
}
public static void DropViewIfExists(this IExecuteExpressionRoot execute, string viewName)
{
execute.Sql(string.Format("IF EXISTS(select * FROM sys.views where name = '{0}') DROP VIEW [{0}]", viewName));
}
public static void DropViewsIfExists(this IExecuteExpressionRoot execute, string views)
{
execute.Sql(string.Format("IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = '{0}')) BEGIN DROP VIEW [{0}] END", views));
}
public static void SqlFormat(this IExecuteExpressionRoot execute, string format, params object[] args)
{
execute.Sql(string.Format(format, args));
}
public static void DropSPIfExists(this IExecuteExpressionRoot execute, string sp)
{
execute.Sql(string.Format("IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = '{0}')) BEGIN DROP PROCEDURE [{0}] END", sp));
}
