Reading database tables in Powershell

Firstly, a correction.  Last time I posted about Powershell, I came up with something that did the job, but was, well, a tat long winded.  Kind of like building a skyscraper so that you can store garden tools in the basement.  Basically I didn’t realise that the Sort cmdlet already had a –Unique switch.  Thanks to Stephen Mills for pointing it out:

import-csv c:\mydata.csv | select Category, Subcategory | sort category, subcategory -Unique | Group-Object -Property category

Now to the business at hand – reading database tables.  Consider a situation where you’re asked to read in the data in a database table into some readable form, for reference, printing or whatnot.  Rather than using SQL Management Studio to query the results, selecting the entire results grid, copying it, pasting into Excel (if available on the same machine), or into a text file, then getting it into Excel – you could just use Powershell to connect to the DB and dump out the results into a HTML table.  Behold:

1. # Parse Database tables 
2. # This will connect to a database, do a "select *" on a table or view, and produce a html file with the data in a table. 
3. # Note: don't forget to loosen up your execution policy "Set-ExecutionPolicy Unrestricted" 
4. # and the connection string is ADO style : "Data Source=database;Initial Catalog=oesc_offerman;User Id =username;Password=password;Trusted_Connection=False;" 
5.  
6. param ( 
7. $connectionString = $(throw "Specify connection string" ), 
8. $tableName = $(throw "Specify a table or view name"), 
9. $outputPath =  $(throw "Specify output path") 
10. ) 
11.  
12. echo ("Processing " + $tableName) 
13. $table = new-object System.Data.DataTable; 
14. $sqlConn = new-object System.Data.SqlClient.SqlConnection($connectionString); 
15. $sqlConn.Open(); 
16. $adapter = new-object System.Data.SqlClient.SqlDataAdapter(("select * from " +$tableName),$sqlConn); 
17. $adapter.Fill($table); 
18. $sqlConn.Close(); 
19. $table.Rows | ConvertTo-Html | out-file $outputPath