PowerQuery

MS SQL Server PowerShell LINQ

The easy way to access your database with PowerShell 1.0 and 2.0

RC2 Released! Added support for "sql_variant" 

Follow alexpilotti on Twitter


PowerQuey dynamically generates a LINQ to SQL assembly on the fly, containing the data model and a DataContext, ready to be used or saved for later usage.

This is achieved by reading your database schema and directly emitting the .Net IL, generating an assembly DLL. It allows you to transparently perform queries, insert/update/delete commands and execute stored procedures using the shell!

The following PowerShell sample generates and loads a LINQ to SQL data model assembly DLL for the Northwind database in your temporary directory.

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()



As you can see, the data model is generated without the need of Microsoft SqlMetal.exe or the integrated Visual Studio Designer.
The inspiration for this solution came from using Joseph Albahari's superb LINQPad. One of the most useful tools to learn LINQ.

Installation

Requirements: .Net Framework 3.5
http://www.microsoft.com/DOWNLOADS/details.aspx?familyid=AB99342F-5D1A-413D-8319-81DA479AB0D7

To install just run RegisterPowerQuery.cmd
To uninstall run UnregisterPowerQuery.cmd

The provided RegisterPowerQuery.ps1 script will handle the necessary snap in registration and GAC deployment.

Data model

Currently, the data model is generated by including all the tables in the database provided by your connection string (Note: Support for views and stored procedures will be included with the next beta release).


PowerQuery1.jpg

If you are new to ORMs and to LINQ to SQL I suggest you to read Scott Gu's excellent tutorials:
http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

Filtering, sorting, etc

Here's how we can perform a query with filters, sorting and projections:

$ctx = New-Object "Northwind.MainDataContext" $connStr

# Get some products
New-Query $ctx.Products | 
		  Where-LinqObject "CategoryId = 2 And UnitPrice > 20" | 
		  Sort-LinqObject "UnitPrice Desc"

# Get an employee		  
New-Query $ctx.Employees | Where-LinqObject "LastName = ""Davolio""" | 
          Select-LinqObject "New(EmployeeID, FirstName, LastName)"



The LINQ syntax is sadly not available in PowerShell and we need to look for alternatives. Luckily, Microsoft provides a sample called Dynamic LINQ, which fits our needs and thus has been included in this project.

Please read Scott Gu's post if you like to learn more about it:
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

We are not yet done! PowerShell does neither support C# 3.0's Extension Methods used by Dynamic LINQ.

We get around this issue by employing Bart de Smet's simple and brilliant solution:
http://bartdesmet.net/blogs/bart/archive/2008/06/07/linq-through-powershell.aspx

Which is where we get the following cmdlets:

New-Query, Where-LinqObject, Sort-LinqObject, Select-LinqObject, Take-LinqObject, Skip-LinqObject, Execute-Query, Defer-Query

That's it! More cmdlets will be added in the near future to better exploit LINQ's features in the PowerShell.

Navigation

Navigation to related tables is performed by LINQ to SQL, all you have to do is to "navigate" the relation properties and the data will be retrieved automatically.
The name of the relation property is set to the name of the target table:

$p = New-Query $ctx.Products | Where-LinqObject "UnitPrice > 20"

# Show the product's category by navigating the "Categiories" property
$p | % { "Product:  " + $_.ProductName; "Category: " + $_.Categories.CategoryName }



Please read Scott Gu's blog about LINQ to SQL if you are new to this subject!

Update data

Updating data using an ORM, like (LINQ 2 SQL, the Entity Framework or NHibernate) is really easy. You just set the property values on your obejcts and finally submit the updates to the db

# increase all product's unit price by 20%
$ctx.Products | % {$_.UnitPrice *= 1.2}

# Once you're done, submit your changes to SQL Server
$ctx.SubmitChanges()

Insert data

Just create a new object, let the DataContext know about it and sumbit the changes to the database once you are done.

$emp = New-Object "Northwind.Employees"
$emp.FirstName = "Homer"
$emp.LastName = "Simpson"
$emp.BirthDate = "1956-03-13"

$ctx.Employees.InsertOnSubmit($emp)
$ctx.SubmitChanges()

 

Delete data

Also really easy, using LINQ to SQL's DeleteOnSubmit

# Remove all employees whose last name matches. D'oh!
$emp = new-query $ctx.Employees | Where-LinqObject "LastName = ""Simpson"""

$emp | % {$ctx.Employees.DeleteOnSubmit($_) }

$ctx.SubmitChanges()

 

Execute stored procedures

PowerQuery is able to generate methods for any stored procedure (T-SQL, CLR) or function (scalar, table, inline table, CLR scalar, CLR table).

To generate the procedures / functions call methods, just set IncludeProcedures to true in a call to New-DataModelAssembly.

In the following example a stored procedure named sptest_ with an input and an output parameter gets called.

New-DataModelAssembly $connStr $assemblyName $assemblyParentPath $namespace $false $false $true

$ctx = New-Object "Northwind.MainDataContext" $connStr

$a = 0
$ctx.sp_test(100, [ref] $a)
$a



Performance

One of the benefits of using an ORM like LINQ to SQL is to avoid messing with SQL statements. If you need to check the generated statements, just set the Log property of the DataContext sto something like Console.Out or a file Stream.

$ctx.Log = [System.Console]::Out

FAQ

Q: Do I Need to generate the data model assembly each time?

A: No you don't. Once generated you can load it as any other assembly:

$assemblyParentPath = $Env:Temp
$assemblyPath = Join-Path $assemblyParentPath  "MyNorthwindModel.dll"

[System.Reflection.Assembly]::LoadFile($assemblyPath)


Q: Why did you use LINQ to SQL instead of ADO Entity Framework, NHibernate or (... put your favorite ORM here)?

A: Because it's really lightweight and fits our needs. In a standard .Net application it's quite limited but for scripting purposes IMHO fits the bill quite well.
We will probably add support for the ADO Entity Framework or NHibernate to access different database servers (MySQL, Oracle, PostgreSQL, etc).

Q: Why do I have to install PowerQuery assemblies in the GAC?

A: Becouse the assembly ILGenerator code is executed in a separate AppDomain. This is necessary in order to avoid loading it in the current app domain as needed and to overcome some limitations in the PowerShell, which prevent the execution of some dynamically generated code.

Copyright (C) Alessandro Pilotti 2009

Last edited Oct 31, 2012 at 10:45 AM by alexp, version 15