How To: Use SqlMetal.exe to Create a Class from XML and Database

Article
06/07/2007

If you don't have Visual C# Codename Orcas Express Edition installed on your development machine, and you are using Visual Studio 2005 with the LINQ 2006 CTP, then SqlMetal.exe is something that you must lookout for.

It is the O/R mapper that you must use to create a strongly typed class, and use it with DLinq.

You can find SqlMetal.exe at: %systemdrive%\Program Files\LINQ Preview\Bin folder.

Its used as following:

 usage: sqlmetal [options] [<input file>]
options:
  /server:<name>     database server name
  /database:<name>   database catalog on server
  /user:<name>       login user id
  /password:<name>   login password
  /views             extract database views
  /functions         extract database functions
  /sprocs            extract stored procedures
  /xml[:file]        output as xml
  /code[:file]       output as source code
  /map[:file]        generate xml mapping file instead of attributes
  /language:xxx      language for source code (vb,csharp)
  /namespace:<name>  namespace used for source code
  /pluralize         auto-pluralize table names
  /dataAttributes    auto-generate DataObjectField and Precision attributes
  /timeout:<seconds> timeout value in seconds to use for database commands

examples:
To generate an XML file with extracted SQL metadata
  sqlmetal /server:myserver /database:northwind /xml:mymeta.xml

To generate an XML file with extracted SQL metadata from an .mdf file
  sqlmetal /xml:mymeta.xml mydbfile.mdf

To generate source code from an XML metadata file
  sqlmetal /namespace:nwind /code:nwind.cs /language:csharp mymetal.xml

To generate source code from SQL metadata directly
  sqlmetal /server:myserver /database:northwind /namespace:nwind /code:nwind.cs /language:csharp

To create an XML file from a database like Northwind, I copied the .mdf as suggested by the above help and pasted it to the same folder. I used the following command:
sqlmetal /xml:northwind.xml Northwind.mdf
but it failed with:

 Unhandled Exception: System.Data.SqlClient.SqlException: An error has occurred while establishing a
connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fac
t that under the default settings SQL Server does not allow remote connections. (provider: SQL Netwo
rk Interfaces, error: 26 - Error Locating Server/Instance Specified)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConne
ction)

I thought its asking for the server name, so I tried this:
sqlmetal /xml:northwind.xml /server:myserver Northwind.mdf
it also failed with the following error:

 Unhandled Exception: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database
 for file Northwind.mdf failed. A database with the same name exists, or specified file cannot be op
ened, or it is located on UNC share.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConne
ction)

The command that worked is following:
sqlmetal /server:myserver /database:Northwind /xml:northwind.xml

Then I thought of creating a class out of the generated XML. I used the following command and it worked the first time:
sqlmetal /code:Northwind.cs /language:csharp Northwind.xml

Creating a class file directly from the database is pretty starightforward, I used the following command:
sqlmetal /server:myserver /database:Northwind /code:Northwind.cs /language:csharp

C# IDE Support for Visual Studio 2005/C# 2005 Express

While this preliminary IDE support greatly enhances the editing experience when writing C# 3.0 code in VS 2005, the language service and compiler are still very much in an alpha phase. In the event of a compiler error, many more errors may be reported than are actually present. If you see a large number of errors when building C# 3.0 code, fix the top error in the error list first and build after each fix.

Follow these steps to enable LINQ IDE support for Visual Studio 2005 and Visual C# 2005 Express:

First, verify that you are running the release version of Visual Studio 2005 or Visual C# 2005 Express. The language service that will be installed only works with the English version of VS 2005/C# 2005 Express and may cause severe problems if installed on earlier versions. You can verify your version by selecting Help\About in Visual Studio 2005.
Shut down any running instances of Visual Studio.
Open the \bin folder under the LINQ preview installation directory and run “Install C# IDE Support” .
Start VS 2005/C# 2005 Express.

Follow these steps to restore your original VS 2005/C# 2005 Express language service:

Shut down any running instances of Visual Studio.
Open the \bin folder under the LINQ preview installation directory and run “Uninstall C# IDE Support” .
Start VS 2005/C# 2005 Express.

How To: Use SqlMetal.exe to Create a Class from XML and Database

Additional resources