Introduction
LINQ to SQL is a component of .NET Framework version 3.5 that provides a run-time infrastructure for managing relational data as objects. Developers using Visual Studio typically use the Object Relational Designer, which provides a user interface for implementing many of the features of LINQ to SQL; however sometimes large databases might take some time to generate and if your net project contains lots of files and solutions that need to be built; when there are distributed development teams, then this might actually be part of the build process.
Background
For demo purposes, I’ll be using:
- PowerShell commands to execute the script
- SqlMetal command-line tool
- AdventureWorks2008 Database
Before starting:
Assuming you previously installed AdventureWorks2008, go ahead and copy AdventureWorks_Data.mdf into any folder for testing. I’m using C:\adw\AdventureWorks_Data.mdf. Before you copy, you might need to stop the SQL Service.
Using the Code
The SqlMetal command-line tool generates code and mapping for the LINQ to SQL component of the .NET Framework.
By applying options, you can instruct SqlMetal to perform several different actions that include the following:
- From a database, generate source code and mapping attributes or a mapping file.
- From a database, generate an intermediate database markup language (.dbml) file for customization.
- From a .dbml file, generate code and mapping attributes or a mapping file.
Usually installs on C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin.
This approach scales well for large databases and perhaps you might as well find it useful in your build process.
The SQLMetal file is included in the Windows SDK that is installed with Visual Studio.
The command syntax looks as follows:
sqlmetal [options] [path]
To view the most current option list, type sqlmetal /? at a command prompt from the installed location. There are currently four option types: Connection, Extraction, Output and Miscellaneous.
Points of Interest
Make sure sqlmetal works by simply running the command pointing to the folder where the *.mdf file is at; as shown below. In the Visual Studio Command Prompt(2010), type something like:
sqlmetal /code:"c:\adw\AdventureWorks.cs" /language:csharp
"C:\adw\AdventureWorks_Data.mdf"
The result should be a C# code file however you can specify VB using /language:vb.
Here’s how it should look like:
The output should be:
Now that we have successfully generated our object model, let’s go ahead and create another one that supports serialization.
Like this:
sqlmetal /code:"c:\adw\SerialAdventureWorks.cs" /language:csharp
/serialization:Unidirectional "C:\adw\AdventureWorks_Data.mdf"
Now we have another object that supports serialization. Obviously, when supporting serialization, it takes longer to build and the resulting code class file generated is bigger in size.
There’s almost 100kb difference between those two files! Furthermore, if you want to generate *.dbml file, you can accomplish it by running:
sqlmetal /dbml:"c:\adw\AdventureWorks.dbml" /language:csharp
/serialization:Unidirectional "C:\adw\AdventureWorks_Data.mdf"
You call also generate views, functions and stored procedures too but you must indicate such on the command params.
I’ll show how you can accomplish that using PowerShell scripts.
Now we can start working in our script. For this, we’ll be using cmdlets.
Cmdlet Overview
A cmdlet is a lightweight command that is used in the Windows PowerShell environment. The Windows PowerShell runtime invokes these cmdlets within the context of automation scripts that are provided at the command line. The Windows PowerShell runtime also invokes them programmatically through Windows PowerShell APIs. Here's how the command should look like on the PowerShell Editor:
$FrameworkPath = "C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319"
$SDKPath = "C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin"
invoke-expression "& '$SDKPath\sqlmetal.exe' '/code:c:\adw\AdventureWorks.cs'
'/language:csharp' 'C:\adw\AdventureWorks_Data.mdf' '/namespace:AdventureWorks.Database'
'/views' '/functions' '/sprocs' '/serialization:Unidirectional'"
This *.PS1 file generates another class with all features including store procedures, views, functions, etc.:
As you can see, the class file just keeps getting bigger and bigger, the final size is 759.
There are certainly other minor tweaks that must be done to that file in order to use it on a build process; but that’s another post!
Source: http://msdn.microsoft.com/en-us/library/bb386987.aspx
Acknowledgement
Corrections made were recommended by Mark. Thanks Mark for your recommendations.
History
- 4th August, 2011: Initial version
