4

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.

adam0101
  • 29,096
  • 21
  • 96
  • 174

2 Answers2

5

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.

Ed Elliott
  • 6,666
  • 17
  • 32
  • This would be great to have out there - an auto-generator for Entity Framework Code First classes would be a great use case. – Kevin Cunnane Apr 09 '15 at 22:01
  • I often think of doing a ORM that only lets you call stored procedures and does this, one day maybe! – Ed Elliott Apr 09 '15 at 22:05
  • Thanks! This might have a lot of potential. 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? – adam0101 Apr 10 '15 at 16:14
  • On the plus side, @Kevin's idea of using this for Entity Framework classes should be pretty easy. I might throw something together and put that on GitHub. – adam0101 Apr 10 '15 at 16:15
  • @EdElliott, I added a [follow-up question here](http://stackoverflow.com/questions/29573096/how-to-get-selectstatement-from-procedure) regarding getting the `SelectStatement` objects. I tried repeating what Dave did for the DacpacExplorer, but I'm having some trouble. Would you mind taking a look? – adam0101 Apr 11 '15 at 04:02
  • @EdElliott, here's my first release on github if you want to check it out: https://github.com/aeslinger0/sqlsharpener – adam0101 Apr 21 '15 at 02:14
  • cool, I saw kevin posted it on twitter and had a little look earlier on the train - i'll take a closer look when i get a chance but it looks great, thanks for sharing! – Ed Elliott Apr 21 '15 at 08:35
  • @KevinCunnane, since it was your idea, I thought you'd like to know that I just released a version of SqlSharpener that can generate Entity Framework Code-First classes. I included a [T4 template](https://github.com/aeslinger0/sqlsharpener/blob/master/examples/SimpleExample/SimpleExample.DataLayer/EFCodeFirstExample.tt) in the example solution. Thanks for the great idea! – adam0101 May 26 '15 at 19:46
  • This is great Adam. I am asking our PM to check this out as it's very useful for scenarios where you want to use EF as your ORM, but to have Database Projects support to work around some of the limitations of code first development. – Kevin Cunnane May 26 '15 at 22:10
1

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 :-)
Community
  • 1
  • 1
Kevin Cunnane
  • 8,020
  • 1
  • 32
  • 35
  • OK, I'm getting closer. I now realize that I needed to add my tables to the model for them to show up as a BodyDependency for a procedure. – adam0101 Apr 10 '15 at 18:33
  • Your answer really helped point me in the right direction, but I can't seem to get any SelectStatement objects to show up in my Visitor. [I created a follow-up question here](http://stackoverflow.com/questions/29573096/how-to-get-selectstatement-from-procedure). Would you mind taking a look? – adam0101 Apr 11 '15 at 01:46