public UISalvaScript(string vservidor, string vuser, string vpassword,
string vdataBase, byte vtipoConexao)
{
InitializeComponent();
_objProcBO = new ProcViewBO(vservidor: vservidor, vuser: vuser, vpassword: vpassword,
vdataBase: vdataBase, vtipoConexao: vtipoConexao);
objConstrBo = new constraintsBo();
toolTip.SetToolTip(btnAtualizar, "Atualizar Procedures e Views");
Carrega();
}
public string GerarDuplicar_SP()
{
StringBuilder sp = new StringBuilder();
if (_colunas != null)
{
constraintsBo objConstBo = new constraintsBo();
List <constraintsModel> lConstraints = objConstBo.GetConstraints(_colunas.First().NomeTabela);
string campos = String.Empty;
string sValues = String.Empty;
string where = String.Empty;
ProcNome_Duplicar = _colunas.First().TabelaOwner + ".Proc_copy_" + _colunas.First().NomeTabela;
sp.Append("CREATE PROCEDURE [");
sp.Append(_colunas.First().TabelaOwner);
sp.Append("].[Proc_copy_");
sp.Append(_colunas.First().NomeTabela + "]{0}");
sp.Append("({0}");
int qtd = CarregaPK(_colunas.First().NomeTabela).Count; //Método CarregaPK fica na DAO e retorna uma lista de string com as colunas primarias
for (int i = 0; i < qtd; i++)
{
string chaveP = CarregaPK(_colunas.First().NomeTabela)[i];
string tipoc = _colunas.Where(c => c.NomeColuna == chaveP).FirstOrDefault().TipoColuna;
sp.Append(" @" + chaveP + " " + (tipoc == "int identity" ? "int" : tipoc) + ((qtd - i == 1) ? "" : ",{0}"));
where += _colunas.FirstOrDefault().NomeTabela + "." + chaveP + " = @" + chaveP + ((qtd - i == 1) ? "" : "{0}and ");
}
for (int i = 0; i < _colunas.Count; i++)
{
if (_colunas[i].TipoColuna != "int identity")
{
campos += "{0} " + _colunas[i].NomeTabela + "." + _colunas[i].NomeColuna +
(_colunas.Count - i == 1 ? "" : ",");
}
}
for (int i = 0; i < _colunas.Count; i++)
{
if (_colunas[i].TipoColuna != "int identity")
{
sValues += "{0} "
+ ((lConstraints.Where(c => c.sColumnName == _colunas[i].NomeColuna).Count() > 0) ?
"case when (select c.CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS c where c.COLUMN_NAME = '" + _colunas[i].NomeColuna + "'" +
" and c.TABLE_NAME = '" + _colunas[i].NomeTabela + "') < 7 " +
"then " +
"case when ((select c.IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS c where c.COLUMN_NAME = '" + _colunas[i].NomeColuna + "' " +
"and c.TABLE_NAME = '" + _colunas[i].NomeTabela + "') = 'NO') " +
"then " +
"Reverse(SUBSTRING(Reverse(IDENT_CURRENT('" + _colunas[i].NomeTabela + "')), 1, " +
"(select c.CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS c where c.COLUMN_NAME = '" + _colunas[i].NomeColuna + "' " +
"and c.TABLE_NAME = '" + _colunas[i].NomeTabela + "'))) " +
"else " +
"null " +
"end " +
"else " +
"case when (select count(j1." + _colunas[i].NomeColuna + ") from " + _colunas[i].NomeTabela + " j1 where j1." + _colunas[i].NomeColuna + " like 'copy_%') = 0 " +
"then " +
"'copy_1' " +
"else " +
"'copy_'+(Cast(Cast(Substring((select top(1) j2." + _colunas[i].NomeColuna + " from " + _colunas[i].NomeTabela + " j2" +
" where j2." + _colunas[i].NomeColuna + " like 'copy_%' order by j2." + _colunas[i].NomeColuna + " desc), " +
"PATINDEX('%copy_%', (select top(1) j3." + _colunas[i].NomeColuna + " from " + _colunas[i].NomeTabela + " j3 " +
"where j3." + _colunas[i].NomeColuna + " like 'copy_%' order by j3." + _colunas[i].NomeColuna + "))+5, " +
"(select c.CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS c where c.COLUMN_NAME = '" + _colunas[i].NomeColuna + "' " +
"and c.TABLE_NAME = '" + _colunas[i].NomeTabela + "'))as int) + 1 as varchar(max))) " +
"end " +
"end" :
_colunas[i].NomeTabela + "." + _colunas[i].NomeColuna) +
(_colunas.Count - i == 1 ? "" : ",");
}
}
sp.Append("{0}) {0}AS {0}BEGIN {0}");
sp.Append("INSERT INTO " + _colunas.FirstOrDefault().NomeTabela);
sp.Append("{0}(");
sp.Append(campos);
sp.Append("{0}){0}SELECT");
sp.Append(sValues);
sp.Append("{0}FROM " + _colunas.FirstOrDefault().NomeTabela + "{0}");
sp.Append("WHERE ");
sp.Append(where);
sp.Append(";{0}{0}DECLARE @ret int;{0}");
sp.Append("SET @ret = @@IDENTITY{0}");
sp.Append("SELECT @ret");
sp.Append("{0}END");
}
return(string.Format(sp.ToString(), Environment.NewLine));
}
