private void Get_Fields() { string _sqltext = @"SELECT a.oid ,c.attnum as num ,c.attname as field , (case when f.character_maximum_length is null then c.attlen else f.character_maximum_length end) as length ,c.attnotnull as notnull ,d.description as comment ,(case when e.typcategory ='G' then e.typname when e.typelem = 0 then e.typname else e2.typname end) as type ,(case when e.typelem = 0 then e.typtype else e2.typtype end) as data_type ,e.typcategory ,f.is_identity from pg_class a inner join pg_namespace b on a.relnamespace=b.oid inner join pg_attribute c on attrelid = a.oid LEFT OUTER JOIN pg_description d ON c.attrelid = d.objoid AND c.attnum = d.objsubid and c.attnum > 0 inner join pg_type e on e.oid=c.atttypid left join pg_type e2 on e2.oid=e.typelem inner join information_schema.columns f on f.table_schema = b.nspname and f.table_name=a.relname and column_name = c.attname WHERE b.nspname='{0}' and a.relname='{1}';"; _sqltext = string.Format(_sqltext, this.schemaName, this.table.name); PgSqlHelper.ExecuteDataReader(dr => { FieldInfo fi = new FieldInfo(); fi.Oid = Convert.ToInt32(dr["oid"]); fi.Field = dr["field"].ToString(); fi.Length = Convert.ToInt32(dr["length"].ToString()); fi.Is_not_null = Convert.ToBoolean(dr["notnull"]); fi.Comment = dr["comment"].ToString(); fi.Data_Type = dr["data_type"].ToString(); fi.Db_type = dr["type"].ToString(); fi.Db_type = fi.Db_type.StartsWith("_") ? fi.Db_type.Remove(0, 1) : fi.Db_type; fi.PgDbType = PgsqlType.SwitchToSql(fi.Data_Type, fi.Db_type); fi.Is_identity = dr["is_identity"].ToString() == "YES"; fi.Is_array = dr["typcategory"].ToString() == "A"; fi.Is_enum = fi.Data_Type == "e"; fi.CsType = PgsqlType.SwitchToCSharp(fi.Db_type); if (fi.Is_enum) { fi.CsType = fi.CsType.ToUpperPascal(); } string _notnull = ""; if ( fi.CsType != "string" && fi.CsType != "byte[]" && fi.CsType != "JToken" && !fi.Is_array && fi.CsType != "System.Net.IPAddress" && fi.CsType != "System.Net.NetworkInformation.PhysicalAddress" && fi.CsType != "System.Xml.Linq.XDocument" && fi.CsType != "System.Collections.BitArray" && fi.CsType != "object" ) { _notnull = fi.Is_not_null ? "" : "?"; } string _array = fi.Is_array ? "[]" : ""; fi.RelType = $"{fi.CsType}{_notnull}{_array}"; // dal this.fieldList.Add(fi); }, CommandType.Text, _sqltext); }
private void GetFields(TableInfo table) { string _sqltext = @"SELECT a.oid ,c.attnum as num ,c.attname as field ,c.attnotnull as notnull ,d.description as comment ,(case when e.typcategory ='G' then e.typname when e.typelem = 0 then e.typname else e2.typname end) as type ,(case when e.typelem = 0 then e.typtype else e2.typtype end) as data_type ,COALESCE(( case when (case when e.typcategory ='G' then e.typname when e.typelem = 0 then e.typname else e2.typname end) in ('numeric','int2','int4','int8','float4','float8') then f.numeric_precision when (case when e.typcategory ='G' then e.typname when e.typelem = 0 then e.typname else e2.typname end) in ('timestamp','timestamptz','interval','time','date','timetz') then f.datetime_precision when f.character_maximum_length is null then 0 else f.character_maximum_length end ),0) as length ,COALESCE(( case when (case when e.typcategory ='G' then e.typname when e.typelem = 0 then e.typname else e2.typname end) in ('numeric') then f.numeric_scale else 0 end ),0) numeric_scale ,e.typcategory ,f.udt_schema ,f.column_default from pg_class a inner join pg_namespace b on a.relnamespace=b.oid inner join pg_attribute c on attrelid = a.oid LEFT OUTER JOIN pg_description d ON c.attrelid = d.objoid AND c.attnum = d.objsubid and c.attnum > 0 inner join pg_type e on e.oid=c.atttypid left join pg_type e2 on e2.oid=e.typelem inner join information_schema.columns f on f.table_schema = b.nspname and f.table_name=a.relname and column_name = c.attname WHERE b.nspname='{0}' and a.relname='{1}';"; _sqltext = string.Format(_sqltext, table.Schema, table.Name); dbContext.Execute.ExecuteDataReader(dr => { DbFieldInfo fi = new DbFieldInfo { Oid = Convert.ToInt32(dr["oid"]), Name = dr["field"].ToString(), Length = Convert.ToInt32(dr["length"].ToString()), NotNull = Convert.ToBoolean(dr["notnull"]), Comment = dr["comment"].ToString(), Numeric_scale = Convert.ToInt32(dr["numeric_scale"].ToString()), ColumnDefault = dr["column_default"].ToString(), }; var udt_schema = dr["udt_schema"].ToString(); var typcategory = dr["typcategory"].ToString(); var dbtype = dr["type"].ToString(); fi.DbType = typcategory == "E" ? udt_schema + "." + dbtype : dbtype; fi.IsArray = typcategory == "A"; fi.CsType = PgsqlType.SwitchToCSharp(dbtype); fi.AutoIncrement = fi.ColumnDefault != null && fi.ColumnDefault.StartsWith("nextval('") && fi.ColumnDefault.EndsWith("'::regclass)"); string _notnull = ""; if ( fi.CsType != "string" && fi.CsType != "byte[]" && fi.CsType != "JToken" && !fi.IsArray && fi.CsType != "System.Net.IPAddress" && fi.CsType != "System.Net.NetworkInformation.PhysicalAddress" && fi.CsType != "System.Xml.Linq.XDocument" && fi.CsType != "System.Collections.BitArray" && fi.CsType != "object" ) { _notnull = fi.NotNull ? "" : "?"; } string _array = fi.IsArray ? "[]" : ""; fi.RelType = $"{fi.CsType}{_notnull}{_array}"; if (fi.RelType == "string" && (fi.Length != 0 && fi.Length != 255)) { fi.DbTypeFull = $"{fi.DbType}({fi.Length})"; } else if (fi.Numeric_scale > 0) { fi.DbTypeFull = ($"{fi.DbType}({fi.Length},{fi.Numeric_scale})"); } else if (PgsqlType.ContrastType(fi.DbType) == null) { fi.DbTypeFull = fi.DbType; } table.Fields.Add(fi); }, CommandType.Text, _sqltext); if (table.Type == TableType.Table) { GetPrimarykey(table); } }