Using T-SQL to generate ASP.NET code - 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).

Published Thursday, December 11, 2003 11:30 PM by Jon Galloway
Filed under:

Comments

# re: Using T-SQL to generate ASP.NET code

Looks good! How would it be done using just a CodeSmith template? Specifically, I want to do this, but I don't think you can.

<%@ Property Name="ControlID" Type="System.String" Default="" Category="Options" Description="The ID of the form control." %>

<asp:TextBox ID="ControlID" Runat="server" /></td>

Monday, June 28, 2004 4:37 PM by Dan