Invoke-SQL.ps1
|
<#
.Synopsis Executes a SQL Statement on the database server .DESCRIPTION Will return an object model when records were retrieved .NOTES .PREREQUISITES #> function Invoke-SQL { [CmdLetBinding()] param( [string] $DatabaseServer = [net.dns]::gethostname(), [String] $DatabaseInstance = '', [string] $DatabaseName = 'Master', [string] $SQLCommand = $(throw 'Please specify a query.') ) if (!([string]::IsNullOrEmpty($DatabaseInstance))){ $DatabaseServer = "$($DatabaseServer)\$($DatabaseInstance)" } $connectionString = "Data Source=$DatabaseServer; Integrated Security=SSPI; Initial Catalog=$DatabaseName" write-Host -ForegroundColor Green "Invoke-SQL with this statement on database '$DatabaseName':" Write-Host -ForegroundColor Gray $SQLCommand $connection = new-object system.data.SqlClient.SQLConnection($connectionString) $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection) $connection.Open() $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command $dataset = New-Object System.Data.DataSet $adapter.Fill($dataSet) | Out-Null $connection.Close() $connection.Dispose() $dataSet.Tables } |