Using T4 Templates with GDR Database Projects

re: Using T4 Templates with GDR Database Projects

amselem — Thu, 22 Jan 2009 04:08:47 GMT

When accessing an ISqlProcedure element, I can enumerate all dependant columns (proc.BodyDependencies), but if I have two or more resultsets then I get a flat enumeration of both resultset 1 columns + resultset 2 columns.

How could I get the resultset index belonging to a given column?

re: Using T4 Templates with GDR Database Projects

Gokhan Caglar - MSFT — Thu, 22 Jan 2009 07:27:56 GMT

The relationships between elements are mostly meant for dependencies. So you can't get what you're asking consistently. For example, if the same column is referred to twice from a proc, we collapse, and you'll only have one column reference.

You might want to get the AST of the proc body and walk that instead. You'd have to walk the statement list until you find the select statement you're looking for.

re: Using T4 Templates with GDR Database Projects

psirr — Thu, 22 Jan 2009 19:55:52 GMT

The following I copied from an email by Liangxiao Zhu:

On the relationshipentry, we have a list of RelationshipEntrySource. From that you can get a position for each column, even if they are collapsed, you can still get the RelationshipEntrySource for each select column. But if you want to know what column belongs to which select statement, as Gokhan said, you need to walk the AST tree, at least get the position range of the statement and then get the select columns with position inside the range.

Following is the sample line of getting the relationship entry source. On it, you can get startoffset, length, script and IScriptFragment.

IList<RelationshipEntrySource> relationshipEntrySources = relatingElement.GetRelationshipEntrySources(entry);

re: Using T4 Templates with GDR Database Projects

amselem — Sun, 25 Jan 2009 05:08:16 GMT

Thanks a lot for your suggestions but I'm still struggling how to get the resulting schema of a procedure. I'd like to code gen a DAL from db project objects, but things get worse when analyzing multi-statement procs with calculated columns. Parsing the AST solves some cases but the solving the complicated ones looks like hard to me.

What I'm looking is some way to "SET FMTONLY ON" and then execute the proc to get its metadata, but since there is no more underlying DB I think that it won't be possible. Any other suggestions would be appreciated, thanks!

re: Using T4 Templates with GDR Database Projects

Gokhan Caglar - MSFT — Sun, 25 Jan 2009 22:20:45 GMT

For complex select statements, it may be hard for you to analyze the Ast yourself. You can try to create a view for these complex ones (Temp tables may be a problem still) and walk the columns of the view.