public void GeraPKeyInterface(StringBuilder Out, ColDef PKFld)
{
Out.ApLine();
Out.ApLine(" // Open by PK");
Out.ApLine(" public void OpenPK(Int32 pkVal)");
Out.ApLine(" {");
Out.ApLine(" Close();");
Out.ApLine();
Out.ApLine(" Open(");
Out.Append(" \"select * from ")
.Append(Name)
.ApLine(" \" + Environment.NewLine +");
Out.Append(" \" where ")
.Append(PKFld.NomeCampo)
.ApLine(" = \" + pkVal.ToString());");
Out.ApLine(" }");
Out.ApLine();
Out.ApLine(" public DBInt32Field PKField()");
Out.ApLine(" {");
Out.Append(" return ")
.Append(PKFld.NomeCampo)
.ApLine(";");
Out.ApLine(" }");
}
private SqlDataReader LerColunas(SqlCommand cmdObj)
{
SqlDataReader rdObj = null;
try
{
cmdObj.CommandText =
"SELECT SC.column_id, SC.name, SC.max_length, SC.precision, " + Environment.NewLine +
" CAST(SC.scale as smallint) as scale, " + Environment.NewLine +
" RTRIM(TP.name) AS Tipo, SC.is_nullable, " + Environment.NewLine +
" CAST(IC.key_ordinal as smallint) as key_ordinal " + Environment.NewLine +
" FROM sys.columns SC " + Environment.NewLine +
" JOIN sys.types TP " + Environment.NewLine +
" ON TP.system_type_id = SC.system_type_id " + Environment.NewLine +
" AND TP.user_type_id = SC.user_type_id " + Environment.NewLine +
" LEFT JOIN sys.index_columns IC " + Environment.NewLine +
" on IC.object_id = SC.object_id " + Environment.NewLine +
" and IC.index_id = " + IdPKey.GetValueOrDefault().ToString() + Environment.NewLine +
" and IC.column_id = SC.column_id " + Environment.NewLine +
" where SC.object_id =" + IdObjeto.ToString() + Environment.NewLine +
" order by SC.column_id ";
rdObj = cmdObj.ExecuteReader();
while (rdObj.Read())
{
ColDef Col = new ColDef();
Col.IdColuna = rdObj.GetInt32(0);
Col.NomeCampo = rdObj.GetString(1);
Col.Tamanho = rdObj.GetInt16(2);
Col.Digitos = rdObj.GetInt16(4);
Col.TipoCampo = rdObj.GetString(5);
Col.PermNull = rdObj.GetBoolean(6);
Col.IsOutParam = false;
Col.PKey = null;
if (!rdObj.IsDBNull(7))
{
Col.PKey = rdObj.GetInt16(7); // key_ordinal começa em 1
}
Col.Index = Cols.Count;
Cols.Add(Col);
// Guarda a coluna do PK
if (Col.PKey.HasValue)
{
if (ColPKey == null)
{
ColPKey = Col;
}
PKCols++;
}
}
}
finally
{
rdObj.Close();
}
return(rdObj);
}
public Boolean EIgual(ColDef target)
{
// PKey não pode comparar pq nao tem no view/proc....
// Idem null..
return(NomeCampo == target.NomeCampo &&
TipoCampo == target.TipoCampo &&
Tamanho == target.Tamanho &&
Digitos == target.Digitos &&
IsOutParam == target.IsOutParam);
}
private SqlDataReader LerParametros(SqlCommand cmdObj)
{
SqlDataReader rdObj = null;
try
{
cmdObj.CommandText =
"SELECT P.parameter_id, P.name, P.max_length, P.precision, " + Environment.NewLine +
" CAST(P.scale as smallint) as scale, " + Environment.NewLine +
" RTRIM(TP.name) AS Tipo, P.is_output " + Environment.NewLine +
" FROM sys.parameters P " + Environment.NewLine +
" JOIN sys.types TP " + Environment.NewLine +
" ON TP.system_type_id = P.system_type_id " + Environment.NewLine +
" AND TP.user_type_id = P.user_type_id " + Environment.NewLine +
" where P.object_id =" + IdObjeto.ToString() + Environment.NewLine +
" order by P.parameter_id ";
rdObj = cmdObj.ExecuteReader();
while (rdObj.Read())
{
ColDef Col = new ColDef();
Col.IdColuna = rdObj.GetInt32(0);
String NomeCampo = rdObj.GetString(1);
Col.NomeCampo = NomeCampo.Replace("@", String.Empty);
Col.Tamanho = rdObj.GetInt16(2);
Col.Digitos = rdObj.GetInt16(4);
Col.TipoCampo = rdObj.GetString(5);
Col.PermNull = true;
Col.IsOutParam = rdObj.GetBoolean(6);
Col.PKey = null;
Col.Index = Cols.Count;
Cols.Add(Col);
}
}
finally
{
rdObj.Close();
}
return(rdObj);
}
public void GeraProcSql(StringBuilder SB)
{
const Int32 MaxInsL = 40;
// Os campos sem o Timestamp
// Será o 1o lambda da historia ??
ColDef LastCol = ColsSemTS.FindLast(N => true);
// Troca o tb_ pelo sp_ !!
String NomeSp = Name;
Int32 NameL = Name.Length - 3;
if (NameL > 0)
{
if (Name.Substring(0, 3).ToLower() == "tb_")
{
NomeSp = "sp_" + Name.Substring(3, NameL);
}
}
SB.ApLine("Create Proc " + NomeSp + "_Update");
foreach (ColDef Col in ColsSemTS)
{
SB.Append(" @");
SB.Append(Col.NomeCampo);
SB.Append(" ");
SB.Append(Col.SqlVar);
SB.ApLine(",");
}
SB.ApLine(" @SqlType nvarchar(20),");
SB.ApLine(" @IdUsuarioAlt int,");
SB.Append(" @")
.Append(ColPKey.NomeCampo)
.ApLine("_Out int output");
SB.ApLine("as");
SB.ApLine("begin");
SB.ApLine(" Set Xact_Abort on");
SB.ApLine(" Set Nocount on");
SB.ApLine(" Begin Tran");
SB.ApLine();
SB.ApLine(" If @SqlType = 'Insert'");
SB.ApLine(" begin");
SB.Append(" Insert into " + Name + " (");
Int32 InsL = MaxInsL; // Força Quebra
foreach (ColDef Col in ColsSemTS)
{
if (InsL >= MaxInsL)
{
SB.ApLine();
SB.Append(" ");
InsL = 0;
}
if (Col != ColPKey)
{
SB.Append(" ");
SB.Append(Col.NomeCampo);
if (Col != LastCol)
{
SB.Append(",");
}
InsL += Col.NomeCampo.Length;
}
}
SB.ApLine();
SB.Append(" ) values (");
InsL = MaxInsL;
foreach (ColDef Col in ColsSemTS)
{
if (InsL >= MaxInsL)
{
SB.ApLine();
SB.Append(" ");
InsL = 0;
}
if (Col != ColPKey)
{
SB.Append(" @");
SB.Append(Col.NomeCampo);
if (Col != LastCol)
{
SB.Append(",");
}
InsL += Col.NomeCampo.Length;
}
}
SB.ApLine();
SB.ApLine(" )");
SB.ApLine(" Set @" + ColPKey.NomeCampo + "_Out = @@IDENTITY");
SB.ApLine(" end");
SB.ApLine();
SB.ApLine(" If @SqlType = 'Update'");
SB.ApLine(" begin");
SB.ApLine(" Update " + Name + " set");
foreach (ColDef Col in ColsSemTS)
{
if (Col != ColPKey)
{
SB.Append(" ");
SB.Append(Col.NomeCampo);
SB.Append(" = @");
SB.Append(Col.NomeCampo);
if (Col != LastCol)
{
SB.Append(",");
}
SB.ApLine();
}
}
SB.ApLine(" where " + ColPKey.NomeCampo + " = @" + ColPKey.NomeCampo);
SB.ApLine(" end");
SB.ApLine();
SB.ApLine(" If @SqlType = 'Delete'");
SB.ApLine(" begin");
SB.ApLine(" Delete from " + Name);
SB.ApLine(" where " + ColPKey.NomeCampo + " = @" + ColPKey.NomeCampo);
SB.ApLine(" end");
SB.ApLine();
SB.ApLine(" Commit");
SB.ApLine("end");
SB.ApLine();
}
