New-DbCommand.ps1
|
<#
.SYNOPSIS Create an SQL command. .DESCRIPTION Creates an SQL command safely. This combines: * A connection string or connection object. * A command. * Parameters specified as a hash table. * Query timeouts. * An existing transaction. .PARAMETER Connection Connection string or SqlConnection. .PARAMETER Command The command or stored procedure name to execute. .PARAMETER Parameters A hash table of parameters to use in this query. This is done safely without concatenating strings or using variable substitution. .PARAMETER QueryTimeout Integer for the number of seconds to wait before expiring the query. If unspecified the .NET default is 30 seconds. This normally holds Severity 10 and lower information. If a higher severity error occurs as part of the batch then prior output is included in the Exception instead. .PARAMETER FireInfoMessageEventOnUserErrors 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. .PARAMETER CommandType Text, StoredProcedure, or TableDirect. .PARAMETER Transaction An existing System.Data.SqlClient.Transaction object. .PARAMETER VarChar 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. .INPUTS Pipe in a connection string, or a System.Data.SqlClient.SqlConnection object. .OUTPUTS A System.Data.SqlClient.SqlCommand object. .EXAMPLE 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 $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. .NOTES #> function New-DbCommand { [CmdletBinding()] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] $Connection, # Connection string or System.Data.SqlClient.SqlConnection [Parameter(Mandatory = $true, Position = 0)] [Alias("Query")] $Command, [Parameter(Position = 1)] [Hashtable] $Parameters = @{}, [Parameter(Position = 2)] [System.Data.CommandType] $CommandType = "Text", [int] $CommandTimeout, [switch] $FireInfoMessageEventOnUserErrors, [System.Data.SqlClient.SqlTransaction] $Transaction, [switch] $VarChar ) begin { } process { # If we are passed a connection string instead of a connection, build the connection object if (!$Connection) { Write-Error "Needs a Connection" } elseif ($Connection -is [string]) { $Connection = New-Object System.Data.SqlClient.SqlConnection($Connection) } # If neither a connection or connection string were specified then no connection is attached $sqlCommand = New-Object System.Data.SqlClient.SqlCommand($Command, $Connection) $sqlCommand.CommandType = $CommandType if ($CommandTimeout) { $sqlCommand.CommandTimeout = $CommandTimeout } if ($Transaction) { $sqlCommand.Transaction = $Transaction } if ($FireInfoMessageEventOnUserErrors) { $sqlCommand.Connection.FireInfoMessageEventOnUserErrors = $FireInfoMessageEventOnUserErrors } foreach ($parameterName in $Parameters.Keys) { # It's not safe to call the shortcut constructor because of boxing issues $parameter = New-Object System.Data.SqlClient.SqlParameter $parameter.ParameterName = $parameterName $parameter.Value = $Parameters[$parameterName] if ($null -eq $Parameters[$parameterName]) { $parameter.Value = [DBNull]::Value } if ($VarChar -and $parameter.SqlDbType -eq "NVarChar") { $parameter.DbType = "AnsiString" } [void] $sqlCommand.Parameters.Add($parameter) } # Return the command $sqlCommand } end { } } |