101  

For others who need to do this with just stock .net and PowerShell (no additional SQL tools installed) here is the function that I use:

对于其他需要使用net和PowerShell(不安装其他SQL工具)的人来说，这里是我使用的函数:

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

}

I have been using this so long I don't know who wrote which parts but this was distilled from other's examples but simplified to be clear and just what is needed without extra dependencies or features.

我已经用了这么长时间了，我不知道谁写了哪些部分，但这是从别人的例子中提炼出来的，简化为清晰明了，没有额外的依赖或特性。

I use and share this often enough that I have turned this into a script module on GitHub so that you can now go to your modules directory and execute git clone https://github.com/ChrisMagnuson/InvokeSQL and from that point forward invoke-sql will automatically be loaded when you go to use it (assuming your using powershell v3 or later).

我经常这样使用和分享,我把这变成了一个脚本模块在GitHub,你现在可以去您的模块目录并执行git克隆https://github.com/ChrisMagnuson/InvokeSQL和从那以后调用sql将自动加载当你去使用它(假设你使用powershell v3或更高版本)。

77  

You can use the Invoke-Sqlcmd cmdlet

您可以使用Invoke-Sqlcmd cmdlet

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"

http://technet.microsoft.com/en-us/library/cc281720.aspx

http://technet.microsoft.com/en-us/library/cc281720.aspx

20  

Here's an example I found on this blog.

这是我在这个博客上找到的一个例子。

$cn2 = new-object system.data.SqlClient.SQLConnection("Data Source=machine1;Integrated Security=SSPI;Initial Catalog=master");
$cmd = new-object system.data.sqlclient.sqlcommand("dbcc freeproccache", $cn2);
$cn2.Open();
if ($cmd.ExecuteNonQuery() -ne -1)
{
    echo "Failed";
}
$cn2.Close();

Presumably you could substitute a different TSQL statement where it says dbcc freeproccache.

假设您可以替换另一个TSQL语句，其中它说dbcc freeproccache。

15  

This function will return the results of a query as an array of powershell objects so you can use them in filters and access columns easily:

这个函数会将查询的结果作为powershell对象的数组返回，这样您就可以轻松地在过滤器和访问列中使用它们:

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
}

6  

If you want to do it on your local machine instead of in the context of SQL server then I would use the following. It is what we use at my company.

如果您希望在本地机器上而不是在SQL server上下文中执行，那么我将使用以下命令。这是我们在公司使用的。

$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

Just fill in the $ServerName, $DatabaseName and the $Query variables and you should be good to go.

只需填写$ServerName、$DatabaseName和$Query变量，就可以了。

I am not sure how we originally found this out, but there is something very similar here.

我不确定我们最初是怎么发现的，但是这里有一些非常相似的东西。

5  

There isn't a built-in "PowerShell" way of running a SQL query. If you have the SQL Server tools installed, you'll get an Invoke-SqlCmd cmdlet.

没有一种内置的“PowerShell”方式来运行SQL查询。如果安装了SQL Server工具，您将获得一个Invoke-SqlCmd cmdlet。

Because PowerShell is built on .NET, you can use the ADO.NET API to run your queries.

因为PowerShell构建在。net上，所以您可以使用ADO。NET API来运行查询。

2  

Invoke-Sqlcmd -Query "sp_who" -ServerInstance . -QueryTimeout 3