DbData PowerShell Module by Cody Konior

Read the CHANGELOG

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.

Installation

• Install-Module DbData

Requirements (3.x series)

Please note: There are major breaking changes in DbData 3.0 from previous versions of DbData. This build is experimental.

• Requires PowerShell 7.5 or later.
• Requires the SqlServer module.

Recommendations if you’re using Microsoft Entra ID:

if (-not $PSDefaultParameterValues.ContainsKey("New-DbConnection:Authentication")) {
    $PSDefaultParameterValues.Add("New-DbConnection:Authentication", "ActiveDirectoryDefault")
}
if (-not $PSDefaultParameterValues.ContainsKey("Get-DbSmo:Raw")) {
    $PSDefaultParameterValues.Add("Get-DbSmo:Raw", $true)
}

Requirements (1.x and 2.x series)

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

• 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