Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Generating Entity Classes dynamically in your build processes using PowerShell Scripts

, 8 Aug 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
CodeProject LINQ to SQL is a component of .NET Framework version 3.5 that provides a run-timeinfrastructure for managing relational data as objects. Developers using Visual Studio typically use the Object Relational Designer, whichprovides a user interface for implementing many of the features of LI

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.

image

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 :

image

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Esteban Alvarez
Software Developer (Senior) fiserv
Costa Rica Costa Rica
Net developer with more than 10 years of experience in SDLC using MS technologies such as Windows Forms and Asp.Net.
Have a background experience developing for engineering fields and the financial sector.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.141015.1 | Last Updated 8 Aug 2011
Article Copyright 2011 by Esteban Alvarez
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid