< Back
Get-DbData
Post
NAME Get-DbData
SYNOPSIS
Execute a command against a SQL Server instance.
SYNTAX
Get-DbData [-SqlCommand] <SqlCommand> [[-TableMapping] <String[]>] [[-OutputAs] <Object>] [[-InfoMessageVariable]
<Object>] [-Alter] [[-AlterCollectionSeparator] <Object>] [[-RetryCount] <Object>] [[-RetrySeconds] <Object>]
[<CommonParameters>]
DESCRIPTION
Executes a command with output sent to the Verbose stream. Commands can be non-queries, scalars, or return rows
(the default), data tables, or a data set.
For data tables and dataset, if the command is a simple Select statement against a single table (and optional
simple Where clause), then Insert and Update/Delete statements (if a PK exists) are generated and an Alter()
function added to the first data table.
This can then be executed to trigger deletions (after modifying the rows in the table), or upserts (by passing in
a hash table of column names and values).
PARAMETERS
-SqlCommand <SqlCommand>
Required? true
Position? 1
Default value
Accept pipeline input? true (ByValue)
Accept wildcard characters? false
-TableMapping <String[]>
An optional list of custom table names to use for the result set, in order. By default these are Table,
Table1, Table2, etc.
Required? false
Position? 2
Default value @()
Accept pipeline input? false
Accept wildcard characters? false
-OutputAs <Object>
The type of data to return. It can be scalar (the first column of the first row of a result set), a non query
(an integer), datarow, pscustomobject, datatable, or a dataset.
Required? false
Position? 3
Default value PSCustomObject
Accept pipeline input? false
Accept wildcard characters? false
-InfoMessageVariable <Object>
An object of System.Collections.ArrayList which will be appended with all info message objects received while
running the command, in addition to formatted versions written to the verbose stream.
Required? false
Position? 4
Default value (New-Object System.Collections.ArrayList)
Accept pipeline input? false
Accept wildcard characters? false
-Alter [<SwitchParameter>]
Fil the schema, create a command builder, and add an Alter function to the first returned table.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-AlterCollectionSeparator <Object>
A character to join any non-string collections that are passed in when calling .Alter() on a table. For
example, for joining @() and ArrayList. Empty collections are converted to DBNull.
Required? false
Position? 5
Default value [Environment]::NewLine
Accept pipeline input? false
Accept wildcard characters? false
-RetryCount <Object>
Required? false
Position? 6
Default value
Accept pipeline input? false
Accept wildcard characters? false
-RetrySeconds <Object>
Required? false
Position? 7
Default value
Accept pipeline input? false
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
Pipe in an SqlCommand like from New-DbCommand.
OUTPUTS
See the OutputAs parameter.
NOTES
Be careful about supplying additional columns which are not in the destination table. These are ignored.
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>$serverInstance = ".\\SQL2016"
New-DbConnection $serverInstance master | New-DbCommand "If Object_Id('dbo.Moo', 'U') Is Not Null Drop Table
dbo.Moo; Create Table dbo.Moo (A Int Identity (1, 1) Primary Key, B Nvarchar(Max)); Dbcc Checkident('dbo.Moo',
Reseed, 100);" | Get-DbData -As NonQuery | Out-Null
$dbData = New-DbConnection $serverInstance master | New-DbCommand "Select * From dbo.Moo;" | Get-DbData -As
DataTable
$dbData.Alter(@{ B = "AAA" }) | Out-Null
$dbData.Alter(@{ B = @("AAA", "BBB", "CCC") }) | Out-Null
$dbData.Alter(@{ B = @(000, 001, 002) }) | Out-Null
$dbData.Alter(@{ B = @() }) | Out-Null
$dbData.Alter(@{ A = 100; B = "CCC" }) | Out-Null
$dbData.Alter(@{ A = 4; B = "DDD" }) | Out-Null
$dbData | Format-List
Results:
A : 100
B : CCC
A : 101
B : AAA
BBB
CCC
A : 102
B : 0
1
2
A : 103
B : DDD
This drops and creates a dummy table with an identity column seeding at 100. It then inserts a row, two array
(collection) rows, updates the first row, then attempts a fixed identity insert.
The result is four rows, with the collections concatenated, and the special identity of the last column discarded
(but the properly allocated identity value returned).
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>$serverInstance = ".\\SQL2016"
$infoMessage = New-Object System.Collections.ArrayList
New-DbConnection $serverInstance master | New-DbCommand "Print 'Moo';" | Get-DbData -As NonQuery
-InfoMessageVariable $infoMessage | Out-Null
$infoMessage
Results:
Source : .Net SqlClient Data Provider
Number : 0
State : 1
Class : 0
Server : .\\SQL2016
Message : Moo
Procedure :
LineNumber : 1
Stores objects from the message stream into a variable (which is overwritten).
RELATED LINKS
SYNOPSIS
Execute a command against a SQL Server instance.
SYNTAX
Get-DbData [-SqlCommand] <SqlCommand> [[-TableMapping] <String[]>] [[-OutputAs] <Object>] [[-InfoMessageVariable]
<Object>] [-Alter] [[-AlterCollectionSeparator] <Object>] [[-RetryCount] <Object>] [[-RetrySeconds] <Object>]
[<CommonParameters>]
DESCRIPTION
Executes a command with output sent to the Verbose stream. Commands can be non-queries, scalars, or return rows
(the default), data tables, or a data set.
For data tables and dataset, if the command is a simple Select statement against a single table (and optional
simple Where clause), then Insert and Update/Delete statements (if a PK exists) are generated and an Alter()
function added to the first data table.
This can then be executed to trigger deletions (after modifying the rows in the table), or upserts (by passing in
a hash table of column names and values).
PARAMETERS
-SqlCommand <SqlCommand>
Required? true
Position? 1
Default value
Accept pipeline input? true (ByValue)
Accept wildcard characters? false
-TableMapping <String[]>
An optional list of custom table names to use for the result set, in order. By default these are Table,
Table1, Table2, etc.
Required? false
Position? 2
Default value @()
Accept pipeline input? false
Accept wildcard characters? false
-OutputAs <Object>
The type of data to return. It can be scalar (the first column of the first row of a result set), a non query
(an integer), datarow, pscustomobject, datatable, or a dataset.
Required? false
Position? 3
Default value PSCustomObject
Accept pipeline input? false
Accept wildcard characters? false
-InfoMessageVariable <Object>
An object of System.Collections.ArrayList which will be appended with all info message objects received while
running the command, in addition to formatted versions written to the verbose stream.
Required? false
Position? 4
Default value (New-Object System.Collections.ArrayList)
Accept pipeline input? false
Accept wildcard characters? false
-Alter [<SwitchParameter>]
Fil the schema, create a command builder, and add an Alter function to the first returned table.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-AlterCollectionSeparator <Object>
A character to join any non-string collections that are passed in when calling .Alter() on a table. For
example, for joining @() and ArrayList. Empty collections are converted to DBNull.
Required? false
Position? 5
Default value [Environment]::NewLine
Accept pipeline input? false
Accept wildcard characters? false
-RetryCount <Object>
Required? false
Position? 6
Default value
Accept pipeline input? false
Accept wildcard characters? false
-RetrySeconds <Object>
Required? false
Position? 7
Default value
Accept pipeline input? false
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
Pipe in an SqlCommand like from New-DbCommand.
OUTPUTS
See the OutputAs parameter.
NOTES
Be careful about supplying additional columns which are not in the destination table. These are ignored.
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>$serverInstance = ".\\SQL2016"
New-DbConnection $serverInstance master | New-DbCommand "If Object_Id('dbo.Moo', 'U') Is Not Null Drop Table
dbo.Moo; Create Table dbo.Moo (A Int Identity (1, 1) Primary Key, B Nvarchar(Max)); Dbcc Checkident('dbo.Moo',
Reseed, 100);" | Get-DbData -As NonQuery | Out-Null
$dbData = New-DbConnection $serverInstance master | New-DbCommand "Select * From dbo.Moo;" | Get-DbData -As
DataTable
$dbData.Alter(@{ B = "AAA" }) | Out-Null
$dbData.Alter(@{ B = @("AAA", "BBB", "CCC") }) | Out-Null
$dbData.Alter(@{ B = @(000, 001, 002) }) | Out-Null
$dbData.Alter(@{ B = @() }) | Out-Null
$dbData.Alter(@{ A = 100; B = "CCC" }) | Out-Null
$dbData.Alter(@{ A = 4; B = "DDD" }) | Out-Null
$dbData | Format-List
Results:
A : 100
B : CCC
A : 101
B : AAA
BBB
CCC
A : 102
B : 0
1
2
A : 103
B : DDD
This drops and creates a dummy table with an identity column seeding at 100. It then inserts a row, two array
(collection) rows, updates the first row, then attempts a fixed identity insert.
The result is four rows, with the collections concatenated, and the special identity of the last column discarded
(but the properly allocated identity value returned).
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>$serverInstance = ".\\SQL2016"
$infoMessage = New-Object System.Collections.ArrayList
New-DbConnection $serverInstance master | New-DbCommand "Print 'Moo';" | Get-DbData -As NonQuery
-InfoMessageVariable $infoMessage | Out-Null
$infoMessage
Results:
Source : .Net SqlClient Data Provider
Number : 0
State : 1
Class : 0
Server : .\\SQL2016
Message : Moo
Procedure :
LineNumber : 1
Stores objects from the message stream into a variable (which is overwritten).
RELATED LINKS