T-SQL Form Generator Template
Most of the time a web developer spends creating some kind of form. This is why almost any object-relational mapper product (EntitySpaces, Subsonic, LINQ to SQL, NHibernate) has some kind of scaffolding generator. Generated pages are might be good for the admin side, but for the front end, those pages need to be created manually. The front end usually has specific design requirements, so there you need to do this tedious task manually over and over again.
You can always use T4 templates as a code generator, but I prefer to use old solid T-SQL.
Microsoft SQL Server Management Studio has a lot of pre-build templates for you just press Ctrl+ALT+T to see them (View->Template Explorer). If you are not using them yet, check them out it might be really helpful. Hint: Ctrl+Shift+M brings parameter menu.
Let’s create a simple T-SQL template form generator for ASP.NET:
Declare @width varchar(3)
set @width = '<field_width,int,150>'
select
'<tr>
<td class="textfield">' + COLUMN_NAME + '</td>
<td class="valuefield"><asp:TextBox ID="txt' + COLUMN_NAME + '" runat="server" Width="' + @width +'"px"></asp:TextBox></td>
</tr>'
from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '<table_name,sysname,Table Name>'
<field_width,int,150> - width of input box template parameter
<table_name,sysname,Table Name> - table name template parameter
Press Ctrl+Shift+M. Set the above parameters. Switch to “Result to Text” mode. Run and your form HTML is ready. Just copy and paste it into your aspx file.
This is a good start. But forms rarely consist of just TextBox-es, so let’s modify this a little bit:
select
case
when c.CONSTRAINT_TYPE = 'PRIMARY KEY' then
'<tr>
<td class="textfield">' + cl.COLUMN_NAME + ':</td>
<td class="valuefield"><asp:Literal ID="lit' + cl.COLUMN_NAME + '" runat="server" /></td>
</tr>'
when DATA_TYPE = 'bit' then
'<tr>
<td class="textfield">' + cl.COLUMN_NAME + ':</td>
<td class="valuefield"><asp:CheckBox ID="chk' + cl.COLUMN_NAME + '" runat="server" /></td>
</tr>'
when DATA_TYPE = 'varchar' and CHARACTER_MAXIMUM_LENGTH = -1 then
'<tr>
<td class="textfield">' + cl.COLUMN_NAME + ':</td>
<td class="valuefield"><asp:TextBox ID="txt' + cl.COLUMN_NAME + '" TextMode="MultiLine" runat="server" Width="' + @width +'px"></asp:TextBox></td>
</tr>'
when DATA_TYPE = 'date' then
'<tr>
<td class="textfield">' + cl.COLUMN_NAME + ':</td>
<td class="valuefield"><dxe:ASPxDateEdit ID="txt' + cl.COLUMN_NAME + '" runat="server" Width="' + @width +'px"></dxe:ASPxDateEdit></td>
</tr>'
else
'<tr>
<td class="textfield">' + cl.COLUMN_NAME + ':</td>
<td class="valuefield"><asp:TextBox ID="txt' + cl.COLUMN_NAME + '" runat="server" Width="' + @width +'px"></asp:TextBox></td>
</tr>'
end
from
INFORMATION_SCHEMA.COLUMNS cl LEFT JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u on cl.COLUMN_NAME = u.COLUMN_NAME and cl.TABLE_NAME = u.TABLE_NAME LEFT JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS c on u.CONSTRAINT_NAME = c.CONSTRAINT_NAME AND c.TABLE_NAME = cl.TABLE_NAME
where cl.TABLE_NAME = '<table_name,sysname,Table Name>'
I hope this helps. I used some DevExpress controls in my code, which might not be suitable for you. Feel free to modify the template according to your needs.
