#TIL: Querying SQL in Powershell

I’m no powershell pro by any means, but I did manage to scrape together a “setup script” of sorts to help devs on our team set up their newly formatted laptops with all the source code and IIS settings required to work on our projects. Recently we had to add some sql queries to the script – here’s how I did it!

Thanks entirely to the code I found on Iris Classon’s site, I was reminded that you can simply create .NET objects by using the New-Object syntax. From there, it’s as easy as working with any SqlConnection object in .NET.

1
2
3
4
5
6
7
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Server=MyServer;Database=MyDatabase;Integrated Security=true"
$connection.Open()
$cmd = $connection.CreateCommand()
$cmd.CommandText = "SELECT Version FROM CurrentVersions WHERE Application = 'MyApp'"
$version = $cmd.ExecuteScalar().ToString()
$connection.Close()

Simple as that! There’s also ways to work with tables and other .NET System.Data types, for more info look at Iris’ example. Using this we looked up the current version of a test database, and then download the appropriately labeled source code from TFS to build and deploy on our local machines.

Comments