PowerShell – LINQ, what are we talking about ?

Well LINQ stands for for .net Language INtegrated Query.

LINQ to SQL provides a runtime infrastructure for managing relational data as objects (object relational mapping) without losing the ability to query.

Since PowerShell can’t handle LINQ natively Sad smile we need to call in some help of LINQPad first to get acquainted with the data and the syntax upfront.

Well the great LinqPad can handle a lot of data sources (even oData see here ) as well as conventional databases using conventional SQL syntax.

image

The beauty about this tool is that it can convert between different languages and as well between LINQ and conventional SQL by a push on the button !

image

Back to PowerShell :

To add the LINQ feature to PowerShell we need the help of the LINQ snapin

To install it, run the RegisterPowerQuery.cmd

Make sure you open a CMD prompt as Administrator go to the folder where you extracted the zip.

image

And run the command.

image

If that does not work on your machine. Use the RegisterPowerQuery.ps1 that is enclosed and run it as Adminstrator.

Alternatively, use the Windows SDK gacutil.exe command to register all the dlls.

Important : When using Powershell or IDE make sure you are starting the x64 bit version !

Next run the PS Script

CLS

add-pssnapin AlexPilotti.PowerQuery

# Set your connection string and target path (your temp folder in this sample)
$connStr = "Data Source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Northwind"
$assemblyParentPath = $Env:Temp

# Generate the assembly
New-DataModelAssembly $connStr "MyNorthwindModel" $assemblyParentPath "Northwind"

# Load your LINQ to SQL DataContext
$ctx = New-Object "Northwind.MainDataContext" $connStr

# Get the content of the Products table using the generated data model:
$ctx.Products

# Close the database connection and free al related resources
$ctx.Dispose()

Sorry but I did not have a SQL database at hand at the moment to run a live example.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: