I am working on an LPT template to generate a partial class for RetrievalProcedures and ActionProcedures.

Here is my progress so far.

Project currentProject = _executingGenerator.ProjectDefinition; 
SPCallDefinitionList dbProcedures =  currentProject.SPCalls;
    
// alternative for specific procedure types
// var retrievalProcs = _executingGenerator.RetrievalProcedures;
    
//walk the procedures 
foreach(SPCallDefinition spCall in dbProcedures)
{
    // get the procedure parameters
   IEnumerable<SPCallParameterDefinition> parameters = GeneratorUtils.GetStoredProcedureParametersToWalk(false, false, spCall, _executingGenerator);
    
    //do stuff here with the procedure and the parameters
}

I am looking to get several properties that seem to me like they should be in SPCallDefinition. They are:

• Number of Resultsets

• The mapped database name for the procedure (both partial and fully qualified, i.e. pr_SalesReport or Northwind.dbo.pr_SalesReport)

Where can I get those values?

I think you can use th following TDL tokens.

Number of Resultsets

<[ProcedureOutputType]> Will be replaced by the DataSet if the current stored procedure has more than 1 resultset, otherwise DataTable. Used in SqlServer templates, because SqlServer stored procedures can return multiple resultsets without cursors. Not used in Oracle templates for that reason. Used inside currentSPCall affecting loops.

So if <[ProcedureOutputType]> is DataTable then the ResultsetsNumber is 1 else it's more than 1.

The mapped database name for the procedure (both partial and fully qualified, i.e. pr_SalesReport or Northwind.dbo.pr_SalesReport)

Northwind.dbo.pr_SalesReport = [<[SourceCatalogName]>].[<[SourceSchemaName]>].[<[ActualStoredProcedureName]>] pr_SalesReport = <[ActualStoredProcedureName]>

I am using LPT not TDL. How can I get that in an LPT template?

jovball wrote:

I am working on an LPT template to generate a partial class for RetrievalProcedures and ActionProcedures.

Here is my progress so far.

Project currentProject = _executingGenerator.ProjectDefinition; 
SPCallDefinitionList dbProcedures =  currentProject.SPCalls;
    
// alternative for specific procedure types
// var retrievalProcs = _executingGenerator.RetrievalProcedures;
    
//walk the procedures 
foreach(SPCallDefinition spCall in dbProcedures)
{
    // get the procedure parameters
   IEnumerable<SPCallParameterDefinition> parameters = GeneratorUtils.GetStoredProcedureParametersToWalk(false, false, spCall, _executingGenerator);
    
    //do stuff here with the procedure and the parameters
}

I am looking to get several properties that seem to me like they should be in SPCallDefinition. They are:

• Number of Resultsets

• The mapped database name for the procedure (both partial and fully qualified, i.e. pr_SalesReport or Northwind.dbo.pr_SalesReport)

Where can I get those values?

NumberOfResultsets isn't part of the spcalldefinition, as the SPCallDefinition is just that, a call definition. Which proc is called and thus which resultsets are returned are stored in the target of the SPCallDefinition in its mapping.

So you have to obtain the mapping for the SPCallDefinition. This is easy:

SPCallMapping mapping = currentProject.GetGroupableModelElementMapping(spCall, _executingGenerator.DriverID) as SPCallMapping;
if(mapping!=null)
{
    var targetProc = mapping.MappedTarget as DBStoredProcedure;
    if(targetProc!=null)
    {
           var numberOfResultsets = targetProc.NumberOfResultsets;
    }
}

The db name is also obtainable from this, as targetProc holds a reference to the DBSchema it is in which holds a reference to the DBCatalog the schema is in.

Please see the Linq to Sql context template and the EF context include template for more examples, as we generate code for stored procs there using .lpt templates.

That was exactly what I needed. Thanks for the help.