SQL

Using PowerShell to run SQL

This morning I read Scott Hanselman’s 2011 Ultimate Developer Power Tools and I was again reminded that PowerShell is an essential development tool that I haven’t learned.  I’ve given PowerShell a try on a couple different occasions all of them unsuccessful.  Today I have vowed to give PowerShell two hours for one final try.  The first thing I’d need is some concrete problem that I can use PowerShell for to improve my productivity on a daily basis.  Thinking it through I noticed that a couple times a day I find myself wanting to execute a simple piece of SQL.  Typically that workflow will look like.

  1. Launch SQL Management Studio
  2. Write and execute some SQL or run a stored procedure
  3. Close SQL Management Studio

That is pretty heavy handed way to just run some simple SQL, so I decided to give this a try doing using PowerShell instead of SQL Management Studio.

Install PowerShell

Windows 7 comes pre-loaded with PowerShell so I can skip that step.  If you’re not on Windows 7 you can find the download guide on the Widows PowerShell Blog.

Install PowerShell Community Extensions

Scott had a link to the the PowerShell Community Extensions CodePlex project, and their installation instructions were pretty straight forward.  However, I did run into one gotcha on this step.  After downloading the zip file for the installation I didn’t change the security attribute to trust the download.  That step is a must or you’ll run into trust issues when running the extensions.  To be fair the download guide on the CodePlex site pointed this out, but I was moving fast and skipped for no good reason.

Import the Community Extensions every time I run PowerShell

I simply added “Import-Module PSCX” as the first line of my Microsoft.PowerShell_profile.ps1 file (I had to create this file and place in <MyDocuments>\WindowsPowerShell\).  My full Microsoft.PowerShell_profile.ps1 is available later in this post.

Write functions allowing for easy database access

This was by far the most difficult step.  I’m not very accustomed to the PowerShell syntax and I found my self trying to write C# most of the time.  That didn’t work and caused me a lot of pain.  I followed some examples on the web, fumbled around for a while, and finally got it all working.  Microsoft is very upfront that PowerShell is a new language their getting started guide (read more here), but I still struggled with this.

Execute a command and return a dataset:

Function ExecuteDataSet([string]$connString, [string]$commandText)
{
    $conn = Get-AdoConnection -Provider $Provider -ConnectionString $connString

    $ds = Invoke-AdoCommand -ProviderName $Provider -Connection $conn -CommandText $commandText -AsDataSet

    $conn.Close()
    $conn.Dispose()

    return $ds
}

Create a simple sp_help wrapper

Function sp_help ([string]$connString, [string]$name)
{
    $ds = ExecuteDataSet $connString "sp_help $name"
    $ds.Tables[1] | Format-Table Column_Name, Type, Length, Prec, Scale, Nullable -AutoSize
}

Create a wrapper that sends in my dev connection string

$ConnectionDev="Data Source=(local);Database=my_db;UID=sa;PASSWORD=XXXX;"

Function sp_help_dev ([string]$name)
{
    sp_help $ConnectionDev $name
}

See them in action

The result were some simple functions that I could call for simple db queries.   In the below example I am able to find out about the columns of a table, and run a SQL statement to aggregate some statistics (look in full ps1 file for exec_sql functions).

RunningSQL_thumb[5]

Summary

All and all I’m a lot happier with this attempt at PowerShell then I have been in the past.  I know that I’m only scratching at the surface of its capabilities, but by spending 2 hours with PowerShell and learning some of these simple capabilities I can defiantly speed up this common daily activity.

The full text of my Microsoft.PowerShell_profile.ps1 file could be found here.