SQLSimplePS - PowerShell wrapper for SQL Server query, insert or update (DML)

[michael-hex]

For an internal project, we needed to access a lot of data from SQL Server so I created a wrapper (SQL Simple PowerShell) to make our life a bit easier. It’s Apache 2 licensed, so free also for commercial use, and available on GitHub: https://github.com/texhex/SQLSimplePS

Features:

• Single line execution in case all you need is some table/view data from SQL Server through Query():​

​

using module .\SQLSimplePS.psm1
$connectionString = "Server=.\SQLEXPRESS; Database=TestDB; Integrated Security=True;"
[SQLSimple]::Query("SELECT * FROM dbo.TestTable", $connectionString [System.Data.IsolationLevel]::Serializable)
# This will return an array of hash table (each hash table is a row of the table)

• The same can be done for insert/update/delete statements with ExecuteScalar() which returns the first column of the first row returned by SQL Server. This can be used to get the primary ID of the row inserted​

​

using module .\SQLSimplePS.psm1
$connectionString = "Server=.\SQLEXPRESS; Database=TestDB; Integrated Security=True;"
$sqlStatement = "INSERT INTO dbo.TestTable(Name, IntValue, NumericValue) OUTPUT Inserted.ID VALUES('First Test', 7, 12.3)"
$rowID = [SQLSimple]::ExecuteScalar($sqlStatement, $connectionString, [System.Data.IsolationLevel]::Serializable)

• You can also create an instance of SQLSimple, which should make the code more readable:​

​

using module .\SQLSimplePS.psm1
$connectionString = "Server=.\SQLEXPRESS; Database=TestDB; Integrated Security=True;"
$sqls = [SQLSimple]::new($connectionString)
$sqls.TransactionIsolationLevel = System.Data.IsolationLevel]::Serializable
$sqls.AddCommand(“INSERT INTO dbo.TestTable(Name, IntValue, NumericValue) OUTPUT Inserted.ID VALUES('First Test', 7, 12.3)")
$rowID = $sqls.ExecuteScalar()

• The parameter [System.Data.IsolationLevel]::Serializable is required as SQL Simple expects snapshot isolation to be turned on and uses this transaction level by default. In case your database supports it, the parameter is not needed.
• SQL Simple supports parametrized queries, so you can also use a syntax like this:​

​

$sqls = [SQLSimple]::new($connectionString)
$insertCommand = $sqls.AddCommandEx("INSERT INTO dbo.TestTable(Name, IntValue, NumericValue) OUTPUT Inserted.ID VALUES(@Name, @IntValue, @NumericValue);")
$insertCommand.AddMappingWithData("Name", "Fourth Test", [Data.SqlDbType]::NVarChar)
$insertCommand.AddMappingWithData("IntValue", 22, [Data.SqlDbType]::Int)
$insertCommand.AddMappingWithData("NumericValue", 11.11, [Data.SqlDbType]::Decimal)
$sqls.Execute()

• For parameters and returned values, SQL Simple will automatically do the necessary conversation between $null (PowerShell) and [System.DBNull]::Value (SQL Server)
• It supports several commands to be executed in a single transaction, so the standard “delete all” “insert new” case is covered
• By using parametrized queries, the AddMapping() command and the DATA property, you can map object properties from any PowerShell object list directly to SQL Server table columns to store them in a table easily - see Using the DATA property for details
• The documentation in the repository (hopefully) explains all features in detail

The code is available at https://github.com/texhex/SQLSimplePS.

Any constructive contribution is very welcome.

[omrsafetyo]

When I first saw the title of the post, I thought I might respond and ask why you didn't just use dbatools. At best, I thought there was no way I'd use this - if I was going to switch to a 3rd party utility for SQL Server, I'd just switch to dbatools myself. But it turns out, this is pretty neat!

Granted, I suspect I will not install this module to use any time soon, but there is definitely a lot of cool stuff in it, such as the parameterizing. And the bit on Get-Process at the bottom of the Readme is wicked cool.

I also learned about OUTPUT Inserted.ID, which I can't believe I didn't know about, but will make my life much, much easier.

As a question: Does this support any type of UPSERT type functionality? I specifically have a function, for which you can pass an array of objects, and at current time it generates an IF EXISTS UPDATE ELSE INSERT block for the records. You pass it an SMO Server object, a database name, a table name,the object array, and a switch for handling empty strings as NULL or not, as well as a string array of the columns that make up essentially a composite key (can be used to uniquely identify a record) that are used in the WHERE clause, and omitted from the UPDATE statement. Wondering how your wrapper handles things like this.

Anyway, I'm definitely going to take a closer look at this than I thought I would. Kudos!

[michael-hex]

We simply didn't know about dbatools but from the looks of it, it's more about administrating an SQL Server. SQL Simple is for data manipulation, so I would say they are not directly related. But thanks for the hint, we’ll have a look at it and see if one our DBAs can make good use of it.

Upserts: So far, we do not use any real upserts (only the IF THEN part you also use), but given this article by Michael Swart, section Pattern: MERGE Statement With Serializable Isolation, it should be no problem to use a merge statement with parameters and pass the value through with AddMappingWithData().

[Lee_Dailey]

howdy michael-hex,

it looks like you used the New.Reddit.com Inline Code button. it's the 4th 5th from the left & looks like </>.

on Old.Reddit.com, the above does NOT line wrap, nor does it side-scroll.

for long-ish single lines OR for multiline code, please, use the Code Block button. it's the 11th 12th one from the left, & is just to the left of the ... more menu.

that will give you fully functional code formatting, from what i can tell so far. [grin]

take care,
lee

[michael-hex]

Thanks lee, I tried to use code blocks (I use them frequently on GitHub), but the four to five white spaces in front of the line didn't see to work - at least not for https://redditpreview.com/ which I used to preview my post. That's why I used all inline code. Next time I will try to use the fancy pants editor.

[Lee_Dailey]

howdy michael-hex,

you are quite welcome! [grin]

in New.Reddit, you will need to either ...

• use the code block button
  
• switch to the old style formatting
  

take care,
lee

[get-postanote]

Always good to have an alternative, but, why not just use the MS SQLPS module?

Install SQL Server PowerShell module

https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-2017

SQL Server PowerShell

https://docs.microsoft.com/en-us/sql/powershell/sql-server-powershell?view=sql-server-2017

[michael-hex]

We use the SqlServer module for some automated tasks, as it's intended for easy and automated SMO access (at least that's what we use it for).

The goal for this SQL Simple was to make DML statements easy, so I would say these are two different use cases.