public List <ViewColumnEntity> GetViewColumns(string connStr, string databaseName, string schemaName, string viewName)
{
var sql = @"SELECT col.table_schema AS SchemaName, col.table_name TableName,col.ordinal_position AS Index, col.column_name ColumnName, col.character_maximum_length Length,
case when col.is_nullable='YES' then 'true' else 'false' end IsNullAble,
col.numeric_precision AS Precision, col.numeric_scale Scale,
col_description(c.oid, col.ordinal_position) AS Comment,
col.column_default AS DefaultValue,
case when col.is_identity='YES' then 'true' else 'false' end IsIdentity,
col.data_type AS col_type,
bt.typname AS elem_name
FROM information_schema.columns AS col
LEFT JOIN pg_namespace ns ON ns.nspname = col.table_schema
LEFT JOIN pg_class c ON col.table_name = c.relname AND c.relnamespace = ns.oid
LEFT JOIN pg_attrdef a ON c.oid = a.adrelid AND col.ordinal_position = a.adnum
LEFT JOIN pg_attribute b ON b.attrelid = c.oid AND b.attname = col.column_name
LEFT JOIN pg_type et ON et.oid = b.atttypid
LEFT JOIN pg_collation coll ON coll.oid = b.attcollation
LEFT JOIN pg_namespace colnsp ON coll.collnamespace = colnsp.oid
LEFT JOIN pg_type bt ON et.typelem = bt.oid LEFT JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid
WHERE col.table_schema =@0 AND col.table_name =@1
ORDER BY col.table_schema, col.table_name, col.ordinal_position";
var helper = SqlHelperFactory.OpenDatabase(connStr, _provider.GetProviderFactory(), SqlType.PostgreSQL);
helper.ChangeDatabase(databaseName);
var entities = helper.Select <TableColumnTemp>(sql, schemaName, viewName);
helper.Dispose();
List <ViewColumnEntity> result = new List <ViewColumnEntity>();
foreach (var temp in entities)
{
ViewColumnEntity entity = new ViewColumnEntity()
{
DatabaseName = databaseName,
SchemaName = temp.SchemaName,
ViewName = temp.TableName,
Index = temp.Index,
ColumnName = temp.ColumnName,
Comment = temp.Comment,
Length = temp.Length,
Precision = temp.Precision,
Scale = temp.Scale,
IsNullAble = temp.IsNullAble,
Type = temp.col_type,
};
if (entity.Type == "ARRAY")
{
entity.Type = temp.elem_name + "[]";
}
result.Add(entity);
}
return(result);
}
public StructureModel(ViewColumnEntity columnEntity)
{
var t = columnEntity.Type.ToLower();
if (t == "v" || t == "view")
{
Type = "v";
}
else
{
Type = "t";
}
Name = columnEntity.ColumnName;
if (string.IsNullOrEmpty(columnEntity.Comment) == false)
{
Comment = columnEntity.Comment;
}
Items = new List <StructureItemModel>();
}
public StructureItemModel(ViewColumnEntity columnEntity)
{
ColumnName = columnEntity.ColumnName;
Nullable = columnEntity.IsNullAble ? "?" : null;
if (string.IsNullOrEmpty(columnEntity.Comment) == false)
{
Comment = columnEntity.Comment;
}
KeyType = "f";
ColumnType = columnEntity.Type.ToLower();
if (ColumnType == "varchar" || ColumnType == "nvarchar" || ColumnType == "char" || ColumnType == "nchar")
{
ColumnType = ColumnType + "(" + columnEntity.Length + ")";
}
else if (ColumnType == "decimal" || ColumnType == "numeric")
{
ColumnType = ColumnType + "(" + columnEntity.Precision + "," + columnEntity.Scale + ")";
}
}
