Select-Sql.ps1
|
function Select-SQL { <# .Synopsis Select SQL data .Description Select data from a SQL databsae .Example Select-Sql -FromTable ATable -Property Name, Day, Month, Year -Where "Year = 2005" -ConnectionSetting SqlAzureConnectionString .Example Select-Sql -FromTable INFORMATION_SCHEMA.TABLES -ConnectionSetting SqlAzureConnectionString -Property Table_Name -verbose .Example Select-Sql -FromTable INFORMATION_SCHEMA.TABLES -ConnectionSetting "Data Source=$env:ComputerName;Initial Catalog=Master;Integrated Security=SSPI;" -Property Table_Name -verbose .Example Select-Sql " SELECT sys.objects.name, SUM(row_count) AS 'Row Count', SUM(reserved_page_count) * 8.0 / 1024 AS 'Table Size (MB)' FROM sys.dm_db_partition_stats, sys.objects WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id GROUP BY sys.objects.name ORDER BY [Table Size (MB)] DESC " .Link Add-SqlTable .Link Update-SQL #> [CmdletBinding(DefaultParameterSetName='SQLQuery')] [OutputType([PSObject], [Hashtable], [Data.DataRow])] param( # The table containing SQL results [Parameter(Mandatory=$true,Position=0,ValueFromPipelineByPropertyName=$true,ParameterSetName='SQLQuery')] [Alias('SQL')] [string]$Query, # The path to a SQL file [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true,ParameterSetName='SQLFile')] [Alias('Fullname')] [string]$SqlFile, # The table containing SQL results [Parameter(Mandatory=$true,Position=0,ValueFromPipelineByPropertyName=$true,ParameterSetName='SimpleSQL')] [Alias('Table','From', 'TableName')] [string]$FromTable, # If set, will only return unique values. This corresponds to the DISTINCT SQL qualifier. [Parameter(ValueFromPipelineByPropertyName=$true,ParameterSetName='SimpleSQL')] [Alias('Unique')] [Switch]$Distinct, # The properties to pull from SQL. If not set, all properties (*) will be returned [Parameter(ValueFromPipelineByPropertyName=$true,ParameterSetName='SimpleSQL')] [string[]]$Property, # The sort order of the returned objects [Parameter(ValueFromPipelineByPropertyName=$true,ParameterSetName='SimpleSQL')] [Alias('First')] [Uint32]$Top, # The sort order of the returned objects [Parameter(ValueFromPipelineByPropertyName=$true,ParameterSetName='SimpleSQL')] [Alias('Sort')] [string[]]$OrderBy, # If set, sorted items will be returned in descending order. By default, if items are sorted, they will be in ascending order. [Parameter(ValueFromPipelineByPropertyName=$true,ParameterSetName='SimpleSQL')] [Switch]$Descending, # The where clause. [Parameter(ValueFromPipelineByPropertyName=$true,ParameterSetName='SimpleSQL')] [string]$Where, # A connection string or setting. [Alias('ConnectionString', 'ConnectionSetting')] [string]$ConnectionStringOrSetting, # The name of the SQL server. This is used with a database name to craft a connection string to SQL server [string] $Server, # The database on a SQL server. This is used with the server name to craft a connection string to SQL server [string] $Database, # If set, will output the SQL [Switch] $OutputSql, # If set, will use SQL server compact edition [Switch] $UseSQLCompact, # The path to SQL Compact. If not provided, SQL compact will be loaded from the GAC [string] $SqlCompactPath, # If set, will use SQL lite [Alias('UseSqlLite')] [switch] $UseSQLite, # The path to SQLite. If not provided, SQLite will be loaded from Program Files [Alias('SqlLitePath')] [string] $SqlitePath, # If set, will use MySql to connect to the database [Switch] $UseMySql, # The path to MySql's .NET connector. If not provided, MySql will be loaded from Program Files [string] $MySqlPath, # The path to a SQL compact or SQL lite database [Alias('DBPath')] [string] $DatabasePath, # The way the data will be outputted. [ValidateSet("Hashtable", "Datatable", "DataSet", "PSObject")] [string] $AsA = "PSObject", # If set, the select statement will be run as a dirty read. # In SQL Server, this will be With (nolock). # In MYSql, this will change the session options for the transaction to enable a dirty read. [Switch] $Dirty ) begin { if ($PSBoundParameters.ConnectionStringOrSetting) { if ($ConnectionStringOrSetting -notlike "*;*") { $ConnectionString = Get-SecureSetting -Name $ConnectionStringOrSetting -ValueOnly } else { $ConnectionString = $ConnectionStringOrSetting } $script:CachedConnectionString = $ConnectionString } elseif ($psBoundParameters.Server -and $psBoundParameters.Database) { $ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True;" $script:CachedConnectionString = $ConnectionString } elseif ($script:CachedConnectionString){ $ConnectionString = $script:CachedConnectionString } else { $ConnectionString = "" } if (-not $ConnectionString -and -not ($UseSQLite -or $UseSQLCompact)) { throw "No Connection String" return } if (-not $OutputSQL) { if ($UseSQLCompact) { if (-not ('Data.SqlServerCE.SqlCeConnection' -as [type])) { if ($SqlCompactPath) { $resolvedCompactPath = $ExecutionContext.SessionState.Path.GetResolvedPSPathFromPSPath($SqlCompactPath) $asm = [reflection.assembly]::LoadFrom($resolvedCompactPath) } else { $asm = [reflection.assembly]::LoadWithPartialName("System.Data.SqlServerCe") } $null = $asm } $resolvedDatabasePath = $ExecutionContext.SessionState.Path.GetResolvedPSPathFromPSPath($DatabasePath) $sqlConnection = New-Object Data.SqlServerCE.SqlCeConnection "Data Source=$resolvedDatabasePath" $sqlConnection.Open() } elseif ($UseSqlite) { if (-not ('Data.Sqlite.SqliteConnection' -as [type])) { if ($sqlitePath) { $resolvedLitePath = $ExecutionContext.SessionState.Path.GetResolvedPSPathFromPSPath($sqlitePath) $asm = [reflection.assembly]::LoadFrom($resolvedLitePath) } else { $asm = [Reflection.Assembly]::LoadFrom("$env:ProgramFiles\System.Data.SQLite\2010\bin\System.Data.SQLite.dll") } $null = $asm } $resolvedDbPath = $ExecutionContext.SessionState.Path.GetResolvedPSPathFromPSPath($DatabasePath) $sqlConnection = New-Object Data.Sqlite.SqliteConnection "Data Source=$resolvedDbPath" $sqlConnection.Open() } elseif ($useMySql) { if (-not ('MySql.Data.MySqlClient.MySqlConnection' -as [type])) { if (-not $mySqlPath) { $programDir = if (${env:ProgramFiles(x86)}) { ${env:ProgramFiles(x86)} } else { ${env:ProgramFiles} } $mySqlPath = Get-ChildItem "$programDir\MySQL\Connector NET 6.7.4\Assemblies\"| Where-Object { $_.Name -like "*v*" } | Sort-Object { $_.Name.Replace("v", "") -as [Version] } -Descending | Select-object -First 1 | Get-ChildItem -filter "MySql.Data.dll" | Select-Object -ExpandProperty Fullname } $asm = [Reflection.Assembly]::LoadFrom($MySqlPath) $null = $asm } $sqlConnection = New-Object MySql.Data.MySqlClient.MySqlConnection "$ConnectionString" $sqlConnection.Open() } else { $sqlConnection = New-Object Data.SqlClient.SqlConnection "$connectionString" $sqlConnection.Open() } } } process { $dataSet = $null if ($PSCmdlet.ParameterSetName -eq 'SimpleSQL') { if (-not $Property) { $property = "*" } if ($Property -eq '*') { $propString = '*' } else { if ($Property -like "*(*)*") { $propString = "$($Property -join ',')" } else { $propString = "`"$($Property -join '","')`"" } } # Very minor SQL injection prevention. If this is your last line of defense, you're in trouble, but using this will keep you out of some trouble. if ($where.IndexOfAny(";$([Environment]::NewLine)`0`b`t".ToCharArray()) -ne -1) { Write-Error "The Where Statement doesn't look safe" return } $sqlStatement = "SELECT $(if ($Top) { "TOP $Top" } ) $(if ($Distinct) { 'DISTINCT ' }) $propString FROM $FromTable $(if ($Where) { "WHERE $where"}) $(if ($OrderBy) { "ORDER BY $($orderBy -join ',') $(if ($Descending) { 'DESC'})"})".TrimEnd("\").TrimEnd("/") Write-Verbose "$sqlStatement" } elseif ($PSCmdlet.ParameterSetName -eq 'SQLQuery') { $sqlStatement = $Query } elseif ($PSCmdlet.ParameterSetName -eq 'SQLFile') { $resolvedPath = $ExecutionContext.SessionState.Path.GetResolvedPSPathFromPSPath($SqlFile) if (-not $resolvedPath) { return } $sqlStatement = [IO.File]::ReadAllText("$resolvedPath") } if ($Dirty) { if ($UseMySql) { $sqlStatement = " SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; $sqlStatement ; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; " } elseif (-not ($UseSQLCompact -or $UseSQLite)) { $sqlStatement += " WITH (nolock)" } } $dataset = $null if ($OutputSql) { $sqlStatement } else { if ($UseSQLCompact) { $sqlAdapter= New-Object "Data.SqlServerCE.SqlCeDataAdapter" ($sqlStatement, $sqlConnection) $sqlAdapter.SelectCommand.CommandTimeout = 0 $dataSet = New-Object Data.DataSet $rowCount = $sqlAdapter.Fill($dataSet) } elseif ($UseSQLite) { $sqlAdapter= New-Object "Data.SQLite.SQLiteDataAdapter" ($sqlStatement, $sqlConnection) $sqlAdapter.SelectCommand.CommandTimeout = 0 $dataSet = New-Object Data.DataSet $rowCount = $sqlAdapter.Fill($dataSet) } elseif ($UseMySql) { $sqlAdapter= New-Object "MySql.Data.MySqlClient.MySqlDataAdapter" ($sqlStatement, $sqlConnection) $sqlAdapter.SelectCommand.CommandTimeout = 0 $dataSet = New-Object Data.DataSet $rowCount = $sqlAdapter.Fill($dataSet) } else { $sqlAdapter= New-Object "Data.SqlClient.SqlDataAdapter" ($sqlStatement, $sqlConnection) $sqlAdapter.SelectCommand.CommandTimeout = 0 $dataSet = New-Object Data.DataSet $rowCount = $sqlAdapter.Fill($dataSet) } } if ($dataSet) { if ($AsA -eq 'DataSet') { $dataSet } elseif ($AsA -eq 'DataTable') { foreach ($t in $dataSet.Tables) { ,$t } } elseif ($AsA -eq 'PSObject') { foreach ($t in $dataSet.Tables) { foreach ($r in $t.Rows) { if ($r.pstypename) { $r.pstypenames.clear() foreach ($tn in ($r.pstypename -split "\|")) { if ($tn) { $r.pstypenames.add($tn) } } } $null = $r.psobject.properties.Remove("pstypename") $r } } } elseif ($AsA -eq 'Hashtable') { $avoidProperties = @{} foreach ($pName in 'RowError', 'RowState', 'Table', 'ItemArray', 'HasErrors') { $avoidProperties[$pName] = $true } foreach ($t in $dataSet.Tables) { foreach ($r in $t.Rows) { $out = @{} foreach ($prop in $r.psobject.Properties) { if ($avoidProperties[$prop.Name]) { continue } $out[$prop.Name] = $prop.Value } $out } } } } } end { if ($sqlConnection) { $sqlConnection.Close() $sqlConnection.Dispose() } } } |