Using SQLMetal code generator tool for LINQ to SQL

• Download SQLMetal - 243.03 KB

Introduction 

An OR mapper release from MS was long over due and I'm sure some/most of you have already explored LINQ.

So what is SQLMetal?

It's a code generation tool built by Microsoft to aid in LINQ based development. The tool simply builds code and mapping  that you would otherwise have to code by hand. Unless you had access to the Visual Studio 2008 IDE. Even then its a matter of dragging and dropping tables and other Database objects into the LINQ to SQL environment.  Also when working in a development team,  database changes made by a DBA or other developers will need to be incorporated in your development environment. You would need to delete and recreate each database object in the LINQ to SQL environment every time they changes are made. Needless to say this would become an unnecessarily tedious task to perform. But with SQLMetal you could generate and distribute the dbml file every time changes are made. It can also be included as part of an automated build, as it is a command line tool.

Please note that I have illustrated just one method of using SQLMetal in this article. There are several other ways of using it.

Overview   

SQLMetal works as a command line tool that accepts a bunch of parameter options such as database connection parameters and outputs mapping or code files. You would then use this code file within your development environment with LINQ code for accessing and manipulating data in the database. Note SQLMetal generates one file with all code or mappings.

Location  

The tool is located under the following directory

C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\SqlMetal.exe

How to use the tool  

1. If you are using Visual studio, create a web project E.g : NorthWind. Then add a folder called DAL.

2. Create an empty folder for SQLMetal output file E.g : C:\MyProject

3. Then open a Command Window and navigate to the directory (see location) where SqlMetal is located.

4. Then use the following command for generating a single file called NorthWind.dbml.

SqlMetal.exe /server:localhost /database:NorthWind /dbml:C:\MyProject\DAL\NorthWind.dbml /namespace:NorthWind.DAL<br />/Context:NorthWindDataContext /provider:SQL2005 /pluralize


* To stick with the MS naming convention I have appended "DataContext" to the name of my DataContext class, this helps identify it as a DataContext object. However you can call it what ever you want.
* I have created a separate folder E.G: DAL for the auto generated code, this ensures better management of your code.
* I use the pluralize option when generating the code, as it makes naming enumeration objects easy to write and comprehend.

Using the auto generated code. 

If you are using Visual studio, you will need to add the NorthWind.dbml in the DAL folder. To do this Right click the DAL folder under your project in solution explorer, then click on add then on Existing Item navigate to DAL folder or where ever else you have generated the output file and include it in the project.

First we need to create a connection string, for all practical reasons its good to do so in the web config.

<connectionStrings>
  <add name="NorthwindConnectionString" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True"
  providerName="System.Data.SqlClient" />
</connectionStrings>

You can declare a string variable and use the ConfigurationManager to get the connection string values from the web config

string connStr = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

Instantiate an Object of the NorthWindDataContext class and write your LINQ query

NorthWind.DAL.NorthWindDataContext db = new NorthWind.DAL.NorthWindDataContext(connStr);
var cust = from c in db.Customers  select c;

You can then use the data in anyway that you wish, I have used it here to bind to a GirdView control.

gv.DataSource = cust;
gv.DataBind();

* If you have to extend the DataContext object I would recommend you write wrapper classes to do so rather than hack the auto generated code as you may need to regenerate the code several times during development.

Improving productivity

You can store the command line execution in a batch file. This will help you regenerate code with a single click every time there are changes made to the DataBase. I have included a batch file in the download file.

* I am assuming you will deploy this under C: see batch file. If not please make appropriate changes to the batch file.

Complete list of options and usage for SQLMetal 

SqlMetal [options] [<input file>]

  - Generates code and mapping for the LINQ to SQL component of the .NET framework. SqlMetal can:
  - Generate source code and mapping attributes or a mapping file from a database.
  - Generate an intermediate dbml file for customization from the database.
  - Generate code and mapping attributes or mapping file from a dbml file.

Options: 

  /server:<name>                   Database server name.
  /database:<name>               Database catalog on server.
  /user:<name>                      Login user ID (default: use Windows Authentication).
  /password:<password>          Login password (default: use Windows Authentication).
  /conn:<connection string>     Database connection string. Cannot be used with /server, /database, /user or /password options.
  /timeout:<seconds>              Timeout value to use when SqlMetal accesses the database (default: 0 which means infinite).

  /views                                Extract database views.
  /functions                           Extract database functions.
  /sprocs                               Extract stored procedures.

  /dbml[:file]                          Output as dbml. Cannot be used with /map option.
  /code[:file]                          Output as source code. Cannot be used with /dbml option.
  /map[:file]                           Generate mapping file, not attributes. Cannot be used with /dbml option.

  /language:<language>           Language for source code: VB or C# (default: derived from extension on code file name).
  /namespace:<name>              Namespace of generated code (default: no namespace).
  /context:<type>                    Name of data context class (default: derived from database name).
  /entitybase:<type>                Base class of entity classes in the generated code (default: entities have no base class).
  /pluralize                              Automatically pluralize or singularize class and member names using English language rules.
  /serialization:<option>            Generate serializable classes: None or Unidirectional (default: None).
  /provider:<type>                   Provider type: SQLCompact, SQL2000, or SQL2005. (default: provider is determined at run time).

  <input file>                           May be a SqlExpress mdf file, a SqlCE sdf file, or a dbml intermediate file.

Create code from SqlServer:
  SqlMetal /server:myserver /database:northwind /code:nwind.cs /namespace:nwind

Generate intermediate dbml file from SqlServer:
  SqlMetal /server:myserver /database:northwind /dbml:northwind.dbml /namespace:nwind

Generate code with external mapping from dbml:
  SqlMetal /code:nwind.cs /map:nwind.map northwind.dbml

Generate dbml from a SqlCE sdf file:
  SqlMetal /dbml:northwind.dbml northwind.sdf

Generate dbml from SqlExpress local server:
  SqlMetal /server:.\sqlexpress /database:northwind /dbml:northwind.dbml

Generate dbml by using a connection string in the command line:
  SqlMetal /conn:"server='myserver'; database='northwind'" /dbml:northwind.dbml

Points of Interest 

Since it is a command line tool it can be used in your build file/process.

Sadly there is no support for maintaining version history of the automated code but you can over come this by integrating output with a code repository see the batch file included in the download.  

Resources  

The sample Northwind database doesn't ship with SQL2005 you can get it from here.

http://www.sunsource.net/scdocs/ddUsingSVN_command-line

History