예제 #1
0
        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("        }");
        }
예제 #2
0
        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);
        }
예제 #3
0
 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);
 }
예제 #4
0
        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);
        }
예제 #5
0
        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();
        }