This is kind of long-winded, if you want, skip down to the code and it should be clear (I hope).

Where the story starts
As I mentioned here, the original adolib module got copied a few times and with cut-and-paste programming got turned into modules for other database platforms. While it was cool that it worked, I felt for a long time that this wasn’t the right solution. It felt like an object-oriented exercise where a parent class (abstract?) was called for to implement the ADO.NET pattern. Then, “child” modules could somehow cause the parent module functions to use the correct platform.

An awesome clue!
When I went looking for a way to build a better solution, I found Invoke-ADOCommand in the PowerShell Community Extensions project. It used ADO.NET providers as a key to implement a “generic” cmdlet for querying different platforms. If I recall correctly (it has been several years now), I think someone on StackOverflow pointed me there.

Going my own way
So I knew the key ingredient in the solution would be ADO.NET providers, but how to use those and preserve the flow of the adolib module? I wanted the new solution to work as closely as possible to the old solution for a few reasons:

I have used a module (which I wrote) similar to adolib at work for quite some time and I really like it.

I didn’t want people who had used adolib to have to relearn anything

I didn’t want to significantly rewrite the code in adolib

Introducing POSH_Ado
The solution I came up with is a parameterized module called POSH_Ado which allows you to specify the provider that you want to use. If you compare the functions in POSH_Ado to the corresponding functions in adolib, you will see that they are very similar, with a few extra variables to help deal with variations in the syntax on different platforms, and a modified way of creating new platform-specific objects.

Using POSH_Ado
In order to use POSH_Ado, you need to install the POSH_Ado module, as well as the helper module for the specific platform(s) that you want to access. For the purposes of this article, I will be using POSH_Ado_SQLServer.

Once the modules are installed in an appropriate location, you simply import the platform-specific module. The module in turn imports the POSH_Ado module with a platform-specific prefix and sets options which are appropriate to the platform you’re using.

Shut up! Let’s see some code!
To illustrate, I will recreate the commands from the previous post (about adolib) using POSH_Ado_SQLServer. The only changes here are the “SQLServer” prefix in the cmdlets, and the change from Invoke-SQL to Invoke-Command. Also, I’m lazy so the text is just copied from the previous post.

Query a database on the local machine (.) with an windows-authenticated ad-hoc connection.

invoke-SQLServerQuery -sql 'select top 1 * from AdventureWorks2012.Person.Person' -server . 

Query a database on the local machine (.) with a windows-authenticated persistent connection

$conn=new-SQLServerConnection -server '.'

invoke-SQLServerQuery -sql 'select top 1 * from AdventureWorks2012.Person.Person' -connection $conn

Note that we can include the database in the connection as well:

$conn=new-SQLServerConnection -server '.' -database AdventureWorks2012
 
invoke-SQLServerQuery -sql 'select top 1 * from Person.Person' -connection $conn

If we need to use SQL Security, we can supply a -User and -Password (in plaintext :-()

$conn=new-SQLServerConnection -server '.' -User MyUser -Password P@ssword

We can run sql statements that don’t return rows (like INSERT, UPDATE, DELETE) with Invoke-SQL:

invoke-SQLServerCommand -sql "Update Person.Person set MiddleName='F' where BusinessEntityID=@ID" -parameters @{ID=1} -connection $conn

Here I’m also using SQL parameters. It’s pretty simple. You just include the parameters prefixed with @ in the SQL statement and then provide a -Parameters hashtable including values for each of the parameters you use. Here I only used a single parameter (ID), so there was only one entry in the hashtable. Invoke-SQL returns the number of rows affected by the SQL statement, by the way.

There’s a cmdlet for executing Stored Procedures called Invoke-SQLServerStoredProcedure (with parameters that match Invoke-SQLServerQuery):

invoke-SQLServerStoredProcedure -storedProcName sp_who2 -connection $conn

What’s next?
So, all of that work and I’ve basically got a module which works just like adolib did. Why bother? In the next post, I’ll show you:

What the POSH_Ado_SQLServer module looks like

How the POSH_Ado_Oracle module is different from POSH_Ado_SQLServer

All of the other platforms that I’ve tested this code on. 🙂

I think once you’ve seen those three things, you’ll agree that this was a worthwhile exercise.

For now, that’s all.

Let me know what you think!

-Mike

P.S. You can find the source here