/// <summary> /// 获取数据库中列(字段)的信息。 /// </summary> /// <param name="tableName">表名,不带[]等符号。</param> /// <param name="columnName">列(字段)名,不带[]等符号。</param> /// <param name="schemaName">架构名称,默认public</param> /// <returns>不存在将new一个,并且Exists为false。</returns> public override DatabaseTableField GetColumnInfo(string tableName, string columnName, string schemaName = "@default") { DatabaseTableField item = null; if (!string.IsNullOrEmpty(tableName) && !string.IsNullOrEmpty(columnName)) { if (string.IsNullOrEmpty(schemaName) || string.Equals(schemaName, "@default", System.StringComparison.OrdinalIgnoreCase)) { schemaName = "public"; } item = CreateQuery <DatabaseTableField>(@" SELECT 1::boolean as ""Exists"", col.table_name as ""TableName"", col.column_name as ""Name"", col.data_type as ""Type"", col.ordinal_position as ""Position"", col.is_nullable as ""Nullable"", exists(select 1 from pg_constraint as cst where cst.conname ~*('^pk_' || col.table_name || '_' || col.column_name || '$')) as ""IsPrimary"", (not col.column_default is null and col.column_default ~*'^nextval') as ""IsIdentity"", (case when attr.attlen = -1 then coalesce(coalesce(col.character_maximum_length, col.numeric_precision), -1) else attr.attlen end) as ""Length"", col.numeric_scale as ""Scale"", col.column_default as ""DefaultValue"", des.description as ""Description"" FROM information_schema.columns col left join pg_class as cls on cls.relname = col.table_name LEFT JOIN pg_description des ON des.objoid = cls.oid AND col.ordinal_position = des.objsubid left join pg_attribute as attr on attr.attrelid = cls.oid and attr.attname = col.column_name WHERE table_schema = @p1 AND table_name = @p2 and lower(col.column_name)=lower(@p3) ORDER BY col.table_name,col.ordinal_position; ", schemaName, tableName, columnName).FirstOrDefault(); } if (item == null) { item = new DatabaseTableField() { Exists = false, TableName = tableName, Name = columnName, Nullable = true, }; } return(item); }
/// <summary> /// 获取数据库中列(字段)的信息。 /// </summary> /// <param name="tableName">表名,不带[]等符号。</param> /// <param name="columnName">列(字段)名,不带[]等符号。</param> /// <param name="schemaName">架构名称,@default由实现者解析为默认值(dbo或数据库名称)</param> /// <returns>永远不会返回null。</returns> public override DatabaseTableField GetColumnInfo(string tableName, string columnName, string schemaName = "@default") { System.Collections.Generic.List <DatabaseTableField> list = GetColumns(tableName); DatabaseTableField item = list.Find(p => string.Equals(p.Name, columnName, System.StringComparison.OrdinalIgnoreCase)); if (item == null) { item = new DatabaseTableField() { Exists = false, TableName = tableName, Name = columnName, Nullable = true, }; } return(item); }
/// <summary> /// 获取数据库中列(字段)的信息。 /// </summary> /// <param name="tableName">表名,不带[]等符号。</param> /// <param name="columnName">列(字段)名,不带[]等符号。</param> /// <param name="schemaName">架构名称,@default由实现者解析为默认值(dbo或数据库名称)</param> /// <returns>永远不会返回null。</returns> public override DatabaseTableField GetColumnInfo(string tableName, string columnName, string schemaName = "@default") { DatabaseTableField item = null; if (!string.IsNullOrEmpty(tableName) && !string.IsNullOrEmpty(columnName)) { item = CreateQuery <DatabaseTableField>(@" select 1 as [Exists] ,obj.name as TableName,c.name as [Name],t.name as [Type],c.colid as [Position] ,convert(bit,c.IsNullable) as [Nullable] ,convert(bit,case when exists(select 1 from sysobjects where xtype='PK' and parent_obj=c.id and name in ( select name from sysindexes where indid in( select indid from sysindexkeys where id = c.id and colid=c.colid))) then 1 else 0 end) as [IsPrimary] ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [IsIdentity] --,c.Length as [ByteLength] ,COLUMNPROPERTY(c.id,c.name,'PRECISION') as [Length] ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [Scale] ,ISNULL(CM.text,'') as [DefaultValue] ,isnull(ETP.value,'') AS [Description] --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row] from syscolumns c inner join systypes t on c.xusertype = t.xusertype left join sys.extended_properties ETP on ETP.major_id = c.id and ETP.minor_id = c.colid and ETP.name ='MS_Description' left join syscomments CM on c.cdefault=CM.id left join sys.objects obj on obj.object_id=c.id where obj.type='U' and c.id = object_id(@p1) and c.name=@p2 order by c.id,c.colid ", tableName, columnName).FirstOrDefault(); } if (item == null) { item = new DatabaseTableField() { Exists = false, TableName = tableName, Name = columnName, Nullable = true, }; } return(item); }
/// <summary> /// 获取数据库中列(字段)的信息。 /// </summary> /// <param name="tableName">表名,不带[]等符号。</param> /// <param name="columnName">列(字段)名,不带[]等符号。</param> /// <param name="schemaName">架构名称,@default由实现者解析为默认值(dbo或数据库名称)</param> /// <returns>永远不会返回null。</returns> public override DatabaseTableField GetColumnInfo(string tableName, string columnName, string schemaName = "@default") { DatabaseTableField item = null; if (!string.IsNullOrEmpty(tableName) && !string.IsNullOrEmpty(columnName)) { if (string.IsNullOrEmpty(schemaName) || string.Equals(schemaName, "@default", System.StringComparison.OrdinalIgnoreCase)) { schemaName = Connection?.DatabaseName; } item = CreateQuery <DatabaseTableField>(@" SELECT 1 as `Exists`,`TABLE_NAME` as `TableName`,`COLUMN_NAME` as `Name`, `DATA_TYPE` as `Type`, `ORDINAL_POSITION` as `Position`,`IS_NULLABLE` as `Nullable`, (`COLUMN_KEY` like 'PRI') as `IsPrimary`, (`EXTRA` like 'auto_increment') as `IsIdentity`, (case when `CHARACTER_MAXIMUM_LENGTH` is null or `CHARACTER_MAXIMUM_LENGTH`=0 then `NUMERIC_PRECISION` else `CHARACTER_MAXIMUM_LENGTH` end) as `Length`, `NUMERIC_SCALE` as `Scale`, `COLUMN_COMMENT` as 'Description', `COLUMN_DEFAULT` as `DefaultValue` FROM information_schema.COLUMNS where TABLE_SCHEMA=@p1 AND table_name=@p2 AND COLUMN_NAME=@p3 order by `Position`,`Name`; ", schemaName, tableName, columnName).FirstOrDefault(); } if (item == null) { item = new DatabaseTableField() { Exists = false, TableName = tableName, Name = columnName, Nullable = true, }; } return(item); }