I haven’t done a serious learning project for a while so this week I decided to play around with Windows Server 2008 and PowerShell. I’ll post some thoughts about Server 2008 soon (at first glance it looks terrific once you get past the fact that IIS is now a "role" instead of something you install). But I wanted to post a PowerShell example first because I wanted to do something with it that I couldn’t find information about anywhere — specifically, perform a database query and dump the contents to the console. (Later we’ll get fancy and dump it to HTML or send it somewhere via email, etc.)
PowerShell can invoke any .NET object, so I knew it was possible to do database stuff with a PowerShell script, but I couldn’t find any examples anywhere, so here’s what I cooked up via trial and error.
The first step is to create a database connection:
[system.reflection.assembly]::LoadWithPartialName("MySql.Data")
$cn = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
The syntax to load a .NET assembly in PowerShell is pretty awkward; couldn’t they have provided a Get-Assembly cmdlet? (I suspect that thousands of PowerShell users have already written that one already.) Also, you have to know the assembly name of your ADO.NET provider (which, for MySql, is "MySql.Data," not "MySql.Data.MySqlClient," which was my first guess). The thing to remember is that loading an assembly is syntactically different than including a namespace even though the objective is sort of the same.
Fortunately this is the only part of the process that I didn’t get right the first time, and I had it resolved after a few minutes of experimentation.
I should mention that I’m using the MySql data provider because we use MySql on Approver.com, but you can obviously use any ADO.NET provider you want here. This assumes that your ADO.NET provider is installed in the GAC; if you need to make an explicit reference to an assembly in the file system the syntax is different.
Next, open the database connection:
$cn.ConnectionString = "SERVER=localhost;DATABASE=mydb;UID=xxx;PWD=yyy"
$cn.Open()
If you want, you can check the value of $cn.State here to make sure it worked. Then execute the query:
$cm = New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand
$sql = "SELECT ID, FirstName, LastName FROM Person LIMIT 50"
$cm.Connection = $cn
$cm.CommandText = $sql
$dr = $cm.ExecuteReader()
ADO.NET programmers know that there are lots of ways to do this that require fewer lines of code, but I like to assign everything out explicitly using properties of the ADO.NET Connection and Command objects.
Finally we iterate over the results and close the connection:
while ($dr.Read())
{
write-host $dr.GetInt32(0) " " $dr.GetString(1) " " $dr.GetString(2)
}
$cn.Close()
This is not too different than what a C# developer is accustomed to, particularly reading out the data. I like that you can just pass a big list of values to write-host without having to explicitly concatenate or convert to strings here. PowerShell figures out what you want to do and does the right thing.
On Approver.com we have a bunch of utilities that we use to monitor system health and site activity (like how many people registered that day, whether people are discovering and using certain features of the site, etc.). Every web site should crunch these numbers frequently (otherwise you have no idea whether the work you’re doing matters to your users).
I think I’m going to eventually migrate our various statistical and maintenance utilities to Powershell. The power of the .NET framework combined with the convenience of script is going to come in handy for a lot of stuff.
