Finding data type information for certain objects in DacFx API

Question

I'm writing a code generation tool against Sql Server Data Tools and I need to be able to get the data type for:

1. View Columns
2. Computed Columns on a table

Where is this information? For tables (with the exception of computed columns), it's here:

TSqlObject table;  //retrieved elsewhere
TSqlObject column = table.GetReferenced(Table.Columns).First(); //first column
TSqlObject dataType = column.GetReferenced(Column.DataType).FirstOrDefault();

For computed columns, dataType above is null.

For views, I've tried:

TSqlObject view;  //retrieved elsewhere
TSqlObject column = view.GetReferenced(View.Columns).First(); //first column
TSqlObject dataType = column.GetReferenced(Column.DataType).FirstOrDefault();//null

Is this information anywhere? Are there any other options for getting this information other than publishing the source .DACPAC to a database?

---

EDIT: in response to Ed Elliot below (regarding the use of the strongly-typed DacFx model)

The following code fails to bring back type information for the view:

TSqlTypedModel model = new TSqlTypedModel(@"path.dacpac");
var view = model.GetObjects<TSqlView>(Microsoft.SqlServer.Dac.Model.DacQueryScopes.UserDefined).FirstOrDefault();
var viewcolumns = view.Columns;

//false
bool viewHasTypeInformation = viewcolumns.Any(c => c.DataType.Count() > 0); 

var table = model.GetObjects<TSqlTable>(Microsoft.SqlServer.Dac.Model.DacQueryScopes.UserDefined).FirstOrDefault();
var tablecolumns = table.Columns;

//true
bool tableHasTypeInformation = tablecolumns.Any(c => c.DataType.Count() > 0);

I'm starting to think this is a limitation of the DAC model itself.

Answer 1

oooh great topic :)

The easiest way to query using the DacFxStronglyTypedModel object which is available:

https://github.com/Microsoft/DACExtensions

It is a bit odd in that it is a sample which you build and then it gives you easy access to query the DacFx:

https://github.com/Microsoft/DACExtensions/tree/master/DacFxStronglyTypedModel

To get a strongly typed model do:

var model = new TSqlTypedModel("dacpacPath");

Then when you query it for all the views (or whatever) you get a list of typed objects which are a lot "saner" than the DacFx.

The interface that you get back for views:

ISql120TSqlView (change the version number to your version number) has an IEnumerable of Columns:

IEnumerable<Microsoft.SqlServer.Dac.Extensions.Prototype.ISql120TSqlColumn> Columns 
{
    get;
}

The column interface then has an IEnumerable of DataTypes:

IEnumerable<Microsoft.SqlServer.Dac.Extensions.Prototype.ISqlDataType> DataType 
{
    get;
}

I haven't got a windows machine right now to get you a full demo but that should be enough, if you dont' get what you need put a comment and i'll get a sample tomorrow (if no one else does in the meantime).

To get the list of columns on a view do:

var views = model.GetObjects<TSqlView>(DacQueryScopes.UserDefined);
foreach (var v in views)
{
    Console.WriteLine(v.Columns.Count());
}

This works for me with the 130 version of the Dac dll's.

To get to the details for computed columns you need to view the "ExpressionDependencies" on the column (see v.Columns) which is the same as for tables.

---

EDIT

So I have been having a play and there are some things that you just can't determine until runtime so the DacFx won't be able to figure out the type for those and they only way I know of it actually generating the record set and examining what you get back but there is some things we can do with computed columns, if we take this example:

create table [dbo].[the_table]
(
    [Id] INT not null primary key,
    [StringCol] varchar(234) not null,
    [a] int,
    [b] decimal,
    [a_and_b] as [a] + [b]
)

for columns, Id, StringCol, a and b when we use the strongly typed dacfx we can get the column types by doing this:

var tables = model.GetObjects(DacQueryScopes.UserDefined); 
foreach (var t in tables) 
{ 
    switch (c.ColumnType) 
    { 
        case ColumnType.Column: 
            ShowType(c.DataType); 
            break;
    }
}

ShowType looks like this:

void ShowType(IEnumerable<ISqlDataType> types)
{
    var builder = new StringBuilder();

    foreach (var type in types)
    {
        var t = new TSqlDataType(type.Element);
        builder.Append($"{t.SqlDataType.ToString()} ");
    }

    Console.Write(builder);
}

What we do is have a list of data types for each column, it might just be int or something like that but it is a list.

Now because we have a computed column, instead of just the data type(s) we have references to the underlying columns which we can get the data type(s) from:

void ShowDependencies(IEnumerable<ISqlModelElementReference> dependencies)
{
    foreach (var dependency in dependencies)
    {
        if (dependency is TSqlColumnReference)
        {
            var column = new TSqlColumn(dependency.Element);
            Console.Write(column.Name + " ");
            ShowType(column.DataType);
        }
    }
}

to know when to call this version:

var tables = model.GetObjects<TSqlTable>(DacQueryScopes.UserDefined);

foreach (var t in tables)
{
    Console.WriteLine($"table - {t.Name}");
    foreach (var c in t.Columns)
    {
        Console.Write("\r\n" + c.Name.ToString() + " ");
        switch (c.ColumnType)
        {
            case ColumnType.Column:
                ShowType(c.DataType);
                break;
            case ColumnType.ComputedColumn:
                Console.Write($"({c.Expression}) ");
                ShowDependencies(c.ExpressionDependencies);
                break;

for the sample table about we get this output:

table - [dbo].[the_table]

[dbo].[the_table].[Id] Int
[dbo].[the_table].[StringCol] VarChar
[dbo].[the_table].[a] Int
[dbo].[the_table].[b] Decimal
[dbo].[the_table].[a_and_b] ([a] + [b]) [dbo].[the_table].[a] Int [dbo].[the_table].[b] Decimal view - [dbo].[mutli_type]

we would then need to decide what the type is, as a guess sql will do an implicit cast as runtime to a decimal but at compile time I don't think it is known (happy to be corrected here!)

If we then take a view as an example:

create view the_view
as
select
    *,
    object_name(4) some_name,
    123 as an_int
from
    the_table

we have the columns from the base table which can be enumerated simply but the object_name and 123 are slightly harder, using the same code above but for views we get:

[dbo].[the_view].[Id] [dbo].[the_table].[Id] Int
[dbo].[the_view].[StringCol] [dbo].[the_table].[StringCol] VarChar
[dbo].[the_view].[a] [dbo].[the_table].[a] Int
[dbo].[the_view].[b] [dbo].[the_table].[b] Decimal
[dbo].[the_view].[a_and_b] [dbo].[the_table].[a_and_b]
[dbo].[the_view].[some_name] some_name = an_int =
[dbo].[the_view].[an_int] some_name = an_int =

So no type for the computed columns plus to get to the value for a_and_b we would need to further enumerate again to get the types we had above.

At this point what you have is a view with columns that point to functions / expressions and this is where is starts to get harder :) if you take the example above you could probably work out what object_name returned and determine that but when you get a view that is non-deterministic on either the data or data type what do you do?

If we take:

create view mutli_type
as
    select case datepart(day, getdate()) 
        when 1
            then 100
        when 2 
            then 'hello'
        else
            getdate()
        end as multitype

depending on the day we get a different data type returned - double ouch.

If you really needed to know what the view returned you could get the select elements in the view and use the TSqlScript Dom to parse them into parts and try and infer each one, I have mocked up a sample that finds the getdate() function in this view to give you an idea of what you need to do but it isn't straightforward and I don't even want to consider stored procedures where you can pass in dynamic sql:

---

Full sample:

create table [dbo].[the_table]
(
    [Id] INT not null primary key,
    [StringCol] varchar(234) not null,
    [a] int,
    [b] decimal,
    [a_and_b] as [a] + [b]
)
go
create view the_view
as
select *, object_name(4) some_name, 123 as an_int from the_table
go
create view mutli_type
as
    select case datepart(day, getdate()) 
        when 1
            then 100
        when 2 
            then 'hello'
        else
            getdate()
        end as multitype
go

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Dac.Extensions.Prototype;
using Microsoft.SqlServer.Dac.Model;
using Microsoft.SqlServer.TransactSql.ScriptDom;
using ColumnType = Microsoft.SqlServer.Dac.Model.ColumnType;

namespace ConsoleApplication1
{
    class Program
    {
        static void ShowType(IEnumerable<ISqlDataType> types)
        {
            var builder = new StringBuilder();

            foreach (var type in types)
            {
                var t = new TSqlDataType(type.Element);
                builder.Append($"{t.SqlDataType.ToString()} ");
            }

            Console.Write(builder);
        }

        static void ShowDependencies(IEnumerable<ISqlModelElementReference> dependencies)
        {
            foreach (var dependency in dependencies)
            {
                if (dependency is TSqlColumnReference)
                {
                    var column = new TSqlColumn(dependency.Element);
                    Console.Write(column.Name + " ");
                    ShowType(column.DataType);
                }
            }
        }
        
        static void Main(string[] args)
        {

            var model = new TSqlTypedModel(@"path\Da.dacpac");

            var views = model.GetObjects<TSqlView>(DacQueryScopes.UserDefined);

            var tables = model.GetObjects<TSqlTable>(DacQueryScopes.UserDefined);

            foreach (var t in tables)
            {
                Console.WriteLine($"table - {t.Name}");
                foreach (var c in t.Columns)
                {
                    Console.Write("\r\n" + c.Name.ToString() + " ");
                    switch (c.ColumnType)
                    {
                        case ColumnType.Column:
                            ShowType(c.DataType);
                            break;
                        case ColumnType.ComputedColumn:
                            Console.Write($"({c.Expression}) ");
                            ShowDependencies(c.ExpressionDependencies);
                            break;
                        case ColumnType.ColumnSet:
                            break;
                        default:
                            throw new ArgumentOutOfRangeException();
                    }
                }
            }


            foreach (var v in views)
            {
                Console.WriteLine($"view - {v.Name}");

                foreach (var c in v.Columns)
                {
                    Console.Write("\r\n" + c.Name.ToString() + " ");

                    var needDomParse = false;

                    switch (c.ColumnType)
                    {
                        case ColumnType.Column:

                            ShowType(c.DataType);
                            ShowDependencies(c.ExpressionDependencies);
                            
                            break;
                        case ColumnType.ComputedColumn:

                            ShowType(c.DataType);
                            ShowDependencies(c.ExpressionDependencies);

                            if (!c.DataType.Any() && !c.ExpressionDependencies.Any())
                            {
                                needDomParse = true;
                            }

                            break;
                        case ColumnType.ColumnSet:
                            break;
                        default:
                            throw new ArgumentOutOfRangeException();
                    }

                    if (needDomParse)
                    {
                        //ouch
                        
                        var create = new CreateViewStatement();
                        var parser = new TSql130Parser(false);
                        IList<ParseError> errors;
                        var fragment = parser.Parse(new StringReader(v.GetScript()), out errors);
                        var selectVisitor = new SelectVisitor();
                        fragment.Accept(selectVisitor);

                        foreach (var s in selectVisitor.Selects)
                        {
                            var spec = s.QueryExpression as QuerySpecification;
                            foreach (var element in spec.SelectElements)
                            {
                                var select = element as SelectScalarExpression;
                                if (select != null)
                                {
                                    Console.Write(select.ColumnName.Value + " = ");
                                    var caseExpression = select.Expression as SimpleCaseExpression;
                                    if (caseExpression != null)
                                    {
                                        var func = caseExpression.ElseExpression as FunctionCall;
                                        Console.WriteLine(func.FunctionName.Value);
                                    }

                                }
                            }
                        }
                    }
                }
            }
        }
    }

    internal class SelectVisitor : TSqlConcreteFragmentVisitor
    {
        public List<SelectStatement> Selects = new List<SelectStatement>();
        public override void Visit(SelectStatement node)
        {
            Selects.Add(node);
            base.Visit(node);
        }
    }
}

I hope it helps, I know it isn't a just do this but hopefully explains some of the challenges :)

Ed