/// <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);
}
