THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Richard Hundhausen: The DBAgilist

This is a mirror of Richard Hundhausen's (aka The DBAgilist) blog "Tales from the Doghouse."

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

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'

    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.

Published Tuesday, September 18, 2007 5:02 PM by Tales from the Doghouse - SQLblog
Filed under: ,

Comments

 

Gordon Rayburn said:

This is a snippet of code that builds jobs on a server automatically via dynamic sql calls to sp_add_job & it's supporting procedures.  The code determines both the OperatorEmailAddr to send to, and inserts the @@SERVERNAME value into the subject and file attachment.  I use this as a means of daily systems reporting on about 65 servers at the moment in order to easily deploy system reports to new servers.  What used to take about a day to configure a server, now takes about an hour.  :)

SET @DynJob = '

master..xp_sendmail @recipients = ''' + @OperatorEmailAddr + '''

, @subject = ''' + @@SERVERNAME + ' errorlog report.''

, @query = ''usp_Check_Error_Logs''

, @width = 1000

, @dbuse = ''Admin''

, @attachments = ''' + @@SERVERNAME + '_ErrorLog_Report.txt''

, @attach_results = ''TRUE''

'

Builds a job that executes the following:

master..xp_sendmail @recipients = '24x7dba@mycorp.com'

, @subject = 'ProdServer_01 errorlog report.'

, @query = 'usp_Check_Error_Logs'

, @width = 1000

, @dbuse = 'Admin'

, @attachments = 'ProdServer_01_ErrorLog_Report.txt'

, @attach_results = 'TRUE'

September 21, 2007 1:56 PM
 

Alex Kuznetsov said:

Hi Richard,

On one hand, I use T-SQL to generate code myself all the time. On the other hand, I sometimes feel guilty about it, because C# is a much better language for string manipulation. What do you think?

September 22, 2007 4:00 PM
 

.NETer said:

Where do you expect this would get used?

September 24, 2007 1:26 PM
 

Roy Tate said:

I am working on a code generator in c# and .Net 2.0 based on the same ideas as you describe.  It uses a SQL database schema to generate objects and some UI elements.  Your blog post inspired me to add a basic [ASP edit form] template.  The new template and updated code are not in the downloads section yet, but you can get the latest from subversion.

October 2, 2007 2:13 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement