Value of extended property in T4 script returns a quoted, escaped string?

Question

Post reply

Value of extended property in T4 script returns a quoted, escaped string?

rolandpater

Old Hand

Points: 318

October 12, 2019 at 9:10 am

#3688201

Hi,

I have a T4 script which reads extended properties. The value of these properties seems to be escaped.

Example: I have a table person with an extended property called propertyName that has the value propertyValue.

When I read this value I get: N'propertyValue'. The value that appears in the T4 script is the escaped value.

The script that reads the properties of the table 'person'

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ output extension=".sql" #>
<#@ SqlModelDirective processor="SqlModelDirectiveProcessor" #>

<#@ assembly name="System.Core" #>
<#@ import namespace="System.Linq" #>

<#@ import namespace="System.Text" #>
<#@ import namespace="Microsoft.SqlServer.Dac" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="Microsoft.SqlServer.Dac.Model" #>

<#
{
   WriteLine(@"/*");

   foreach (var table in Model.GetObjects(DacQueryScopes.All, ModelSchema.Table).Where(x=> x.Name.Parts[1] == "person"))
   {
   WriteLine(@"{0}", table.Name);

   foreach (var prop in table.GetReferencing().Where(x => (x.ObjectType.Name == "ExtendedProperty")))
   {
       WriteLine(@"{0}", prop.Name);
       WriteLine(@"{0}", prop.GetProperty(ExtendedProperty.Value).GetType());
       WriteLine(@"{0}", (String)prop.GetProperty(ExtendedProperty.Value));
       WriteLine(@"{0}", ((String)prop.GetProperty(ExtendedProperty.Value)).Length);
   }
   }

   WriteLine(@"*/");
}

#>

The result of the script:

/*
[dbo].[person]
[SqlTableBase].[dbo].[person].[propertyName]
System.String
N'propertyValue'
16
*/

If I put quotes in the value, they will be escaped as double quotes. It looks like a value meant for an SQL parser.

Is there any way to read the property value in a normal way, without the N prefix and the extra quotes?

Regards,

Roland.

Edit:

If I remove the quotes in the script that generates the extended property:

EXECUTE sp_addextendedproperty @name = N'propertyName', @value = propertyValue, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'person';

the T4 script will generate the desired output:

/*
[dbo].[person]
[SqlTableBase].[dbo].[person].[propertyName]
System.String
propertyValue
13
*/

But this is not a good solution of course.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.