Querying the DacFx API – Getting Column Type Information

If you are using the DacFx api, it is easy to get a table, you just create a TSqlModel object with your model in and call GetObject or GetObjects and you can get to your TSqlObject table, then to get to the list of columns there are a couple of ways you can do it, you can call the table’s GetChildren which returns all the children on the table and in amongst that lot is one or more Columns:

foreach (var child in table.GetChildren())
{
Console.WriteLine(child.Name + ” : ” + child.ObjectType.Name);
}

This will print out a list of all the children on the table, the Name property is the column name and the ObjectType is the type of object, we want columns so we need to check that ObjectType is the same as ModelSchema.Column. As an aside, the ModelSchema class is very useful as it is a way to query for specific types or to know what type of TSqlObject it is you have. If you need to find out how something works, go to the ModelSchema to get started!

The second way is to query the API using GetReferencedRelationshipInstances which lets you specify the type of objects that you want, in this case we just want the Columns relationships and because we have a Table object, we use the Table.Columns ModelRelationshipClass which returns all the Columns that this table directly references:

foreach (var child in table.GetReferencedRelationshipInstances(Table.Columns))

When we query for relationships, we get a list of relationships (who would have guessed), a relationship looks something like this:

FromObject – this is the table we asked for the references columns from

Object – this is the TSqlObject for the column

ObjectName – the name of the column

Relationship – describes how they are related, in this case it is actually the Table.Columns class (get used to seeing things go round and round in circles with this api!)

Now we have the columns we can query each one to find the actual data type, to do this we query the Column for it’s referenced objects, this time not a related object like a column to a table but just something that is referenced by the column, so we use Column.DataType which gets us an IEnumerable list of the 1 datatype that the column is:

var types = child.Object.GetReferenced(Column.DataType)

The Name property of the type gives the column type such as varchar or tinyint and then the object has some properties you can query to get whether the column is nullable or a max datatype and the length, scale and precision:

var type = child.Object.GetReferenced(Column.DataType).FirstOrDefault();
var isNullable = type.GetProperty<bool?> (DataType.UddtNullable);
var length = type.GetProperty<int?>(DataType.UddtLength);

I guess the important thing to know here is that you should always know what type of TSqlObject you have and as long as you query the API using the GetReferenced* and GetProperty methods and you use the ModelSchema class to get the top level objects, it is easy to get to the properties you need, so in this example using ModelSchema.Table, then Table.Columns then Column.DataType and finally DataType.XX you can easily get the column type.

In my example I enumerate all the objects, it can be simplified (I am not sure I mean simplified!) using linq and something like:

var dataType = table.First()
.GetReferencedRelationshipInstances(Table.Columns)
.First(p => p.ObjectName.ToString() == “[dbo].[AWBuildVersion].[SystemInformationID]”)
.Object.GetReferenced(Column.DataType)
.First()
.Name;

(In case you recognise the table – Yes I do use a dacpac for AdventureWorks for testing!)

As always there is a demo project in github https://github.com/GoEddie/DacFxApi – this example is the ShowTableColumns example.

 

Enjoy!

Advertisements

Dacpac Explorer

I have been playing around with the DacFx Api alot recently and am finishing  off the week with a final demo, the dacpac explorer:

https://github.com/GoEddie/DacpacExplorer/raw/master/download/DacPacExplorer.zip

Github repo is here

What this does is open a dacpac file and show an explorer like interface to let you see what is inside the dacpac without having to manually extract it and then go searching for what you want, I will write a further blog on how it uses the DacFx api but I have tried where possible to use the model to make displaying objects as easy as possible so for example to show all the properties of an object I use:

foreach (var property in item.ObjectType.Properties.OrderBy(p => p.Name))
{
var val = property.GetValue<object>(item);
if (val == null)
{
val = “NULL”;
}

Console.WriteLine(“Property Name: {0} Value: {1}”, property.Name, val.ToString());

}

item in this case is of course our good olf friend TSqlObject.

One extra thing that the Dacpac Explorer does is to give you the option to validate the model when it opens it, this will then give you a list of any warnings or errors (of course if there are no errors then it let’s you know that!), to use the demo app you just choose the dacpac file and choose open, if you want to validate the model then tick the checkbox:

browse

When you then open the dacpac you get a nice (bear in mind I am a developer and data guy and not a designer! ) explorer like interface for viewing the dacpac contents, I have used the adventure works schema as an example:

explore

 

 

This is just a small example of what we can do with the DacFx API, I will write some blogs about how it all works as there are some things that weren’t immediately obvious to me how to do things like getting a columns data type and the columns that belong to an index, but once you see a simple example it is pretty straightforward.

 

Have you used the DacFx Api to write a tool around Sql Server? Share it! Have a request? Share it!

 

Counting logical lines of T-Sql code in a DacPac

There are two general ways of counting lines of code, the first is basically just count the lines in each file that make up a project, the second is to count the logical statements as specified by the language.

Counting lines of T-SQL code is typically quite hard as, personally, I am interested in things like stored procedures and functions and their statements as a metric for lines of code rather than including columns and tables as, for me, they are separate.
I thought this would be a great example of how to use the DacFx API and the T-Sql script dom together to create a tool that I, and hopefully the other person who has visited this blog, would find useful!

What this tool does is count the logical lines of code in any stored procedure, function (scalar or table-valued) and dml triggers, if anyone thinks of other items of code then it is open source so feel free to check it out, add it in and submit a pull request, it is really trivial to add other types of code.

To count the number of statements, I use the T-Sql visitor class to break the each code file into statements.

Feel free to download the tool from:

https://raw.githubusercontent.com/GoEddie/DacFxApi/master/Download/CountLinesOfSqlCodeInDacPac.zip

The source code is in my DacFx Api github repro:

https://github.com/GoEddie/DacFxApi

How does this work?

The first step is to open the dacpac file:

var model = new TSqlModel(fileName, DacSchemaModelStorageType.File);

I then need to query the model for the Objects of the specific type I am looking for:

model.GetObjects(DacQueryScopes.Default, Procedure.TypeClass)
model.GetObjects(DacQueryScopes.Default, ScalarFunction.TypeClass)

Each of these return an IEnumerable<TSqlObject> which I pass to a helper function to query for the name and the script:

Name = codeUnit.Name.ToString()
var script = “”;
if (codeUnit.TryGetScript(out script))

return script;

Once I have the names and contents of all the scripts I am interested in, I need to parse the scripts and break them into statements. This is made really simple using the TSqlFragmentVisitor class.

The way the TSqlFragmentVisitor class works is that you create a class that inherits from it:

public class SqlVisitor : TSqlFragmentVisitor
{
}

There are then a set of methods you can override which are called everytime a statement of that type is encountered, so for example if you use:

public override void Visit(TSqlStatement node)
{
}

This will be called for every statement, it is what I use in this tool as I don’t care what type of statement it is, as long as it is a statement.

There are other override-able methods such as:

public override void ExplicitVisit(SelectStatement node)

This is only called for select statements so it really is powerful and so simple to use!

Once I have the statement count, I tally up the lines of code for all code units and I end up with the total lines of logical code for just code units in a dacpac file!

This is obviously really basic, if you have an example where the lines of code is incorrect, send it to me and I will fix the tool or fix it yourself and send a pull request on github!

Happy DaxFx + TransactSql ScriptDom’ing!

 

p.s. to see how many lines in each code unit, use the /detailed switch!