DbData

SQL Server interface for PowerShell encompassing SMO and resilient ADO.NET connections for Enterprise use

View the Project on GitHub codykonior/DbData

DbData PowerShell Module by Cody Konior

Build status

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.

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

Requirements

Demo

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