Executing SQL the right way in PowerShell (revisited)

Almost 6 years ago I wrote this post about how I thought you should go about executing SQL statements and queries in PowerShell. It included a function which allowed you to pass a hashtable of parameter names and values.  This post has been one of the most popular on the site.

I submitted a module to the SQLPSX project called adolib which included a greatly expanded version of this function, along with others that let you invoke commands, stored procedures, and queries, as well as use the .NET SQLBulkCopy class to do high-performance data manipulation.

The SQLPSX has seen a lot of contributors come and go, but unfortunately there hasn’t been a release in several years and behind the scenes, not even very much activity.  At the recommendation of members of the PowerShell community (and after discussing with Chad Miller, the founder of SQLPSX) I copied the project to GitHub.  The repository can be found here.  I’ve opened a few issues for some “low-hanging fruit”, and welcome input from anyone.

One interesting facet of working in SQLPSX on adolib was that it was copied and modified to create MySQLLib and OracleClient modules.  These modules were based on the same code, except instead of using the SQLClient namespace they used the Oracle.DataAccess.Client and MySql.Data.MySqlClient namespaces.  Because ADO.NET works much the same no matter which provider you use, this worked fine.

I wasn’t really satisfied with copy-paste programming, though, and wrote a new version of adolib which allows you to specify the provider as a parameter when importing the module.  I’ve tested the approach with several different providers (SQL Server, MySQL, DB2, Oracle, FireBird, and even Paradox).  The code sat untouched as a fork on the SQLPSX project, so when I moved SQLPSX to GitHub, I decided to remove that fork and create a separate repository for it as POSH_ADO.  Watch for a post or two on using POSH_ADO in the next week or so.

-Mike