/// <summary>
/// 获取Sqlserver2008字段信息
/// </summary>
public List <Column> GetColumns2008(string database, string owner, string tableName)
{
var cmd = _connection.CreateCommand();
cmd.CommandText = string.Format(@"
-- 必须在指定库内运行该语句
SELECT
sch.name as schema_name
,tbl.name as table_name
,col.name as column_name
,tp.name as column_type
,col.max_length
,col.precision
,col.scale
,col.is_nullable
,col.is_identity
,p.value as description
FROM sys.tables tbl
inner join sys.schemas sch on sch.schema_id=tbl.schema_id
inner JOIN sys.columns col ON col.object_id = tbl.object_id
left join systypes tp on tp.xusertype=col.user_type_id
LEFT JOIN sys.extended_properties p ON p.major_id = col.object_id AND p.minor_id = col.column_id
WHERE sch.name = '{0}'
and tbl.name = '{1}'
", owner, tableName
);
cmd.CommandType = CommandType.Text;
using (var rdr = cmd.ExecuteReader())
{
var columns = new List <Column>();
while (rdr.Read())
{
var col = new Column();
col.TableSchema = DbCommon.ToString(rdr["schema_name"]);
col.TableName = DbCommon.ToString(rdr["table_name"]);
col.Name = DbCommon.ToString(rdr["column_name"]);
col.DataType = DbCommon.ToString(rdr["column_type"]);
col.CharacterMaximumLength = DbCommon.ToInt32(rdr["max_length"]);
col.NumericPrecision = DbCommon.ToInt32(rdr["precision"]);
col.NumericScale = DbCommon.ToInt32(rdr["scale"]);
col.IsNullbable = DbCommon.ToBoolean(rdr["is_nullable"]);
col.Identity = DbCommon.ToBoolean(rdr["is_identity"]);
col.Description = DbCommon.ToString(rdr["description"]);
columns.Add(col);
}
return(columns);
}
}
public Column(DataRow row)
{
TableCatalog = DbCommon.ToString(row["TABLE_CATALOG"]);
TableSchema = DbCommon.ToString(row["TABLE_SCHEMA"]);
TableName = DbCommon.ToString(row["TABLE_NAME"]);
Name = DbCommon.ToString(row["COLUMN_NAME"]);
OrdinalPosition = DbCommon.ToInt32(row["ORDINAL_POSITION"]);
Defalut = DbCommon.ToString(row["COLUMN_DEFAULT"]);
IsNullbable = DbCommon.ToString(row["IS_NULLABLE"]) == "Yes";
DataType = DbCommon.ToString(row["DATA_TYPE"]);
CharacterMaximumLength = DbCommon.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]);
CharacterOctetLength = DbCommon.ToInt32(row["CHARACTER_OCTET_LENGTH"]);
NumericPrecision = DbCommon.ToInt32(row["NUMERIC_PRECISION"]);
NumericPrecisionRadix = DbCommon.ToInt32(row["NUMERIC_PRECISION_RADIX"]);
NumericScale = DbCommon.ToInt32(row["NUMERIC_SCALE"]);
DateTimePrecision = DbCommon.ToInt32(row["DATETIME_PRECISION"]);
CharacterSetCatalog = DbCommon.ToString(row["CHARACTER_SET_CATALOG"]);
CharacterSetSchema = DbCommon.ToString(row["CHARACTER_SET_SCHEMA"]);
CharacterSetName = DbCommon.ToString(row["CHARACTER_SET_NAME"]);
CollationCatalog = DbCommon.ToString(row["COLLATION_CATALOG"]);
}
