SQL Server interface for PowerShell encompassing SMO and resilient ADO.NET connections for Enterprise use
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
.github
.vscode
DbData
Images
Tests
.gitattributes
CHANGELOG.md
DbData.code-workspace
LICENSE
README.md
_config.yml
appveyor.yml

README.md

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.

  • 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.

    DbData makes a connection

  • Forming a command and retrieving data.

    DbData runs a query

  • Creating SMO and WMI objects.

    DbData connects over SMO and WMI

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