about_DbData.help.txt
|
TOPIC
about_DbData SHORT DESCRIPTION DbData, is the awesome replacement for Invoke-Sqlcmd and Invoke-Sqlcmd2. LONG DESCRIPTION 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 procedure, etc, while retaining full message output (to the verbose stream and an optional arraylist variable) * Read, and alter, table data * Bulk copy tables * Optionally use SQL transactions * Optionally wrap all of the above with retries for deadlocks and timeouts 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. EXAMPLE #1 Connect to a database and get rows back. $serverInstance = ".\SQL2016" New-DbConnection $serverInstance master | New-DbCommand "Select * From sys.master_files" | Get-DbData EXAMPLE #2 Connect to a database and get multiple result sets into different tables. $serverInstance = ".\SQL2016" $dbData = New-DbConnection $serverInstance master | New-DbCommand "Select * From sys.databases; Select * From sys.master_files" | Get-DbData -TableMapping "Databases", "Files" -OutputAs DataSet $dbData.Tables["Databases"] $dbData.Tables["Files"] EXAMPLE #3 Connect to a database, begin a transaction, add data, and then rollback. $serverInstance = ".\SQL2016" $dbData = New-DbConnection $serverInstance msdb | New-DbCommand "Select * From dbo.suspect_pages" | Enter-DbTransaction -PassThru | Get-DbData -OutputAs DataTables # Add a record $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 LINKS https://github.com/codykonior/DbData |