/// <summary>
        /// Return all table details based on table and owner.
        /// </summary>
        /// <param name="table"></param>
        /// <param name="owner"></param>
        /// <returns></returns>
        public IList <Column> GetTableDetails(Table table, string owner)
        {
            var columns = new List <Column>();

            using (var conn = new OracleConnection(connectionStr))
            {
                conn.Open();
                using (OracleCommand tableCommand = conn.CreateCommand())
                {
                    tableCommand.CommandText =
                        @"SELECT tc.column_name AS column_name, tc.data_type AS data_type, tc.nullable AS NULLABLE, 
                                    nvl(c.constraint_type,'CHANGE THIS IN CODE') AS constraint_type, 
                                    data_length, data_precision, data_scale
                            from all_tab_columns tc
                                left outer join (
                                        all_cons_columns cc
                                        join all_constraints c on (
                                            c.owner=cc.owner 
                                            and c.constraint_name = cc.constraint_name 
                                            and c.constraint_type <> 'C'
                                        )
                                    ) on (
                                        tc.owner = cc.owner
                                        and tc.table_name = cc.table_name
                                        and tc.column_name = cc.column_name
                                    )
                                where tc.table_name = :table_name and tc.owner = :owner
                            order by tc.table_name, cc.position nulls last, tc.column_id";
                    tableCommand.Parameters.Add("table_name", table.Name);
                    tableCommand.Parameters.Add("owner", owner);
                    using (OracleDataReader oracleDataReader = tableCommand.ExecuteReader(CommandBehavior.Default))
                    {
                        var m = new DataTypeMapper();
                        while (oracleDataReader.Read())
                        {
                            var constraintType = oracleDataReader.GetOracleString(3).Value;
                            int?dataLength     = oracleDataReader.IsDBNull(4) ? (int?)null : Convert.ToInt32(oracleDataReader.GetOracleNumber(4).Value);
                            int?dataPrecision  = oracleDataReader.IsDBNull(5) ? (int?)null : Convert.ToInt32(oracleDataReader.GetOracleNumber(5).Value);
                            int?dataScale      = oracleDataReader.IsDBNull(6) ? (int?)null : Convert.ToInt32(oracleDataReader.GetOracleNumber(6).Value);

                            columns.Add(new Column
                            {
                                Name           = oracleDataReader.GetOracleString(0).Value,
                                DataType       = oracleDataReader.GetOracleString(1).Value,
                                IsNullable     = string.Equals(oracleDataReader.GetOracleString(2).Value, "Y", StringComparison.OrdinalIgnoreCase),
                                IsPrimaryKey   = ConstraintTypeResolver.IsPrimaryKey(constraintType),
                                IsForeignKey   = ConstraintTypeResolver.IsForeignKey(constraintType),
                                IsUnique       = ConstraintTypeResolver.IsUnique(constraintType),
                                MappedDataType = m.MapFromDBType(ServerType.Oracle, oracleDataReader.GetOracleString(1).Value, dataLength, dataPrecision, dataScale).ToString(),
                                DataLength     = dataLength,
                                DataPrecision  = dataPrecision,
                                DataScale      = dataScale
                            });
                        }
                        table.Columns              = columns;
                        table.PrimaryKey           = DeterminePrimaryKeys(table);
                        table.ForeignKeys          = DetermineForeignKeyReferences(table);
                        table.HasManyRelationships = DetermineHasManyRelationships(table);
                    }
                }
                conn.Close();
            }

            return(columns);
        }
Example #2
0
        /// <summary>
        /// Return all table details based on table and owner.
        /// </summary>
        /// <param name="table"></param>
        /// <param name="owner"></param>
        /// <returns></returns>
        public IList <Column> GetTableDetails(Table table, string owner)
        {
            var columns = new List <Column>();

            using (var conn = new OracleConnection(connectionStr))
            {
                conn.Open();
                using (OracleCommand tableCommand = conn.CreateCommand())
                {
                    tableCommand.CommandText =
                        @"select column_name, data_type, nullable, sum(constraint_type) constraint_type, data_length, data_precision, data_scale
from (
SELECT tc.column_name AS column_name, tc.data_type AS data_type, tc.nullable AS NULLABLE, 
                                    decode(c.constraint_type, 'P', 1, 'R', 2, 'U', 4, 'C', 8, 16) AS constraint_type, 
                                    data_length, data_precision, data_scale, column_id
from all_tab_columns tc
    left outer join (
            all_cons_columns cc
            join all_constraints c on (
                c.owner=cc.owner 
                and c.constraint_name = cc.constraint_name 
            )
        ) on (
            tc.owner = cc.owner
            and tc.table_name = cc.table_name
            and tc.column_name = cc.column_name
        )
    where tc.table_name = :table_name and tc.owner = :owner    
order by tc.table_name, cc.position nulls last, tc.column_id)
group by column_name, data_type, nullable, data_length, data_precision, data_scale, column_id
order by column_id";

                    tableCommand.Parameters.Add("table_name", table.Name);
                    tableCommand.Parameters.Add("owner", owner);
                    using (OracleDataReader oracleDataReader = tableCommand.ExecuteReader(CommandBehavior.Default))
                    {
                        var m = new DataTypeMapper();
                        while (oracleDataReader.Read())
                        {
                            var constraintType = Convert.ToInt32(oracleDataReader.GetOracleNumber(3).Value);
                            int?dataLength     = oracleDataReader.IsDBNull(4) ? (int?)null : Convert.ToInt32(oracleDataReader.GetOracleNumber(4).Value);
                            int?dataPrecision  = oracleDataReader.IsDBNull(5) ? (int?)null : Convert.ToInt32(oracleDataReader.GetOracleNumber(5).Value);
                            int?dataScale      = oracleDataReader.IsDBNull(6) ? (int?)null : Convert.ToInt32(oracleDataReader.GetOracleNumber(6).Value);

                            columns.Add(new Column {
                                Name           = oracleDataReader.GetOracleString(0).Value,
                                DataType       = oracleDataReader.GetOracleString(1).Value,
                                IsNullable     = string.Equals(oracleDataReader.GetOracleString(2).Value, "Y", StringComparison.OrdinalIgnoreCase),
                                IsPrimaryKey   = ConstraintTypeResolver.IsPrimaryKey(constraintType),
                                IsForeignKey   = ConstraintTypeResolver.IsForeignKey(constraintType),
                                IsUnique       = ConstraintTypeResolver.IsUnique(constraintType),
                                MappedDataType = m.MapFromDBType(ServerType.Oracle, oracleDataReader.GetOracleString(1).Value, dataLength, dataPrecision, dataScale).ToString(),
                                DataLength     = dataLength,
                                DataPrecision  = dataPrecision,
                                DataScale      = dataScale
                            });
                        }
                        table.Owner      = owner;
                        table.Columns    = columns;
                        table.PrimaryKey = DeterminePrimaryKeys(table);

                        // Need to find the table name associated with the FK
                        foreach (var c in table.Columns.Where(c => c.IsForeignKey))
                        {
                            var foreignInfo = GetForeignKeyReferenceTableName(table.Name, c.Name);
                            c.ForeignKeyTableName  = foreignInfo.Item1;
                            c.ForeignKeyColumnName = foreignInfo.Item2;
                        }
                        table.ForeignKeys          = DetermineForeignKeyReferences(table);
                        table.HasManyRelationships = DetermineHasManyRelationships(table);
                    }
                }
                conn.Close();
            }

            return(columns);
        }