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 lost of files and solutions that needs to be build; when there are distributed development teams then this might actually be part of the build process.
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.
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>
]
</path>
To view the most current option list, type sqlmetal /? at a command
prompt from the installed location. There currently four
option types.
Connection - Extraction - Output – Miscellaneous.
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 a create another
one that supports serialization.
Like this:
sqlmetal /code:"c:\adw\SerialAdventureWorks.cs"
/language:csharp /serialization:Unidirectional "C:\adw\AdventureWorks_Data.mdf"
As per the msdn library there are two possible combinations: Valid
<option>: None, Unidirectional. Default value: None.>
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 so to use it
on a build process; but that’s another Post!
Source: http://msdn.microsoft.com/en-us/library/bb386987.aspx
