In the last post, I gave a quick history of my involvement with SQL and SQLPSX. A big part of that was the ADOLib module in SQLPSX, which I haven’t ever really explained here. Since it has been almost 6 years now, I don’t see how I have managed to skip it.
This post should correct that oversight.
First of all, SQLPSX is mainly about administering SQL. ADOLib, on the other hand is specifically geared towards using SQL as a service, executing queries and SQL commands as reasonably possible.
To that end, here are some examples of ADOLib usage:
Query a database on the local machine (.) with an windows-authenticated ad-hoc connection.
invoke-query -sql 'select top 1 * from AdventureWorks2012.Person.Person' -server .
Query a database on the local machine (.) with a windows-authenticated persistent connection
$conn=new-connection -server '.' invoke-query -sql 'select top 1 * from AdventureWorks2012.Person.Person' -connection $conn
Note that we can include the database in the connection as well:
$conn=new-connection -server '.' -database AdventureWorks2012 invoke-query -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-connection -server '.' -User MyUser -Password P@ssword
We can run sql statements that don’t return rows (like INSERT, UPDATE, DELETE) with Invoke-SQL:
invoke-sql -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-StoredProcedure (with parameters that match Invoke-Query):
invoke-storedprocedure -storedProcName sp_who2 -connection $conn
The final important piece in ADOLib is Invoke-BulkCopy which is used to move lots of data quickly between ADO providers. You will usually be moving data between different servers (because there are lots easier ways to move data on the same server), but in this example we will use the same server as source and destination.
I’ve created a copy of the DatabaseLog table in AdventureWorks2012 and called it DatabaseLog2. Databaselog2 is empty, but we can use the following commands to copy the data.
$cmd=new-sqlcommand -connection $conn -sql 'Select * from DatabaseLog' invoke-bulkcopy -records $cmd -server '.' -table DatabaseLog2
Here you can see that I am creating a command object with the source query but not executing it. I pass it into Invoke-BulkCopy so the .NET SQLBulkCopy class can take care of handling the rows. I also have to provide new connection information to the cmdlet. I’m not sure why I didn’t allow you to pass an established connection in, but I’ve created an issue so I can follow up on that.
There is more in ADOLib (including transaction support and output parameters), but I’ll let you look inside and see them.
Next time I’ll show how these same kinds of things can be accomplished using the new POSH_Ado module.
Let me know what you think in the comments
-Mike
P.S. ADOLib can be found here
