The power of SQLMetal

During my time with Linq, I have created all of my DataContext’s using the designer in Visual Studio 2008.  I’ve been aware of SQLMetal but always thought it was just a command line tool which didn’t offer much – after looking at it today, I am wrong.  SqlMetal is very powerful and cool!

To start with, SqlMetal can generate a DataContext for your entire database with a single command.  This is very useful if you have a large number of tables in your system, as dragging and dropping them onto the designer would have got boring very quickly. By entering this command we can have our Northwind datacontext created for us and saved to the file NorthwindDataContext.cs.

SqlMetal /server:. /database:Northwind /code:NorthwindDataContext.cs

We can then include the class within our project and use it as if the designer had created it.  However, it would have been nice if it accepted an array of tables to exclude, or only include, during the creation process.  Also it also doesn’t create an overload for the constructor so it uses the App.config connection string like the designer does.

We can also get SqlMetal to include all of our views, functions and sprocs

SqlMetal /server:. /database:Northwind /code:NorthwindDataContext.cs /views /functions /sprocs

If it cannot extract an item from the database into code, then it will continue with the process and report the error at the end.  In my case I had this single error:

warning SQM1014: Unable to extract stored procedure ‘dbo.sp_upgraddiagrams’ from SqlServer. Invalid object name ‘dbo.dtproperties’.

I think that’s a really powerful feature and makes life a lot simpler than manually creating everything.

In the above example, we are asking SQLMetal to generate our DataContext as code. However, SQLMetal can also output the dbml (/dbml:file.dbml) or mapping (/map:file.xml) file for the database.

By default, the Linq to SQL designer will pluralise all of the table names (Orders => Order).  SQLMetal doesn’t do this by default, however by adding /pluralize you can force the changes.  I’ve spoken about this before.

You can define the language which the code should be generated using the /language: option and you can set the namespace for which the DataContext should be part of by using the /namespace: property.

If you wanted a different name for your datacontext class then you could use the /context: option, by default it uses the same name as the database.

Onto my favourite two options. The entitybase option (/entitybase:) allows you to specify a class or interface which all the entities in the datacontext must inherit from.

SqlMetal /server:. /database:northwind /code:”%~dp0Northwind.cs” /entitybase:MyNamespace.ITable

The code generated would then look like this:

[Table(Name=”dbo.Categories”)]
public partial class Categories : MyNamespace.ITable, INotifyPropertyChanging, INotifyPropertyChanged

This is a very useful feature, as discussed in the previous post we should place all custom code in a partial class. I don’t think interfaces are best used in this situation as you would need to implement it on all the entities, however it could be useful for base classes.

Finally, /serialization:Unidirectional option adds a [DataContract()] attribute to the classes and [DataMember(Order=#)] to the properties.  These two attributes allow for objects to be serialised which means they can be used with WCF.

Ben’s top tip

Use a classic batch file (.bat) to store your SQLMetal command. Then when you need to regenerate the DataContext you will not end up using different settings by mistake.  This batch file can then be included within your source control for the rest of the team to use.  Use the “%~dp0″ variable to ensure it runs from its current location and not the default command line location, generally it should be your project folder.

Given this command in a batch file, it will convert the %~dp0 to the path where the path file is located.

SqlMetal /server:. /database:northwind /code:”%~dp0Northwind.cs”

Becomes

SqlMetal /server:. /database:northwind /code:”E:PATHTOMYPROJECTSNorthwind.cs”

Technorati Tags: , ,

4 thoughts on “The power of SQLMetal”

  1. Why not create an MSBuild target that parsed an XML file for settings to use? Not unlike ASP.NET’s BuildProvider.

  2. Hi,

    Thanks for the comments.

    The reason why I went for the bat file is that you really only need to generate the DataContext when the database changes, so having it recreated on every build is a bit unnecessary and if your SQL Server is down then your build will fail. Plus, it would be additional overhead for your build process. Having it as a bat file means it can be executed on an ad-hoc basis.

    However, it some situations, I can see why you would want this.

    Ben

  3. Thanks for having a blog. I find your color scheme extremely difficult to read. You might want to think about being a little more conventional if you want people to stick around.

Leave a Reply

Your email address will not be published. Required fields are marked *