Jon Galloway

Using T-SQL to generate ASP.NET code

No rocket science here, but this query has saved me a lot of time. I've got it saved as a tql file in “Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer\” so I can hit 'Alt+F N' and pick it from the templates.

Scenerio: I've generated data objects with CodeSmith that have a property for each table column. I still need to build edit screens that map textboxes (for the single edit case) or datagrid columns to the objects.

Enter the INFORMATION_SCHEMA view, which returns a row for each column in a given database. I switch query analyzer to text output mode (Ctrl-T) , run the queries, copy the html into the ASPX (HTML view), switch to Design view (so the IDE sees the new controls and throws references to them in the ASPX.CS). Then I pop into the code behind and paste the appropriate “fields to properties“ and “properties to fields“ code and I'm off... to correct the compilation errors. I'm not mapping the datatypes here, and I'm generating edit fields for ID fields that shouldn't be edited. But, it's a good start on an edit page.

declare @table varchar(100)

declare @database varchar(100)



set @table = 'PortfolioPerformance'

set @database = 'ResearchReports'



--**SINGLE ITEM EDIT**

--Generate single item edit textboxes

select 

'<TR>

   <TD>' + column_name + '</TD>

   <TD>

      <asp:TextBox id="txt' + column_name + '" runat="server"></asp:TextBox></TD>

</TR>'

from information_schema.columns 

where table_name = @table 

and table_catalog = @database



--Fill Text fields from class

select 

'txt' + column_name + '.Text = _' + @database + '.' + column_name + '.ToString();'

from information_schema.columns 

where table_name = @table

and table_catalog = @database



--Set class properties from text boxes

select 

'_' + @database + '.' + column_name + ' = decimal.Parse(txt' + column_name + '.Text);'

from information_schema.columns 

where table_name = @table

and table_catalog = @database



--**DATAGRID EDIT**

--Generate datagrid template column

select 

'<asp:TemplateColumn HeaderText="' + column_name + '">

   <ItemTemplate>

      <asp:Label runat="server" 

         Text=''''<%# DataBinder.Eval(Container, "DataItem.' + column_name + '") %>''''>

      </asp:Label>

   </ItemTemplate>

   <FooterTemplate>

      <nobr>

         <asp:TextBox id="txt' + column_name + 'Add" runat="server"></asp:TextBox>

         <asp:RequiredFieldValidator 

            EnableClientScript="False" 

            Enabled="False" 

            id="rv' + column_name + 'Add" 

            runat="server" 

            ControlToValidate="txt' + column_name + 'Add" 

            ErrorMessage="' + column_name + ' is required.">

            <nobr></nobr>

         </asp:RequiredFieldValidator>

      </nobr>

   </FooterTemplate>

   <EditItemTemplate>

      <asp:TextBox id="txt' + column_name + '" 

         runat="server" 

         Text=''''<%# DataBinder.Eval(Container, "DataItem.' + column_name + '") %>''''>

      </asp:TextBox>

      <asp:RequiredFieldValidator id="rv' + column_name + '" 

         runat="server" 

         ControlToValidate="txt' + column_name + '" 

         ErrorMessage="' + column_name + ' is required.">

         <nobr></nobr>

      </asp:RequiredFieldValidator>

   </EditItemTemplate>

</asp:TemplateColumn>'

from information_schema.columns 

where table_name = @table

and table_catalog = @database



--Set class properties from datagrid 

select

'_' + @table + '.' + column_name + ' = ((TextBox)e.Item.FindControl("txt' + column_name + '")).Text;'

from information_schema.columns 

where table_name = @table

and table_catalog = @database

Could this be done start to finish with a CodeSmith template? Sure. Um... that's not what I did (this one goes to eleven).