public static List <ColumnContext> GetColumnContextListByTableName(string databaseName, string tableName)
{
var list = new List <ColumnContext>();
var sql = @" select
col.COLUMN_NAME as columnName,
(case when PKeyCol.COLUMN_NAME is null then '' else 'PK' end) +
(case when KeyCol2.COLUMN_NAME is null then ''
when NOT PKeyCol.COLUMN_NAME is null then ',FK' else 'FK' end) as kk,--PK=主键,FK=外键,PKFK=是主键同时外键
col.DATA_TYPE as dataType,
(case when CHARACTER_MAXIMUM_LENGTH is null then '' else CAST(CHARACTER_MAXIMUM_LENGTH as varchar(50)) end) as columnLength,
col.IS_NULLABLE as isNullAble,--NO=不能为空,YES=可空
col.COLUMN_DEFAULT as defaultValue,
ISNULL (CAST(coldesc.[value] AS nvarchar(50)) , '') AS columnDescription
from INFORMATION_SCHEMA.COLUMNS as col
LEFT OUTER JOIN
(select COLUMN_NAME,TABLE_NAME FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol
LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol ON
KeyCol.CONSTRAINT_CATALOG=RefCol.CONSTRAINT_CATALOG AND
KeyCol.CONSTRAINT_NAME=RefCol.CONSTRAINT_NAME
WHERE RefCol.CONSTRAINT_NAME IS NULL) PKeyCol
ON PKeyCol.COLUMN_NAME=Col.COLUMN_NAME AND PKeyCol.TABLE_NAME=Col.TABLE_NAME
LEFT OUTER JOIN
(INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol2
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol2 ON
KeyCol2.CONSTRAINT_CATALOG=RefCol2.CONSTRAINT_CATALOG AND
KeyCol2.CONSTRAINT_NAME=RefCol2.CONSTRAINT_NAME)
ON KeyCol2.COLUMN_NAME=Col.COLUMN_NAME AND KeyCol2.TABLE_NAME=Col.TABLE_NAME
LEFT OUTER JOIN ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '" + tableName + "', 'column', default) as coldesc ON col.COLUMN_NAME = coldesc.objname COLLATE Chinese_PRC_CI_AS where col.TABLE_NAME='" + tableName + "'";
var cmd = new SqlCommand(sql);
using (var dataReader = new SqlDatabase(DataBaseConnectionConfig.GetDataBaseConnectionString(databaseName)).ExecuteReader(cmd))
{
while (dataReader.Read())
{
var column = new ColumnContext();
column.Name = dataReader.GetString(0);//列名
column.IsPk = dataReader.GetString(1).Equals("PK");
column.IsFk = dataReader.GetString(1).Equals("FK");
column.DateType = dataReader.GetString(2); //数据类型
column.Length = string.IsNullOrEmpty(dataReader.GetString(3)) ? 0 : int.Parse(dataReader.GetString(3)); //数据长度
column.IsNull = dataReader.GetString(4).ToLower().Equals("yes"); //是否为空
column.DefaultValue = dataReader.GetValue(5) == null ? string.Empty : dataReader.GetValue(5).ToString(); //string.IsNullOrEmpty(dataReader.GetString(5)) ? string.Empty : dataReader.GetString(5);//默认值
column.NameDescription = dataReader.GetString(6); //字段说明
list.Add(column);
}
}
return(list);
}
/// <summary>
/// 根据表名获取所有字段集合
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static List<ColumnContext> GetColumnContextListByTableName(string tableName)
{
var list=new List<ColumnContext>();
var sql = @" select
col.COLUMN_NAME as columnName,
(case when PKeyCol.COLUMN_NAME is null then '' else 'PK' end) +
(case when KeyCol2.COLUMN_NAME is null then ''
when NOT PKeyCol.COLUMN_NAME is null then ',FK' else 'FK' end) as kk,--PK=主键,FK=外键,PKFK=是主键同时外键
col.DATA_TYPE as dataType,
(case when CHARACTER_MAXIMUM_LENGTH is null then '' else CAST(CHARACTER_MAXIMUM_LENGTH as varchar(50)) end) as columnLength,
col.IS_NULLABLE as isNullAble,--NO=不能为空,YES=可空
col.COLUMN_DEFAULT as defaultValue,
ISNULL (CAST(coldesc.[value] AS nvarchar(50)) , '') AS columnDescription
from INFORMATION_SCHEMA.COLUMNS as col
LEFT OUTER JOIN
(select COLUMN_NAME,TABLE_NAME FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol
LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol ON
KeyCol.CONSTRAINT_CATALOG=RefCol.CONSTRAINT_CATALOG AND
KeyCol.CONSTRAINT_NAME=RefCol.CONSTRAINT_NAME
WHERE RefCol.CONSTRAINT_NAME IS NULL) PKeyCol
ON PKeyCol.COLUMN_NAME=Col.COLUMN_NAME AND PKeyCol.TABLE_NAME=Col.TABLE_NAME
LEFT OUTER JOIN
(INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyCol2
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RefCol2 ON
KeyCol2.CONSTRAINT_CATALOG=RefCol2.CONSTRAINT_CATALOG AND
KeyCol2.CONSTRAINT_NAME=RefCol2.CONSTRAINT_NAME)
ON KeyCol2.COLUMN_NAME=Col.COLUMN_NAME AND KeyCol2.TABLE_NAME=Col.TABLE_NAME
LEFT OUTER JOIN ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '"+tableName+"', 'column', default) as coldesc ON col.COLUMN_NAME = coldesc.objname COLLATE Chinese_PRC_CI_AS where col.TABLE_NAME='"+tableName+"'";
var cmd = new SqlCommand(sql);
using (var dataReader = _db.ExecuteReader(cmd))
{
while (dataReader.Read())
{
var column = new ColumnContext();
column.Name = dataReader.GetString(0);//列名
column.IsPk = dataReader.GetString(1).Equals("PK");
column.IsFk = dataReader.GetString(1).Equals("FK");
column.DateType = dataReader.GetString(2);//数据类型
column.Length = string.IsNullOrEmpty(dataReader.GetString(3)) ? 0 : int.Parse(dataReader.GetString(3));//数据长度
column.IsNull = dataReader.GetString(4).ToLower().Equals("yes");//是否为空
column.DefaultValue = dataReader.GetValue(5) == null ? string.Empty : dataReader.GetValue(5).ToString(); //string.IsNullOrEmpty(dataReader.GetString(5)) ? string.Empty : dataReader.GetString(5);//默认值
column.NameDescription = dataReader.GetString(6);//字段说明
list.Add(column);
}
}
return list;
}
