SQL Server 2008 Column Extended Properties not working?

Question

I have been staring at this for a bit and I must be missing something stupid. I have a table:

 

Code Snippet

CREATE TABLE

---

.[Employee](

[EmployeeID] [int] IDENTITY(1,1) NOT NULL,

[LoginID] [varchar](16) NULL,

[Password] [varchar](16) NULL,

[FirstName] [varchar](50) NOT NULL,

[MiddleName] [varchar](50) NULL,

[LastName] [varchar](50) NOT NULL,

[GenderID] [int] NOT NULL,

[Active] [bit] NOT NULL,

CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED

(

[EmployeeID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

EXEC sys.sp_addextendedproperty @name=N'LabelCaption', @value=N'Login ID:' , @level0type=N'SCHEMA',@level0name=N'HR', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'LoginID'

GO

EXEC sys.sp_addextendedproperty @name=N'LabelCaption', @value=N'Password:' , @level0type=N'SCHEMA',@level0name=N'HR', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Password'

GO

EXEC sys.sp_addextendedproperty @name=N'LabelCaption', @value=N'First Name:' , @level0type=N'SCHEMA',@level0name=N'HR', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'FirstName'

GO

EXEC sys.sp_addextendedproperty @name=N'LabelDisplay', @value=N'Middle Name:' , @level0type=N'SCHEMA',@level0name=N'HR', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'MiddleName'

GO

EXEC sys.sp_addextendedproperty @name=N'LabelCaption', @value=N'Last Name:' , @level0type=N'SCHEMA',@level0name=N'HR', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'LastName'

GO

EXEC sys.sp_addextendedproperty @name=N'LabelCaption', @value=N'Gender:' , @level0type=N'SCHEMA',@level0name=N'HR', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'GenderID'

GO

EXEC sys.sp_addextendedproperty @name=N'LabelCaption', @value=N'Active:' , @level0type=N'SCHEMA',@level0name=N'HR', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Active'

GO

ALTER TABLE

---

.[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Gender] FOREIGN KEY([GenderID])

REFERENCES

---

.[Gender] ([GenderID])

GO

ALTER TABLE

---

.[Employee] CHECK CONSTRAINT [FK_Employee_Gender]

GO

 

 

In my data layer, I am filling the data table something like this:

 

Code Snippet

DataTable ret = new DataTable();

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, _sqlConnection);

sqlDataAdapter.FillSchema(ret, SchemaType.Source);

sqlDataAdapter.Fill(ret);

 

 

In my test code, I am enumerating the table like this:

 

Code Snippet

string connectionString = "Data Source=(local);Integrated Security=SSPI;Initial Catalog=Lab;Pooling=true;";

using (Database database = new Database(connectionString))

{

database.Open();

string sql = "SELECT * FROM HR.Employee ORDER BY LastName, FirstName, MiddleName";

DataTable dataTable = database.ExecuteTable(sql);

DataColumnCollection dataColumns = dataTable.Columns;

foreach (DataRow dataRow in dataTable.Rows)

{

for (int i = 0; i < dataRow.ItemArray.Length; i++)

{

string labelCaption = string.Empty;

if (dataColumns[i].ExtendedProperties != null)

if (dataColumns[i].ExtendedProperties.ContainsKey("LabelCaption"))

labelCaption = dataColumns[i].ExtendedProperties["LabelCaption"].ToString();

listBoxData.Items.Add(labelCaption + " " + dataRow.ItemArray.GetValue(i).ToString());

}

listBoxData.Items.Add(" ");

}

}

 

 

I can't seem to read the extended properties for each column. The count is always zero. I am getting data back through the dataTable however. Am I missing something obvious? I would expect the extended properties to be returned when I ask for the schema.

 

Thank you for your help!

Michael

Answer 1

 the dataColumn.ExtendedProperties is not related to the extended property of column on the SQL server backend.

 

the extended property of a column is not returned in the sqlclient schema query, so you need to find alternative methods to get the extended property.

 

for example:

http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-description-property-of-a-column.html

 

 

Answer 2

Thanks! Your link was the answer.

 

Answer 3

Hello,

 

In fact , you can gain access to the extended properties of columns only thru Smo

Please,could have a look on this link ?

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.column.extendedproperties.aspx

I will post later a piece of code to obtain the extend property ( name "MS_Description")

it is simple enough but for a beginner in Smo, it might seem complicated

You have an example from Kuntal Loya (MSFT) here :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1686537&SiteID=1

( an example short but excellent )

 

Have a nice day

 

PS : i hope that the name of Kuntal is well-written