pix
pix

Reputation: 1290

DacFx Get columns informations from function

I am currently trying to extract the column datatype from the SQL Server tables, views & functions in C# using DacFx in .NET.

I would like to retrieve the same information with DacFx that I can get with the following SQL query:

WITH [PrimaryIndex] AS
(
    SELECT
        [ic].[object_id],
        [ic].[column_id], 
        [i].[is_primary_key]
    FROM     
        [sys].[index_columns] AS [ic]
    JOIN 
        [sys].[indexes] AS [i] ON [ic].[object_id] = [i].[object_id]
                               AND [ic].[index_id] = [i].[index_id]
    WHERE    
        [i].[is_primary_key] = 1
)
SELECT
    [c].[name] AS [ColumnName],
    [t].[name] AS [Datatype],
    [c].[max_length] AS [MaxLength],
    [c].[precision],
    [c].[scale],
    [c].[is_nullable],
    ISNULL([pri].[is_primary_key], 0) AS [Primary Key],
    c.column_id,
    c.collation_name
FROM
    [sys].[columns] AS [c]
INNER JOIN 
    [sys].[types] AS [t] ON [c].[user_type_id] = [t].[user_type_id]
LEFT JOIN  
    [PrimaryIndex] AS [pri] ON [pri].[object_id] = [c].[object_id]
                            AND [pri].[column_id] = [c].[column_id]
WHERE 
    c.object_id = OBJECT_ID('DemoSchema.FunctionName')
ColumnName Datatype MaxLength precision scale is_nullable Primary Key column_id Collation_name
Col1 int 4 10 0 1 0 1 NULL
Col2 date 3 10 0 0 0 2 NULL
Col3 decimal 17 38 10 1 0 3 NULL
Col4 decimal 17 38 10 1 0 4 NULL
Col5 decimal 17 38 10 1 0 5 NULL
Col6 decimal 17 38 10 1 0 6 NULL
Col7 decimal 17 38 10 1 0 7 NULL
Col8 decimal 17 38 10 1 0 8 NULL

This is my C# code - I need to get the information the TableValuedFunction.Columns with the following code.

 Console.WriteLine($"Load DacPac Content [{dacpacFullPath}]");
 var dapacs = Directory.GetFiles(dacpacFullPath, "*.dacpac");
 Console.WriteLine($"[{dapacs.Length}] DacPac Found!");
 foreach (var dapac in dapacs)
 {
     var modelFromDacpac = new TSqlModel(dapac);
     Console.WriteLine($"LoadDing Current DacPac [{dapac}]");
     this.dacPacObjects.AddRange(modelFromDacpac.GetObjects(DacQueryScopes.Default).ToList());
     Console.WriteLine($"DacPac  Objects [{this.dacPacObjects.Count}]");
 }

// Data type documentation : https://github.com/GoEddie/DacFx-Info/blob/master/DacFxRelationships/ModelSchema.csv
// Look up a specific table by ID. Note that if no schema is defined when creating 
// an element the default "dbo" schema is used
var currentObject = this.dacPacObjects.FirstOrDefault(x => x.Name.Parts.Count >= 2 && $"{x.Name.Parts[0]}.{x.Name.Parts[1]}".ToLower() == fullName.ToLower());
if (currentObject.ObjectType == TableValuedFunction.TypeClass)
{
    foreach (var col in currentObject.GetReferenced(TableValuedFunction.Columns, DacQueryScopes.All).ToList())
    {
        Console.WriteLine("\t" + col.Name);
        Console.WriteLine("\tProperties:");
        foreach (var property in col.ObjectType.Properties)
        {
            Console.WriteLine("\t\t" + property.Name + "\t\t" + property.DataType.FullName);
        }

        Console.WriteLine("\tMetadata:");
        foreach (var metaData in col.ObjectType.Metadata)
        {
            Console.WriteLine("\t\t" + metaData.Name + "\t\t" + metaData.DataType.FullName);
        }

        Console.WriteLine($"Precision ->{col.GetProperty<Int32>(Column.Precision)}");
        Console.WriteLine($"Scale->{col.GetProperty<Int32>(Column.Scale)}");
        Console.WriteLine($"Length->{col.GetProperty<Int32>(Column.Length)}");
    }
}


Everything is equal to 0 and I cannot even get the column datatype.

And this my console output:

Load DacPac Content [E:\DacPac]
[5] DacPac Found!
LoadDing Current DacPac [E:\DacPac\Dac1.dacpac]
DacPac  Objects [318]
LoadDing Current DacPac  [E:\DacPac\master.dacpac]
DacPac  Objects [2743]
LoadDing Current DacPac [E:\DacPac\Dac2.dacpac]
DacPac  Objects [5917]
LoadDing Current DacPac [E:\DacPac\Dac3.dacpac]
DacPac  Objects [11755]
LoadDing Current DacPac [E:\DacPac\Dac4.dacpac]
DacPac  Objects [13640]
        [DemoSchema].[FunctionName].[Col1]
        Properties:
                Collation               System.String
                IsIdentityNotForReplication             System.Boolean
                Nullable                System.Boolean
                IsRowGuidCol            System.Boolean
                Sparse          System.Boolean
                Expression              Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlScriptProperty
                Persisted               System.Boolean
                PersistedNullable               System.Nullable`1[[System.Boolean, System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]]
                Scale           System.Int32
                Precision               System.Int32
                Length          System.Int32
                IsMax           System.Boolean
                XmlStyle                Microsoft.SqlServer.Dac.Model.XmlStyle
                EncryptionAlgorithmName         System.String
                EncryptionType          System.Int32
                GeneratedAlwaysType             Microsoft.SqlServer.Dac.Model.ColumnGeneratedAlwaysType
                GraphType               System.Int32
                IdentityIncrement               System.String
                IdentitySeed            System.String
                IsFileStream            System.Boolean
                IsHidden                System.Boolean
                IsIdentity              System.Boolean
                IsPseudoColumn          System.Boolean
                MaskingFunction         System.String
                SensitivityInformationType              System.String
                SensitivityInformationTypeId            System.String
                SensitivityLabel                System.String
                SensitivityLabelId              System.String
                SensitivityRank         System.Int32
        Metadata:
                ColumnType              Microsoft.SqlServer.Dac.Model.ColumnType
Precision ->0
Scale->0
Length->0
        [DemoSchema].[FunctionName].[Col2]
        Properties:
                Collation               System.String
                IsIdentityNotForReplication             System.Boolean
                Nullable                System.Boolean
                IsRowGuidCol            System.Boolean
                Sparse          System.Boolean
                Expression              Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlScriptProperty
                Persisted               System.Boolean
                PersistedNullable               System.Nullable`1[[System.Boolean, System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]]
                Scale           System.Int32
                Precision               System.Int32
                Length          System.Int32
                IsMax           System.Boolean
                XmlStyle                Microsoft.SqlServer.Dac.Model.XmlStyle
                EncryptionAlgorithmName         System.String
                EncryptionType          System.Int32
                GeneratedAlwaysType             Microsoft.SqlServer.Dac.Model.ColumnGeneratedAlwaysType
                GraphType               System.Int32
                IdentityIncrement               System.String
                IdentitySeed            System.String
                IsFileStream            System.Boolean
                IsHidden                System.Boolean
                IsIdentity              System.Boolean
                IsPseudoColumn          System.Boolean
                MaskingFunction         System.String
                SensitivityInformationType              System.String
                SensitivityInformationTypeId            System.String
                SensitivityLabel                System.String
                SensitivityLabelId              System.String
                SensitivityRank         System.Int32
        Metadata:
                ColumnType              Microsoft.SqlServer.Dac.Model.ColumnType
Precision ->0
Scale->0
Length->0
etc...


I also try to a ton of research from the two following websites: https://the.agilesql.club/2019/04/dacfx-how-to-get-the-data-type-from-a-column-a-discussion-of-properties-relationships-and-the-tsqlmodel/

https://sqlserverfunctions.wordpress.com/2014/09/27/querying-the-dacfx-api-getting-column-type-information/

But they both refer to a Table.Columns type and not the TableValuedFunction.Columns

Is there a solution to retrieve this information from the Dacpac? Or do I have skip this option and retrieve the data from SQL (I would like to avoid this step).

Thank you in advance.

Upvotes: 0

Views: 34

Answers (0)

Related Questions