PowerQuery - Home

 

PowerQuery

MS SQL Server + PowerShell + LINQ

The easy way to access your database with PowerShell 1.0 and 2.0
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 queriers, 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.

PowerQuery - Home