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 Files\Microsoft SDKs\Windows\v6.0A\bin\SQLMetal.exe" /conn:"CONNECTIONSTRING" 
      /timeout:0 /namespace:MODELNAMESPACE /context:DATACONTEXTNAME /language:csharp /pluralize 
"%~msxsl.exe" "%~dp0DataModel.dbml" "%~dp0ModifyDbml.xslt" -o "%~dp0DataModel.dbml"
"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SQLMetal.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 Name="dbo.Person" Member="Persons">
    <Type Name="Person">
      <Column Name="PersonID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false"></Column>
      <Column Name="AddressID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false"></Column>
      <Column Name="Email" Type="System.String" DbType="VarChar(255) NOT NULL" CanBeNull="false"></Column
      <Column Name="NameFirst" Type="System.String" DbType="VarChar(255) NOT NULL" CanBeNull="false"></Column>
      <Column Name="NameLast" Type="System.String" DbType="VarChar(255) NOT NULL" CanBeNull="false"></Column>
<Association Name="FK_Person_Address" Member="Address" ThisKey="AddressID" OtherKey="AddressID" Type="Address" IsForeignKey="true"></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"?>
<xsl:stylesheet version="1.0"
  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="dbml:Database/dbml:Table/dbml:Type[@Name = 'Person']/dbml:Column[@Name = 'AddressID']">
      <xsl:apply-templates select="@* | node()" />
       <xsl:attribute name="AccessModifier">Private</xsl:attribute>

<xsl:template match="@* | node()">
      <xsl:apply-templates select="@* | node()"/>

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: