从SQL Server抓取扩展属性到数据表 [英] Grabbing extended properties from SQL Server into DataTable
问题描述
我有一个名为视图 PersonOverview 里面有一堆列;这是一个完全正常的观点，没有什么特别的地方。
我添加了名为扩展属性 FlexGridHide 与 1 的一个值 DatenbereichCD 使用该视图的列。
EXEC sys.sp_addextendedproperty
@name = N'FlexGridHide'，
@value = N'1'，
@ level0type = N'SCHEMA'，@ level0name = DBO，
@ level1type = N'VIEW'，@ level1name = vPersonOverview，
@ level2type = N'COLUMN'，@ level2name = DatenbereichCD;
我可以找到扩展属性在SQL Server - 没问题 - 它的存在。
但是，当我从视图数据加载到一个数据表，我显然不能够真正读出的扩展属性：
字符串的SQLSelect = @SELECT TOP 5 DatenbereichCD从dbo.vPersonOverview;
数据表personUebersicht =新的DataTable（）;
使用（SqlConnection的CON =新的SqlConnection（ConfigurationManager.ConnectionStrings [MYDB。的ConnectionString））
使用（CMD的SqlCommand =新的SqlCommand（一个SQLSelect，CON））
使用（SqlDataAdapter的DAP =新的SqlDataAdapter（CMD））
{
dap.Fill（personUebersicht）;
}
DataColumn的datenbereichCD = personUebersicht.Columns [DatenbereichCD];
INT extendedProps = datenbereichCD.ExtendedProperties.Count;
连接工作得很好，查询被执行就好了，返回五行不如预期，该列是present，一切似乎罚款 - 但我不明白，在 .Count之间始终为0
任何想法？有什么我可以做真正的获得的扩展属性？连接字符串参数或属性的的SqlCommand 或模糊的东西？
更新：未经训练的无知，其上的SQL Server列的扩展属性和扩展属性在ADO.NET 的DataColumn 键入类的建议，这些SQL Server扩展属性将被加载到ADO.NET扩展属性 - 但是这确实不是似乎是这样的 - 不错啊.....
我最终使用第二个查询，凯文建议 - 但因为我需要为表和视图扩展属性，你必须的指定的，你要寻找的使用则fn_listextendedproperty 功能，我却选择查询 sys.extended_properties 系统目录视图，因为我需要的信息。这这里是我使用得到我需要有关从SQL Server扩展属性的信息，我的查询：
SELECT
ep.class，ep.class_desc，ep.name，ep.value，
的SchemaName = s.name，
的ObjectName = o.name，
的ColumnName = c.Name，
对象类型= o.type，
ObjectTypeDesc = o.type_desc
从sys.extended_properties EP
INNER JOIN sys.objects中o在ep.major_id = o.object_id
INNER JOIN sys.schemas S上o.schema_id = s.schema_id
INNER JOIN SYS.COLUMNS C对ep.major_id = c.object_id和ep.minor_id = c.column_id
据<一href="http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/9371cf9c-48a1-4fdc-89e5-d01fcf69e2c1"相对=nofollow>这个帖子的SQL Server扩展性和ADO.NET扩展属性是没有关系的：
的dataColumn.ExtendedProperties是不相关的列上的SQL服务器后端的扩展属性
看来，你将需要诉诸于<一个检索扩展属性href="http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-description-property-of-a-column.html"相对=nofollow>单独的查询。
数据表personUebersicht =新的DataTable（）;
数据表extendedProperties =新的DataTable（）;
使用（SqlConnection的CON =新的SqlConnection（ConfigurationManager.ConnectionStrings [MYDB。的ConnectionString））
{
字符串的SQLSelect = @SELECT TOP 5 DatenbereichCD从dbo.vPersonOverview;
使用（CMD的SqlCommand =新的SqlCommand（一个SQLSelect，CON））
使用（SqlDataAdapter的DAP =新的SqlDataAdapter（CMD））
{
dap.Fill（personUebersicht）;
}
字符串sqlProperties = @SELECT名字，值从::则fn_listextendedproperty（NULL，用户，DBO，观点，vPersonOverview'，'柱'，'DatenbereichCD'）;
使用（CMD的SqlCommand =新的SqlCommand（sqlProperties，CON））
使用（SqlDataAdapter的DAP =新的SqlDataAdapter（CMD））
{
dap.Fill（extendedProperties）;
}
}
//测试结果
的foreach（DataRow的行extendedProperties.Rows）
{
Console.WriteLine（的String.Format（{0}：{1}，行[名称]，行[价值]））;
}
I have a view called PersonOverview which has a bunch of columns; it's a totally normal view, nothing special about it.
I added an extended property called FlexGridHide with a value of 1 to the DatenbereichCD column of that view using.
EXEC sys.sp_addextendedproperty
@name = N'FlexGridHide',
@value = N'1',
@level0type = N'SCHEMA', @level0name = dbo,
@level1type = N'VIEW', @level1name = vPersonOverview,
@level2type = N'COLUMN', @level2name = DatenbereichCD;
I can find that extended property in SQL Server - no problem - it's there.
But when I load data from the view into a DataTable, I'm obviously not able to actually read out that extended property:
string sqlSelect = @"SELECT TOP 5 DatenbereichCD FROM dbo.vPersonOverview";
DataTable personUebersicht = new DataTable();
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MYDB"].ConnectionString))
using (SqlCommand cmd = new SqlCommand(sqlSelect, con))
using (SqlDataAdapter dap = new SqlDataAdapter(cmd))
{
dap.Fill(personUebersicht);
}
DataColumn datenbereichCD = personUebersicht.Columns["DatenbereichCD"];
int extendedProps = datenbereichCD.ExtendedProperties.Count;
The connection works just fine, the query gets executed just fine, returns five rows as expected, the column is present, and everything seems fine - except I don't get any values in the ExtendedProperties collection - the .Count is always 0.
Any ideas? Is there anything I can do to actually get those extended properties? Connection string parameter or a property on the SqlCommand or something obscure?
Update: to the untrained ignorant, having Extended Properties on SQL Server columns, and Extended Properties on the ADO.NET DataColumn type sort of suggested that those SQL Server extended properties would be loaded into the ADO.NET extended properties - but that really doesn't seem to be the case - ah well.....
I ended up using a second query, as Kevin suggested - but since I need to get extended properties for both tables and views and you have to specify what you're looking for using the fn_listextendedproperty function, I instead chose to query the sys.extended_properties system catalog view for the information I need. This here is my query that I'm using to get the information I need about the extended properties from SQL Server:
SELECT
ep.class, ep.class_desc, ep.name, ep.value,
SchemaName = s.name,
ObjectName = o.name,
ColumnName = c.Name,
ObjectType = o.type,
ObjectTypeDesc = o.type_desc
FROM sys.extended_properties ep
INNER JOIN sys.objects o ON ep.major_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.columns c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
According to this post the SQL Server extended properties and the ADO.NET extended properties are not related:
The dataColumn.ExtendedProperties is not related to the extended property of column on the SQL server backend.
It appears that you will need to resort to retrieving the extended properties in a separate query.
DataTable personUebersicht = new DataTable();
DataTable extendedProperties = new DataTable();
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MYDB"].ConnectionString))
{
string sqlSelect = @"SELECT TOP 5 DatenbereichCD FROM dbo.vPersonOverview";
using (SqlCommand cmd = new SqlCommand(sqlSelect, con))
using (SqlDataAdapter dap = new SqlDataAdapter(cmd))
{
dap.Fill(personUebersicht);
}
string sqlProperties = @"SELECT name, value FROM ::fn_listextendedproperty(null,'user','dbo','view','vPersonOverview','column','DatenbereichCD')";
using (SqlCommand cmd = new SqlCommand(sqlProperties, con))
using (SqlDataAdapter dap = new SqlDataAdapter(cmd))
{
dap.Fill(extendedProperties);
}
}
// Test the results
foreach (DataRow row in extendedProperties.Rows)
{
Console.WriteLine(string.Format("{0}: {1}", row["name"], row["value"]));
}
这篇关于从SQL Server抓取扩展属性到数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
