PowerShell查詢sql server

標籤：style   blog   http   color   os   io   strong   ar   for   

function Invoke-SQL1：

function Invoke-SQL {    param(        [string] $dataSource = ".\SQLEXPRESS",        [string] $database = "MasterData",        [string] $sqlCommand = $(throw "Please specify a query.")      )    $connectionString = "Data Source=$dataSource; " +            "Integrated Security=SSPI; " +            "Initial Catalog=$database"    $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()    $dataSet.Tables}

function SQL2：

function sql($sqlText, $database = "master", $server = "."){    $connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database");    $cmd = new-object System.Data.SqlClient.SqlCommand($sqlText, $connection);    $connection.Open();    $reader = $cmd.ExecuteReader()    $results = @()    while ($reader.Read())    {        $row = @{}        for ($i = 0; $i -lt $reader.FieldCount; $i++)        {            $row[$reader.GetName($i)] = $reader.GetValue($i)        }        $results += new-object psobject -property $row                }    $connection.Close();    $results}

SQL Query3：

$ServerName = "_ServerName_"$DatabaseName = "_DatabaseName_"$Query = "SELECT * FROM Table WHERE Column = ‘‘"#Timeout parameters$QueryTimeout = 120$ConnectionTimeout = 30#Action of connecting to the Database and executing the query and returning results if there were any.$conn=New-Object System.Data.SqlClient.SQLConnection$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$DatabaseName,$ConnectionTimeout$conn.ConnectionString=$ConnectionString$conn.Open()$cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$conn)$cmd.CommandTimeout=$QueryTimeout$ds=New-Object system.Data.DataSet$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)[void]$da.fill($ds)$conn.Close()$ds.Tables

 

from：http://stackoverflow.com/questions/8423541/how-do-you-run-a-sql-server-query-from-powershell

PowerShell查詢sql server