7

I am attempting to read extended properties on tables and columns in a winforms C# application. I am using SQL Server SMO to do so. When I execute the application it does not see the extended properties, but when I read the extended properties using PowerShell, it does see the extended properties.

The C# code:

var x = col.ExtendedProperties.Count;
var NPI = col.ExtendedProperties["NPI"].Value;
bool npi = bool.Parse(NPI.ToString());

The PowerShell code:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$server.Databases["<db name>"].Tables["<table name>"].Columns["<column name>"].ExtendedProperties | Select Name, Value, State

I have checked and both Visual Studio and PowerShell are using the same version of SMO (11.0.0.0). When I execute the C# code the col.ExtendedProperties.Count = 0, but when I execute the PowerShell code I get:

Name Value    State
---- -----    -----
NPI  False Existing

Does anyone have any ideas as to why this could be happening?

Additional Information

In the C# code I open up a DataReader on a table using:

sourceServer.ConnectionContext.ExecuteReader(<select command>)

to retrieve the data from the table. I then go into a while loop with DataReader and inside that while loop I have:

foreach (Column col in sourceTable.Columns) 
{ 
StringBuilder cleanData = CleanseColumn(col, dr[col.Name].ToString());
sbvalues.Append("'" + cleanData + "', "); 
}

When I step through the foreach, the sourceTable variable has its extended property, but the col column variable does not.

  • Wayne, have you run SQL Server Profiler, or Extended Events, to capture what queries are being executed by SMO in each case? It would help to know if the queries being executed by SMO via PowerShell are, or are not, being executed when run via C#. It would also really help to see some actual code posted in the question (not in a comment here) as I am curious if you are connecting the exact same way in each case. – Solomon Rutzky Apr 20 '17 at 20:00
2

I just want to shadow @BradC a little. I tested his code on Visual Studio 2015 (Update 3) on a Windows 10 Pro x64 machine against an SQL Server 2014 instance and I can confirm you can get Extended Properties of a field without issues.

To replicate I use one of my local databases. I used the following code snip to define some extended properties.

USE MyLocalDb;
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_DescriptionExample', 
@value = N'Some EP example.', 
@level0type = N'SCHEMA', @level0name = dbo, 
@level1type = N'TABLE',  @level1name = tblATableWithAtblPrefix,
@level2type = N'COLUMN', @level2name = Forename;
GO

I created a new console project and added the following references:

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum

All from:

\%Install Path%\Microsoft SQL Server\130\SDK\Assemblies\

So my whole code, based on @BradC's looks like:

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace ConsoleApplication7
{
    class Program
    {
        private static Database database;
        static void Main(string[] args)
        {
            Microsoft.SqlServer.Management.Smo.Server server;

            SqlConnection connection = new SqlConnection("Integrated Security=SSPI; Data Source=SIS_DBA");
            Microsoft.SqlServer.Management.Common.ServerConnection serverConnection = 
                new Microsoft.SqlServer.Management.Common.ServerConnection(connection);

            server = new Server(serverConnection);

            database = server.Databases["ImmigSql"];

            foreach (Table table in database.Tables)
            {
                Console.WriteLine(" " + table.Name);
                foreach (Column col in table.Columns)
                {
                    Console.WriteLine("  " + col.Name + " " + col.DataType.Name);
                    foreach (var property in col.ExtendedProperties)
                    {
                        Console.WriteLine(" " + property.ToString() + "");
                    }
                }
            }
            Console.ReadLine();
        }
    }
}

enter image description here

Of course you can now access the properties and use their values.

The Count value that wasn't working on your example:

enter image description here

1

You haven't shown your code that instantiates the col object, can we see that code?

Using something like datareader's GetSchemaTable() method isn't going to actually return the actual (SQL SMO) table object, it returns a new table with many useful column properties, but (as far as I can see) that does not include extended properties.

Take a look at this StackOverflow answer by Christopher Klein. If I'm reading that right, that should return an actual SQL SMO table object, which I think will allow you to retrieve extended column properties, something like:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;


// Add references: (in c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\)
// Microsoft SqlServer.ConnectionInfo
// Microsoft SqlServer.Management.Sdk.Sfc
// Microsoft SqlServer.Smo

namespace SMO
{
    class Program
    {
        static Database db;

        static void Main(string[] args)
        {
            Microsoft.SqlServer.Management.Smo.Server server;

            SqlConnection sqlConnection = new SqlConnection(@"Integrated Security=SSPI; Data Source=LOCAL");
            //build a "serverConnection" with the information of the "sqlConnection"
            Microsoft.SqlServer.Management.Common.ServerConnection serverConnection =
              new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);

            //The "serverConnection is used in the ctor of the Server.
            server = new Server(serverConnection);

            db = server.Databases["TestDB"];

            Table tbl;
            tbl = db.Tables["Sales"];

            //warning, not tested, your code goes here
            foreach (Column col in tbl.Columns)
            {
               var x = col.ExtendedProperties.Count;
               var NPI = col.ExtendedProperties["NPI"].Value;
               bool npi = bool.Parse(NPI.ToString());
            } 
        }
    }
}

If that doesn't work, just stick with your working PowerShell version, or resort to one of the other ideas in this related answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.