DbData PowerShell Module by Cody Konior

Description

DbData is an awesome replacement for Invoke-Sqlcmd and Invoke-Sqlcmd2.

Invoke-Sqlcmd is littered with bugs, both past and current. DbData fulfills the promise of Invoke-Sqlcmd with better PowerShell semantics, though without trying to be a drop-in replacement.

Safely build connection strings and connections
Construct commands with really injection-safe parameters
Execute statements, stored procedures, etc
Read and alter (insert, update, delete - and upsert!) table data
Bulk copy tables
Optionally wrap all of the above with SQL transactions
Optionally wrap all of the above with retries for deadlocks and timeouts

It also provides quick access to SMO and WMI objects.

Please note: There are minor breaking changes in DbData 1.5 from previous versions of DbData.

Installation

Install-Module DbData

Requirements

Requires PowerShell 2.0 or later.
Requires .NET 3.5 or later installed.
Options for New-DbConnection vary between .NET Framework versions. Some were added as recently as 4.6.1.

Demo

Making a connection.
Forming a command and retrieving data.
Creating SMO and WMI objects.

Further Examples

Connect to a database and get rows back.

$serverInstance = "SEC1N1"
New-DbConnection $serverInstance master | New-DbCommand "SELECT * FROM sys.master_files;" | Get-DbData

Connect to a database and get multiple result sets into different tables.

$serverInstance = "SEC1N1"
$dbData = New-DbConnection $serverInstance master | New-DbCommand "SELECT * FROM sys.databases; SELECT * FROM sys.master_files;" | Get-DbData -TableMapping "Databases", "Files" -As DataSet
$dbData.Tables["Databases"]
$dbData.Tables["Files"]

Connect to a database, begin a transaction, add data, and then rollback.

$serverInstance = "SEC1N1"
$dbData = New-DbConnection $serverInstance msdb | New-DbCommand "SELECT * FROM dbo.suspect_pages;" | Enter-DbTransaction -PassThru | Get-DbData -As DataTables

# Add a record
[void] $dbData.Alter(@{
        database_id = 1
        file_id = 1
        page_id = 1
        event_type = 1
        error_count = 1
        last_update_date = (Get-Date).ToDateTime($null)
    })
Exit-DbTransaction $dbData -Rollback