Foliotek Developer Blog

Automatic Update of Datamodel In Linq

One of the issues that we came across fairly quickly when converting some projects to Linq was how unusable the default Linq to SQL utility was. The interface worked extremely well for 2 or 3 tables, but quickly became unmanageable with much more than that.

We began looking for other solutions, and discovered a command line utility called SQLMetal that can be used to generate a DataModel from a connection string and/or an XML file.

The solution we settled on uses SQLMetal to generate XML markup from the database, then uses XSL Transformations to make desired property/model changes (Private properties, Delay loaded, etc), and then uses SQLMetal to generate a code file from this XML file.

To start, we created a batch file called updateModel.bat and placed it in the project:

"C:Program FilesMicrosoft SDKsWindowsv6.0AbinSQLMetal.exe" /conn:"CONNECTIONSTRING" /timeout:0 /namespace:MODELNAMESPACE /context:DATACONTEXTNAME /language:csharp /pluralize /dbml:"%~dp0DataModel.dbml" "%~msxsl.exe" "%~dp0DataModel.dbml" "%~dp0ModifyDbml.xslt" -o "%~dp0DataModel.dbml" "C:Program FilesMicrosoft SDKsWindowsv6.0AbinSQLMetal.exe" /code:"%~dp0DataModel.cs" /namespace:MODELNAMESPACE /context:DATACONTEXTNAME /language:csharp /pluralize %DataModel.dbml

The output of SQLMetal in the first line of this file is an XML file called DataModel.dbml that looks something like this :

<table member="Persons" name="dbo.Person">  
  <type name="Person">
    <column canbenull="false" dbtype="Int NOT NULL IDENTITY" isdbgenerated="true" isprimarykey="true" name="PersonID" type="System.Int32"></column>
    <column canbenull="false" dbtype="Int NOT NULL" name="AddressID" type="System.Int32"></column>
    <column canbenull="false" dbtype="VarChar(255) NOT NULL" name="Email" type="System.String"></column>
    <column canbenull="false" dbtype="VarChar(255) NOT NULL" name="NameFirst" type="System.String"></column>
    <column canbenull="false" dbtype="VarChar(255) NOT NULL" name="NameLast" type="System.String"></column>
    <association isforeignkey="true" member="Address" name="FK_Person_Address" otherkey="AddressID" thiskey="AddressID" type="Address"></association>

The second line of this script uses a utility called msxsl.exe (note that this requires MSXML). This program uses a file called ModifyDbml.xslt to perform an XSL tranformation on the DataModel.dbml file.

<?xml version="1.0" encoding="utf-8"?>  
<stylesheet exclude-result-prefixes="msxsl" version="1.0" xmlns:dbml="" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:xsl="">  
  <output indent="yes" method="xml"></output>
  <template match="dbml:Database/dbml:Table/dbml:Type[@Name = 'Person']/dbml:Column[@Name = 'AddressID']">
      <apply-templates select="@* | node()"></apply-templates>
      <attribute name="AccessModifier">Private</attribute>
  <template match="@* | node()">
      <apply-templates select="@* | node()"></apply-templates>

This will make it so the "AddressID" property of "Person" is a private property – and this is where all such DataModel changes should be stored. Note that any changes made directly to the DataModel after this point will be lost each time the files are generated.

The final line of this script generates a DataModel.cs file from the updated XML file.

Finally, we looked for a way to call this script to update from within visual studio. To do this, we went to Tools -> External Tools -> Add, and used the following arguments:

This allows you to highlight the updateModel.bat from Visual Studio and go to "Tools->CMD Prompt" to update your DataModel.