Accessing Command Results

 Hi All!

 I want to create a template to generate vb wrappers for stored procs in sql server db. I wanted to use CommandResult object to evaluate whether stored procedure is a query type or non query type procedure. But when I execute the template bellow it gives me "invalid column name  'column_name' " error. Im not sure if its a bug or my fault. Thought to ask the experts!

The tpl:

<%@ CodeTemplate Language="VB" TargetLanguage="Text" Description="Figure out the Schema Explorer object model." Debug="True" %>
<%@ Property Name="DB" Type="SchemaExplorer.DatabaseSchema" Category="Database" Description="Data Source that the object is based on." %>
<%@ Assembly Name="SchemaExplorer" %>

<%@ Import Namespace="SchemaExplorer" %>


<%
     Dim commands as new CommandSchemaCollection(DB.Commands)
     commands.Sort( new PropertyComparer("FullName"))
     For Each c as CommandSchema in commands
%>

<% For Each cr as CommandResultSchema in c.CommandResults %>
<% For Each crc as CommandResultColumnSchema in cr.Columns %>
--------------
<%= crc.Name %>
--------------
<% Next %>
<% Next %>
<% Next %>

Any ideas?

Thank You! 

Hello,

I have attached to this post a VB.Net command wrapper template written by Eric J. Smith and Shannon Davidson (CodeSmith), this should save you a bunch of time .

As to your "invalid column name ’column_name' " error, it’s not your fault.  This error can happen when CodeSmith is unable to compile a stored procedure.

Thanks
-Blake Niemyjski

Hello Blake

Thank you for your input!

So CodeSmith compiles the stored procedures and tries to generate the result set, by, I assume, feeding it dummy parameters and analysing the resultset.

And as long as the resultset is returned no error will be generated?

Nik 

Hello Nik,

I don’t know if Compile was the exact word I should have used. But in TSQL there are various ways to return information about tables, views, etc…  CodeSmith gathers this information from the SQL Provider located on your local machine (C:\Users\Administrator\Documents\CodeSmith\Samples\v4.1\Projects\SqlSchemaProvider this is my path, you will need to edit it to match yours.) The below code should give you an idea as to what the provider is doing.

For Example:

SELECT
    [TABLE_NAME] as N'Table Name',
    [COLUMN_NAME] as N'Column Name',
    [DATA_TYPE] as N'Data Type'
FROM
    [Information_Schema].[Columns]
WHERE [TABLE_NAME] IN
    (SELECT
        [TABLE_NAME]    
    FROM
        [Information_Schema].[Tables]
    WHERE
        [TABLE_TYPE] = N'Base Table')
ORDER BY
    [TABLE_NAME],
    [COLUMN_NAME],
    [DATA_TYPE]

Will return

Table Name              Column Name                  Data Type
aspnet_Applications    ApplicationId                      uniqueidentifier
aspnet_Applications    ApplicationName                nvarchar
aspnet_Applications    Description                        nvarchar
aspnet_Applications    LoweredApplicationName    nvarchar

You can get all the information about your databases with a few queries. CodeSmith has the SourceCommand and other Helper methods like GetParameterName that can get the name of a certain parameter in the parameter list, these use the provider.

You can take a look at how CodeSmith accomplishes this in the Command Wrapper template that I attached earlier in this thread. Just open the template and search for ('"Populate Parameters") It will give you a pretty good idea of how CodeSmith is able to retrieve information about a Stored Procedure.

Thanks

-Blake Niemyjski