Entity.works (1/5)

If you are like me you love data and data-modeling! Maybe also like me, you really hate using any of the popular ER modeling tools like PowerDesigner, Erwin or even Visio. Too much tedious clicking all over the place if you ask me. I’ve tried just working in the schema with notepad but that seems a little too close to the metal. On this page I present a simple, no-cost (beyond the Visual Studio 2010 which you already have) solution that bridges the gap between tedious clicking and notepad nonsense.

My solution involves defining my entities in a simple, easy to edit XML document and then running that XML document into a T4 template in Visual Studio 2010. The template, a simple file with the .TT extension reads the XML and then outputs the entity properties formatted with the schema boiler-plate contained within the same template. If you want a quick overview on T4 templates in VS then click here. Otherwise, see below for some illustration.

Get the code.. see link at end of page.

XML (source) Document (show/hide )

<?xml version="1.0" encoding="utf-8" ?>

<DataBase TablePrefix="T_" >

  <Entity Name="Application" Type="primary" Schema="dbo" GenerateCrudSproc="true" >
    <Columns AddAuditColumns="true" AddAltPk="true">
      <Column Name="ApplicationId" DataType="Int" IsPk="true" />
      <Column Name="ApplicationName" DataType="Char(25)" Nullable="false"  />
      <Column Name="ApplicationType" DataType="Char(02)" Nullable="false"  />
    </Columns>
    <Keys/>
  </Entity>

  <Entity Name="Release" Type="dependent" Schema="dbo" GenerateCrudSproc="false" >
    <Columns AddAuditColumns="true" AddAltPk="true">
      <Column Name="ApplicationId" DataType="Int" IsPk="true" />
      <Column Name="ReleaseSeq" DataType="Int" IsPk="true"  />
      <Column Name="ReleaseName" DataType="Char(25)" Nullable="false"  />
    </Columns>
    <Keys>
      <Key Name="Application" ReferenceName="Application" />
    </Keys>
  </Entity>

  <Entity Name="Iteration" Type="dependent" Schema="dbo" GenerateCrudSproc="false" >
    <Columns AddAuditColumns="true" AddAltPk="true" >
      <Column Name="ApplicationId" DataType="Int" IsPk="true" />
      <Column Name="ReleaseSeq" DataType="Int" IsPk="true" />
      <Column Name="IterationSeq" DataType="Int" IsPk="true" />
      <Column Name="IterationBegin" DataType="datetime" Nullable="false"  />
    </Columns>
    <Keys>
      <Key Name="Release" ReferenceName="Release" />
    </Keys>
  </Entity>

</DataBase>

T4 Template (show/hide )

<#@ template  debug="true" hostSpecific="true" #>
<#@ output extension=".sql" #>
<#@ Assembly Name="System.Core.dll" #>
<#@ Assembly Name="System.Xml.dll" #>
<#@ Assembly Name="System.Windows.Forms.dll" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Xml" #>
<#
	// TODO: add validation keywords and the column level
	// TODO: add optional column defaults
#>
<#
	var xmlDoc = new System.Xml.XmlDocument();
	xmlDoc.Load(this.Host.ResolvePath("entity.works.xml"));

	var entityNodes = xmlDoc.SelectNodes("/DataBase/Entity");		// entity nodes
	foreach (XmlNode entityNode in entityNodes)
	{
		var eName = entityNode.Attributes["Name"].Value;
#>

-- --------------------------------------------------------------------------------------------------------
-- [<#= eName #>] generated on <#= DateTime.Now #> by SdlcSchema.tt
-- --------------------------------------------------------------------------------------------------------
CREATE TABLE dbo.<#= eName #> (
<#
		var comma = "";
		var pkList = "";
		var columnsNode = entityNode.SelectSingleNode("Columns");
		var columnNodes = entityNode.SelectNodes("Columns/Column");	// column nodes
		foreach (XmlNode columnNode in columnNodes)
		{
			var cName = columnNode.Attributes["Name"].Value;
			var cDataType = columnNode.Attributes["DataType"].Value;
			var cNullable = columnNode.Attributes["Nullable"] == null ? "" : "NOT NULL";
			
			var isPk = columnNode.Attributes["IsPk"] == null ? "no" : columnNode.Attributes["IsPk"].Value;
			if (isPk == "true")
			{
				pkList = pkList == "" ? cName + " ASC" : pkList + ", " + cName + " ASC";
				cNullable = "NOT NULL";
			}
#>
	<#= comma + cName #>	<#= cDataType #> <#= cNullable #>
<#			
			comma = ", ";
		}
		if (columnsNode.Attributes["AddAltPk"].Value == "true")
		{
#>
	<#= comma + eName #>AltPk	uniqueidentifier NOT NULL CONSTRAINT dflt<#= eName #>AltPk DEFAULT newid()
<#	
		}				
		if (columnsNode.Attributes["AddAuditColumns"].Value == "true")
		{
#>
	<#= comma + eName #>Added	datetime NOT NULL CONSTRAINT dflt<#= eName #>Added DEFAULT getdate()
	<#= comma + eName #>Updated	datetime 
	<#= comma + eName #>Version	timestamp NOT NULL
<#	
		}				
#>
	CONSTRAINT <#= eName #>_pk PRIMARY KEY CLUSTERED 
	(
		<#= pkList #>
	) 
	WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]
) ON [PRIMARY]
<#
		if (columnsNode.Attributes["AddAltPk"].Value == "true")
		{
#>			
CREATE UNIQUE NONCLUSTERED INDEX <#= eName #>_UX ON dbo.<#= eName #> ( <#= eName #>AltPk ASC )
WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON) 
ON [PRIMARY]
<#	
		}
		var keyNodes = entityNode.SelectNodes("Keys/Key");			// key nodes
		foreach (XmlNode keyNode in keyNodes)
		{
			var kName = keyNode.Attributes["ReferenceName"].Value;
			string kList = getPkList(xmlDoc.SelectSingleNode("/DataBase/Entity[@Name='" + kName + "']"));
#>

ALTER TABLE dbo.<#= eName #>  WITH CHECK ADD  CONSTRAINT <#= eName #>_<#= kName #>_FK FOREIGN KEY
(
	<#= kList #>
) 
REFERENCES dbo.<#= kName #> (<#= kList #>)	
<#		
		}
	}	
#>
<#+
// local methods
string getPkList(XmlNode entityNode)
{
	var pkList = string.Empty;
	var pkNodes = entityNode.SelectNodes("Columns/Column[@IsPk='true']");
	foreach (XmlNode pkNode in pkNodes)
	{
		pkList = (pkList) + (pkList == "" ? "" : ",") + (pkNode.Attributes["Name"].Value);
	}
	return pkList;
}
#>

Generated Schema DDL (show/hide )


-- --------------------------------------------------------------------------------------------------------
-- [Application] generated on 11/11/2011 11:11:11 by gen.schema.tt
-- --------------------------------------------------------------------------------------------------------
CREATE TABLE dbo.Application (
	ApplicationId	Int NOT NULL
	, ApplicationName	Char(25) NOT NULL
	, ApplicationType	Char(02) NOT NULL
	, ApplicationAltPk	uniqueidentifier NOT NULL CONSTRAINT dfltApplicationAltPk DEFAULT newid()
	, ApplicationAdded	datetime NOT NULL CONSTRAINT dfltApplicationAdded DEFAULT getdate()
	, ApplicationUpdated	datetime 
	, ApplicationVersion	timestamp NOT NULL
	CONSTRAINT Application_pk PRIMARY KEY CLUSTERED 
	(
		ApplicationId ASC
	) 
	WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]
) ON [PRIMARY]
			
CREATE UNIQUE NONCLUSTERED INDEX Application_UX ON dbo.Application ( ApplicationAltPk ASC )
WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON) 
ON [PRIMARY]

-- --------------------------------------------------------------------------------------------------------
-- [Release] generated on 11/11/2011 11:11:11 by gen.schema.tt
-- --------------------------------------------------------------------------------------------------------
CREATE TABLE dbo.Release (
	ApplicationId	Int NOT NULL
	, ReleaseSeq	Int NOT NULL
	, ReleaseName	Char(25) NOT NULL
	, ReleaseAltPk	uniqueidentifier NOT NULL CONSTRAINT dfltReleaseAltPk DEFAULT newid()
	, ReleaseAdded	datetime NOT NULL CONSTRAINT dfltReleaseAdded DEFAULT getdate()
	, ReleaseUpdated	datetime 
	, ReleaseVersion	timestamp NOT NULL
	CONSTRAINT Release_pk PRIMARY KEY CLUSTERED 
	(
		ApplicationId ASC, ReleaseSeq ASC
	) 
	WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]
) ON [PRIMARY]
			
CREATE UNIQUE NONCLUSTERED INDEX Release_UX ON dbo.Release ( ReleaseAltPk ASC )
WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON) 
ON [PRIMARY]

ALTER TABLE dbo.Release  WITH CHECK ADD  CONSTRAINT Release_Application_FK FOREIGN KEY
(
	ApplicationId
) 
REFERENCES dbo.Application (ApplicationId)	

-- --------------------------------------------------------------------------------------------------------
-- [Iteration] generated on 11/11/2011 11:11:11 by gen.schema.tt
-- --------------------------------------------------------------------------------------------------------
CREATE TABLE dbo.Iteration (
	ApplicationId	Int NOT NULL
	, ReleaseSeq	Int NOT NULL
	, IterationSeq	Int NOT NULL
	, IterationBegin	datetime NOT NULL
	, IterationAltPk	uniqueidentifier NOT NULL CONSTRAINT dfltIterationAltPk DEFAULT newid()
	, IterationAdded	datetime NOT NULL CONSTRAINT dfltIterationAdded DEFAULT getdate()
	, IterationUpdated	datetime 
	, IterationVersion	timestamp NOT NULL
	CONSTRAINT Iteration_pk PRIMARY KEY CLUSTERED 
	(
		ApplicationId ASC, ReleaseSeq ASC, IterationSeq ASC
	) 
	WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]
) ON [PRIMARY]
			
CREATE UNIQUE NONCLUSTERED INDEX Iteration_UX ON dbo.Iteration ( IterationAltPk ASC )
WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON) 
ON [PRIMARY]

ALTER TABLE dbo.Iteration  WITH CHECK ADD  CONSTRAINT Iteration_Release_FK FOREIGN KEY
(
	ApplicationId,ReleaseSeq
) 
REFERENCES dbo.Release (ApplicationId,ReleaseSeq)

That's all there is to it! I'd rather maintain my entity model in an XML document like this any day of the week. Using the simple features of NotePad give me all I want. For good measure (and brownie points) I reverse-engineer my SQL Server database into Visio and create a pretty entity diagram and pass it around for discussion.

Actually, that's not all. The entity XML model is useful for even more than generating a database. Click here to see more... and get the code!