T-SQL Form Generator Template

Most of the time web developer spends creating some kind of forms. This is why almost any object-relational mapper product (EntitySpaces, Subsonic, LINQ to SQL, NHibernate) has some kind of scaffolding generators. Generated pages are might be good for admin side, but for front-end those pages needed to be created manually. 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 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 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 above parameters. Switch to "Result to Text" mode. Run and your form HTML is ready. Just copy paste it in your aspx file.

This is a good start. But forms are rarely consist of just TextBox-es, so lets 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 your. Feel free to modify template according to your needs.


kick it on DotNetKicks.com

Posted on Monday, January 25, 2010 by | Comments (1) | Add Comment

Comments

Gravatar

Re:T-SQL Form Generator Template

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

Posted on 10/11/2011 1:20:11 AM by Maresolaris #

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Valid HTML5