Overview:

The core of DeadSimpleDTO2 is a single sql batch script that will generate C# POCO DTO classes.

It is capable of identifying and properly rendering 1:M, M:1, M:N and 1:? relations as Lists and object references.

It was written specifically to be runnable with no function or sproc dependencies.

The script can be run stand-alone in a sql query tool, via ADO or most likely using the included T4 template for code generation in Visual Studio 2008.

Running it either way with default options will produce flat, unadorned property bag C# DTO classes for each user table and view in a database.

Specifying one or more optional parameters can result in a finely tuned, robust and feature rich library of DTO classes that can be used as is or extended via partial classes for use in an ORM scenario or by the simple reflective loader that is included.

The sample source code includes a test solution with a large variety of usage examples with accompanying class diagrams.

Links to very short screen casts demostrating various use cases and quick starts can be found at the end of this file as well.

Background:

What started out as a simple sql script to generate some dto classes with fields for, ironically enough, a sql server schema discovery tool, gradually grew to what is now DeadSimpleDTO2.

Having been LLBLGen, EF and L2S bound for so long, once I started writing some less than trivial SQL it felt good to be writing some procedural code again and I just kept going. It seems to be a worthwile endeavor if only to give me something interesting to
brush up my sql chops on.

The best case scenario is that perhaps some may find it useful and someone who IS a badass SQL developer takes a minor interest in the script and offers some input.

Getting Started:

QUICK START FOR THOSE IN THE KNOW AND THOSE THAT THINK THEY ARE.. ;-)

The basic process is as follows

1) Add a copy of DeadSimpleDTO2.ttinclude and DeadSimpleDTO2.sql to the root of
a project. (see known issues and test project regarding subdirectories and
using relative paths)

2) Add an .tt file from an item template if you have a T4 editor installed or
simply add a text file and specify .tt as the extension.

3) Add an include tag, open a script tag and set up the parameters for
rendering. Basic example follows.

<#@include file="DeadSimpleDTO2.ttinclude" #>
<#
var p = new DeadSimpleParameters();
p.connectionString = "a connection string";
// or get it from the config file along with any other params you
// would like to set there.
p.Load(this);
// set some parameters in code, if desired
p.renderSeparateFiles = true;
p.rootNamespace="MyDTONamespace";
// instantiate a renderer with this and the parameters
var renderer = new DeadSimpleRenderer(this,p);
// by default the include looks for the script in the same directory, if
// we move it we have path it ourselves
// renderer.ScriptPath =System.IO.Path.GetDirectoryName(this.Host.TemplateFile) + @"\DSDTO\DeadSimpleDTO2.sql";
// render the code
renderer.Render();
#>

4) enjoy your generated classes.

5) Optionally RTFM.

TFM:

All parameters, with the exception of connectionString and renderSeparateFiles, are used by the script either in batch mode in a query tool or via a parameter value object used by the included T4 template harness.

The deployed script has it's parameters and default values commented out to allow calling via ado by the included T4 template.

To run the script in batch mode, simply uncomment the declarations and default assignments, make desired changes to the assignments, set the tool's scope to the target database or add a USE directive and run the script. The code will be rendered via print
statements.

The included T4 template include provides rich code generation facilities in the context of any Visual Studio 2008 C# project/web site. It is likely the template is also compatible with VS 2005 and 2010 but this has not been tested.

A basic understanding of behaviour of T4 templates may help you understand how the template works but is not necessary to use the templates. The 'getting started' project contains simple examples of usage, the linked screencasts provide very quick walk-thoughs
for common scenarios and edge cases as they become apparent.

It is suggested to browse Oleg Sych's blog for T4 background and to download Tangible's free T4 editor.

The test project contains a wide variety of use cases with the resultant class diagrams for reference, edification and verification.

A suite of unit tests is being built to ensure the output but, as I have no illusions that I am a SQL guru, I am hoping for a more targeted and experience SQL programmer/DB architect to put some eyeballs on the generate class diagrams and script to confirm
or deny that my logic is correct and the resultant code/diagrams accurately represent the datastore given the supplied parameters before I start writing the 100's of tests required.

The number of parameters, listed below, may seem daunting but typically you will need to set only a handful to produce a batch of robust DTO classes that you may freely extend using partial classes. If one-off generation is desired/required, simply copy the
.cs file(s) to another project.

The harness, configuration and code generation functionality is contained in DeadSimpleDTO2.ttinclude.

The rendering logic is completely contained in DeadSimpleDTO2.sql.

You may have as many .tt files that include DeadSimpleDTO2.ttinclude as desired. See the test project for an example.

Parameters and connection string may be set entirely in code, entirely in app/web config, entirely in an arbitrarily named configuration file, or a combination of both. The rendering engine takes an instance of DeadSimpleParameter. This class is set to the
defaults listed below and is configuration aware.

If a parameter is not found in the specified file, the current value set on the parameter object will be used.

You may assign values after loading from a config file and before passing to the rendering engine.

Again, see the test project and sample project for usage examples and resultant class diagrams.

Known Issues:

The templates are run in the context of the active solution project. If a solution has but one project, the 'Transform All Text Templates' button on the top of the solution explorer may work just fine for you, but if there are multiple projects in the
solution, you may want to run each template manually by right-clicking the file and selecting 'Run Custom Tool'. YMMV.

The nature of the text transformation system is such that the templates are compiled to temp assemblies. If you change a configuration file based parameter, you may not get expected results upon running the template and/or may get an obscure remoting exception.
This is an issue with visual studio, not the templates.

The quickest solution is to open the file that is calling the include and make any modification, e.g. add and remove a space to set the dirty flag, and then save the file. The template will be recompiled, the configuration file will be reloaded and the tranformation
will be processed.

Forward relative file paths are processed as expected by the t4 subsystem but it seems not to like backing out of a directory. e.g. .\..\a child of my parent\deadSimpleDTO2.ttinclude.

By default, the rendering engine expectes the sql script to be in the same directory as the calling template. If it is placed elsewhere the scriptPath of the renderer must explicitly be set. Again, as far as I can tell, only forward relative paths are recognized.

I have toyed with using project items to located the script and include file to enable adding a link to a common file instead of requiring a copy of both files in each project that uses it but have met with mixed success and have put this on hold.

IMPORTANT: I am desperately seeking persons with more experience and SQL knowledge than I to review this script and it's output and provide feedback and perhaps tips on how to optimize the code or advice to just drop it.

While managed languages are definitely better suited for this sort of task, I think that a SQL based code generator is an interesting idea and am willing to spend some time if it is well recieved.

Specific areas where help and advice are invited are the cardinality discovery sections. There must be a better way to identify 1:? and M:N relationships than what I eventually came up with, after 3 days of pulling my hair out.....

PARAMTER REFERENCE: The Real FM
SQL/CLR/CONFIG Parameters
--------------------------+---------------------------+-------------------------
Name | Type | Default Value
--------------------------+---------------------------+-------------------------
--------------------------+---------------------------+-------------------------
rootNamespace | NVARCHAR / string (256) | NULL / string.Empty
--------------------------+---------------------------+-------------------------
| The namespace in which to root all rendered DTO classes. If run in
| batch mode, the default namespace is the Catalog name. If run from
| the T4 template, the default namespace is the hosting project's
| default namespace.
--------------------------+---------------------------+-------------------------
renderSchemaNamespace | BIT / bool | 0 / false
--------------------------+---------------------------+-------------------------
| Further namespace objects with the name of the schema.
| e.g. dbo.mynamespace.myDto
| Can be necessary if multiple schemas contain similarobject names.
--------------------------+---------------------------+-------------------------
namespacesToInclude | NVARCHAR / string (2048) | NULL / string.Empty
--------------------------+---------------------------+-------------------------
| A non-quoted, comma delimited string containing a list of
| namespaces to introduce with using statements.
--------------------------+---------------------------+-------------------------
objectsToRender | NVARCHAR / string (2048) | NULL / string.Empty
--------------------------+---------------------------+-------------------------
| A non-quoted, comma delimited string containing a list of tables
| and views to be rendered.
| Only listed objects will be rendered.
| If objectsToRender is specified, objectsToExclude isignored.
| The default value is NULL/string.Empty and will render all user
| tables and views in all namespaces.
--------------------------+---------------------------+-------------------------
objectsToExclude | NVARCHAR / string (2048) | NULL / string.Empty
--------------------------+---------------------------+-------------------------
| A non-quoted, comma delimited string containing a list of tables
| and views to be excluded fromrendering.
| Ignored if objectsToRender is specified.
--------------------------+---------------------------+-------------------------
membersToExclude | NVARCHAR / string (2048) | NULL / string.Empty
--------------------------+---------------------------+-------------------------
| A non-quoted, comma delimited string containing a list of columns
| and FK constraints to be excluded from rendering.
| The format for columns is 'schema.table/view.column'
| FK constraints may be dropped on one side or both.
| To drop both sides of an FK, specify 'schema.constraint.'
| To drop one side, specify 'schema.table+schema.constraint'
|
|
| columns = schema.object.column
|
| e.g. dbo.Orders.CustomerId
|
| Foreign Keys = schema.constraint OR schema.object+schema.constraint
| e.g.
| dbo.FK_Order_DetailsOrders OR dbo.Orders+dbo.FK_Order_DetailsOrders
|
| the schema.object qualifier indicates which side of the
| relationship to drop
|
| e.g.
| @membersToExclude =
| 'dbo.Orders+dbo.FK_Orders_Customers,dbo.Orders.ShipCountry'
--------------------------+---------------------------+-------------------------
memberNameSubstitutions | NVARCHAR / string (2048) | NULL / string.Empty
--------------------------+---------------------------+-------------------------
| A non-quoted, comma delimited string containing a list of related
| object names and substitutions.
| Substitutions are performed only on FK relations.
|
| The format is 'schema.table.defaultName|substitution'
|
| default name will be the identifier that is rendered if no
| substitutions are performed. E.G. Northwind.Employees, with both
| 1:M and M:1 relations set will render FK members Employees1 and
| Employees2
|
| The replacement tokens are
| dbo.Employees.Employees1|Supervisor,
| dbo.Employees.Employees2|Subordinates
|
| See Northwind.Relations.MTO.tt and
| Diagrams\Northwind\Northwind.Relations.MTO.cd
| When specifying substitutions, you are responsiblefor preventing
| collisions.
--------------------------+---------------------------+-------------------------
objectPrefix | NVARCHAR / string (256) | NULL / string.Empty
--------------------------+---------------------------+-------------------------
| A string to prepend to the class name of each object rendered. The
| value is prepended AFTER any processing.
| When specifying objectPrefix, you are responsible for preventing
| collisions.
--------------------------+---------------------------+-------------------------
objectSuffix | NVARCHAR / string (256) | NULL / string.Empty
--------------------------+---------------------------+-------------------------
| A string to append to the class name of each object rendered. The
| value is append AFTER any processing.
| When specifying objectSuffix, you are responsible for preventing
| collisions.
--------------------------+---------------------------+-------------------------
identifierMode | NVARCHAR / string (10) | 'Underscore'
--------------------------+---------------------------+-------------------------
| Specifies how to handle embedded spaces and underscores in schema
| names, object names and column names. The default value is
| 'Underscore' which simply replaced embedded spaces with
| underscores.
| If collisions occur, E.g. dbo.Table_1 and dbo.Table 1,
| the dbo.Table 1 will have an underscore added and in that there has
| already been an object named dbo.Table_1 rendered, it will be
| named dbo.Table_11 and so on. Time to use substitutions.
--------------------------+---------------------------+-------------------------
renderRelationships | BIT / bool | false
--------------------------+---------------------------+-------------------------
| Render default 1:M relationships as empty Lists.
--------------------------+---------------------------+-------------------------
renderManyToOneRelations | BIT / bool | false
--------------------------+---------------------------+-------------------------
| Render 1:M as empty Lists, M:1 and 1:? as object references
--------------------------+---------------------------+-------------------------
dropFkMembers | BIT / bool | false
--------------------------+---------------------------+-------------------------
| If an FK column has been rendered as a List or object and is NOT a
| component in ANY unique constraint, it will be dropped.
--------------------------+---------------------------+-------------------------
renderManyToManyRelations | BIT / bool | false
--------------------------+---------------------------+-------------------------
| Render M:N relations as empty Lists and omit the Junction table 1:M
| member if it is composed entirely of key columns. If it contains
| any columns that are not part of any constraint it is considered to
| be data and will be rendered. If a junction table contains, for
| instance a DateCreated field and you do not want it surfaced on
| your object, you will need to either exclude it specifically or
| conversely, not include it.
--------------------------+---------------------------+-------------------------
renderJunctionRelations | BIT / bool | false
--------------------------+---------------------------+-------------------------
| Forces all junction 1:M collections to be rendered.
--------------------------+---------------------------+-------------------------
listMemberType | NVARCHAR / string (256) | 'IList'
--------------------------+---------------------------+-------------------------
| The generic list type return value for a plural member. Do not
| specify the angle brackets.
| There is no constraint on the type you choose to specify, unless
| you choose to use the provided rudimentary reflective data loader.
| If you do choose to use DeadSimpleLoader, first of all, i feel
| sorry for you ;-), but more importantly you will need to specify
| 'renderLoaderAttributes' and ensure that the listMemberType and
| listInstanceTypeboth implement IList<>
--------------------------+---------------------------+-------------------------
listInstanceType | NVARCHAR / string (256) | 'List'
--------------------------+---------------------------+-------------------------
| The generic type that will be instantiated to back the property.
--------------------------+---------------------------+-------------------------
renderDataContract | BIT / bool | 0 / false
--------------------------+---------------------------+-------------------------
| Render a System.Runtime.Serialization.DataContract attribute on the
| class and DataMember attributes on all members.
--------------------------+---------------------------+-------------------------
dataContractNamespace | NVARCHAR / string (256) | NULL / string.Empty
--------------------------+---------------------------+-------------------------
| The namespace for DataContract. in its entirety e.g.
| http://mytempuri.org/mydeadsimpledtos
|If null, only the Name argument will be supplied.
--------------------------+---------------------------+-------------------------
renderSerializable | BIT / bool | 0 / false
--------------------------+---------------------------+-------------------------
| Render System.Serializable on each class.
--------------------------+---------------------------+-------------------------
renderCommonBase | BIT / bool | 0 / false
--------------------------+---------------------------+-------------------------
| Inherit all classes from a common partial base class The default
| class implementation contains only a byte array comparison routine
|
| If renderINotify is true, the INotifyPropertyChanged implementation
| is housed in the baseclass regardless of the value of this paramter
--------------------------+---------------------------+-------------------------
commonBaseName | NVARCHAR / string (256) | 'DeadSimpleDTO'
--------------------------+---------------------------+-------------------------
| The typename to use for the common base class, if rendered.
| The format for specifying a generic base class is classname<$>.
| Only one generic type parameter is supported and the format is
| strict. '<$>', NO SPACES. This is a TSQL program for frak's sake.
|
| e.g. @commonBaseName='MyDTOBase<$>'
|
| On the DTO classes it will be rendered as
|
| public partial class MyTable : MyDTOBase<MyTable>
|
| and the base class will be rendered
|
| public partial class MyDTOBase<T> [: xxx] where T:new()
|
| If renderDataContract is specified, the DataContractName will be
| 'MyTable' and the DataContract name of the base class will be
| 'MyDTOBaseOf{0}' where 0 is the final generated typename of the DTO
|
| e.g.
|
| [DataContract([datacontract namespace,]Name="MyDTOOf{0}")]
| public partial class MyDTO<T> :INotifyPropertyChanged where T:new()
| {
|
| On the other end of the wire the type will be, for Orders,
| MyDTOOfOrders.
| It is probably a good idea to specify a datacontract namespace
| especially if using a generic base class name.
|
| You can then use another partial part to constrain T and or inherit
| from another base such as ActiveRecordBase<T>.
|
| psst.. that was just me making noise, I have not tested it yet.....
|
| see http://msdn.microsoft.com/en-us/library/ms731045.aspx for more
| information on generic types and datacontract names.
--------------------------+---------------------------+-------------------------
renderINotify | BIT / bool | 0 / false
--------------------------+---------------------------+-------------------------
| Implement INotifyPropertyChanged in a common base class.
--------------------------+---------------------------+-------------------------
renderLoaderAttributes | BIT / bool | 0 / false
--------------------------+---------------------------+-------------------------
| Render attributes that enable the simple reflective dataloader,
| DeadSimpleLoader. The attributes are rendered in the namespace of
| the DTO thus carrying no dependencies. The loader discovers them
| by name.
--------------------------+---------------------------+-------------------------
returnResultSet | BIT / bool | 0 / false
--------------------------+---------------------------+-------------------------
| If false, simply render the code via PRINT statements. If running
| as a batch the output can be copied manually. If run via a
| SqlConnection, handle the InfoMessage event, the code will be the
| message.
|
| If true, returns a resultset formatted in such a way as to enable
| the provided T4 template include file to render the classes in a
| single file or in multiple files.
|
| Although you should not need to handle the resultset manually,
| the format follows:
| The resultset is comprised of two columns, [name] and [line].
| The resultset is opened with [name]='NAMESPACE_OPEN',
| [line] = render options listing behindcomment whacks, using
| directives the root namespace and open curly brace.
|
| The dto types and support classes are then returned in chunks.
| A type is signalled by a [name] and null [line]. The code for that
| type is comprised of the following records with null [name] and
| non-null [line].
|
| [name]='NAMESPACE_CLOSE' , [line]='}' signals the end of the result
--------------------------+---------------------------+-------------------------
CLR/CONFIG ONLY Parameters
--------------------------+---------------------------+-------------------------
renderSeparateFiles | bool | false
--------------------------+---------------------------+-------------------------
| If true, each type is written to a seperate file names
| [objectname].generated.cs nestedunder the calling template file.
|
| If false, the entire output is rendered under the calling template
| as a single file.
|
| The template is source control aware and will check out a file
| before writing to it if necessary.
|
| The template makes no attempt to manage the files. If the
| parameters change you may wish to delete all nested file(s) before
| running the transform.
--------------------------+---------------------------+-------------------------
connectionString | string | string.Empty
--------------------------+---------------------------+-------------------------
| The SqlServer 2005/2008 connection string for the target database.
--------------------------+---------------------------+-------------------------
-- NOTE: Be sure to execute within the scope of the desired database.
-- You may wish to use a 'USE' statement if this script is called
-- interactively from a SQL tool e.g. USE Northwind
-- TODO: get column defaults to determine PK generator type for NHibernate and ActiveRecord
-- TODO: implement ActiveRecord attributes and Fluent NHibernate coe