Using SSDT as source for T4 templates

Question

I have a SQL Server Data Tools (SSDT) project that has a number of stored procedures for which I would like to generate the C# code to call them using T4. Are there any existing examples for doing this?

So far, I can create a function per proc, but I'd really like to be able to tap into the meta data SSDT creates so I can get the parameters, data types, and return values from it rather than doing string parsing.

Answer 1

COOL WITH A CAPITAL C! (but don't tell anyone who uses an ORM!)

To get the datatypes etc make sure you grab the latest DacExtensions from the MS DacFx team:

https://github.com/Microsoft/DACExtensions

The new api (which incidentally is written using T4 templates) makes finding the info you need many many times simpler.

There should be enough information you need in this blog to get you going:

https://the.agilesql.club/Blogs/Ed-Elliott/DacFx-Create-tSQLt-Tests-From-A-Dacpac

The only difference is that you are creating C# and not T-SQL so you won't have to deal with the ScriptDom.

When you do this, please dump it on github it sounds like a really useful project.

Answer 2

To answer this question in the comments:

I can create methods with the correct parameters, but I'm struggling to find where the objects are in the model are that represent the content of a stored procedure. I need to know the columns returned by a SELECT statement in order to generate the return objects. Any ideas?

The referenced objects are provided by the TSqlProcedure.BodyDependencies relationship. That will return objects referenced in the stored proc body, but won't tell you how they are used. The relational model doesn't try to embed this info as it doesn't help in deployment, but you can get it by querying the SQLDOM AST for the procedure.

The AST is a syntax tree defining the actual structure of the Procedure statement, including the structure of the procedur body. What you need to do is:

• Create a Visitor that visits SelectStatement nodes (or their children)
• Find the column names used in the select
• Map these names to names of objects returned by TSqlProcedure.BodyDependencies. Now you have a rich object that can state the table the column is contained in, the column's data type, etc.
• Do whatever you need to based on this (for example define a return type with the correct properties matching the column data types?)

A few notes / resources:

• Ed's DacpacExplorer will help you view and understand the code.
• Dave Ballantyne just added SQLDOM support to DacpacExplorer. Not only will this help you see what statements you need to match in the visitor, you should also look at how they use loadAsScriptBackedModel (see this commit) to ensure you have the full AST for the procedure body. Without this you would just get the body as one SqlScript object which isn't much use to you.
• Further examples of the visitor pattern are Dave's TSqlSmells and the DacExtensions project
• Twitter is an easy way to contact Ed, Dave and me if you are blocked :-)