Code that Writes Code (or TSQL that writes ASP.NET)

Code that Writes Code (or TSQL that writes ASP.NET)

September 18, 2007 | SQL Server

Call it a code generator, software factory, or just a clever script. If you can write code that writes code – you win, even if just a small victory for humans in this contest we call software development.

For example, I’ve been working on an ASP.NET application which contains many data entry screens. You know the kind: very simple, table-format with a label and a textbox of a certain width, that may or may not require some validation. In other words, a whole lot of markup like this:

<tr>

  <td class=”EditLabel”>Number</td>

  <td class=”Edit”>

    <asp:TextBox ID=”txtNumber” runat=”Server” Width=”200px” MaxLength=”20″></asp:TextBox>

  </td>

</tr>

 

Now, if you have to type the above more than once or twice, you will go insane (been there, gone there). More importantly, you will probably introduce a bug or two. So, I opened up SQL Server 2005 Management Studio and wrote the following T-SQL code:

USE SomeDB

GO

 

DECLARE @Table  varchar(128)

DECLARE @Column varchar(128)

DECLARE @Width  varchar(10)

DECLARE @Length int

DECLARE @Type   int

 

SET @Table = ‘Employer’ — Pass this as a parameter

 

DECLARE ColumnCursor CURSOR FOR

   SELECT C.Name, C.Max_Length, C.User_Type_ID FROM Sys.Columns C

   INNER JOIN Sys.Tables T ON C.Object_ID = T.Object_ID

   WHERE T.Name = @Table

   ORDER BY Column_ID

 

OPEN ColumnCursor

 

FETCH NEXT FROM ColumnCursor INTO @Column, @Length, @Type

WHILE @@FETCH_STATUS = 0

BEGIN

  IF @Type <> 36 — No GUIDs

  BEGIN

    IF @Length < 0 SET @Length = 100

    IF @Length < 10

      SET @Width = ’50px’

    ELSE IF @Length < 20

      SET @Width = ‘100px’2013-08-28 18:34:09’

    ELSE IF @Length < 50

      SET @Width = ‘200px’

    ELSE IF @Length < 100

      SET @Width = ‘300px’

    ELSE

      SET @Width = ‘400px’

 

    PRINT ‘<tr>’

    PRINT ‘  <td class=”EditLabel”>’ + @Column + ‘</td>’

    PRINT ‘  <td class=”Edit”>’

    PRINT ‘    <asp:TextBox ID=”txt’ + @Column + ‘” runat=”Server” Width=”‘ + @Width + ‘” MaxLength=”‘ + CONVERT(varchar(10),@Length) + ‘”></asp:TextBox>’

    PRINT ‘  </td>’

    PRINT ‘</tr>’

  END

  FETCH NEXT FROM ColumnCursor INTO @Column, @Length, @Type

END

 

CLOSE ColumnCursor

DEALLOCATE ColumnCursor

 

You get the picture. Feel free to customize this code to introduce additional formatting, a slick UI, or other business rules to the mix.