The stored in sample are using a SQL 2000 compatible syntax , with a more recent SQL is better to use TRY…CATCH.

If you have a table with many fields the activity of fields list writing and data type verification in SQL Management Studio (for the part in OpenXML) could be tedious, prone to errors and time consuming.

I’m still using an ancient 2.6 free version , at this moment i don’t feel the need for a more recent version ; but i should try, because the 2.6 version uses the .NET 1.1 framework (!) which could be a nightmare to install on a Windows 7: on my pc after the 1.1 installation it was necessary to reinstall the 4.0 and now is all ok, but i can’t assure that on every pc the things goes well as on mine.

There is another free code generator, MyGeneration, but i never tried this software.

CodeSmith uses an approach similar to ASP.NET pages, you write a template with a server code / code behind approach near identical to ASP.NET, and the result is generated code; instead of aspx pages here we have .cst files.

Here the environment:

CodeSmith provides many useful samples (i show opened the interesting part for DatabaseSchema) , and as in the Code Snippet Manager in Visual Studio you can hook your template directory (for example CodeSmithFolder in image); the Cst files are edited with a SciLite Scintilla Editor.

Very interesting is the native functionality , with specific objects, for database connectivity.

Years ago i wrote a stored generator in Vb6 but for some strange reason the exe does not work in Windows 7, so i wrote this GenXmlStoredFields.cst :

The code is not the better one that could be written, but i think is a good start point.

For example the test <%if (i < SourceTable.Columns.Count – 1) {%>,<%} %> works correctly only if , in our sample, the excluded fields are not the last field in structure, otherwise there will be a not needed comma : but is just a template .

By right clicking the file you can execute the template

In code we have defined a SchemaExplorer.TableSchema parameter , so we can explore every reachable SQL Server and select a table:

And then launch by clicking Generate:

And the code is generated, ready for to be copied and pasted in SQL Management Studio.

The CodeSmith site is full of documentation, for example about the ColumnSchema used in my sample see this page