Reputation: 1290
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/
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