Public/Send-SQLDataToExcel.ps1
|
function Send-SQLDataToExcel { [CmdletBinding(DefaultParameterSetName="none")] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidGlobalVars', '', Justification="Allowed to use DBSessions Global variable from GETSQL Module")] param ( [Parameter(ParameterSetName="SQLConnection", Mandatory=$true)] [Parameter(ParameterSetName="ODBCConnection", Mandatory=$true)] $Connection, [Parameter(ParameterSetName="ExistingSession", Mandatory=$true)] $Session, [Parameter(ParameterSetName="SQLConnection", Mandatory=$true)] [switch]$MsSqlServer, [Parameter(ParameterSetName="SQLConnection")] [String]$DataBase, [Parameter(ParameterSetName="SQLConnection", Mandatory=$true)] [Parameter(ParameterSetName="ODBCConnection", Mandatory=$true)] [Parameter(ParameterSetName="ExistingSession", Mandatory=$true)] [string]$SQL, [int]$QueryTimeout, [Parameter(ParameterSetName="Pre-FetchedData", Mandatory=$true)] [System.Data.DataTable]$DataTable, [switch]$Force ) #Import the parameters from Export-Excel, we will pass InputObject, and we have the common parameters so exclude those, #and re-write the [Parmameter] attribute on each one to avoid parameterSetName here competing with the settings in Export excel. #The down side of this that impossible parameter combinations won't be filtered out and need to be caught later. DynamicParam { $ParameterAttribute = "System.Management.Automation.ParameterAttribute" $RuntimeDefinedParam = "System.Management.Automation.RuntimeDefinedParameter" $paramDictionary = New-Object -TypeName System.Management.Automation.RuntimeDefinedParameterDictionary $attributeCollection = New-Object -TypeName System.Collections.ObjectModel.Collection[System.Attribute] $attributeCollection.Add((New-Object -TypeName $ParameterAttribute -Property @{ ParameterSetName = "__AllParameterSets" ;Mandatory = $false})) foreach ($P in (Get-Command -Name Export-Excel).Parameters.values.where({$_.name -notmatch 'Verbose|Debug|Action$|Variable$|Buffer$|TargetData$|InputObject$'})) { $paramDictionary.Add($p.Name, (New-Object -TypeName $RuntimeDefinedParam -ArgumentList $p.name, $p.ParameterType, $attributeCollection ) ) } return $paramDictionary } process { #region Dynamic params mean we can get passed parameter combination Export-Excel will reject, so throw here, rather than get data and then have Export-Excel error. if ($PSBoundParameters.Path -and $PSBoundParameters.ExcelPackage) { throw 'Parameter error: you cannot specify both a path and an Excel Package.' return } if ($PSBoundParameters.AutoFilter -and ($PSBoundParameters.TableName -or $PSBoundParameters.TableStyle)) { Write-Warning "Tables are automatically auto-filtered, -AutoFilter will be ignored" $null = $PSBoundParameters.Remove('AutoFilter') } #endregion #region if we were either given a session object or a connection string (& optionally -MsSqlServer) make sure we can connect try { #If we got -MsSqlServer, create a SQL connection, if we didn't but we got -Connection create an ODBC connection if ($MsSqlServer -and $Connection) { if ($Connection -notmatch '=') {$Connection = "server=$Connection;trusted_connection=true;timeout=60"} $Session = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $Connection if ($Session.State -ne 'Open') {$Session.Open()} if ($DataBase) {$Session.ChangeDatabase($DataBase) } } elseif ($Connection) { $Session = New-Object -TypeName System.Data.Odbc.OdbcConnection -ArgumentList $Connection ; $Session.ConnectionTimeout = 30 } } catch { Write-Warning "An Error occured trying to connect to $Connection, the error was $([Environment]::NewLine + $_.Exception.InnerException))" } if ($Session -is [String] -and $Global:DbSessions[$Session]) {$Session = $Global:DbSessions[$Session]} #endregion #region we may have been given a table, but if there is a db session to connect to, send the query if ($Session) { try { #If the session a SQL one make a SQL DataAdapter, otherwise make an ODBC one if ($Session.GetType().name -match "SqlConnection") { $dataAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter -ArgumentList ( New-Object -TypeName System.Data.SqlClient.SqlCommand -ArgumentList $SQL, $Session) } else { $dataAdapter = New-Object -TypeName System.Data.Odbc.OdbcDataAdapter -ArgumentList ( New-Object -TypeName System.Data.Odbc.OdbcCommand -ArgumentList $SQL, $Session ) } if ($QueryTimeout) {$dataAdapter.SelectCommand.CommandTimeout = $QueryTimeout} #Both adapter types output the same kind of table, create one and fill it from the adapter $DataTable = New-Object -TypeName System.Data.DataTable $rowCount = $dataAdapter.fill($dataTable) Write-Verbose -Message "Query returned $rowCount row(s)" } catch { Write-Warning "An Error occured trying to run the query, the error was $([Environment]::NewLine + $_.Exception.InnerException))" } } #endregion #region send the table to Excel #remove parameters which relate to querying SQL, leaving the ones used by Export-Excel 'Connection' , 'Database' , 'Session' , 'MsSqlServer' , 'SQL' , 'DataTable' , 'QueryTimeout' , 'Force' | ForEach-Object {$null = $PSBoundParameters.Remove($_) } #if force was specified export even if there are no rows. If there are no columns, the query failed and export "null" if forced if ($DataTable.Rows.Count) { Export-Excel @PSBoundParameters -InputObject $DataTable } elseif ($Force -and $DataTable.Columns.Count) { Write-Warning -Message "Zero rows returned, and -Force was specified, sending empty table to Excel." Export-Excel @PSBoundParameters -InputObject $DataTable } elseif ($Force) { Write-Warning -Message "-Force was specified but there is no data to send." Export-Excel @PSBoundParameters -InputObject $null } else {Write-Warning -Message 'There is no Data to insert, and -Force was not specified.' } #endregion #If we were passed a connection and opened a session, close that session. if ($Connection) {$Session.close() } } } |