powershell script query mssql windows authentication
function global:SelectAllUsers()
{
Read-Query -ConnectionString 'Server=localhost;Database=Ulysses;UID=EMEA\XJ193;PWD=somepassword;Integrated Security=true;' `
-Query "SELECT * FROM Users" `
-Action {
echo "I can take an action here"
}
}
function Read-Query
{
param (
[Parameter(Mandatory=$true)]
[string]$ConnectionString,
[Parameter(Mandatory=$true)]
[string]$Query,
[Parameter(Mandatory=$true)]
[scriptblock]$Action
)
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ConnectionString
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection
$reader = $SqlCmd.ExecuteReader()
while ($reader.Read())
{
$x = $null
$x = @{}
for ($i = 0; $i -lt $reader.FieldCount; ++$i)
{
$x.add($reader.GetName($i), $reader[$i])
}
Invoke-Command -ScriptBlock $action -ArgumentList $x
}
$SqlConnection.Close()
}
SelectAllUsers