none
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

    Wednesday, November 12, 2008 5:42 PM

Answers

All replies