Tuesday, 5 February 2008

Rehash: Dave's simple SQL Workbench/Query Analyzer Code Generator

This is a perenially handy tool for generating database-oriented code using the most basic of tools: SQL 2005 Workbench. It makes use of system tables or the information_schema and is a very basic (but very useful) tool for code generation - especially when you have hundreds of tables.

Here is one I just made today to avoid having to create a CodeSmith template (which we don't have licences for) or doing the monkey work of manually typing in these attributes. Just set the output mode in isqlw/SQL Workbench to text and generate away! (minus the chimps..)


SELECT
'[EntityPropertyMapping("' + column_name + '")]' + CHAR(10) +
'public ' + CASE data_type
WHEN 'nvarchar' THEN 'string'
WHEN 'int' THEN 'int'
WHEN 'datetime' THEN 'DateTime'
END
+ ' ' + column_name + ' { get; set; }' + CHAR(10) + CHAR(10)
FROM information_schema.columns
WHERE table_name = 'Asset'

Output is:


[EntityPropertyMapping("AssetName")]
public string AssetName { get; set; }

[EntityPropertyMapping("Description")]
public string Description { get; set; }


[EntityPropertyMapping("Comments")]
public string Comments { get; set; }

No comments: