public Task <IList <Column> > GetTableDetails(Table table, string owner)
{
IList <Column> columns = new List <Column>();
using (var sqlCon = new DB2Connection(_connectionStr))
{
try
{
sqlCon.Open();
using (DB2Command tableDetailsCommand = sqlCon.CreateCommand())
{
tableDetailsCommand.CommandText = $@"
select c.colno, c.colname, c.coltype
, cast(case when c.collength >= 256 then null else c.collength end as int) collength
, cast(case when c.collength >= 256 then c.collength / 256 else null end as int) colprecision
, cast(case when c.collength >= 256 then c.collength - (cast(c.collength / 256 as int) * 256) else null end as int) colscale
-- , cast(case when c.collength >= 256 then c.collength / 256 else c.collength end as int) collength
-- , cast(case when c.collength >= 256 then c.collength / 256 else c.collength end as int) colprecision
-- , cast(case when c.collength >= 256 then c.collength - (cast(c.collength / 256 as int) * 256) else 0 end as int) colscale
, case when pkidx.idxtype is not null then 'P' else '-' end ispk
, nvl(nl.constrtype, '-') isnullable
, (select nvl(min(uidx.idxtype), '-') uniq
from sysindexes uidx
where uidx.tabid = c.tabid and uidx.idxtype = 'U'
and c.colno in (uidx.part1, uidx.part2, uidx.part3, uidx.part4, uidx.part5, uidx.part6, uidx.part7, uidx.part8,
uidx.part9, uidx.part10, uidx.part11, uidx.part12, uidx.part13, uidx.part14, uidx.part15, uidx.part16)
) isunique
, case when c.coltype in (6, 18, 262, 274) then 'I' else '-' end isidentity
from syscolumns c
inner join systables t on c.tabid = t.tabid
left outer join syscoldepend d on d.tabid = c.tabid and d.colno = c.colno
left outer join sysconstraints nl on nl.constrid = d.constrid and nl.constrtype = 'N'
left outer join sysconstraints pk on pk.tabid = c.tabid and pk.constrtype = 'P'
left outer join sysindexes pkidx on pkidx.tabid = c.tabid and pkidx.idxname = pk.idxname
and c.colno in (pkidx.part1, pkidx.part2, pkidx.part3, pkidx.part4, pkidx.part5, pkidx.part6, pkidx.part7, pkidx.part8,
pkidx.part9, pkidx.part10, pkidx.part11, pkidx.part12, pkidx.part13, pkidx.part14, pkidx.part15, pkidx.part16)
where t.owner = '{owner}'
and t.tabname = '{table.Name}'
";
using (DB2DataReader reader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
{
var m = new DataTypeMapper();
while (reader.Read())
{
string dataType = GetColumnType(reader.Get <short>("coltype"));
int? dataLength = reader.Get <int?>("collength");
int? dataPrecision = reader.Get <int?>("colprecision");
int? dataScale = reader.Get <int?>("colscale");
columns.Add(new Column
{
Name = reader.GetString("colname"),
DataType = dataType,
DataLength = dataLength,
DataPrecision = dataPrecision,
DataScale = dataScale,
IsNullable = reader.GetString("isnullable") != "N",
IsPrimaryKey = reader.GetString("ispk") == "P",
IsUnique = reader.GetString("isunique") == "U",
IsIdentity = reader.GetString("isidentity") == "I",
MappedDataType = m.MapFromDBType(ServerType.Informix, dataType, dataLength, dataPrecision, dataScale),
});
}
}
}
table.Owner = owner;
table.Columns = columns;
}
finally
{
sqlCon.Close();
}
}
table.PrimaryKey = DeterminePrimaryKeys(table);
table.ForeignKeys = DetermineForeignKeyReferences(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
return(Task.FromResult(columns));
}
