Access extended properties from a dataset

Posted on 2006-04-14
Medium Priority
Last Modified: 2010-08-05
Greetings, I added some extended properties to some columns in my sql 2005 table, but I'm having trouble accessing these properties from my dataset.  Do I need to remove my TableAdaptor compeletly and re-add it in my .xsd file in order for me to be able to access the extended propeties I added?  Here is the VB code I'm trying.

For Each DC As DataColumn In ourData.Cont.Columns
     If DC.ExtendedProperties.ContainsKey("Prop1") AndAlso DC.ExtendedProperties.Item("Prop1").ToString = "Y" Then
          ' Do something...
     End If

DC.ExtendedProperties.count is 0 for some reason
Question by:Chaffe
  • 3
  • 3

Expert Comment

ID: 16455806
Try using the fn_listextendedproperty function when you create your dataset:

--View  extended property
SELECT * FROM ::fn_listextendedproperty(NULL,'user','dbo','table',urTable,NULL,NULL)

fn_listextendedproperty (
    { default | [ @name = ] 'property_name' | NULL }
    , { default | [ @level0type = ] 'level0_object_type' | NULL }
    , { default | [ @level0name = ] 'level0_object_name' | NULL }
    , { default | [ @level1type = ] 'level1_object_type' | NULL }
    , { default | [ @level1name = ] 'level1_object_name' | NULL }
    , { default | [ @level2type = ] 'level2_object_type' | NULL }
    , { default | [ @level2name = ] 'level2_object_name' | NULL }


Author Comment

ID: 16456254
JRossi1, I'm still new to this.  can you tell me where does the syntax of the fn_listextendedproperty goes?

Accepted Solution

JRossi1 earned 1600 total points
ID: 16456650
When you create your dataset, use the following SQL Statement, substituting your table name etc...,

"SELECT   * FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'YourTableName', 'column', default)"  The following dataset will be created:

objtype      objname   name        value

COLUMN      id      caption      Employee ID
COLUMN      id      MS_Description      Employee unique ID
COLUMN      name      caption      Employee Name
COLUMN      name      MS_Description      Employee Full Name

You can loop through this dataset and populate a table, DataGrid, etc... with a tables extended properties.  In the 'name' column, 'MS_Description' refers to the column description in the design view of a SQL table.

Let me know if you need further clarification.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 16457737
JRossi1,  Thanks for the clarificaiton, I got it working.  Now if I want to get the acutal table data and the properties of the columns, I would have to send 2 SELECT queries to get what I want?

SELECT   * FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'MyTable', 'column', default);
SELECT   * FROM   MyTable;

Expert Comment

ID: 16468631
Correct....  In your last comment, the first SELECT statement would return only the table properties and the 2nd SELECT statement would return the data...

Author Comment

ID: 16471465
Got it.  Thanks for your help

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question