Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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>();
        }
Esempio n. 3
0
        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 + ")";
            }
        }