KARPACH

WEB DEVELOPER BLOG

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.

Posted on January 25, 2010 by

Comments

Posted on 10/11/2011 01:20:11 AM by Maresolaris

Thank you very much for this! I was looking for something like this and your code really helps!