Yodel.psm1
|
#Requires -Version 5.1 Set-StrictMode -Version 'Latest' # Functions should use $moduleRoot as the relative root from which to find # things. A published module has its function appended to this file, while a # module in development has its functions in the Functions directory. $moduleRoot = $PSScriptRoot # Store each of your module's functions in its own file in the Functions # directory. On the build server, your module's functions will be appended to # this file, so only dot-source files that exist on the file system. This allows # developers to work on a module without having to build it first. Grab all the # functions that are in their own files. $functionsPath = Join-Path -Path $moduleRoot -ChildPath 'Functions\*.ps1' if( (Test-Path -Path $functionsPath) ) { foreach( $functionPath in (Get-Item $functionsPath) ) { . $functionPath.FullName } } function Connect-YDatabase { <# .SYNOPSIS Opens an ADO.NET connection to a database. .DESCRIPTION The `Connect-YDatabase` function opens an ADO.NET (i.e. pure .NET) connection to a database. Pass the connection string to the `ConnectionString` parameter. Pass the provider to use to connect to the `Provider` parameter. This parameter should be an instance of a `Data.Common.DbProviderFactory` object. The .NET framework ships with several: * SQL Server: `Connect-YDatabase -Provider ([Data.SqlClient.SqlClientFactory]::Instance)` * ODBC: `Connect-YDatabase -Provider ([Data.Odbc.OdbcFactory]::Instance)` * OLE: `Connect-YDatabase -Provider ([Data.OleDb.OleDbFactory]::Instance)` * Entity Framework: `Connect-YDatabase -Provider ([Data.EntityClient.EntityProviderFactory]::Instance)` * Oracle: `Connect-YDatabase -Provider ([Data.OracleClient.OracleClientFactory]::Instance)` The function uses each provider to create a connection object, sets that connection's connection string, open the connection, and then returns the connection. The `Connect-YDatabase` also has a simplified parameter set to open a connection to SQL Server. Pass the SQL Server name (e.g. `HOST\INSTANCE`) to the `SqlServerName` parameter, the database name to the `DatabaseName` parameter, and any other connection properties to the `ConnectionString` property. The function will create a connection to the SQL Server database using integrated authentication. To connect as a specific user, pass that user's credentials to the `Credential` parameter. ADO.NET requires that the credential's password be in read-only mode, so `Connect-YDatabase` will call `$Credential.Password.MakeReadOnly()`. Returns a `Data.Common.DbConnection` object, the base class for all ADO.NET connections. You are responsible for closing the connection: $conn = Connect-YDatabase -SqlServerName '.' -DatabaseName 'master' try { # run some queries } finally { # YOU MUST DO THIS! $conn.Close() } .EXAMPLE Connect-YDatabase -SqlServerName '.' -DatabaseName 'master' Demonstrates how to connect to Microsoft SQL Server using integrated authentiction. .EXAMPLE Connect-YDatabase -SqlServerName '.' -DatabaseName 'master' -Credential $credential Demonstrates how to connect to Microsoft SQL Server as a specific user. The `$credential` parameter must be `PSCredential` object. .EXAMPLE Connect-YDatabase -SqlServerName '.' -DatabaseName 'master' -ConnectionString 'Application Name=Yodel;Workstation ID=SomeComputerName' Demonstrates how to supply additional connection string properties when using the SQL Server parameter set. .EXAMPLE Connect-YDatabase -Provider ([Data.Odbc.OdbcFactory]::Instance) -ConnectionString 'some connection string' Demonstrates how to connect to a database using ODBC. .EXAMPLE Connect-YDatabase -Provider ([Data.OleDb.OleDbFactory]::Instance) -ConnectionString 'some connection string' Demonstrates how to connect to a database using OLE. .EXAMPLE Connect-YDatabase -Provider ([Data.EntityClient.EntityProviderFactory]::Instance) -ConnectionString 'some connection string' Demonstrates how to connect to a database using the Entity Framework provider. .EXAMPLE Connect-YDatabase -Provider ([[Data.OracleClient.OracleClientFactory]::Instance) -ConnectionString 'some connection string' Demonstrates how to connect to a database using Oracle. #> [CmdletBinding()] [OutputType([Data.Common.DbConnection])] param( [Parameter(Mandatory,ParameterSetName='SqlServer')] [String]$SqlServerName, [Parameter(Mandatory,ParameterSetName='SqlServer')] [String]$DatabaseName, [Parameter(ParameterSetName='SqlServer')] [pscredential]$Credential, [Parameter(Mandatory,ParameterSetName='Generic')] [Data.Common.DbProviderFactory]$Provider, # The connection string to use. [String]$ConnectionString, # The connection timeout. By default, uses the .NET default of 30 seconds. If it takes longer than this number of seconds to connect, the function will fail. # # Setting this property adds a `Connection Timeout` property to the connection string if you're connecting to a SQL Server database (i.e. using the `SqlServerName` parameter). If you're connecting via ODBC, the `ConnectionTimeout` property is set. In all other cases, this parameter is ignored. # # If you get an error that `ConnectionTimeout` is a read-only property, you'll need to pass the timeout as a property in your connection string. [int]$ConnectionTimeout ) Set-StrictMode -Version 'Latest' Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState if( $PSCmdlet.ParameterSetName -eq 'SqlServer' ) { $Provider = [Data.SqlClient.SqlClientFactory]::Instance } $connection = $Provider.CreateConnection() $connStringBuilder = $Provider.CreateConnectionStringBuilder() if( $ConnectionString ) { # There's some weird PowerShell magic going on when setting the ConnectionString property, so directly call the setter function. $connStringBuilder.set_ConnectionString($ConnectionString) } if( $PSCmdlet.ParameterSetName -eq 'SqlServer' ) { $connStringBuilder['Server'] = $SqlServerName $connStringBuilder['Database'] = $DatabaseName if( $Credential ) { $Credential.Password.MakeReadOnly() $sqlCredential = [Data.SqlClient.SqlCredential]::new($Credential.UserName, $Credential.Password) $connection.Credential = $sqlCredential } else { $connStringBuilder['Integrated Security'] = 'True' } if( $ConnectionTimeout ) { $connStringBuilder['Connection Timeout'] = $ConnectionTimeout } $ConnectionString = $connStringBuilder.ToString() } else { if( $ConnectionTimeout ) { $connection.ConnectionTimeout = $ConnectionTimeout } } $connection.ConnectionString = $ConnectionString $connection.Open() return $connection } function Invoke-YDbCommand { <# .SYNOPSIS Uses ADO.NET to execute a database command. .DESCRIPTION `Invoke-YDbCommand` executes a command against a database and returns a generic object for each row in the result set. Each object has a property for each column. If a column doesn't have a name, a generic `ColumnX` name is assigned, where `X` starts at 0 and increases by one for each nameless column. Pass the connection to the database to the `Connection` parameter. (Use the `Connect-YDatabase` function to create a connection.) Pass the command to run to the `Text` parameter. You may also pipe commands to `Invoke-YDbCommand`. If your command should be part of a transaction, pass the transaction to the `Transaction` parameter. If your command returns a single value, use the `-AsScalar` switch. If your command returns no results, use the `-NonQuery` switch. If your command affects any rows, the number of rows affected will be returned. To run a parameterized command, use `@name` parameters in your command and pass the values of those parameters in a hashtable to the `Parameter` parameter, e.g. `@{ '@name' = 'the_name' }`. To execute a stored procedure, set the `Text` parameter to the name of the stored procedure, set the `Type` parameter to `[Data.CommandType]::StoredProcedure`, and pass the procedure's parameters to the `Parameter` parameter (a hashtable of parameter names and values). Commands will time out after 30 seconds (the default .NET timeout). If you have a query that runs longer, pass the number of seconds to wait to the `Timeout` parameter. Command timings are output to the verbose stream, including the text of the command. Command parameters are not output. If you want to suppress sensitive commands from being output, set the `Verbose` parameter to `$false`, e.g. `-Verbose:$false`. Failed queries do not cause a terminating error. If you want your script to stop if your query fails, set the `ErrorAction` parameter to `Stop`, e.g. `-ErrorAction Stop`. .EXAMPLE Invoke-YDbCommand -Connection $conn -Text 'select * from MyTable' Demonstrates how to select rows from a table. .EXAMPLE 'select 1','select 2' | Invoke-YDbCommand -Connection $conn Demonstrates that you can pipe commands to `Invoke-YDbCommand`. .EXAMPLE Invoke-YDbCommand -Connection $conn -Text 'select count(*) from MyTable' -AsScalar Demonstrates how to return a scalar value. If the command returns multiple rows/columns, returns the first row's first column's value. .EXAMPLE $rowsDeleted = Invoke-YDbCommand -Connection $conn -Text 'delete from dbo.Example' -NonQuery Demonstrates how to execute a command that doesn't return a value. If your command updates/deletes rows, the number of rows affected is returned. .EXAMPLE Invoke-YDbCommand -Connection $conn -Text 'insert into MyTable (Two,Three) values @Column2, @Column3' -Parameter @{ '@Column2' = 'Value2'; '@Column3' = 'Value3' } -NonQuery Demonstrates how to use parameterized queries. .EXAMPLE Invoke-YDbCommand -Connection $conn -Text 'sp_addrolemember -CommandType [Data.CommandType]::StoredProcedure -Parameter @{ '@rolename = 'db_owner'; '@membername' = 'myuser'; } Demonstrates how to execute a stored procedure, including how to pass its parameters using the `Parameter` parameter. .EXAMPLE Invoke-YDbCommand -Connection $conn -Text 'create login [yodeltest] with password ''P@$$w0rd''' -Verbose:$false Demonstrates how to prevent command timings for sensitive queries from being written to the verbose stream. .EXAMPLE Invoke-YDbCommand -Connection $conn -Text 'select * from a_really_involved_join_that_takes_a_long_time' -Timeout 120 Demonstrates how to set the command timeout for commands that take longer than .NET's default timeout (30 seconds). .EXAMPLE Invoke-YDbCommand -Connection $conn -Text 'create table my_table (id int)' -Transaction $transaction Demonstrates that you can make the command part of a transaction by passing the transaction to the `Transaction` parameter. #> [CmdletBinding(DefaultParameterSetName='ExecuteReader')] param( [Parameter(Mandatory,Position=0)] # The connection to use. [Data.Common.DbConnection]$Connection, [Parameter(Mandatory,Position=1,ValueFromPipeline)] # The command to run/execute. [String]$Text, # The type of command being run. The default is `Text` for a SQL query. [Data.CommandType]$Type = [Data.CommandType]::Text, # The time (in seconds) to wait for a command to execute. The default is the .NET default, which is 30 seconds. [int]$Timeout, [Parameter(Position=2)] # Any parameters used in the command. [hashtable]$Parameter, [Parameter(Mandatory,ParameterSetName='ExecuteScalar')] # Return the result as a single value instead of a row. If the command returns multiple rows/columns, the value of the first row's first column is returned. [switch]$AsScalar, [Parameter(Mandatory,ParameterSetName='ExecuteNonQuery')] # Executes a command that doesn't return any records. For updates/deletes, the number of rows affected will be returned unless the NOCOUNT options is used. [switch]$NonQuery, # Any transaction the command should be part of. [Data.Common.DbTransaction]$Transaction ) process { Set-StrictMode -Version 'Latest' Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState $cmd = $Connection.CreateCommand() $cmd.CommandText = $Text $cmd.CommandTimeout = $Timeout $cmd.CommandType = $Type if( $Transaction ) { $cmd.Transaction = $Transaction } if( $Parameter ) { foreach( $name in $Parameter.Keys ) { $value = $Parameter[$name] if( -not $name.StartsWith( '@' ) ) { $name = '@{0}' -f $name } [void]$cmd.Parameters.AddWithValue( $name, $value ) } } $stopwatch = [Diagnostics.Stopwatch]::StartNew() try { if( $pscmdlet.ParameterSetName -like 'ExecuteNonQuery*' ) { $rowsAffected = $cmd.ExecuteNonQuery() if( $rowsAffected -ge 0 ) { $rowsAffected } } elseif( $pscmdlet.ParameterSetName -like 'ExecuteScalar*' ) { $cmd.ExecuteScalar() } else { $cmdReader = $cmd.ExecuteReader() try { if( $cmdReader.HasRows ) { while( $cmdReader.Read() ) { $row = @{ } for ($i= 0; $i -lt $cmdReader.FieldCount; $i++) { $name = $cmdReader.GetName( $i ) if( -not $name ) { $name = 'Column{0}' -f $i } $value = $cmdReader.GetValue($i) if( $cmdReader.IsDBNull($i) ) { $value = $null } $row[$name] = $value } New-Object 'PsObject' -Property $row } } } finally { $cmdReader.Close() } } } catch { # SQL Server exceptions can be brutally nested. $ex = $_.Exception while( $ex.InnerException ) { $ex = $ex.InnerException } $errorMsg = '{0}{1}{2}' -f $_.Exception.Message,[Environment]::NewLine,$Text Write-Error -Message $errorMsg -Exception $ex -ErrorAction $ErrorActionPreference } finally { $cmd.Dispose() $stopwatch.Stop() # Only calculate and output timings if verbose output is enabled. We don't even call Write-Verbose because # some queries could have sensitive information in them, and in order to prevent them from being visible, the # user will add `-Verbose:$false` when calling this function. I'm being extra cautious here so there is no way # for someone to intercept sensitive queries. if( (Write-Verbose 'Active' 4>&1) ) { $duration = $stopwatch.Elapsed if( $duration.TotalHours -ge 1 ) { $durationDesc = '{0,2}h {1,3}m ' -f [int]$duration.TotalHours,$duration.Minutes } elseif( $duration.TotalMinutes -ge 1 ) { $durationDesc = '{0,2}m {1,3}s ' -f [int]$duration.TotalMinutes,$duration.Seconds } elseif( $duration.TotalSeconds -ge 1 ) { $durationDesc = '{0,2}s {1,3}ms' -f [int]$duration.TotalSeconds,$duration.Milliseconds } else { $durationDesc = '{0,7}ms' -f [int]$duration.TotalMilliseconds } $lines = $Text -split "\r?\n" Write-Verbose -Message ('{0} {1}' -f $durationDesc, ($lines | Select-Object -First 1)) foreach( $line in ($lines | Select-Object -Skip 1)) { Write-Verbose -Message ('{0} {1}' -f (' ' * $durationDesc.Length), $line) } } } } } function Invoke-YSqlServerCommand { <# .SYNOPSIS Uses ADO.NET to execute a query in a SQL Server database. .DESCRIPTION `Invoke-YSqlServerCommand` executes a SQL query against a SQL Server database. The function opens a connection to SQL Server, executes the query, then closes the connection. Pass the name of the SQL Server (hostname and instance name) to the `SqlServerName` parameter. Pass the database name to the `DatabaseName` parameter. By default, the query is run as the current user. To run as a custom user, pass the user's credentials to the `Credential` parameter. Pass the query to run to the `Text` parameter. You may also pipe queries to `Invoke-YSqlServerCommand`. Piped queries are all run using the same connection. The function returns a generic object for each row in the result set. Each object has a property for each column. If a column doesn't have a name, a generic `ColumnX` name is assigned, where `X` starts at 0 and increases by one for each nameless column. If your query returns a single value, use the `-AsScalar` switch. If your query returns no results, use the `-NonQuery` switch. If your query affects any rows, the number of rows affected will be returned. To run a parameterized query, use `@name` parameters in your query and pass the values of those parameters in a hashtable to the `Parameter` parameter, e.g. `@{ '@name' = 'the_name' }`. To execute a stored procedure, set the `Text` parameter to the name of the stored procedure, set the `Type` parameter to `[Data.CommandType]::StoredProcedure`, and pass the procedure's parameters to the `Parameter` parameter (a hashtable of parameter names and values). Queries will time out after 30 seconds (the default .NET timeout). If you have a query that runs longer, pass the number of seconds to wait to the `Timeout` parameter. Query timings are output to the verbose stream, including the text of the query. If you want to suppress sensitive queries from being output, set the `Verbose` parameter to `$false`, e.g. `-Verbose:$false`. The `Invoke-YSqlServerCommand` function constructs a connection string for you based on the values of the `SqlServerName` and `DatabaseName` parameters. If you have custom properties you'd like added to the connection string, pass them to the `ConnectionString` parameter. Failed queries do not cause a terminating error. If you want your script to stop if your query fails, set the `ErrorAction` parameter to `Stop`, e.g. `-ErrorAction Stop`. .EXAMPLE Invoke-YSqlServerCommand -SqlServerName '.' -DatabaseName 'master' -Text 'select * from MyTable' Demonstrates how to select rows from a table. .EXAMPLE 'select 1','select 2' | Invoke-YSqlServerCommand -SqlServerName '.' -DatabaseName 'master' Demonstrates that you can pipe commands to `Invoke-YSqlServerCommand`. All queries piped to `Invoke-YSqlServerCommand` are run using the same connection. .EXAMPLE Invoke-YSqlServerCommand -SqlServerName '.' -DatabaseName 'master' -Text 'select count(*) from MyTable' -AsScalar Demonstrates how to return a scalar value. If the command returns multiple rows/columns, returns the first row's first column's value. .EXAMPLE $rowsDeleted = Invoke-YSqlServerCommand -SqlServerName '.' -DatabaseName 'master' -Text 'delete from dbo.Example' -NonQuery Demonstrates how to execute a command that doesn't return a value. If your command updates/deletes rows, the number of rows affected is returned. .EXAMPLE Invoke-YSqlServerCommand -SqlServerName '.' -DatabaseName 'master' -Text 'insert into MyTable (Two,Three) values @Column2, @Column3' -Parameter @{ '@Column2' = 'Value2'; '@Column3' = 'Value3' } -NonQuery Demonstrates how to use parameterized queries. .EXAMPLE Invoke-YSqlServerCommand -SqlServerName '.' -DatabaseName 'master' -Text 'sp_addrolemember' -CommandType [Data.CommandType]::StoredProcedure -Parameter @{ '@rolename' = 'db_owner'; '@membername' = 'myuser'; } Demonstrates how to execute a stored procedure, including how to pass its parameters using the `Parameter` parameter. .EXAMPLE Invoke-YSqlServerCommand -SqlServerName '.' -DatabaseName 'master' -Text 'create login [yodeltest] with password ''P@$$w0rd''' -Verbose:$false Demonstrates how to prevent command timings for sensitive queries from being written to the verbose stream. .EXAMPLE Invoke-YSqlServerCommand -SqlServerName '.' -DatabaseName 'master' -Text 'select * from a_really_involved_join_that_takes_a_long_time' -Timeout 120 Demonstrates how to set the command timeout for commands that take longer than .NET's default timeout (30 seconds). #> [CmdletBinding(DefaultParameterSetName='ExecuteReader')] param( [Parameter(Mandatory,Position=0)] # The SQL Server instance to connect to. [String]$SqlServerName, [Parameter(Mandatory,Position=1)] # The database to connect to. [String]$DatabaseName, [ValidateNotNullOrEmpty()] [pscredential]$Credential, # The connection string to use. [String]$ConnectionString, [Parameter(Mandatory,Position=2,ValueFromPipeline)] # The command to run/execute. [String]$Text, # Any parameters used in the command. [hashtable]$Parameter, [Parameter(Mandatory,ParameterSetName='ExecuteScalar')] # Return the result as a single value instead of a row. If the command returns multiple rows/columns, the value of the first row's first column is returned. [switch]$AsScalar, [Parameter(Mandatory,ParameterSetName='ExecuteNonQuery')] # Executes a command that doesn't return any records. For updates/deletes, the number of rows affected will be returned unless the NOCOUNT option is used. [switch]$NonQuery, # The time (in seconds) to wait for a command to execute. The default is .NET's default timeout, which is 30 seconds. [int]$Timeout, # The type of command being run. The default is Text, or a plain query. [Data.CommandType]$Type = [Data.CommandType]::Text ) begin { Set-StrictMode -Version 'Latest' Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState $optionalParams = @{} if( $Credential ) { $optionalParams['Credential'] = $Credential } if( $ConnectionString ) { $optionalParams['ConnectionString'] = $ConnectionString } $connection = Connect-YDatabase -SqlServerName $SqlServerName -DatabaseName $DatabaseName @optionalParams $optionalParams = @{} if( $AsScalar ) { $optionalParams['AsScalar'] = $true } if( $NonQuery ) { $optionalParams['NonQuery'] = $true } if( $Timeout ) { $optionalParams['Timeout'] = $Timeout } if( $Type ) { $optionalParams['Type'] = $Type } if( $Parameter ) { $optionalParams['Parameter'] = $Parameter } } process { $cmdFailed = $true try { Invoke-YDbCommand -Connection $Connection -Text $Text @optionalParams $cmdFailed = $false } finally { # Terminating errors stop the pipeline. if( $cmdFailed ) { $connection.Close() } } } end { $connection.Close() } } # Copyright 2012 Aaron Jensen # # Licensed under the Apache License, Version 2.0 (the "License"); # you may not use this file except in compliance with the License. # You may obtain a copy of the License at # # http://www.apache.org/licenses/LICENSE-2.0 # # Unless required by applicable law or agreed to in writing, software # distributed under the License is distributed on an "AS IS" BASIS, # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. # See the License for the specific language governing permissions and # limitations under the License. function Use-CallerPreference { <# .SYNOPSIS Sets the PowerShell preference variables in a module's function based on the callers preferences. .DESCRIPTION Script module functions do not automatically inherit their caller's variables, including preferences set by common parameters. This means if you call a script with switches like `-Verbose` or `-WhatIf`, those that parameter don't get passed into any function that belongs to a module. When used in a module function, `Use-CallerPreference` will grab the value of these common parameters used by the function's caller: * ErrorAction * Debug * Confirm * InformationAction * Verbose * WarningAction * WhatIf This function should be used in a module's function to grab the caller's preference variables so the caller doesn't have to explicitly pass common parameters to the module function. This function is adapted from the [`Get-CallerPreference` function written by David Wyatt](https://gallery.technet.microsoft.com/scriptcenter/Inherit-Preference-82343b9d). There is currently a [bug in PowerShell](https://connect.microsoft.com/PowerShell/Feedback/Details/763621) that causes an error when `ErrorAction` is implicitly set to `Ignore`. If you use this function, you'll need to add explicit `-ErrorAction $ErrorActionPreference` to every function/cmdlet call in your function. Please vote up this issue so it can get fixed. .LINK about_Preference_Variables .LINK about_CommonParameters .LINK https://gallery.technet.microsoft.com/scriptcenter/Inherit-Preference-82343b9d .LINK http://powershell.org/wp/2014/01/13/getting-your-script-module-functions-to-inherit-preference-variables-from-the-caller/ .EXAMPLE Use-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState Demonstrates how to set the caller's common parameter preference variables in a module function. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] #[Management.Automation.PSScriptCmdlet] # The module function's `$PSCmdlet` object. Requires the function be decorated with the `[CmdletBinding()]` attribute. $Cmdlet, [Parameter(Mandatory = $true)] [Management.Automation.SessionState] # The module function's `$ExecutionContext.SessionState` object. Requires the function be decorated with the `[CmdletBinding()]` attribute. # # Used to set variables in its callers' scope, even if that caller is in a different script module. $SessionState ) Set-StrictMode -Version 'Latest' # List of preference variables taken from the about_Preference_Variables and their common parameter name (taken from about_CommonParameters). $commonPreferences = @{ 'ErrorActionPreference' = 'ErrorAction'; 'DebugPreference' = 'Debug'; 'ConfirmPreference' = 'Confirm'; 'InformationPreference' = 'InformationAction'; 'VerbosePreference' = 'Verbose'; 'WarningPreference' = 'WarningAction'; 'WhatIfPreference' = 'WhatIf'; } foreach( $prefName in $commonPreferences.Keys ) { $parameterName = $commonPreferences[$prefName] # Don't do anything if the parameter was passed in. if( $Cmdlet.MyInvocation.BoundParameters.ContainsKey($parameterName) ) { continue } $variable = $Cmdlet.SessionState.PSVariable.Get($prefName) # Don't do anything if caller didn't use a common parameter. if( -not $variable ) { continue } if( $SessionState -eq $ExecutionContext.SessionState ) { Set-Variable -Scope 1 -Name $variable.Name -Value $variable.Value -Force -Confirm:$false -WhatIf:$false } else { $SessionState.PSVariable.Set($variable.Name, $variable.Value) } } } |