开发者

T4 Templates : Reading resulting columns of a stored procedure table

I am learning T4 templates right now, and all examples I go开发者_开发问答t on internet is about using the tables for code generation. I want to use stored procedure result columns to generate automated UI, is it possible? OR I have to create view for same query? in that case, how to read from view?

Thanks in advance.


I got the solution and here is how you can generate a rad grid directly from the sp name


<#
'requires: <#@ assembly name="System.Data" #>
  dim Server as new Server(".\sqlexpress")
    dim database as new Database(server, "xxxx")
    dim strSpName as String= "sp_xxxx"
    Dim dt as System.Data.DataTable= database.ExecuteWithResults("exec sp_GetEquipment").Tables(0)
    dim ctlName as String = "grdEqp"
#>
<telerik:RadGrid ID="grd" runat="server" Skin="Web20" AutoGenerateColumns="false">
<MasterTableView>
<Columns>

<#
    For Each column As System.Data.DataColumn In dt.Columns
#><telerik:GridBoundColumn DataField="<#=column.ColumnName #>" HeaderText="<#=column.ColumnName  #>"/>
<#Next#>
</Columns>
                </MasterTableView>
</telerik:RadGrid>


If you don't actually want to execute the stored procedure as various stored procedures have a number of different parameters passed then you could use the sp_describe_first_result_set system stored procedure to return the columns of the result set assuming there is just one.

    /// <summary>
/// Returns table for which stored procedures need to be generated.
/// </summary>

string TableName = "usp_getNominalCode";

string SchemaName = "Financial";

DataTable DataTable
{
    get
    {
        if (_table == null)
        {
            Server server = new Server(new ServerConnection(new SqlConnection(this.ConnectionString)));
            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(this.ConnectionString);
            Database database = new Database(server, connectionStringBuilder.InitialCatalog);
            DataSet storedProcedureColumns = database.ExecuteWithResults("sp_describe_first_result_set @tsql= " + "'[" + SchemaName + "]" + ".[" + TableName + "]'");
            _table = storedProcedureColumns.Tables[0];
        }
        return _table;
    }
}
DataTable _table;

You can then query this table for it's structure like the other answer but it'll be a little more generic

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜