sql server functions

The DacFx Api is an api for querying the object model that both DacPac files and SSDT use, it is a giant leap forward in the available tooling for Sql Server and hopefully there will be a raft of great tools to make T-SQL development better.

I really enjoy writing c# code. I typically use visual studio with Resharper and add NUnit and Moq to my test projects and this setup makes developing and testing really simple and removes a lot of the boring repetition you sometimes get. I also love developing T-SQL but the development environment is nowhere near as smooth.

When I first started looking at the DacFx api, I was a little confused as to how it worked, even after reading the introductory blog (http://blogs.msdn.com/b/ssdt/archive/2013/12/23/dacfx-public-model-tutorial.aspx) and when I finally realised what the implications were of the painless sounding sentence “The public model API is loosely typed: the TSqlModel contains loosely typed TSqlObjects that represent all the elements in your schema.” my heart sunk a little as it felt very similar to every object being returned as a System.Object and then having to cast it to the correct type (you don’t by the way, you leave it as the TsqlObject).

What this means is that all objects in the model, a table, schema, procedure etc are all the same type, a TSqlObject. To get any information such as what columns belong to what table or index or whether the index is clustered or not you need to query the objects and ask for the information you want, in reality what you have with the DacFx Api is a queryable set of classes, you just have to know what you have and what you want and although it is a different paradigm to pretty much every API I have ever seen, it is fairly straight forward. I have put an example application which opens a dacpac file and dumps out a list of indexes which gives an overview of how to use the api to get the information you want.

To open a dacpac file and get a TSqlModel object which is the entry point to the API you just need to call:

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

When you have the model you can then query it for all the objects you would like, if you want all the indexes in the model just do:

model.GetObjects(DacQueryScopes.Default, Index.TypeClass)

GetObjects searches for and returns a list of specific object types, the query scope means all objects, you can limit it to just system objects or just user objects etc. Finally the TypeClass causes a list of just Indexes to be returned.

The way the TypeClass works is that every object such as a table, index, column etc has a TypeClass and also on the static class that has the TypeClass such as Index or Table there are a number of properties which allow you to access the actual properties you want. If this sounds a little strange, I think it is, but it is fairly straight-forward with an example! We will call the class that holds the TypeClass and other property classes the container class as that is what msdn seems to call them. If you retrieve a list of all the indexes and you have a TSqlObject that you got by using the Index.TypeClass (as above) then you can query the index for its properties. The Index container class has, amongst others, these properties:

Index.TypeClass

Index.AllowPageLocks

Index.Clustered

You can then use these properties to get the value you want from your index TSqlObject instance, so if you do:

foreach (TSqlObject index in model.GetObjects(DacQueryScopes.Default, Index.TypeClass ){

var isClustered = index.GetProperty<bool?>(Index.Clustered)

}

You will get a nullable bool saying whether the index is clustered or not. If you do not know the type of the object you want you can call the non-generic version index.GetProperty(Index.Clustered). The example at: https://github.com/GoEddie/DacFxApi/tree/master/DumpIndexProperties (repo: https://github.com/GoEddie/DacFxApi/) shows some extra information like how to query the relationships to get the columns and included columns that are on the index (in, on, used by??) and how to generate the T-SQL script to deploy the index.

I strongly believe that as Sql Server developers we have the ability to really help move our tooling forward and if you throw into the mix the TSql scripting stuff, the next few years are really going to be exciting times for us Sql devs!