patternsqlMinor
Why can't C# SMO see extended properties on a column but Powershell SMO can?
Viewed 0 times
whycancolumnpropertiesbutpowershellextendedseesmo
Problem
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:
The PowerShell code:
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:
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:
to retrieve the data from the table. I then go into a while loop with DataReader and inside that while loop I have:
When I step through the
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[""].Tables[""].Columns[""].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()
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.Solution
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.
I created a new console project and added the following references:
All from:
\%Install Path%\Microsoft SQL Server\130\SDK\Assemblies\
So my whole code, based on @BradC's looks like:
Of course you can now access the properties and use their values.
The Count value that wasn't working on your example:
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;
GOI created a new console project and added the following references:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnumAll 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();
}
}
}Of course you can now access the properties and use their values.
The Count value that wasn't working on your example:
Code Snippets
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;
GOMicrosoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnumusing 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();
}
}
}Context
StackExchange Database Administrators Q#168681, answer score: 2
Revisions (0)
No revisions yet.