Most of the PowerShell I write ends up as Functions: rather than scripts and it’s worth differentiating between the two.
- A function is something which can be called from somewhere else.
- A script is carries out a particular task (which might be to define some function(s) , or might be done with functions )
Several of my 10 tips for better functions are about being smart with inputs and outputs (which is less important for one off scripts). I should have included an extra point “Ask if constants should really be defaults for parameters” . If I notice “I am solving one specific case of X, with a value set to Y, so I can solve other X-problems if I allow the value to be changed.” it makes something reusable out of a one-off. For example, I put a wrapper round PowerShells great “Select-String” cmdlet to search PS1 files , then I realised that I might want to search .SQL or .XML files, but usually it would be PS1. So “*.PS1” became the default value for a -include parameter.
Recently I put together a simple function named Get-SQL – it uses ODBC to send SQL commands – in my case to MySQL on a Linux box but it could be any ODBC source. To avoid constantly creating and tearing sessions, it saves an ODBCconnection object in a global variable between invocations . Once it has been run for the first time in a session I can type Get-SQL “command” and use the existing connection: and I’ve made sure SQL statements can be piped in (that’s what I mean by being smart about inputs).
In it’s basic form Get-SQL takes 3 parameters, a connection string ($connection) a SQL statement ($SQL) and -ForceNew switch, which remakes the connection if it already exists if ($forceNew -or (-not $global:ODBCconn)) {
$global:ODBCconn = New-Object System.Data.Odbc.OdbcConnection($connection)
$global:ODBCconn.open()
}
if ($sql) {
$adapter = New-Object system.Data.Odbc.OdbcDataAdapter(
New-Object system.Data.Odbc.OdbcCommand($sql,$ODBCconn))
$table = New-Object system.Data.datatable
[void]$adapter.fill($table)
$table
}
else {$ODBCconn }
So, if a SQL statement is passed, any data rows it generates will be returned; but if I am just setting up a connection for the rest of my session the ODBC connection object is returned.
PS C:\Users\james\Documents\windowsPowershell> Get-SQL -connection "DSN=foo-Customer5"
ConnectionString : DSN=foo-Customer5
ConnectionTimeout : 15
Database : foo
DataSource : 192.168.1.234 via TCP/IP
ServerVersion : 5.0.77
Driver : myodbc5.dll
State : Open
Site :
Container :
I want to show something shorter and simpler: I could finish with $ODBCConn | format-table DataSource , Database, State
that might be acceptable now, but I don’t know what problems I’m storing up for the future by not returning the full ODBC object.
The better option is to write a Formatting XML file – so the object remains the same but PowerShell displays it the way I want. But writing and debugging the XML longhand is tedious. So the temptation is to go with the format-table
method and fix things up later; but there is a short-cut to writing the XML which avoids parking the problem for later.. Richard mentions that it came up during the PowerShell Deep Dive in Vegas, And I downloaded it from here. (Slightly oddly you need to put Function New-TableFormat { }
into the editor, and paste the code block in then run it)
To use it you get the output the way you want with Format-Table
and then replace “Format-Table” in the command line with New-TableFormat
(with a couple of extra bits of finesse if needed). So it took about a minute to get the ODBC connection to display like this
Data Source Database State
----------- -------- -----
192.168.1.234 via TCP/IP foo Open
I’ve also created wrappers around /n software’s Netcmdlets to make it easier to work with the linux server, and in one of those I call Connect-SSH -Server $server -Credential $credential -Force
which would normally output this :
AuthMode : Password
CertPassword :
CertStore : MY
CertStoreType : User
CertSubject :
Config :
Credential : System.Management.Automation.PSCredential
FirewallHost :
FirewallPassword :
FirewallPort : 0
FirewallType : None
FirewallUser :
Force : True
LocalIP :
Password :
Port : 22
Server : 192.168.1.234
ShellPrompt :
SSHAccept :
Timeout : 10
User :
It took about Two minutes to get Add-Member
to show what the host thinks its name is, and create a formatting XML file to get the result to look like this:
Connected to Port Host name User name
------------ ---- --------- ---------
192.168.4.71 22 foo.contoso.com \root
It was all of another minute to create a manifest file to load my scripts & formatting XML as a PowerShell module and have everything neat and tidy. Cracking stuff
[…] sort the entries into order, and add properties for the directory and fully qualified path and used New-TableFormat to give me the start of a formatting XML file to display the files nicely . The function also […]
Pingback by Enhancing the NetCmdlets « James O'Neill's Blog — August 23, 2011 @ 1:11 pm