Execute SQL Query with PowerShell

Scripting is very powerful. And for me, one of the best scripting languages is PowerShell (PoSH). Yes, PoSH takes a bit of getting used to, but once you pass the initial learning curve, you end up with a powerful tool in your hands. One thing that I love about PoSH is a little secret that not many people know:

PowerShell allows you to import .NET namespaces and libraries and, in effect, you can write C# code to solve any problem you may have.

The PowerShell's libraries, cmd-lets are very comprehensive but you can always come against something not "natively" available. And this is where PoSH shines with its interoperability with the .NET framework.

Today I'll show you how to use PoSh to connect to SQL Server, run a query and load the data to a DataTable. The script to run the whole thing is attached below:

Notice that in this particular example I am using a DataReader to populate the DataTable. Anyone that has worked with ADO.NET will be quite familiar with the concept. Alternatively, you can use a DataAdapter to populate a DataSet if your result set contains multiple tables.

An example using a DataAdapter and DataSet is attached below:

If you need to run adhoc queries to pull data off the database and use quickly in a script ect, PowerShell is your friend. I hope this helps with your scripting requirements.