Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / PowerShell

Generating Entity Classes Dynamically Using Powershell Scripts

Rate me:
Please Sign up or sign in to vote.
4.11/5 (2 votes)
6 Aug 2011CPOL3 min read 21.7K   123   11  
Shows how to generate entity classes by calling an utility from a powershell script

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

Image 1

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:

Image 2

The output should be:

Image 3

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.

Image 4

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" 

Image 5

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 6

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

License

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


Written By
Software Developer (Senior)
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --