/// <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);
}
/// <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);
}
