< Back
New-DbCommand
Post
NAME New-DbCommand
SYNOPSIS
Create an SQL command.
SYNTAX
New-DbCommand -SqlConnection <SqlConnection> [-Command] <Object> [[-Parameters] <Hashtable>] [[-CommandType] {Text
| StoredProcedure | TableDirect}] [-CommandTimeout <Int32>] [-FireInfoMessageEventOnUserErrors] [-Transaction
<SqlTransaction>] [-VarChar] [<CommonParameters>]
DESCRIPTION
Creates an SQL command safely. This combines:
* A connection object.
* A command.
* Parameters specified as a hash table.
* Query timeouts.
* An existing transaction.
PARAMETERS
-SqlConnection <SqlConnection>
Required? true
Position? named
Default value
Accept pipeline input? true (ByValue)
Accept wildcard characters? false
-Command <Object>
The command or stored procedure name to execute.
Required? true
Position? 1
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Parameters <Hashtable>
A hash table of parameters to use in this query. This is done safely without concatenating strings or using
variable substitution.
Required? false
Position? 2
Default value @{}
Accept pipeline input? false
Accept wildcard characters? false
-CommandType
Text, StoredProcedure, or TableDirect.
Required? false
Position? 3
Default value Text
Accept pipeline input? false
Accept wildcard characters? false
-CommandTimeout <Int32>
Required? false
Position? named
Default value 0
Accept pipeline input? false
Accept wildcard characters? false
-FireInfoMessageEventOnUserErrors [<SwitchParameter>]
Messages of severity 10 and lower are output as informational messages, unless an exception occurs, in which
case they are bundled in as part of the exception text.
If this parameter is specified, exceptions up to and including severity 16 will be output as messages and not
cause processing to stop. On a higher exception, the previous messages will still be printed.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Transaction <SqlTransaction>
An existing System.Data.SqlClient.Transaction object.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-VarChar [<SwitchParameter>]
By default strings are passed as NvarChar parameters (because a .NET [string] is Unicode by default). This can
cause some extreme performance issues from implicit conversions (table scans will occur as VarChar columns are
converted up to NvarChar). If you know you are reading from VarChar records then this switch allows you to
force VarChar type parameters and improve performance.
Required? false
Position? named
Default value False
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 a connection string, or a System.Data.SqlClient.SqlConnection object.
OUTPUTS
A System.Data.SqlClient.SqlCommand object.
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>New-DbConnection .\\SQL2016 master | New-DbCommand "Select * From sys.databases Where name = @DatabaseName"
@{ DatabaseName = "master" } | Get-DbData
Prepare a connection, a command, and pull back the data.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>$serverInstance = ".\\SQL2016"
New-DbConnection $serverInstance master | New-DbCommand "Raiserror('Hi 1', 10, 1); Raiserror('Hi 2', 10, 1);" |
Get-DbData -NonQuery -Verbose
New-DbConnection $serverInstance master | New-DbCommand "Raiserror('Hi 1', 10, 1); Raiserror('Hi 2', 11, 1);" |
Get-DbData -NonQuery -Verbose
New-DbConnection $serverInstance master | New-DbCommand "Raiserror('Hi 1', 16, 1); Raiserror('Hi 2', 16, 1);"
-FireInfoMessageEventOnUserErrors | Get-DbData -NonQuery -Verbose
New-DbConnection $serverInstance master | New-DbCommand "Raiserror('Hi 1', 16, 1); Raiserror('Hi 2', 17, 1);"
-FireInfoMessageEventOnUserErrors | Get-DbData -NonQuery -Verbose
The first query prints some output. The second query doesn't print any output but it is instead bundled into an
exception.
The third query prints some output. The fourth query prints output and then triggers an exception.
RELATED LINKS
SYNOPSIS
Create an SQL command.
SYNTAX
New-DbCommand -SqlConnection <SqlConnection> [-Command] <Object> [[-Parameters] <Hashtable>] [[-CommandType] {Text
| StoredProcedure | TableDirect}] [-CommandTimeout <Int32>] [-FireInfoMessageEventOnUserErrors] [-Transaction
<SqlTransaction>] [-VarChar] [<CommonParameters>]
DESCRIPTION
Creates an SQL command safely. This combines:
* A connection object.
* A command.
* Parameters specified as a hash table.
* Query timeouts.
* An existing transaction.
PARAMETERS
-SqlConnection <SqlConnection>
Required? true
Position? named
Default value
Accept pipeline input? true (ByValue)
Accept wildcard characters? false
-Command <Object>
The command or stored procedure name to execute.
Required? true
Position? 1
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Parameters <Hashtable>
A hash table of parameters to use in this query. This is done safely without concatenating strings or using
variable substitution.
Required? false
Position? 2
Default value @{}
Accept pipeline input? false
Accept wildcard characters? false
-CommandType
Text, StoredProcedure, or TableDirect.
Required? false
Position? 3
Default value Text
Accept pipeline input? false
Accept wildcard characters? false
-CommandTimeout <Int32>
Required? false
Position? named
Default value 0
Accept pipeline input? false
Accept wildcard characters? false
-FireInfoMessageEventOnUserErrors [<SwitchParameter>]
Messages of severity 10 and lower are output as informational messages, unless an exception occurs, in which
case they are bundled in as part of the exception text.
If this parameter is specified, exceptions up to and including severity 16 will be output as messages and not
cause processing to stop. On a higher exception, the previous messages will still be printed.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Transaction <SqlTransaction>
An existing System.Data.SqlClient.Transaction object.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-VarChar [<SwitchParameter>]
By default strings are passed as NvarChar parameters (because a .NET [string] is Unicode by default). This can
cause some extreme performance issues from implicit conversions (table scans will occur as VarChar columns are
converted up to NvarChar). If you know you are reading from VarChar records then this switch allows you to
force VarChar type parameters and improve performance.
Required? false
Position? named
Default value False
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 a connection string, or a System.Data.SqlClient.SqlConnection object.
OUTPUTS
A System.Data.SqlClient.SqlCommand object.
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>New-DbConnection .\\SQL2016 master | New-DbCommand "Select * From sys.databases Where name = @DatabaseName"
@{ DatabaseName = "master" } | Get-DbData
Prepare a connection, a command, and pull back the data.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>$serverInstance = ".\\SQL2016"
New-DbConnection $serverInstance master | New-DbCommand "Raiserror('Hi 1', 10, 1); Raiserror('Hi 2', 10, 1);" |
Get-DbData -NonQuery -Verbose
New-DbConnection $serverInstance master | New-DbCommand "Raiserror('Hi 1', 10, 1); Raiserror('Hi 2', 11, 1);" |
Get-DbData -NonQuery -Verbose
New-DbConnection $serverInstance master | New-DbCommand "Raiserror('Hi 1', 16, 1); Raiserror('Hi 2', 16, 1);"
-FireInfoMessageEventOnUserErrors | Get-DbData -NonQuery -Verbose
New-DbConnection $serverInstance master | New-DbCommand "Raiserror('Hi 1', 16, 1); Raiserror('Hi 2', 17, 1);"
-FireInfoMessageEventOnUserErrors | Get-DbData -NonQuery -Verbose
The first query prints some output. The second query doesn't print any output but it is instead bundled into an
exception.
The third query prints some output. The fourth query prints output and then triggers an exception.
RELATED LINKS